JCSDynamix Blog

Auto-Increment field returns '0' in dbExpress

Here's the scenario

I'm using a mySQL database with an auto-increment field as the primary key. 
I'm using a TSQLDataSet to execute an insert into SQL statement on the database.
I'm then using the same TSQLDataSet instance to obtain the last auto_increment value.

eg.

  1. insert into tblMyTable(field1,field2,field3) values(value1,value2,value3)
  2. select last_insert_id() as id

The data is inserted in to the table fine, the auto_increment field is correctly assigned the next sequential number, but the TSQLDataSet returns 0 for the "select last_insert_id()" query.

The same problem exists whether using the TSQLConnection itself (by way of ExecuteDirect, etc) or the TSQLQuery object.

The solution eventually turned out to be the little known AutoClone property of the underlying TSQLConnection object.  By default, tis value is set to true which tells the SQL Connection to create a clone of the connection after every query.  The mySQL server sees each clone as a new client connection and since the last auto_increment value only relates to the most recent insert in the current connection it will always return 0.

The simple soultion was just to set the AutoClone property of the TSQLConnection to false at some point before executing the insert and select queries.  

eg.

  1. sqlConnection=new TSQLConnection(..)
  2. sqlConnection->AutoClone=false;
  3. ...
  4. dataSet=new TSQLDataSet(...)
  5. dataSet->SQLConnection=sqlConnection;
  6. sqlConnection->ExecuteDirect("insert into...");
  7. dataSet->CommandText="select last_insert_id() as id";          // or select @@identity
  8. dataSet->Open();
  9. ....