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:
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')

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


<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.


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.

About Me

CFML/Web/Cloud/Data/Agile Enthusiast, Husband & Dad, an avid learner & android fan who works as a Tech Consultant in Toronto, Canada.!
Get Your Next CFML Job!
ColdFusion Tuts
ColdFusion Tutorials & Resources


Adobe Agile AppCore Creator BlogCFC Books Bootstrap CFBuilder CFEclipse CFML Chennai CFUG Chrome Extensions ColdFusion Google IIS India Learning MySQL Railo Subversion Tools & Utilities Web Windows 7


2021 2018 2017 2016 2014 2013 2012 2011 2010 2009 2008


Akbarsait © 2008 - 2023 | Hosting provided by Vivio Technologies

Home | Blog | Sitemap | RSS Feed