Here’s another ColdFusion 8 goodie that I just learned about yesterday …
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 . All you need to do is look in the optional RESULT structure, and if available the value will just be there. This feature is very DBMS and driver specific (and unfortunately does not seem to be supported by Apache Derby), 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

Very nice indeed!

42 thoughts

  1. Scott, I agree, and had I have discovered this feature much earlier I’d have pushed for that. But too late to change that now. The goos news is that we can always add an abstraction name in the future.
    — Ben

  2. Just curious if for MSSQL if they are using SCOPE_IDENTITY() or @@IDENTITY to get the generated key value, any ideas? In the past I’ve done the following in MSSQL to not have to use triggers or a second query call:
    <cfquery name="qCreate" datasource="#getDatasource().getName()#">
    Person(firstName, lastName, dob)
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#firstName#" />,
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#lastName#" />,
    <cfqueryparam cfsqltype="cf_sql_datetime" value="#dob#" />
    SCOPE_IDENTITY() AS personId
    personId = qCreate.personId;

  3. Phill, neither. I am sure that under the hood that is exactly what the drivers are doing, but, it is the drivers that are exposing this information and we’re capturing it.
    — Ben

  4. Suppose my database server supports multiple row inserts. Is an array returned in this case?
    INSERT INTO test
    (val1, val2, val3, val4)
    (SELECT ‘a1’, ‘a2’, ‘a3’, ‘a4’)
    (SELECT ‘b1’, ‘b2’, ‘b3’, ‘b4’)
    (SELECT ‘c1’, ‘c2’, ‘c3’, ‘c4’)

  5. Ben-
    This is cool, but…..
    Has anyone mentioned the possibility of returning a newID() (mssql).
    I am just saving it now before inserting it, but this would be way easier.
    SET newUUID=newID()
    INSERT INTO table(uuid, name,…)
    VALUES(newUUID, @name,…)

  6. I wonder if it handles triggers. Ive been screwed in the past when an insert fired triggers ; select @@identity returned the last generated ID, which may not be the ID for the record I inserted.

  7. @radek
    It’s easy to do:
    insert("-", CreateUUID(), 23)
    will create a compatable GUID for MSSQL
    If you’re use MSSQL: NEVER depend on @@Identity. Use SCOPE_IDENTITY().

  8. hey Jim,
    That’s why I was wondering if it used @@IDENTITY versus SCOPE_IDENTITY(). @@IDENTITY does not guarantee that the value returned is the last id generated from your transaction, where SCOPE_IDENTITY() does.

  9. i know its a little lower level [beginner] then the other data bases.
    but what about an access *.mdb odbc connection?
    will it return the newly created primary key?

  10. @paul,
    Tell me if I’m wrong here, but as far as I know, MSAccess doesn’t have support for this. In the pass the only way I knew how to get an identity from a table was to use two queries wrapped inside a cftransaction tag. One would do the insert w

  11. I was just wondering, does this conflict with the code I am using for a long time?
    I think that CF just adds the @@identity line to the code, what if it’s allreadt there?
    <cfquery name="qryIns" dsn="some_dsn">
    INSERT INTO tbl_test (name)
    VALUES (‘my name’);
    SELECT @@identity as pkey
    <cfoutput>The primary Key is: #qryIns.pkey#</cfoutput>

  12. Correct me if I’m wrong but I believe that using @@identity like in Jorrit’s code can potentially return the wrong id. That’s why SCOPE_IDENTITY() is used, it only deals with identity value within the same scope, where @@IDENTITY does not. So if user A runs the insert and then user B runs the insert before user A gets his SELECT @@IDENTITY as pkey user A will actually get user B’s insert key.

  13. No that’s not correct. @@identity is bound to your own session, which means you will never get someone else’s PK. The real difference between the 2 is that scope_identity() will only return PKs that are explicitly inserted. For example, if a trigger for insert on table_source inserts a row in table_triggered, @@identity will return the PK for table_triggered whereas scope_identity() will return the PK for table_source.
    It’s not that one is better than the other, they just do different things. Admittedly, yes, if you have an plain insert statement in a cfquery, you probably want to retrieve the identity without worrying of whatever trigger is out there, so scope_identity() is a better fit.

  14. Hey Ben, you say that Derby doesn’t seem to support this, but I find it’s working, as (using your nomenclature) result.identifycol, just like SQL Server.
    FWIW, I’m using the embedded version, and the column definition for the table in which I’m inserting a record defines the primary key as "generated always as identity".
    I’m just offering this in case others (like me) find the entry and wonder about Derby.

  15. This is a great feature but has anyone else been having problems with using existing (MS-SQL) triggers with CF8?

  16. is there have a bug in coldfusion <cfquery/>
    when u passs the cfquery a sql var,the var has the ‘ char,the cfquery will convert to the ‘ to ” auto,why?
    for example:
    <cfset sql="select * from some_table where char_col=’1’"/>
    <cfquery name="testQuery" datasource="someDs">
    the sql pass to db’s will be: select * from some_table where char_col=”1”,so throws a db exception’s
    but if u code the sql inner cfquery the every work ok:
    select * from some_table where char_col=’1’
    is a bug of coldfusion 8 cfquery?!
    please help me!thanks!

  17. john –
    I am also having an issue with CF8 and exisiting triggers..
    my triggers are usualy select scope_identity() or identity@@ as ‘identity’ but in CF8 it is throwing an error saying undefined. confirmed the same query works in cfmx 7

  18. I am new to CF8 and i was wondering what is the best book to start with to learn how to create a application with CF8 and Dreamweaver.
    Thank you

  19. Bobby, I think you’ll find universal agreement that the best book is the CF Web Application Construction Kit Series, which has existed as 2 volumes for several years and as of CF8 is now 3 volumes. The first came out last month. You can find more here on Ben’s site:
    BTW, Ben, curiously it’s not listed in the main page content of your books page (, though it is in the left nav bar. Perhaps an oversight, just not updated since the book shipped?

  20. I think Jorrit was on to something with their inquery earlier. My CF8 is having problems with my old insert statements within the cfquery. Mainly the @@ portion. They just do not work unless I remove the Select @@ option. Anyone else witnessing this?
    Jorritt wrote:
    INSERT INTO tbl_test (name)
    VALUES (‘my name’);
    SELECT @@identity as pkey

  21. As an update. I was using Access and did not see the earlier post about Access’s limitatons. I’ve dropped access and moved on to SQL Server. My old sets of code now work fine on CF8. Thanks for the helpful posts.

  22. I submitted the following bug to Adobe:
    CF8 with SQl Server 2005 and JDBC 1.2. Doing an insert query as follows fails to work as advertised with [result].IDENTITYCOL. I have to use [result].GENERATED_KEYS instead. I use the code below as a workaround until it is fixed.
    <!— Create a record entry in the Database table diaDocuments and return the key that was added. —>
    <cfquery name="qAppenddiadocument" datasource="DocumentImaging" result="diaresult">
    insert into diadocument
    (diadocument.diadocno, diadocument.diaentrydatetime, diadocument.dianoofpages, diadocument.diasuperdoctypekey)
    (#lndiadocno#, #ldnow#, #lndianoofpages#, #lndiasuperdoctypekey#)
    <!— Return the key field from the new DB Table record. This is a CF8 feature.
    However there is a bug currently with SQL Server 2005, JDBC 1.2 and CF8 that I code around here.—>
    <cfif IsDefined("diaresult.IDENTITYCOL") >
    <CFSET lndiadocumentkey = #diaresult.IDENTITYCOL# />
    <CFSET lndiadocumentkey = #diaresult.GENERATED_KEYS# />

  23. Hi!
    I tried unsuccessfully to get the Return Identity Values, the way you show us! All I get is:â€KEY_VALUEâ€ is not defined â€¦â€
    Would you provide a valid example for Coldfusion 8 and MySQL? Thank you!
    <CFQUERY NAME="qInsert" DATASOURCE="verb">
    INSERT INTO club
    (firstname, lastname)
    (‘#formData.firstname#’, ‘#formData.lastname #’)

  24. Here is a working example. REMEMBER you must be using the v4 o5 v5 ODBC driver for mySQL… version 3 does not work!
    <cfquery datasource="#APPLICATION.dsn#" name="update_my_db" result="my_result">
    INSERT INTO tbl_foo
    VALUES (some_field_value)
    <cfoutput>My New Primary KeyID = #my_result.GENERATED_KEY#</cfoutput>

  25. For MS SQL 2005 and above, use SCOPE_INDENTITY to return the id of the newly inserted record in the current session. Using @@IDENTITY will cause problems if there is a trigger associated with the table that you just inserted new record to. Because if the trigger (if you have one) is run AFTER the Insert command (most likey after insert for logig and child records) the newley creted insert by the trigger becomes the newly created id by the current session.
    Thus returing the wrong id. Stick to Scope for currect id return and fresh breath.

  26. Why not make a coldfusion specific variable that could encompass all of these variable names… seems like that would be a lot smarter than having all these different variables to check depending on your DBMS. Or just leave them and add one that is CF specific ie: result.resultID or something.

  27. IDENTITYCOL is not present in my result struct. I see CACHED, EXECUTIONTIME, RECORDCOUNT, SQL and SQLPARAMETERS but no IDENTITYCOL. I’m using SQL Server 2000 sp4 and I do have an identity column defined on the table. Do I need to enable this somewhere?

  28. I am having similar trouble with MySQL 5.1.40-community, using the 4/5 driver, with CF 9,0,0,251028.
    GENERATED_KEY is undefined in result when result="result" on the CFQUERY.
    CFdumped the result struct and it contains the basic info (like execution time) without any identity. Query is simple and does an INSERT into a table with an auto-incrementing id field.

  29. ERROR: [Macromedia][Oracle JDBC Driver][Oracle]ORA-00911: invalid character WHEN WE USE THE FOLLOWING QUERY
    <cfquery name="list" datasource="mycy" maxrows="4">

  30. HI,
    IDENTITYCOL am using for find last inserted id in SQl… Genreted_key for My sql…
    is any method for supporting both sql and mysql for getting last inserted id?

Leave a Reply to bibin Cancel reply