agreed - pretty cool. Would have been extra nice if they returned the same field regardless of db type. Still cool though.

Agreed.

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.

Well, since only one will exist, you could easily look for each in the struct, and when you find one, you have your ID.

Dam,

That's nice, can't believe I never knew about result in 7

Just for reference, in MySQL you use the LAST_INSERT_ID() function.

CF8 lets us dump G.W?!!!!! Best release evar.

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

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

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.

This is a very cool new feature to CF8.

@Phillip,

Thats really interesting. I didn't know that. I can see how that can be extremely handy if used correctly in applications.

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

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.

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.

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

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.

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.

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

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

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

Here is a good story on MSSQL and ID values:
http://www.sqlservercentral...

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

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?

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.

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.

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

http://www.adobe.com/go/wish

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

access returns USE_REAL_DB_KEY

sql server express doesn't return anything!?

@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

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

Why turn it off? If you don't want to use it, then don't use it.

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.

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?

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.

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

This is definitely a CF8 feature.

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

What driver did they use for the connection? MySQL 3 I bet.

It was :)

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.

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?

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

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

You would want to do

<cfdump var="#result#">

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.

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

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.

Thank you Ray! I've spent about an trying to figure this out. Thanks again for the post!

Thanks Raymond. Very handy! :-)

I realize this is an old post, but if, like me, you were trying to do an "insert ... on duplicate key update", note there appears to be a bug in Coldfusion (version 10). I'm on MySQL, so it might be specific to that, but thought I'd document it here if you happen to be on the same. In the case of an *update*, the generatedkey that is returned will NOT be the autoincrement value. It appears to in fact be a list containing two items: the autoincrement value AND the following value, regardless of if this is an existing row or not. MySQL supports returning the last_insert_id on duplicate key update, so that is probably a better bet until this is fixed, or use listfirst, though test this to ensure that result is actually always correct!

Follow up on that last comment. It's not actually a bug, but based on the fact that an update in MySQL actually auto increments the PK value. Coldfusion is thus returning both the original value (which will be the one in the table) as well as the newly generated (unused) value. I should also add that there is a third option and that is that generatedkey is not returned at all. This happens if all the values being updated match what is already in the db. Then essentially nothing happens and generatedkey is not a part of the var returned. You can work around this by always updating the table, ie. a last_edit field or something like this UPDATE id=LAST_INSERT_ID(id).

Watch out for these options if you are using the returned key to update a different table!