Using Result attribute in CFQuery to retrieve Auto-Generated Key on Insert in ColdFusion 8
Posted : Sep 14, 2008 3:54 AM by Akbarsait
Tags:
ColdFusion
ColdFusion 8 added the functionality of retrieving the Auto-Generated key ID in the result structure of the CFQuery after the insert operation which is specific to each databases used.
Database Specific Auto-Generated Keys:
SQL Server: IDENTITYCOL
MySQL: GENERATED_KEY
Sybase: SYB_IDENTITY
Informix: SERIAL_COL
Oracle: ROWID
Here I'm inserting a value into the members table of my MySQL database
Insert Into members (name) values ('Akbar')
</cfquery>
Here is the Result structure when I dump it:
This is really cool 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
Insert Into members (name) values ('Govindh')
</cfquery>
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.
