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
At 00:40 16/08/99 , Jeremy Fitzhardinge wrote:
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 ';'.
Use <!--#var sql-delimiter--> instead: insert users (name, email, type) values('foo', 'bar@biff.org', 'normal') <!--#var sql-delimiter--> select user_id from users where user_id = LAST_INSERT_ID() -- Martijn Pieters, Web Developer | Antraciet http://www.antraciet.nl | Tel: +31-35-7502100 Fax: +31-35-7502111 | mailto:mj@antraciet.nl http://www.antraciet.nl/~mj | PGP: http://wwwkeys.nl.pgp.net:11371/pks/lookup?op=get&search=0xA8A32149 ------------------------------------------
On 16-Aug-99 Martijn Pieters wrote:
Use <!--#var sql-delimiter--> instead:
insert users (name, email, type) values('foo', 'bar@biff.org', 'normal') <!--#var sql-delimiter--> select user_id from users where user_id = LAST_INSERT_ID()
Thanks, that's exactly what I needed (though it's spelled 'sql_delimiter'). J
participants (2)
-
Jeremy Fitzhardinge -
Martijn Pieters