Caves Travel Diving Graphics Mizar Texts Cuisine Lemkov Contact Map RSS Polski
Trybiks' Dive Texts DBExpress last_insert_id() and DBExpress YAC Software












Rhino Mocks



UI Testing




last_insert_id() and DBExpress
As per MySQL's documentation on last_insert_id:

If you insert a record into a table that contains an AUTO_INCREMENT column, you can obtain the value stored into that column by calling the mysql_insert_id() function.
For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis. It is not changed by another client. It is not even changed if you update another AUTO_INCREMENT column with a non-magic value (that is, a value that is not NULL and not 0). Using LAST_INSERT_ID() and AUTO_INCREMENT columns simultaneously from multiple clients is perfectly valid. Each client will receive the last inserted ID for the last statement that client executed.

Ok, so I wanted to get back the auto generated IDs of respondents added to the DB. Pretty easy, I guessed reading the above text. Just start a connection, insert the record, read last_insert_id() back, and that should be that. And, of course, the ID column was an AUTO_INCREMENT column...
    LConnection: TSQLConnection;
    LQuery: TSQLQuery;
    LConnection := CreateSQLConnection;
        'insert into RESPONDENTS (GUID) values (''' + AGUIDStr + ''')');
      LQuery := TSQLQuery.Create(NIL);
        LQuery.SQLConnection := LConnection;
        LQuery.SQL.Text := 'select last_insert_id() from RESPONDENTS';
        Result := LQuery.Fields[0].AsString;
To my surprise, that didn't work at all - the query always returned 0... "Not again..", I thought, "time to dig into SqlExpr.pas again.".

My first thought was that TSQLConnection has a KeepConnection property... that I set to FALSE in CreateSQLConnection (for other reasons). Easy enough, set it to TRUE and at least getting last_insert_id() should work then (and I'll work around other problems differently). But no, that didn't work...

After looking through TSQLConnection's documentation and code, and reading MySQL's documentation over and over again, I finally found that:
  • TSQLConnection has an AutoClone property that is, by default, set to TRUE,
  • it uses this property to automatically close the previous connection and start a new one after each query (be it an ExecuteDirect call or a TSQLQuery),
  • so the assumption that this is a single connection, even though that's what the code looks like, is totally wrong,
  • so you lose the most recently generated ID "maintained in the server on a per-connection basis" right before opening the query...
Thus, it turns out that, in order for the above code to work, you need to set AutoClone to FALSE right after the connection is created. And then it works just fine.



Pablo wrote on 2009-02-06 15:03:05
Thanks for the solution, i have spent hours on this !!!
Simon L wrote on 2009-10-29 14:09:42
Thanks for that! What a stupid API, totally not intuitive.
liceen wrote on 2010-04-13 17:55:30
great,it\'s very very good
MMM wrote on 2010-07-20 17:11:53
Luis wrote on 2012-06-07 03:04:22
Thank. Very easy to understand. Best Regards
Alysson Mogno wrote on 2013-06-05 20:51:18
It works now, thank you man!


Add a comment (fields with an asterisk are required)
Name / nick *
Mail (will remain hidden) *
Your website
Comment (no tags) *
Enter the text displayed below *





Related pages

Delphi interfaces... again

Checking "Dangling" Event Handlers in Delphi Forms

Drag-n-drop files onto the application window

Intraweb and MaxConnections

A Case for FreeAndNIL

Intraweb as an Apache DSO module

"Device not supported" in Intraweb

Automated GUI Testing

Rounding and precision on the 8087 FPU

SessionTimeout in Intraweb

Using TChart with Intraweb

Unknown driver: MySQL

TIdMessage's CharSet

Software Guarantees

Automated Testing of Window Forms

TChart - Missing Labels in Axes

Memory Leaks and Connection Explosions in DBExpress

Controlling Conditional Defines and Compilation Switches

Detecting Memory Leaks with DUnit

Registering Extensions

DBExpress and Thread Safety

Forms as Frames

Checking Dangling Pointers vs. the New Memory Manager

Accessing Protected Members

Objects, interfaces, and memory management in Delphi