Re: [Zope] ZSQL retreive auto_increment id inserted [Q]

17 Nov 1999

      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.