Sam Gendler wrote I didn't take a long look, but it looks as though this solution requires you to call a separate ZSQL query in order to retrieve the ID. Needless to say, there is a wide open race condition if someone else inserts another entry before you get the results from the second query. I was looking for something that returns the last_insert_id as the result set to an INSERT query, much the way the apis (for mysql anyway) do in other environments. Please correct me if I am wrong.
I guess this all depends how Zope shares database connections between multiple ZSQL methods that are called within the same DTML method. eg. With straight CGI and MySQL, this was never a problem; even if 2 people executed the same CGI script, each script had its own database connection and insert_id()'s were specific to the database connection in which the last insert was done. Now, does Zope dedicate the database connection to all the ZSQL scripts within a DTML method before handing it over to another DTML method ? If not, is there any way of forcing this ?
Anthony Baxter wrote : What I do with Oracle and Sequences is have multiple ZSQL methods: the first looks up the next value:
select sequence_name.nextval from dual
I take this number back, and use it in subsequence DTML and SQL code.
Sorry, you've lost me here - is this specific to Oracle ? chas