ColdFusion 8 Can Return Identity Values

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
  • MySQL: result.GENERATED_KEY (MySQL 4 and 5 only, MySQL 3 does not support this feature)

Very nice indeed!

42 responses to “ColdFusion 8 Can Return Identity Values”

  1. Ben Forta Avatar
    Ben Forta

    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. phill.nacelli Avatar

    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. Ben Forta Avatar
    Ben Forta

    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. Daren Munroe Avatar
    Daren Munroe

    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. Dan Avatar

    This is a sweet little tidbit for sure!!

  6. Scott Stroz Avatar
    Scott Stroz

    This is sweet. It would have been just a tad bit sweeter if CF returned the same key name, regardless of DB used.

  7. Phillip Gagnon Avatar
    Phillip Gagnon

    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,…)

  8. radek Avatar

    You insert guid manually to tables?

  9. Jim Collins Avatar
    Jim Collins

    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.

  10. Tony Petruzzi Avatar
    Tony Petruzzi

    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().

  11. Jim Collins Avatar
    Jim Collins

    @tony thanks for the tip will do

  12. phill.nacelli Avatar

    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.

  13. #NAME? Avatar

    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?

  14. Tony Petruzzi Avatar
    Tony Petruzzi

    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

  15. Jorrit Avatar

    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>

  16. Ben Forta Avatar
    Ben Forta

    Tony, I don’t believe so.
    Jorrit, no, no conflict. But that code is unneeded in CF8.
    — Ben

  17. phill.nacelli Avatar

    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.

  18. tof Avatar

    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.

  19. charlie arehart Avatar
    charlie arehart

    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.

  20. charlie arehart Avatar
    charlie arehart

    sorry, I meant to say identitycol (not identifycol).

  21. John Scott Avatar
    John Scott

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

  22. feiy Avatar

    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:
    <cfquery name="testQuery" datasource="someDs">
    select * from some_table where char_col=’1’
    is a bug of coldfusion 8 cfquery?!
    please help me!thanks!

  23. jason Avatar

    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

  24. Bobby S Avatar
    Bobby S

    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

  25. Charlie Arehart Avatar
    Charlie Arehart

    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?

  26. Jeremy Rottman Avatar
    Jeremy Rottman

    For those of you who do not use auto-increment fields, you can achieve the same functionality by using the syntax below.
    result_name.SQLPARAMETERS[index of pk]

  27. Jeremy Rottman Avatar
    Jeremy Rottman

    I guess I should have been more specific on my previous post. You can use that when, you are properly using your cfqueryparams.

  28. Chill Avatar

    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:
    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>

  29. Chill Avatar

    Sorry, typo. *inquiry

  30. Chill Avatar

    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.

  31. Brian Oeding Avatar
    Brian Oeding

    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# />

  32. Silviu Avatar

    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 #’)

  33. nesthone Avatar

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

  34. Steve Avatar

    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>

  35. MP Avatar

    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.

  36. Steven Ross Avatar
    Steven Ross

    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.

  37. Bernie Avatar

    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?

  38. Brian Hendel Avatar
    Brian Hendel

    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.

  39. Brian Hendel Avatar
    Brian Hendel

    Nevermind! Turned out that somehow autoincrement got turned off on the ID field! Blarg…

  40. RAMESH Avatar

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

  41. Matthew Avatar

    @RAMESH – Take out the ; and try it again.

  42. bibin Avatar

    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