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>