[Zope] Multi-statement ZSQL Methods

[Jeremy Fitzhardinge]

Hi all,

I'm using Zope with MySQL to do fairly normal database type things.  A number
of my tables make use of the AUTO_INCREMENT feature to generate unique primary
keys, which I use internally for things like user IDs.  The problem is that
after inserting a user, I want to use LAST_INSERT_ID() to get it back.  This is
what I tried:

insert users (name, email, type) values('foo', 'bar@biff.org', 'normal');
select user_id from users where user_id = LAST_INSERT_ID()

This always complains about a syntax error at the ';'.

Normally with MySQL I could issue a second requst to the the last inserted ID,
but MySQL keeps that on a per-connection basis.  If all Zope threads share the
same database connection, what certainty do I have that another thread hasn't
inserted a new user in between my insert and select?  Do I need to do my own
Zope-level locking?

Is there a better way of dealing with this?

Thanks,
        J