Scenario…I’m using ColdFusion 9 and MySQL 5. I already had my DSN setup but had a requirement to get the last inserted id from a MySQL INSERT statement (I’m using auto-incrementing integer’s).
As most people know, the best way to do this (in MySQL) is using the last_insert_id() function. I wanted to attach this to the end of my INSERT statement for performance reasons, as I’d only be sending one query to the server instead of separate ones. Plus I’m not even sure if I’d get the correct id back in a production environment if I sent it through as a separate query.
Anyway…for this to happen you need to add “allowMultiQueries=true” to your DSN connection string in ColdFusion Administrator (advanced settings) which I did. However I got an error when I tried to run the code, CF didn’t like me running 2 queries at once. Hmm…restarted CF which should have reloaded the drivers. No luck. Ok…how about restarting MySQL? Nope…
Finally took a comment from Will Tomlinson over on Ben Nadel’s blog to help me out. Delete and recreate the DSN, happy days!
Who want’s some sample code?
<cffunction name="addStuff" access="public" output="false" returnType="numeric"> <cfargument name="ref" type="string" required="true"> <cfargument name="stage" type="string" required="true"> <cfset var q = ""> <cfquery name="q" datasource="#variables.instance.config.dsn#"> INSERT INTO mytable ( ref , stage ) VALUES ( <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.ref#"> , <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.stage#"> );SELECT LAST_INSERT_ID() AS newId </cfquery> <cfreturn q.newId> </cffunction>
9 Comments
If you’re on CF8+, the last insert ID is available as “generated_key” in the result structure:
…
#r.generated_key#
Looks like my previous comment wasn’t escaped…
<cfquery name=”q” result=”r”> … </cfquery>
Ah good point Eric I forgot about that. This site will be ported to Railo, so as long as it works there I’ll take that approach.
Thanks
Confirmed that “result” works in Railo. Returns a struct including “generatedKey” which is the newly auto-generated Id for that row.
Doesn’t that make SQL injection easy ?
Not if you use cfqueryparam for all values coming from form or URL
I’m not sure what kind of access you have to the MySQL database, but if you have access to it, this can also be done via stored procedure.
have your stored proc do the insert and call last_insert_id()
then your CF page just has to call the stored proc.
True, for this application we’re not using stored procs. In our environment they would actually complicate things for different reasons, simpler for us to leave the database logic in the core codebase.
FWIW, I believe that you can use seperate cfqueries for the insert and select last_insert_id(). You just need to wrap them in a cftransaction to ensure they happen on the same connection.