Returning IDENTITY ID via ColdFusion 9’s CFQuery

Many of us are familiar with using:

SELECT @@IDENTITY AS ID

or in later versions of MSSQL:

SELECT SCOPE_IDENTITY() AS ID

in your SQL statement to return the last identity value that you just inserted into a SQL Table.

In ColdFusion 8 you could also use:

<cfquery datasource="#DSN#" result="myResult">
INSERT INTO MyTable (col1)
VALUES ('col1')
</cfquery>

<cfoutput>Inserted ID is: #myResult["IDENTITYCOL"]#</cfoutput>

(Use: IDENTITYCOL for SQL Server, ROWID for Oracle (is rowid not primary key), SYB_IDENTITY for Sybase, SERIAL_COL for Informix, GENERATED_KEY for MySQL)

However in ColdFusion 9 they standardized the variable name to GENERATEDKEY for all SQL environments.

<cfquery datasource="#DSN#" result="myResult">
INSERT INTO MyTable (col1)
VALUES ('col1')
</cfquery>

<!--- Tag based output --->
<cfoutput>Inserted ID is: #myResult["GENERATEDKEY"]#</cfoutput>
<!--- Script based output --->
insertedID = myResult.getPrefix().generatedkey

 

Advertisements