Problem with Mysql and using the function last insert ID

Question

When I run my query I get this error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select last_insert_id() as productid' at line 3

NativeErrorCode number 1064

queryError string :

insert into products (systemname, systemversion, created_date) 
values ('web andrea', '', now()); select last_insert_id() as productid;

DatabaseName string MySQL

DatabaseVersion string 5.6.10-log

DriverName string MySQL-AB JDBC Driver

insert into products (systemname, systemversion, created_date) values ('web andrea', '', now()); select last_insert_id() as productid;

I expected the data to get inserted but it fails here.

Thanks in advance

Andrea

Answer 1

You are not running one query but two queries : an INSERT, then a SELECT.

From your application, you would need to make two distinct query calls (one for the insert, the other to get the last inserted id).

Please note that it is likely that your database driver has a built-in function to return the last inserted id (equivalent for mysqli_insert_id in PHP).

Answer 2

In addition to @GMB's comment, the reason it errors is because multiple statements are NOT allowed by default for security reasons - to protect against sql injection. They can be enabled by adding allowMultiQueries=true to the DSN settings. However, be sure ALL queries use cfqueryparam to protect against sql injection.

Having said that, you don't need either last_insert_id() or multiple statements. Instead, use cfquery's "result" attribute. After the insert, CF populates the variable yourResultName.GENERATEDKEY with the new record id

<cfquery result="yourResultName" datasource="yourDSN">
   insert into products (systemname, systemversion, created_date) 
   values (
      <cfqueryparam value="web andrea" cfsqltype="cf_sql_varchar">
      , <cfqueryparam value="" cfsqltype="cf_sql_varchar">
      , now()
   )
</cfquery>

<!--- DEMO --->
<cfoutput>
   New ID (yourResultName.GENERATEDKEY) = #yourResultName.GENERATEDKEY#
</cfoutput>