DBExpress and mysql last insert id

问题:

There are several posts about how to get last_insert_id from MySQL and related issues. I found this one post related to DBExpress: delphi dxExpress MySQL: invalid LAST_INSERT_ID value

However, following that post don't help me using the following code to insert a note into a notes table.

TSQLQuery* tq = new TSQLQuery(NULL);
tq->SQLConnection = atdbDM->SQLConnection1;

stringstream q;
q <<"INSERT INTO note (created_by, note) VALUES ("<<1234<<", \'<none>\');";
q << "SELECT LAST_INSERT_ID() AS lastNoteID;";
tq->SQL->Add(q.str().c_str());
tq->Open();
int noteID = tq->FieldByName("lastNoteID")->AsInteger;
tq->Close();

When Open() executes, I get an exception:

'You have an error in your SQL syntax; Check the manual that correposnds to   your MySQL server version for the right syntax to use near 'SELECT LAST_INSERT_ID... at line1'

If I execute the insert statement first, and the SELECT statement individually, I get no error, but the returned ID is always 0. Anyone knowing what needs to be changed? I am using C++ Builder XE3

回答1:

I found out that to prevent multiple sessions to be created, causing last_insertid function to always return 0, one can set the connections AUTOCLONE parameter to false. Then the last insert id function works fine. I.e:

MySQLConnection->Connected = true;
MySQLConnection->AutoClone = false; //<- makes last_insert_id work as expected!