|
|
 |
| 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...
var
LConnection: TSQLConnection;
LQuery: TSQLQuery;
begin
LConnection := CreateSQLConnection;
try
LConnection.ExecuteDirect(
'insert into RESPONDENTS (GUID) values (''' + AGUIDStr + ''')');
LQuery := TSQLQuery.Create(NIL);
try
LQuery.SQLConnection := LConnection;
LQuery.SQL.Text := 'select last_insert_id() from RESPONDENTS';
LQuery.Open;
Result := LQuery.Fields[0].AsString;
finally
FreeAndNIL(LQuery);
end;
finally
FreeAndNIL(LConnection);
end;
end;
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.
HTH
Top
|
| Comments |
#1
Pablo
wrote on
2009-02-06 15:03:05
|
|
Thanks for the solution, i have spent hours on this !!!
|
#2
Simon L
wrote on
2009-10-29 14:09:42
|
|
Thanks for that! What a stupid API, totally not intuitive.
|
#3
liceen
wrote on
2010-04-13 17:55:30
|
|
great,it\'s very very good
|
#4
MMM
wrote on
2010-07-20 17:11:53
|
|
:*
|
#5
Luis
wrote on
2012-06-07 03:04:22
|
|
Thank. Very easy to understand. Best Regards
|
#6
Alysson Mogno
wrote on
2013-06-05 20:51:18
|
|
It works now, thank you man!
|
|
Top |
|
Top
|
|
|
Texts
