Getting the Generated Key From A Query in ColdFusion (Including Script Based Queries)

Are you are still writing things like SELECT SCOPE_IDENTITY()  or LAST_INSERT_ID() in your ColdFusion Queries?  One of the more powerful and perhaps lesser known features of the cfquery tag is the ability to return the generated ID from an insert statement.

Prior to ColdFusion 9 the value returned in the query struct would change depending on the type of database the value was being inserted into (see cfquickdocs for more information).   For Instance Oracle would return a key called RowID  and SQL Server would return a key called identitycol in the result structure of a query (provided you supplied a result argument in the query itself).

<cfquery name="insertMe" datasource="mysql_db" result="queryResult">
 INSERT INTO
   Contact(firstname, lastname) 
 VALUES('ryan','anklam');
</cfquery>

In ColdFusion 9 Adobe simplified this by always returning a value called generatedkey in the result structure as shown below:

 






Things get a bit more interesting when leveraging ColdFusion's new script based queries.  Lets use the following code as an example:

<cfscript>
 insertQuery = new query();
 
 insertQuery.SetDatasource("facets_sql");
 
 insertQuery.SetSql("insert into contact(firstname, lastname) values('ryan','anklam')");
 
 result = insertQuery.Execute();
</cfscript>

One might expect that generated key would be a property that could be accessed through the result variable.  However, looking at the dump of the result variable we can see that ColdFusion actually wraps all the result goodness in a property called prefix:

So, in order to get the generated key from a script based query the following code is required:

<cfscript>
theKey = result.getPrefix().generatedkey;
</cfscript>