Blog - Akbarsait.com

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

Sep 14, 2008 16899 Views ColdFusion Comments (1)

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.

Related Entries

Using cachedWithin/cachedAfter in CFQUERY with CFQUERYPARAM (Jun 30, 2008)

Comments

1. KeRiCr on Feb 2, 2009 at 10:32 PM

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.