2

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

Community
  • 1
  • 1
ASquared
  • 69
  • 5

2 Answers2

6

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>
Pang
  • 9,564
  • 146
  • 81
  • 122
SOS
  • 6,430
  • 2
  • 11
  • 29
2

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).

GMB
  • 216,147
  • 25
  • 84
  • 135
  • It should also be noted that you wouldn't necessarily _want_ to run back-to-back queries. By the time you `SELECT` the last inserted `id`, another session could have `INSERT`ed a new `id`, and that would be the one your `SELECT` picks up. You want to get the `id` that you `INSERT`ed during the same query session that you did the `INSERT`. – Shawn Jan 29 '19 at 22:56