On Sat, 16 Mar 2002, Robert Ln wrote:
Hi all,
I am using Zope 2.5.0 and the latest ZMySQLDA. I have a few related questions.
1) If I insert a new row into the mysql database that has an auto_insert column (say 'id'), how do I get the automatically generated id? In mysql you would use the LAST_INSERT_ID() function, but this doesnt work in a zsql method
2) The reason 1) doesnt work is that zsql methods (and the underlying python-mysql db interface) doesnt seem to allow select functions. Something like SELECT 1+1 doesnt work. Is there any way of getting this to work?
3) Actually, I see from the python mysql db interface that the cursor object has an insert_id() method. Does the ZMySQL DA allow any way of getting at this method?
4) Finally, the mysql docs say the last_insert_id() returns the last automatically generated id on a per-connection basis. If in zope only a single connection is made and a lot of clients are accessing it at once, how do I guarantee I get back the correct id? I thought you could concatenate multiple SQL queries in one zsql method, but that doesnt seem to work anymore.
SELECT functions work fine; the problem is that 'LIMIT 1000' is appearing at the end of the SQL statement, and to MySQL, SELECT 1 + 1 LIMIT 1000 is an error. Go to the Advanced tab and set "Max rows to retrieve" to 0. Yes, since several Zope shares one connection against several web users, it might be possible that, between the insert and the select last_insert_id(), that they would be different web viewers. So: make it all part of one ZSQL method: insert into test (a) values ('a') <dtml-var sql_delimiter> select last_insert_id; You can combine more than one SQL statement in a ZSQLMethod this way. -- Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton Independent Knowledge Management Consultant