ZSQL retreive auto_increment id inserted [Q]
Hello. I have just a simple question... I am using Zope 2.0.1 on a Solaris Sparc machine. MySQL 3.22.25 and MySQLdb 0.1.1 ... Everything works fine... I have a table that has a field that is an integer auto_increment ... How do I retreive the id just inserted? I mean, when I used the MySQLmodule, directly from Python, there was a method to do just that... Can I do this using a method from the ZSQL? I checked the ZQR but found no such method ... TIA /B Bruno Mattarollo <brunomadv@ciudad.com.ar> --- Python Powered <http://www.python.org/psa/>
Bruno Mattarollo wrote:
Hello.
I have just a simple question... I am using Zope 2.0.1 on a Solaris Sparc machine. MySQL 3.22.25 and MySQLdb 0.1.1 ... Everything works fine...
I have a table that has a field that is an integer auto_increment ... How do I retreive the id just inserted? I mean, when I used the MySQLmodule, directly from Python, there was a method to do just that... Can I do this using a method from the ZSQL? I checked the ZQR but found no such method ...
TIA
/B
Bruno Mattarollo <brunomadv@ciudad.com.ar> --- Python Powered <http://www.python.org/psa/>
I had the same problem, and it would appear that there was no solution. I wound up inserting and then doing a select on at least one of the values I just inserted, and I order the result by the auto_increment value, descending. That way, even if the field I am selecting on has duplicates, I will get the most recently inserted one. There is a race condition, but in my applicattion, the likelihood of two people inserting the same fields at the same time is so small as to be acceptable, until someone (or myself) hacks up the DA for MySQL. It should be simple to have the DA return the inserted rows, with the auto_increment field included, since currently, an insert query returns nothing. --sam
Re: getting auto_insert_id() from MySQLDA : The problem lies in the last few lines of db.py - the variable 'desc' does not contain the correct values for many SQL queries (the last_insert_id() is just one of them; several complex queries including aliases and 'group by' also fail) Kludge ------ (warning : ain't pretty - I just needed to get this working now) : 1. Add 4 lines to the bottom of db.py (in the ZMySQLDA product) : defs=self.defs for d in desc: item={'name': d[0], 'type': defs[d[2]], 'width': d[3], } Becomes : defs=self.defs for d in desc: # stupid hardcode : if d[0] == "last_insert_id()" : d[2] = 'long' d[3] = 11 d[4] = 'pri notnull auto_inc' # end stupid hardcode. item={'name': d[0], 'type': defs[d[2]], 'width': d[3], } 2. Create a ZSQL method called, say, ZSQL_SelectLastInsertID : select last_insert_id() 3. In your DTML method : <dtml-in ZSQL_SelectLastInsertID> <dtml-var "_vars['last_insert_id()']"> </dtml-in> Sorry, I know it's very ugly and stupid but, then again, so am I :-) And, like myself, it works for now. chas
I have a table that has a field that is an integer auto_increment ... How do I retreive the id just inserted? I mean, when I used the MySQLmodule, directly from Python, there was a method to do just that... Can I do this using a method from the ZSQL? I checked the ZQR but found no such method ...
TIA
/B
Bruno Mattarollo <brunomadv@ciudad.com.ar> --- Python Powered <http://www.python.org/psa/>
I had the same problem, and it would appear that there was no solution. I wound up inserting and then doing a select on at least one of the values I just inserted, and I order the result by the auto_increment value, descending. That way, even if the field I am selecting on has duplicates, I will get the most recently inserted one. There is a race condition, but in my applicattion, the likelihood of two people inserting the same fields at the same time is so small as to be acceptable, until someone (or myself) hacks up the DA for MySQL. It should be simple to have the DA return the inserted rows, with the auto_increment field included, since currently, an insert query returns nothing.
chas wrote:
Re: getting auto_insert_id() from MySQLDA :
The problem lies in the last few lines of db.py - the variable 'desc' does not contain the correct values for many SQL queries (the last_insert_id() is just one of them; several complex queries including aliases and 'group by' also fail)
Kludge ------ (warning : ain't pretty - I just needed to get this working now) :
<snip> 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. Thanks for the effort, anyway --sam
participants (3)
-
Bruno Mattarollo -
chas -
Sam Gendler