BorlandTalk.com

Stefan Haeber · Guest

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 not 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

Stefan Haeber · Guest

Hello folks,

i have solved my problem. My fault was to use one SQLConnection only for
all my SQL commands.
After adding a separate SQLConnection and a separate SQLQuery (for all
SQL commands without getting a cursor or a returning dataset) and
disabling the AutoClone feature everything works very well.

Best regards

Stefan Haeber
Germany

Stefan Haeber wrote: