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

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>