Returning IDENTITY ID via ColdFusion 9’s CFQuery

Posted: February 16, 2011 in ColdFusion

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
Comments
  1. LindaKhuu says:

    I am using ColdFusion 9 but I am unable to get the generatedkey value. I checked the Result struct but there is no such property. Any help would be greatly appreciated.

  2. I use the NEWID() value to sort a coldfusion query in a SQL environment.
    EXAMPLE:
    select *
    from datatable
    order by newid()
    This query works fine in coldfusion 8, but not in cf 9.0
    Any tips?

  3. […] This is a followup post to “Returning IDENTITY ID via ColdFusion 9′s CFQuery“. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s