[Zope] ZSQL retreive auto_increment id inserted [Q]

Sam Gendler sgendler@teknolojix.com
Tue, 09 Nov 1999 09:37:36 -0800


> 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