Using Result attribute in CFQuery to retrieve Auto-Generated Key on Insert in ColdFusion 8
ColdFusion 8 added the functionality of retrieving the auto-generated key ID in the result structure of the CFQuery after the specific insert operation to each database used.
Database Specific Auto-Generated Keys:
SQL Server: IDENTITYCOL
Here I’m inserting a value into the members table of my MySQL database.
<cfquery datasource=" MySQL_ChennaiCFUG " result="mysqlresult">
Insert Into members (name) values ('Akbar')
Here is the Result structure when I dump it:
This is extremely refreshing thing from ColdFusion 8 which reduces the developer time on writing another query to fetch the newly created row ID value.
Note: I have created another datasource name for the same database but I have used ODBC socket as datasource driver.
<cfquery datasource="MySQL_ODBC_ChennaiCFUG" result="mysqlodbcresult">
Insert Into members (name) values ('Govindh')
The cfdump result for the above query:
In the above dump GENERATED_KEY is not available but other items in the structure are available. Anyone have any idea about this scenario please comment.
According to the livedocs, "The result variable of INSERT queries contains a key-value pair that is the automatically generated ID of the inserted row; this is available only for databases that support this feature." This is just a guess, but by using the proprietary database driver in your first datasource, you're getting access to this support. By using generic ODBC in your second datasource, you're losing this support.
(Comments migrated from previous blog engine.)