How to Get the Unique ID for the Last Inserted Row

RemHep · Post by **RemHep** » Wed 05 May 2010 05:04

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!

Dimon · Post by **Dimon** » Wed 05 May 2010 09:26

The LAST_INSERT_ID function works only for AUTO_INCREMENT columns.

Dimon · Post by **Dimon** » Wed 05 May 2010 09:29

Use the following code:

Code: Select all

if SQLQuery.Active then
  SQLQuery.Refresh
else
  SQLQuery.Open;
Id := SQLQuery.Fields[0].AsInteger;

RemHep · Post by **RemHep** » Wed 05 May 2010 13:14

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

Dimon · Post by **Dimon** » Wed 05 May 2010 14:58

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.

Dimon · Post by **Dimon** » Thu 06 May 2010 09:44

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');

RemHep · Post by **RemHep** » Thu 06 May 2010 12:59

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!

Dimon · Post by **Dimon** » Thu 06 May 2010 13:19

If any other questions come up, please contact me.