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!
Cool UDF.
And I have a working demo here: http://blog.kukiel.net/2009/12/reading-and-writing...
SELECT {key}
FROM {tablename}
WHERE rowid = {ROWID}
key is the primary key you want, for example 'ID'. You also will need to pass in the tablename and the rowid.
I havent tested this on CF9.
I inserted a record, and the ROWID returned was
AAAqIQAAmAAAfclABT
If you then pass that into the query i posted in my previous comment, it will then return the id.
Thanks for letting me know. I typically use MS SQL or MySQL myself and don't claim to be an Oracle man.
So is it fair to say that most Oracle users would expect the 'key' to be returned vs. the 'rowid'? If so, I could tweak the code to do that.
In the query i showed you, you will need to know the primary key column of the table.
Seems with oracle it isnt as easy. I am by no means an oracle man either.
http://www.lucknowescorts.co.in/call-girl-in-susha...
http://www.lucknowescorts.co.in/call-girls-service...
Thanks, keep it up.
https://www.eroticangel.in/escorts/bangalore
https://www.eroticangel.in/escorts/chennai