[Zope] ZSQL retreive auto_increment id inserted [Q]

[chas]

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.