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
Here I'm inserting a value into the members table of my MySQL database
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
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.
CFML/Web/Salesforce/Agile Enthusiast, Husband & Dad, an avid learner & android fan. Creator of GetCFMLJobs.com who works as a Tech Consultant in Toronto, Canada.