Blog

6Jul
2007
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 <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 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!

Comments (42)



  • Dan

    This is a sweet little tidbit for sure!!

    #1Posted by Dan | Jul 6, 2007, 01:32 PM
  • 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.

  • 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

    #3Posted by Ben Forta | Jul 6, 2007, 02:33 PM
  • phill.nacelli

    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()#">
    INSERT INTO
    Person(firstName, lastName, dob)
    VALUES(
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#firstName#" />,
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#lastName#" />,
    <cfqueryparam cfsqltype="cf_sql_datetime" value="#dob#" />
    )

    SELECT
    SCOPE_IDENTITY() AS personId
    </cfquery>

    <cfscript>
    personId = qCreate.personId;
    </cfscript>

  • 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

    #5Posted by Ben Forta | Jul 6, 2007, 03:43 PM
  • 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')
    UNION
    (SELECT 'b1', 'b2', 'b3', 'b4')
    UNION
    (SELECT 'c1', 'c2', 'c3', 'c4')

    #6Posted by Daren Munroe | Jul 6, 2007, 04:56 PM
  • Phillip Gagnon

    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.

    DECLARE newUUID CHAR(36) OUT
    SET newUUID=newID()

    INSERT INTO table(uuid, name,...)
    VALUES(newUUID, @name,...)

    #7Posted by Phillip Gagnon | Jul 6, 2007, 11:23 PM
  • radek

    Phillip,

    You insert guid manually to tables?

    #8Posted by radek | Jul 7, 2007, 04:08 PM
  • 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.

  • Tony Petruzzi

    @radek

    It's easy to do:

    insert("-", CreateUUID(), 23)

    will create a compatable GUID for MSSQL

    @jim

    If you're use MSSQL: NEVER depend on @@Identity. Use SCOPE_IDENTITY().

  • Jim Collins

    @tony thanks for the tip will do

  • phill.nacelli

    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.

    Cheers..

  • -paul

    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?

    thanks

    #13Posted by -paul | Jul 8, 2007, 01:38 PM
  • Tony Petruzzi

    @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 while the other would do a MAX(PK) afterwards. Now being the DB savvy guy that I am (stop laughing), I know this isn't a guaranteed method. There are some that will claim that setting the isolation level on the driver to serializable would do the trick, but it never sat right with me.

    Anywho. My suggestion is always this: If you're using MSAccess as a database, I see no reason why you couldn't just use the express version of MSSQL 2005 or MSDE.

  • Jorrit

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

    <cfoutput>The primary Key is: #qryIns.pkey#</cfoutput>

    #15Posted by Jorrit | Jul 9, 2007, 04:41 AM
  • Ben Forta

    Tony, I don't believe so.

    Jorrit, no, no conflict. But that code is unneeded in CF8.

    --- Ben

    #16Posted by Ben Forta | Jul 9, 2007, 08:30 AM
  • phill.nacelli

    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.

  • tof

    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.

    Tof

    #18Posted by tof | Jul 9, 2007, 08:23 PM
  • 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.

  • charlie arehart

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

  • John Scott

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

    #21Posted by John Scott | Aug 6, 2007, 11:54 AM
  • feiy

    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:
    <code>
    <cfset sql="select * from some_table where char_col='1'"/>
    <cfquery name="testQuery" datasource="someDs">
    #sql#
    </cfquery>
    </code>

    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'
    </cfquery>

    is a bug of coldfusion 8 cfquery?!

    please help me!thanks!

    #22Posted by feiy | Aug 13, 2007, 11:23 PM
  • jason

    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

    #23Posted by jason | Aug 22, 2007, 10:25 PM
  • 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

    #24Posted by Bobby S | Oct 12, 2007, 02:25 PM
  • 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:

    http://www.forta.com/books/032151548X/

    BTW, Ben, curiously it's not listed in the main page content of your books page (http://www.forta.com/books/), though it is in the left nav bar. Perhaps an oversight, just not updated since the book shipped?

  • 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]

  • 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.

  • Chill

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

    <cfoutput>The primary Key is: #qryIns.pkey#</cfoutput>

    #28Posted by Chill | Jan 27, 2008, 01:29 AM
  • Chill

    Sorry, typo. *inquiry

    #29Posted by Chill | Jan 27, 2008, 01:31 AM
  • Chill

    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.

    #30Posted by Chill | Jan 27, 2008, 08:54 PM
  • 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)
    values
    (#lndiadocno#, #ldnow#, #lndianoofpages#, #lndiasuperdoctypekey#)
    </cfquery>

    <!--- 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# />
    <cfelse>
    <CFSET lndiadocumentkey = #diaresult.GENERATED_KEYS# />
    </cfif>

    #31Posted by Brian Oeding | Apr 11, 2008, 09:56 AM
  • Silviu

    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)

          VALUES
           ('#formData.firstname#', '#formData.lastname #')
       
       </CFQUERY>
    Silviu

    #32Posted by Silviu | Jun 19, 2008, 01:40 PM
  • nesthone

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


    http://www.simpy.com/user/winbill
    http://www.diigo.com/user/lullabot

    #33Posted by nesthone | Aug 14, 2008, 02:57 AM
  • Steve

    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
    (some_field_name)
    VALUES (some_field_value)
    </cfquery>
    <cfoutput>My New Primary KeyID = #my_result.GENERATED_KEY#</cfoutput>

    #34Posted by Steve | Sep 2, 2008, 04:03 PM
  • MP

    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.

    #35Posted by MP | Nov 21, 2008, 01:37 PM
  • 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.

  • Bernie

    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?

    #37Posted by Bernie | Feb 18, 2010, 11:08 AM
  • 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.

    #38Posted by Brian Hendel | May 17, 2010, 11:24 PM
  • Brian Hendel

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

    -Brian

    #39Posted by Brian Hendel | May 17, 2010, 11:36 PM
  • RAMESH

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

    </cfquery>

    #40Posted by RAMESH | May 28, 2010, 12:08 PM
  • Matthew

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

    #41Posted by Matthew | Aug 27, 2010, 08:40 PM
  • bibin

    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?

    #42Posted by bibin | Mar 18, 2013, 12:46 PM