ColdFusion 8: Getting the autonumber insert ID

I love this. So I assume most folks knew about the result attribute added to cfquery in ColdFusion 7. If not - you are missing out. It returns quite a bit of good information about your query. But best of all - it was updated in ColdFusion 8.

Have you ever inserted a record into a table that used an autonumber primary key? I bet you wanted a nice way to get the value of that ID? In SQL Server it is possible with a bit of extra SQL. MySQL - I'm not so sure. But as you can imagine - any solution you pick won't be very cross platform. This is the main reason I use UUIDs in my OS apps.

The good news is that in ColdFusion 8, the result struct will contain a new key that contains the ID of the row you just inserted. The key is different for each support DB. SQL Server returns a key named IDENTITYCOL. MySQL returns GENERATED_KEY. Other database types have their own names - but lets focus on MySQL. Here is the query I used:

<cfquery datasource="test" result="result">
insert into test(name)
values('George Bush')
</cfquery>

Now when I dump the result, I get:

Pretty handy!

Comments (47) Jun 15, 2007 9:44 PM Views: 4786

Comments

[Add Comment]
Scott P's Gravatar agreed - pretty cool. Would have been extra nice if they returned the same field regardless of db type. Still cool though.
# Posted By Scott P | 6/15/07 10:24 PM
Raymond Camden's Gravatar Agreed.
# Posted By Raymond Camden | 6/15/07 10:41 PM
Jake Munson's Gravatar I agree. It's really too bad that they decided to name them differently per database. It's very nice anyway, but it would have been the ultimate solution to this problem if you didn't have to care what DB you were using.
# Posted By Jake Munson | 6/15/07 11:03 PM
Raymond Camden's Gravatar Well, since only one will exist, you could easily look for each in the struct, and when you find one, you have your ID.
# Posted By Raymond Camden | 6/15/07 11:10 PM
Dale Fraser's Gravatar Dam,

That's nice, can't believe I never knew about result in 7
# Posted By Dale Fraser | 6/16/07 12:16 AM
Barney's Gravatar Just for reference, in MySQL you use the LAST_INSERT_ID() function.
# Posted By Barney | 6/16/07 12:34 AM
Justin Lewis's Gravatar CF8 lets us dump G.W?!!!!! Best release evar.
# Posted By Justin Lewis | 6/16/07 2:42 AM
Adam's Gravatar Just trying to think of another way to get the ID: Wouldn't wrapping the insert and then a SELECT MAX(id) in a <CFTransaction> accomplish the same thing? Or would the DBMS allow this and other transactions to get into a race condition? Maybe it differs between DBMS'?
# Posted By Adam | 6/16/07 7:44 AM
andrea's Gravatar Ray,

I do not want your secrets but how to you use UUID now in your apps?

Thanks

Andrea
# Posted By andrea | 6/16/07 9:31 AM
Barney's Gravatar Adam,

Yes, that'd work, but it's pretty inefficient, because you have to use serializable isolation level (which single-threads access to resources). Much better to use metadata from the INSERT statement itself. Not only is it faster, it doesn't drop your concurrency support to "no concurrency".
# Posted By Barney | 6/16/07 10:38 AM
Phillip Senn's Gravatar In SQL Server, there's a subtle difference between identity() and scope_identity().
Identity() will simply give you the primary key of the last row inserted, whereas scope_identity() will give you the last primary key YOU inserted.
I wonder which one the developer's used.
# Posted By Phillip Senn | 6/16/07 12:06 PM
Sam Farmer's Gravatar This is a very cool new feature to CF8.
# Posted By Sam Farmer | 6/16/07 12:14 PM
Jeff Coughlin's Gravatar @Phillip,

Thats really interesting. I didn't know that. I can see how that can be extremely handy if used correctly in applications.
# Posted By Jeff Coughlin | 6/16/07 1:18 PM
Tony G's Gravatar @Andrea,

I think Ray means that, instead of using an automatically incremented ID number as a primary key, he sets the ID himself by using CreateUUID() and therefore doesn't need to worry about retrieving the ID of the last inserted record from the database.
# Posted By Tony G | 6/16/07 4:07 PM
Phillip Senn's Gravatar There are advantages to using a GUID over an integer as well.
Suppose all your tables have a primary key starting with 1.
So during your development, you see the following values in a row:

1, 1, 1, 1.

I'm laughing just typing that!
Anyway, with a GUID, you immediately see, "Oh wait, there's no use to looking at the values of the primary keys 'cause they're too hard to look."
That actually is a good thing because the less you have to think about the primary key the better. It's just a way to identity a row, and for the computer to keep track of relationships. Don't try to make it mean anything else.

One way to solve the 1,1,1,1 problem is to start each table with it's own identity seed. But then you start thinking too hard about giving meaning to the primary key.
It's just an identifier. Don't even display it.
# Posted By Phillip Senn | 6/16/07 4:29 PM
Phillip Senn's Gravatar Here's what I meant by the 1,1,1,1 example:

You have a row in tblOrderDetail with the following values:
OrderDetailID: 1
OrderHeaderID: 1
ItemID: 1
SpecialInstructionsID: 1

