Sep 14

Using Result attribute in CFQuery to retrieve Auto-Generated Key on Insert in ColdFusion 8

Posted At : September 14, 2008 3:54 AM | Posted By : Akbarsait
Related Categories: 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

<cfquery datasource=" MySQL_ChennaiCFUG " result="mysqlresult">
Insert Into members (name) values ('Akbar')
</cfquery>

Here is the Result structure when I dump it:

MySQLResult_WithAutoGeneratedKey

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

MySQLODBCConnection

<cfquery datasource="MySQL_ODBC_ChennaiCFUG" result="mysqlodbcresult">
Insert Into members (name) values ('Govindh')
</cfquery>

The cfdump result for the above query:

MySQLResult_WithoutAutoGeneratedKey

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.