Hi Skip, Skip Montanaro wrote:
Andrew> This is really a general SQL problem rather than anything to do Andrew> with Zope/ZSQL.
So ZSQL methods don't provide any special syntactic sugar other than <dtml-sqlvar>?
Andrew> Then do a select nextval('whatever'); to get the next id which Andrew> can then be used for the inserts.
Thanks, I'll check it out. This seems (naively) like it would be a race to me though. Let's say I do this:
insert into phone ( ... ) values ( ... );
The new row in phone got a unique id (I do use the serial type for my primary keys). Now, I need the id that row got to use in another insert. If I do something like:
insert into contact ( ... phone, ... ) values ( ... (select currval(phone_id_seq)), ... );
That will get the id of the record I just inserted assuming someone else didn't sneak in and insert another row in the phone table before I queried phone_id_seq.
Or were you suggesting I do something like:
<some dtml assignment ... select nextval(phone_id_seq); ...> insert into phone ( id, ... ) values ( <value retrieved above> ... );
If so, how do I capture the value of nextval(...) within the ZSQL method?
I've struggled with the same thing, but never got it work satisfactorily. The approach I take is to have a separate ZSQL method for each insert and glue them together with a python script. ZSQL method nextPhoneId: select nextval('phone_id_seq') as phone_id; ZSQL method nextContactId: select nextval('contact_id_seq') as contact_id; ZSQL method insertPhone: insert into phones (phone_id, phone_number ) values (<dtml-sqlvar phone_id type=int>,<dtml-sqlvar phone_number type=string>) Python script: request = container.REQUEST response = request.RESPONSE contact_id = context.nextContactId() request.set(contact_id) phone_id = context.nextPhoneId() request.set(phone_id) # call ZSQL method to insert the contact context.insertContact() # call ZSQL method to insert the phone context.insertPhone() # return the user a page template to view the newly added contact response.redirect('viewContact?contact_id=%s' % (contact_id)) I've found this works well for me, although you do tend to end up with a lot of separate ZSQL methods. Jim