During development, this is a very likely scenario.
So you could create OrderDetailID Identity(1000,1), OrderHeaderID Identity(100,1), ItemID Identity(10000,1), SpecialInstructionsID Identity(10,1).
But then again, it's best to just think of the primary key as a concept, a pointer if you will, and not try to make sense of it's value.
# Posted By Phillip Senn | 6/16/07 4:35 PM
Jake Munson's Gravatar @Philip,

You got it a little wrong. @@identity will return the most recent ID that YOU created in your session, not from anybody else. The problem is that you might have created another one you didn't think of. For example, if your table has a trigger that inserts a record into a separate logging table, @@identity will return the ID from that logging table, not the one from the original insert. scope_identity() avoids this problem by returning the most recent ID from your current scope.
# Posted By Jake Munson | 6/16/07 6:32 PM
Will Tomlinson's Gravatar I always loved creating my own GUID's for PK's. Except I screwed up and used them for invoice ID's. :)

Our customers were NOT happy with those Invoice #'s.
# Posted By Will Tomlinson | 6/16/07 8:27 PM
Mike Rankin's Gravatar With MSSQL, I think it's just easier to always use scope_identity() and just forget about the other two methods. It will always give you what you want when working with cf. Can anybody think of a reason why you would want to use either @@identity or table_ident?

I have to admit, I prefer working with integers instead of uuids, especially when you testing or in development mode.
# Posted By Mike Rankin | 6/17/07 7:44 AM
Cutter's Gravatar The advantage of using a GUID is that it is a unique value to tie into. The disadvantage is that it is a 36(?) character string that the database must index, which can slow down queries and takes up a lot more disk space. Each approach has it's pros and cons, so you have to use what makes sense for your situation/application.
# Posted By Cutter | 6/18/07 8:37 AM
Andrew Deren's Gravatar Also, GUIDs are a pain to pass by url.
Having Product.cfm?id=3 is easier to remember and more pleasing than Product.cfm?id=ABCDEF....

Do you know which method cf8 will use to retrieve this value? I hope it's scope_identity()
# Posted By Andrew Deren | 6/18/07 9:56 AM
Tom Mollerus's Gravatar @Andrew: Having GUIDs in your URLs may not be as "pretty", but they're also a bit more secure, since no one can guess what a GUID is. However, if you're using integer ID's, someone could start incrementing the ID in the URL to see what information they can get access to.
# Posted By Tom Mollerus | 6/18/07 10:21 AM
David's Gravatar This seems as good a place as any to ask my question:

I've been thinking of using GUIDS in an application, where I'm thinking that would be advantageious - user information, for example, not the afore mentioned "productID" example (where I can see simple integers working).

Thing is, I can't pull the trigger on it - I just feel that somewhere along the line, it's going to bite me! Can anyone convince me otherwise, or point to some articles online?

I would most likely use createUUID(), for uniqueness. I'm worried about indexing, sizes of indexes, and general ease of use.

Any thoughts?

Cheers,

David
# Posted By David | 6/18/07 6:45 PM
randy's Gravatar Here is a good story on MSSQL and ID values:
http://www.sqlservercentral.com/columnists/awarren...

Here is one issue/problem we have seen in CF8:
We have a table with a trigger that runs on insert - select @@Identity as newID

In our CFC we do the insert and when we try to ref insertqueryname.newID we get an error - the code runs fine on our CF7 production server.

As for url passing id values - we use both db/table id values and sometimes we use guids - one thing you can do when using simple ids like (1,2,3 auto increm db keys) is pass that value and a hash of the value/key in/on the url then check/compare the two values as first thing on the new page - if they match run the code if they do not send them back to that last page
# Posted By randy | 6/19/07 6:13 PM
Bill Cupps's Gravatar This would have been even nicer, if they didn't break something that has worked since CF4. In previous versions you could do a cfquery, and name it just like a select query, and then on the database add an insert trigger like this:

SELECT accountId FROM INSERTED

and it used to work. As of CF8 this stopped dead in it's tracks. Any ideas how to make this work, without re-doing every application I've used this on?
# Posted By Bill Cupps | 8/5/07 5:15 PM
Raymond Camden's Gravatar I may not get what you mean. How do you name a query like a select query? Query names, in CF, have nothing to do with what they do. Maybe you could post a little code snippet? To be honest I'd be shocked if this really was broken as Adobe is super anal about backwards compatability type issues.
# Posted By Raymond Camden | 8/5/07 5:37 PM
Bill Cupps's Gravatar You could do this:

<cfquery datasource="dsn" name="q1">
INSERT INTO accounts
(accountName, balance)
VALUES
('My Account', 123.00)
</cfquery>

and then on the table accounts you would have an insert trigger like this:
SELECT accountId FROM INSERTED

after the above query would run you could reference q1.accountId, this stopped working on CF8.

This sounds like the same problem the previous poster, Randy, is having.
# Posted By Bill Cupps | 8/5/07 5:46 PM
Raymond Camden's Gravatar Interesting. I wonder if it is new drivers? Either way - file a bug report for sure.

