ODBC and last_insert_id()

from the Artful MySQL Tips List

Under ODBC, for example in Access and Delphi apps, the MySQL function last_insert_id() does not reliably return the last insert ID. The workaround is a longstanding MySQL kludge: setting the MySQL system variable sql_auto_is_null=1 permits retrieval of the last insert ID of the auto_increment key ID from table tbl with this oxymoronic syntax...
select ID from tbl where ID is null;
But the oddness doesn't stop there. The variable sql_auto_is_null cannot be set either in an option file or on the server command line. Then is your only option to set it in every instance of your Access or Delphi app? No. You can set it in a server init file, eg create d:init.sql like this ...
SET @@global.sql_auto_is_null = 1;
... and put the name of that init file in my.ini:
But why doesn't the ODBC/last_insert_id() problem get fixed? Good question.

Last updated 27 Jan 2015

Return to the Artful MySQL Tips page