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 (19) |

Print |

Send |

Comments

[Add Comment]

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

It was a very good post indeed. I thoroughly enjoyed reading it in my lunch time. Will surely come and visit this blog more often. Thanks for sharing.

# Posted By marketing companies | 1/14/19 10:51 AM

I invite you to the page where you can read interesting information on similar topics.
http://www.lucknowescorts.co.in/call-girl-in-susha...
http://www.lucknowescorts.co.in/call-girls-service...
Thanks, keep it up.

# Posted By rasmika chopra | 11/5/20 2:51 AM

It is something worth to read just like the available services of CIPD assignment help. Hats off to the writer for drafting such an impressive piece of work. To get such kind of content one should hire an assignment writing service.

# Posted By cipd assignment help | 1/28/21 1:49 PM

Finding the best female escorts in Hyderabad? Book call girls in Delhi 24*7 available. Click the link and get shot, call girl.

# Posted By Delhi Escorts Service | 4/9/21 9:36 AM

No one can beat the beauty of these girls. They will give one of the best time to you.

# Posted By Sexy Raipur Escorts | 8/16/21 4:09 AM

This is truly cautious thing I'm pondering to be all through. Much respected you for your site; I have attracted with it. This is a stunning site.
https://www.eroticangel.in/escorts/bangalore
https://www.eroticangel.in/escorts/chennai

# Posted By Lucknow Escorts Service | 12/2/22 10:04 PM

[Add Comment]