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

  Sep 14, 2008     15205 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.

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.

About Me

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.

GetCFMLJobs.com!
Get Your Next CFML Job!
ColdFusion Tuts
ColdFusion Tutorials & Resources


Categories

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


Archives

2018 2017 2016 2014 2013 2012 2011 2010 2009 2008

Search


Akbarsait © 2008 - 2019 | Hosting provided by Vivio Technologies

Home | Blog | Sitemap | RSS Feed