[Zope] ZSQL and last_insert_id

[Frank Tegtmeyer]

"Chad Nantais" <cnantais@rednaxel.com> writes:

> What is the right syntax for getting the id of the last recorded inserted
> with ZSQL methods? I tried this:
> SELECT LAST_INSERT_ID() AS last_insert_id

Depending on your database this may be a dangerous way to do this. For
example MySQL handles last_insert_id on a per thread base. This means
that you could run into a race condition where one Zope thread grabs
last_insert_id while another thread has inserted your record. In this
case you get the wrong value.

We used an external method to generate unique id's - in this case by
opening a separate connection to the database, inserting into a
counter table and getting last_insert_id. The connection is closed
after that.

Other implementations are of course possible: for example using the
FSCounter product or an object based on Dieter Maurers SharedResource
(http://www.dieter.handshake.de/pyprojects/zope/SharedResource.html). Like
Perl programmers say: there is more than one way to do it :)

Regards, Frank
-- 
CTO   fte@Lightwerk.com         http://www.Lightwerk.com/
Fax: +49-2434-80 07 94           Phone: +49-2434-80 07 81
Lightwerk GmbH * An der Kull 11 * 41844 Wegberg * Germany
Besuchen Sie uns auf der CeBIT:  Halle 6, Stand F68 / 595