mORMot Open Source

squirrel

Member

Registered: 2015-08-13

Posts: 155

Using StripSemicolon with ODBC Connections

How do I set the StripSemicolon to false when running queries with ODBC Connections (TSQLDBConnectionProperties.Execute) on Mysql?

I need to insert records and get the last inserted id back.  Chaining the select to the insert statement works with firedac connections (libmysql.dll) but not with odbc connections.  This is needed, because if the SELECT last_insert_id() is run separately after the inserts, Mysql sees it as coming from a different connection and returns zero, which makes it impossible to do inserts on master-detail tables.

The following query works perfectly for Firedac, but not Odbc, because the semicolons are removed from the Odbc statement, but not from the Firedac statement:

INSERT INTO test (fld1, fld2) VALUES (?, ?) ; SELECT last_insert_id()

With MS Sql Server its easier, because there I can use a statement like this, which returns correctly:

INSERT INTO test (fld1, fld2)  output inserted.id  VALUES (?, ?) 

Any suggestions will be very helpful.

squirrel

Member

Registered: 2015-08-13

Posts: 155

Re: Using StripSemicolon with ODBC Connections

Does anybody know how to achieve this?  Any suggestions will be helpful.

sakura

Member

From: Germany

Registered: 2018-02-21

Posts: 239

Website

Re: Using StripSemicolon with ODBC Connections

Your problem is, that ODBC simply does not support multiple statements in a single command/query. However, if you use MySQL, there is an option, that will enable that special feature.
More on that: https://stackoverflow.com/questions/414 … 6#15440056

Regards,
Daniel

squirrel

Member

Registered: 2015-08-13

Posts: 155

Re: Using StripSemicolon with ODBC Connections

Thanks Daniel! That saved me a lot of time.  Based on the error, I incorrectly assumed that StripSemicolon was the cause, it didn't occur to me that the cause was the actual connection.

I tried adding the option in the linked mysql documentation and still get the same issue, but at least now I know where to look

Edit: I found this burried deep in the documentation: SQL syntax for prepared statements does not support multi-statements  So now I'm back to where I started.  How to sucessfully get the last inserted id, without mysql just returning zero.

Last edited by squirrel (2020-09-18 10:35:34)