Page 1 of 1
How to Get the Unique ID for the Last Inserted Row
Posted: Wed 05 May 2010 05:04
by RemHep
I've tried to get the last inserted row with MySQL function :
SELECT LAST_INSERT_ID()
But it always return 0.
Can I have an example with TSQLQuery?
Thanks for help!
Posted: Wed 05 May 2010 09:26
by Dimon
The LAST_INSERT_ID function works only for AUTO_INCREMENT columns.
Posted: Wed 05 May 2010 09:29
by Dimon
Use the following code:
Code: Select all
if SQLQuery.Active then
SQLQuery.Refresh
else
SQLQuery.Open;
Id := SQLQuery.Fields[0].AsInteger;
Posted: Wed 05 May 2010 13:14
by RemHep
id is AUTO_INCREMENT
With DSQuery Do
Begin
SQLConnection := TmpSQLConnect;
SQL.Clear;
SQL.Add('INSERT INTO tclients SET id=null');
ExecSQL;
SQL.Clear;
SQL.Add('SELECT LAST_INSERT_ID()');
if DSQuery.Active then
DSQuery.Refresh
else
DSQuery.Open;
Result := DSQuery.Fields[0].AsInteger;
Close;
End;
still return 0
Posted: Wed 05 May 2010 14:58
by Dimon
I still can not reproduce the problem.
Please send me a complete small sample to dmitryg*devart*com to demonstrate it, including a script to create and fill table.
Also supply me the following information:
- the exact version of DbxMda;
- the exact version of your IDE;
- the exact version of MySQL server.
Posted: Thu 06 May 2010 09:44
by Dimon
This problem is connected with the specificity of MySQL client library work. To solve the problem use Direct mode to connect to a MySQL database. Change the following line for this:
Code: Select all
NewSQLConnect.Params.Add('DriverName=MySQL');
to this line:
Code: Select all
NewSQLConnect.Params.Add('DriverName=DevartMySQLDirect');
Posted: Thu 06 May 2010 12:59
by RemHep
Great thanks!
I've already used "DevartMySQLDirect" in other project. Just a bad "Copy and Paste" of one old DB function.
Sorry for that, thanks for your time!
Posted: Thu 06 May 2010 13:19
by Dimon
If any other questions come up, please contact me.