Simple User-Defined Function (UDF) Get ID of New Record With ColdFusion

Posted At: January 7, 2010 11:50 AM

Posted By: Stephen Withington

Related Categories: ColdFusion, SQL

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.

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

Comments (13)

Comments

Nice for CF8. Adobe already took care of this for CF9 the auto inc ID is always generatedKey in the query result.

# Posted By CF9er | 1/7/10 1:32 PM

@CF9er, good to know ... thanks!

# Posted By Stephen Withington | 1/7/10 1:36 PM

You gotta love database standardization :)

Cool UDF.

# Posted By Ben Nadel | 1/7/10 1:38 PM

Ooops, I hadn't refreshed the page; @CF9er, that's awesome - didn't know that either.

# Posted By Ben Nadel | 1/7/10 1:38 PM

@CF9er, i just checked the docs and i don't see that. i know "generated_key" is for MySQL, in fact, that's how i'm getting the id for that db, but i'll have to test 'generatedKey' when i get a chance.

# Posted By Stephen Withington | 1/7/10 1:41 PM

CF9er is correct. It's odd that its not in the docs I also noticed this. Here is is on cook books: http://cookbooks.adobe.com/post_Generated_key___Ge...

And I have a working demo here: http://blog.kukiel.net/2009/12/reading-and-writing...

# Posted By Paul Kukiel | 1/7/10 1:53 PM

@Paul, thanks! i guess i'm late to the party, as usual. this should still work for any CF8ers out there though. :)

# Posted By Stephen Withington | 1/7/10 1:59 PM

What is CF8? This does not compute :)

# Posted By Dan Vega | 1/7/10 2:39 PM

@Dan, yeah, yeah ... i know. you've been putting together some pretty sweet posts lately man.

# Posted By Stephen Withington | 1/7/10 3:31 PM

It looks like you are returning ROWID for oracle. The ROWID is not the actual ID of the new record. If you want to get the ID of the inserted record, you have to do a little more work.

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.

# Posted By Matthew Abbott | 1/26/10 11:56 AM

Doesnt seem to work in 9 either.

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.

# Posted By Matthew Abbott | 1/26/10 12:02 PM

@Matthew,
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.

# Posted By Stephen Withington | 1/27/10 10:55 AM

I personally use the actual ID and not the rowid in my stuff. Here we mostly use stored procs, and in the procs they return the id of the record inserted. The procs are using the Sequence numbers to get the id of the record.

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.

# Posted By Matthew Abbott | 1/27/10 12:43 PM