select last_insert_id() returns always 0

[wseifert]

Hi,

I am developing a app with Delphi 2007 using ZEOS 6.6.3 stable and MySQL 5. I use 7 tables to store data, on 6 of them using last_insert_id() to retrieve the auto_increment id of the last inserted row works as expected, but on one table it does not. I receive allways 0 (zero) as result using >select last_insert_id()<. I have tried several versions to exec this statement, no success.
Code snipped:

qrMain.SQL.Clear;
qrMain.SQL.Text := 'insert into clients (WsName, WsSettings) ' +
' values (:WsName, :WsSettings);';
qrMain.Params.ParamByName('WsName').AsString := R.Mp3ClName;
qrMain.Params.ParamByName('WsSettings').SetBlobData(@D, SizeOf(D));
qrMain.ExecSQL;

qrMain.SQL.Text := ('select last_insert_id() as `clientId`;');
qrMain.Open;
R.ClientID := qrMain.FieldByName('clientId').AsInteger;

I use the same code in all of my tables (adapted to the table row data fields), except one table it works as expected and retuns the id of the last inserted row.
I tested the SQL statements with the command line client on my SQL Server (SLES 10.1) with success. I turned on the debug log on the server to see if the connection from client to server is interupted, this is not the case, all SQL commands my app is executing via ZEOS receives the server ...

Any help and directive to solve the problem is appreciated.

P.S.

I am resident in Upper Austria and would warmly welcome Delphi / ZEOS Developer(s) to contact me via Skype. I am new to SQL / ZEOS and sometimes it would be helpfull not only to have a forum but also be able to talk to some expirienced people(s) about problems and so on.

Regards
Werner

[mdaems]

Werner and I have exchanged some sample code and after a long debugging session I found the reason for this strange result.

It's what I would call a Delphi bug (which exists in all know versions of Delphi, but I don't think it's in fpc).

What Werner did was execute a select, and insert and a select last_insert_id during the FormDestroy event. So he could save the program settings before leaving.
These queries are executed on the server without problems. BUT the resultsets are not retrieved because Componentstate of the TDatasetDescendant is dsDestroying at the moment. I would have expected this should just work OR raise an error.

The solution of the problem was easy : just move the code to the Onclose event handler and everything works fine.

To avoid this kind of trouble in the future I added a (Delphi only) test to ZQuery.Open that just prevents opening a query when the dataset is destroying. No block for ExecSql. There are no problems with that as there must no resultset be read i that case.
As this change might break existing programs I'll not include this patch in the 6.6 branch. If you think 'I want to save myself from this problem' the change is here : http://fisheye2.atlassian.com/changelog/zeos/?cs=514

Mark