Can not get proper results from MySQL last_insert_id() using TSQLQuery of dbExpress?


2005-04-22 02:58:33 AM
delphi248
Hello folks,
i am using dbExpress in Delphi 2005 (Update 1) and MySQL 4.0.24/Win32.
I want to use a TSQLConnection and a TSQLQuery only.
The connection to my database works very well and any single SQL command
can be executed properly. But if i want to know the last id of an
auto_increment field i get very often the value 0 instead of the correct
id.
var
d:TSQLConnection;
q:TSQLQuery;
i:integer;
...
d.Connected:=true; { closed only if the application terminates! }
...
q.sql.clear;
q.sql.add('INSERT INTO tablename (NAME) VALUES (''Smith'')');
q.ExecSql;
...
q.sql.clear;
q.sql.add('SELECT last_insert_id()');
q.Open;
i:=q.FieldByName('last_insert_id()').AsInteger;
q.Close;
Sometimes i is set to the proper value of the last id, but in about 90%
of all cases i is set to 0! ???
I examined the MySQL log and it seems that the database connection got a
new id after the q.Open command. I tried to disable the AutoClone option
of the SQLConnection but i can't use this feature because the value of
MaxStmtperConn is always set to 1. So i manually have to clone the
connection after executing 1 sql command only to avoid an exception.
Any help will be very kind
Best regards
Stefan Haeber
Germany