Add Comment

Return Identity Values Tutorial

ColdFusion Tutorial #9

If you are using a database table with an identity (auto-increment) field, ColdFusion 8 can automatically return the newly created field value for you, without needing a trigger or a subsequent <CFQUERY>. All you need to do is look in the <CFQUERY> optional RESULT structure, and if available the value will just be there. This feature is very DBMS and driver specific (and may not work with all databases), and the name of the structure member containing the generated value is different based on the DBMS being used. Here are the ones you should be aware of:

SQL Server: result.IDENTITYCOL
Oracle: result.ROWID
Sybase: result.SYB_IDENTITY
Informix: result.SERIAL_COL
DB2: result.KEY_VALUE
MySQL: result.GENERATED_KEY (MySQL 4+)
Apache Derby: result.IDENTITYCOL

demo.cfm

This is a really simple example of inserting a record into a database, retrieving it's id and then deleting it again.

The code doesn't need much explanation, but remember to add the result attribute to the <cfquery.

Demo

See this code running!

Download

Download this code as a zip!

Comments

I have all my updates go through stored procedures.
This increases security (you can turn off the ability to insert/update/delete in the cfide/admin).
These stored procedures also do validations and return the identity column if it's an insert.
Phillip Senn @ Friday 14 Sep 2007 - 10:33:07 PM

I am coding an app using mysql as the database. When it moves to a production environment, I suspect it may move to MSSQL.
Is there a way to determine which database engine is being used, so you could code a cfif or cfswitch to choose the correct phrase for the return value?
Michael Brennan-White @ Friday 14 Sep 2007 - 10:37:12 PM

For MSSQL does the 'IDENTITYCOL' return the equivalent of @@identity or scope_identity?
Michael Sharman @ Monday 17 Sep 2007 - 09:32:07 AM

You can determine the DB type with Coldfusion 8 with cfbdinfo. Thanks to Ray Camden for reminding me of this:
<cfdbinfo datasource="mstest" type="version" name="dbInfo" />

<cfdump var="#dbInfo#" />

<cfoutput>#dbInfo.DRIVER_NAME#</cfoutput>
Paul Kukiel @ Wednesday 11 Mar 2009 - 06:48:01 AM

What about for PostgreSQL?
James E @ Wednesday 07 Jul 2010 - 07:42:13 AM

Click button to add a comment

Author

Ben Forta

Published

Friday 14 Sep 2007

Original

This tutorial has been modified and published with permission of the author. The original tutorial can be found here
http://www.forta.com/blog/index.cfm/2007/7/6/ColdFusion-8-Can-Return-Identity-Values