http://www.adobe.com/go/wish
# Posted By Raymond Camden | 8/5/07 6:07 PM
randy's Gravatar You are correct - Ben has a post about this and CF8 has new built in functions that can replace this process but it does require changing your code so while it works not idea
# Posted By randy | 8/5/07 6:42 PM
Scott P's Gravatar access returns USE_REAL_DB_KEY
# Posted By Scott P | 9/7/07 5:33 PM
Laurent's Gravatar sql server express doesn't return anything!?
# Posted By Laurent | 9/12/08 6:22 AM
Dale Fraser's Gravatar @Laurent,

Yes it does, used it many times. Same as full MS SQL. Make sure the table has an identity column and that you use the result attribute
# Posted By Dale Fraser | 9/12/08 6:44 AM
Chris Luksha's Gravatar Ray - can this be turned off on the server in some way?

I am using a process flawlessly on my local machine - but when I try it on my shared server I can dump the trap and get the following: Element GENERATED_KEY is undefined in RINSERTBUS.

My query sets the return var = RINSERTBUS

Any ideas how I might 'turn it on'?

Thanks,
Chris
# Posted By Chris Luksha | 2/19/09 10:45 AM
Raymond Camden's Gravatar Why turn it off? If you don't want to use it, then don't use it.
# Posted By Raymond Camden | 2/19/09 10:47 AM
Chris Luksha's Gravatar I think you misread - I don't want to turn it off I want to turn it ON. It seems that it just doesn't work when I put the page live.
# Posted By Chris Luksha | 2/19/09 11:16 AM
Raymond Camden's Gravatar Oh - don't forget GENERATED_KEY is just for one dbtype (mysql I think). Are you using the same db type? If you dunp RINSERTBUS, what do you see?
# Posted By Raymond Camden | 2/19/09 11:23 AM
Chris Luksha's Gravatar I am using MySQL - in fact I am using the exact same mySQL db - from the live server ( I know bad boy)

dumping gets me :
CACHED
EXECUTIONTIME
RECORDCOUNT
SQL
SQLPARAMETERS

Is this cf 7 maybe? Or maybe the mysql driver is wrong? I am grasping at straws -but it seems reasonable to think the live server is stuck on cf7 maybe.
# Posted By Chris Luksha | 2/19/09 11:36 AM
Raymond Camden's Gravatar Check your server version. <cfdump var="#server#">

This is definitely a CF8 feature.
# Posted By Raymond Camden | 2/19/09 11:40 AM
Chris Luksha's Gravatar I was trying that immediately after I responded the last time

My server is 8,0,1,195765

I would guess that I can use what we need - but the cfdump is not returning any unique key identifier.

I will email support next and see if they know of anything - but I am stumped. It should work...
# Posted By Chris Luksha | 2/19/09 11:59 AM
Raymond Camden's Gravatar What driver did they use for the connection? MySQL 3 I bet.
# Posted By Raymond Camden | 2/19/09 12:04 PM
Chris Luksha's Gravatar It was :)
# Posted By Chris Luksha | 2/19/09 1:22 PM
Raymond Camden's Gravatar Cool. Adobe should just delete the MySQL 3 driver. It causes more trouble then you can imagine. I'm always running into people using my OS stuff and accidentally running the wrong driver.
# Posted By Raymond Camden | 2/19/09 1:34 PM
Nery R. Gonzalez's Gravatar Is this feature avaible with Oracle. I have an insert statament executing and inmediately after I make a cfdump and I get the message error : query (name) is not defined

Any suggestions?
# Posted By Nery R. Gonzalez | 3/25/09 5:09 PM
Raymond Camden's Gravatar Did you use the RESULT attribute? If your query was so:

<cfquery name="foo" result="result">

You would want to do

<cfdump var="#result#">
# Posted By Raymond Camden | 3/26/09 10:31 AM
dcs's Gravatar Oracle returns the ROWID of the inserted record rather than a sequence value (if that is how one is generating keys for one's table). So I guess one might use the ROWID (which is a pseudo-column in Oracle, all tables have it) to get the primary key that was generated by the insert, e.g.:

<cfquery name="myinsert" datasource="#myDSN#" result="myresult">
INSERT INTO mytable (mycolumn)
VALUES ('Faber')
</cfquery>

<cfquery name="getmykey" datasource="#myDSN#">
SELECT my_id FROM mytable
WHERE rowid = '#myresult.rowid#'
</cfquery>

Hope this helps. I'm not sure I would use this method myself - I would probably just query sequence.currval - but it's there if you need it.
# Posted By dcs | 4/6/09 5:27 PM
James Holmes's Gravatar "I would probably just query sequence.currval"

If someone inserts and commits while your transaction is still running, you'll get the wrong ID that way. The ROWID is the best way to get the right value.
# Posted By James Holmes | 5/5/09 10:48 PM
dcs's Gravatar sequence.currval is session-specific so there's no reason that the value should be affected by someone else inserting and committing in between your insert and your call to sequence.currval. In fact, sequence.currval will only exist for your session if it has been preceded by a call to sequence.nextval.
# Posted By dcs | 5/6/09 7:14 AM
[Add Comment]