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
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
Agreed.
# Posted By Raymond Camden
| 6/15/07 10:41 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.
# Posted By Jake Munson
| 6/15/07 11:03 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.
# Posted By Raymond Camden
| 6/15/07 11:10 PM
Dam,
That's nice, can't believe I never knew about result in 7
That's nice, can't believe I never knew about result in 7
# Posted By Dale Fraser
| 6/16/07 12:16 AM
Just for reference, in MySQL you use the LAST_INSERT_ID() function.
CF8 lets us dump G.W?!!!!! Best release evar.
# Posted By Justin Lewis
| 6/16/07 2:42 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'?
Ray,
I do not want your secrets but how to you use UUID now in your apps?
Thanks
Andrea
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
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".
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".
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.
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
This is a very cool new feature to CF8.
# Posted By Sam Farmer
| 6/16/07 12:14 PM
@Phillip,
Thats really interesting. I didn't know that. I can see how that can be extremely handy if used correctly in applications.
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
@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.
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
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.
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
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.
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
@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.
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
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.
Our customers were NOT happy with those Invoice #'s.
# Posted By Will Tomlinson
| 6/16/07 8:27 PM
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.
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
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.
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()
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
@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
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
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
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
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
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?
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
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
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.
<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
Interesting. I wonder if it is new drivers? Either way - file a bug report for sure.
http://www.adobe.com/go/wish
http://www.adobe.com/go/wish
# Posted By Raymond Camden
| 8/5/07 6:07 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
# Posted By randy
| 8/5/07 6:42 PM