| |
Getting The Autonumber Insert ID
By Raymond Camden
Expert Author
Article Date: 2007-06-19
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
About the Author:
Raymond Camden, ray@camdenfamily.com
http://ray.camdenfamily.com
Raymond Camden is Vice President of Technology for roundpeg, Inc. A long
time ColdFusion user, Raymond has worked on numerous ColdFusion books
and is the creator of many of the most popular ColdFusion community web
sites. He is an Adobe Community Expert, user group manager, and the
proud father of three little bundles of joy.
|
|
