Ben Forta

AdobeStock_455007340

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

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

    Reply
  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

    Reply
  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’)
    UNION
    (SELECT ‘b1’, ‘b2’, ‘b3’, ‘b4’)
    UNION
    (SELECT ‘c1’, ‘c2’, ‘c3’, ‘c4’)

    Reply
  5. Dan Avatar
    Dan

    This is a sweet little tidbit for sure!!

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

    Reply
  7. Phillip Gagnon Avatar
    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,…)

    Reply
  8. radek Avatar
    radek

    Phillip,
    You insert guid manually to tables?

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

    Reply
  10. Tony Petruzzi Avatar
    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().

    Reply
  11. Jim Collins Avatar
    Jim Collins

    @tony thanks for the tip will do

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

    Reply
  13. #NAME? Avatar
    #NAME?

    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

    Reply
  14. Tony Petruzzi Avatar
    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 w

    Reply
  15. Jorrit Avatar
    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>

    Reply
  16. Ben Forta Avatar
    Ben Forta

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

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

    Reply
  18. tof Avatar
    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

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

    Reply
  20. charlie arehart Avatar
    charlie arehart

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

    Reply
  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?

    Reply
  22. feiy Avatar
    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!

    Reply
  23. jason Avatar
    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

    Reply
  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

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

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

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

    Reply
  28. Chill Avatar
    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>

    Reply
  29. Chill Avatar
    Chill

    Sorry, typo. *inquiry

    Reply
  30. Chill Avatar
    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.

    Reply
  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)
    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>

    Reply
  32. Silviu Avatar
    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

    Reply
  33. nesthone Avatar
    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

    Reply
  34. Steve Avatar
    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>

    Reply
  35. MP Avatar
    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.

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

    Reply
  37. Bernie Avatar
    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?

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

    Reply
  39. Brian Hendel Avatar
    Brian Hendel

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

    Reply
  40. RAMESH Avatar
    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>

    Reply
  41. Matthew Avatar
    Matthew

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

    Reply
  42. bibin Avatar
    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?

    Reply

Leave a Reply