Simple User-Defined Function (UDF) Get ID of New Record With ColdFusion
I've been doing more development for applications that could be used with a variety of databases. So recently I whipped up this little user-defined function (UDF) to grab the identity (ID) of a row that was inserted into a database that I thought other developers might find useful. You'll need at least ColdFusion 8 to use this little puppy.
<!---
Pass me a CFQuery result struct and I'll try my best to get the identity (ID) of a row inserted into a database.
Works with SQL Server, Oracle, Sybase, Informix, and MySQL.
Requires ColdFusion 8+
@param qResult query result structure (Required)
@return Returns any
@author Stephen Withington (steve@stephenwithington.com)
@version 0, January 7, 2010
--->
<cffunction name="getIdentity" returntype="any" access="remote" output="false">
<cfargument name="qResult" type="struct" required="true" />
<cfscript>
var local = structNew();
local.identity = "unknown";
if ( structKeyExists(arguments, "qResult") ) {
// SQL Server
if ( structKeyExists(arguments.qResult, "IDENTITYCOL") ) {
local.identity = arguments.qResult.identitycol;
} else
// Oracle
if ( structKeyExists(arguments.qResult, "ROWID") ) {
local.identity = arguments.qResult.rowid;
} else
// Sybase
if ( structKeyExists(arguments.qResult, "SYB_IDENTITY") ) {
local.identity = arguments.qResult.syb_identity;
} else
// Informix
if ( structKeyExists(arguments.qResult, "SERIAL_COL") ) {
local.identity = arguments.qResult.serial_col;
} else
// MySQL
if ( structKeyExists(arguments.qResult, "GENERATED_KEY") ) {
local.identity = arguments.qResult.generated_key;
};
};
return local.identity;
</cfscript>
</cffunction>
Example Usage
<cffunction name="addName" access="public" returntype="any" output="false">
<cfargument name="name" type="string" required="true" />
<cfscript>
var local = structNew();
var rs = "";
</cfscript>
<cfquery datasource="#getDatasource()#" username="#getDBUsername()#" password="#getDBPassword()#" name="rs" result="local.rsResult">
INSERT INTO tblNames ([Name])
VALUES (<cfqueryparam value="#arguments.name#" cfsqltype="cf_sql_varchar" maxlength="50" />)
</cfquery>
<cfscript>
local.identity = getIdentity(local.rsResult);
return local.identity;
</cfscript>
</cffunction>
Hope this helps someone else other than me!
