Raymond Camden's ColdFusion Blog: ColdFusion 8: Getting the autonumber insert ID

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:

view plain print about

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

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

Now when I dump the result, I get:

Pretty handy!

This entry was posted on Friday, June 15, 2007 at 9:44 PM. It was filed in the following categories: ColdFusion. It has been viewed 5,894 times and has 47 comments.

TweetBacks

Comments

[Add Comment] [Subscribe to Comments]

Comment 1 written by Scott P on 15 June 2007, at 10:24 PM

agreed - pretty cool. Would have been extra nice if they returned the same field regardless of db type. Still cool though.
Comment 2 written by Raymond Camden on 15 June 2007, at 10:41 PM

Agreed.
Comment 3 written by Jake Munson on 15 June 2007, at 11:03 PM

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.
Comment 4 written by Raymond Camden on 15 June 2007, at 11:10 PM

Well, since only one will exist, you could easily look for each in the struct, and when you find one, you have your ID.
Comment 5 written by Dale Fraser on 16 June 2007, at 12:16 AM

Dam,

That's nice, can't believe I never knew about result in 7
Comment 6 written by Barney on 16 June 2007, at 12:34 AM

Just for reference, in MySQL you use the LAST_INSERT_ID() function.
Comment 7 written by Justin Lewis on 16 June 2007, at 2:42 AM

CF8 lets us dump G.W?!!!!! Best release evar.
Comment 8 written by Adam on 16 June 2007, at 7:44 AM

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'?
Comment 9 written by andrea on 16 June 2007, at 9:31 AM

Ray,

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

Thanks

Andrea
Comment 10 written by Barney on 16 June 2007, at 10:38 AM

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".
Comment 11 written by Phillip Senn on 16 June 2007, at 12:06 PM

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.
Comment 12 written by Sam Farmer on 16 June 2007, at 12:14 PM

This is a very cool new feature to CF8.
Comment 13 written by Jeff Coughlin on 16 June 2007, at 1:18 PM

@Phillip,

Thats really interesting. I didn't know that. I can see how that can be extremely handy if used correctly in applications.
Comment 14 written by Tony G on 16 June 2007, at 4:07 PM

@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.
Comment 15 written by Phillip Senn on 16 June 2007, at 4:29 PM

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.
Comment 16 written by Phillip Senn on 16 June 2007, at 4:35 PM

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.
Comment 17 written by Jake Munson on 16 June 2007, at 6:32 PM

@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.
Comment 18 written by Will Tomlinson on 16 June 2007, at 8:27 PM

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.
Comment 19 written by Mike Rankin on 17 June 2007, at 7:44 AM

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.
Comment 20 written by Cutter on 18 June 2007, at 8:37 AM

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.
Comment 21 written by Andrew Deren on 18 June 2007, at 9:56 AM

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()
Comment 22 written by Tom Mollerus on 18 June 2007, at 10:21 AM

@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.
Comment 23 written by David on 18 June 2007, at 6:45 PM

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
Comment 24 written by randy on 19 June 2007, at 6:13 PM

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
Comment 25 written by Bill Cupps on 5 August 2007, at 5:15 PM

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?
Comment 26 written by Raymond Camden on 5 August 2007, at 5:37 PM

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.
Comment 27 written by Bill Cupps on 5 August 2007, at 5:46 PM

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.
Comment 28 written by Raymond Camden on 5 August 2007, at 6:07 PM

Interesting. I wonder if it is new drivers? Either way - file a bug report for sure.

http://www.adobe.com/go/wish
Comment 29 written by randy on 5 August 2007, at 6:42 PM

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
Comment 30 written by Scott P on 7 September 2007, at 5:33 PM

access returns USE_REAL_DB_KEY
Comment 31 written by Laurent on 12 September 2008, at 6:22 AM

sql server express doesn't return anything!?
Comment 32 written by Dale Fraser on 12 September 2008, at 6:44 AM

@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
Comment 33 written by Chris Luksha on 19 February 2009, at 10:45 AM

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
Comment 34 written by Raymond Camden on 19 February 2009, at 10:47 AM

Why turn it off? If you don't want to use it, then don't use it.
Comment 35 written by Chris Luksha on 19 February 2009, at 11:16 AM

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.
Comment 36 written by Raymond Camden on 19 February 2009, at 11:23 AM

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?
Comment 37 written by Chris Luksha on 19 February 2009, at 11:36 AM

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.
Comment 38 written by Raymond Camden on 19 February 2009, at 11:40 AM

Check your server version. <cfdump var="#server#">

This is definitely a CF8 feature.
Comment 39 written by Chris Luksha on 19 February 2009, at 11:59 AM

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...
Comment 40 written by Raymond Camden on 19 February 2009, at 12:04 PM

What driver did they use for the connection? MySQL 3 I bet.
Comment 41 written by Chris Luksha on 19 February 2009, at 1:22 PM

It was :)
Comment 42 written by Raymond Camden on 19 February 2009, at 1:34 PM

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.
Comment 43 written by Nery R. Gonzalez on 25 March 2009, at 5:09 PM

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?
Comment 44 written by Raymond Camden on 26 March 2009, at 10:31 AM

Did you use the RESULT attribute? If your query was so:

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

You would want to do

<cfdump var="#result#">
Comment 45 written by dcs on 6 April 2009, at 5:27 PM

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.
Comment 46 written by James Holmes on 5 May 2009, at 10:48 PM

"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.
Comment 47 written by dcs on 6 May 2009, at 7:14 AM

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.