Problem

After inserting a record into a database the application needs the id of the record that was just inserted.

Solution

ColdFusion provides wealth of information in the result variable of a <cfquery> tag.

Detailed explanation

Often times an application needs to have the id of a record just interted into a database.  ColdFusion provides an easy way to get this id as well as a host of other information by specifying a variable name in the result attribute of the <cfquery> tag.  The variable that is returned is a structure. The result attributes is available in ColdFusion 7, however the information returned about the id is only available in ColdFusion 8 and later.

The following <cfquery> code specifies the result attribute:

<cfquery datasource="#dsn#" result="stResult">
INSERT INTO
tbl_Person
(
PersonName,
PersonEmail
)

VALUES
(
<cfqueryparam value="#PersonName#" cfsqltype="cf_sql_varchar"
/>,
<cfqueryparam value="#PersonEmail#" cfsqltype="cf_sql_varchar"
/>
)
</cfquery>

After the query executes the stResult variable will hold details about the query.   The general information keys are sql, recordcount, cached, sqlparameters, columnList and ExecutionTime.  The are available in ColdFusion 7.  With ColdFusion 8 additional fields were added to return the id of the last inserted record.  Depending on what database the application is utilizing the database specific field will be populated.

IDENTITYCOL – SQL Server

ROWID – Oracle (This is the ROWID not the primary key. )

SYB_IDENTITY - Sybase

SERIAL_COL – Informix

GENERATED_KEY – MySQL

The following code will output the ID of the last inserted record assuming the application is using SQL Server.

<cfoutput>
#stResult['IDENTITYCOL']#
</cfoutput>

A very simple and quick way to get the id of the last inserted record provided by ColdFusion

source: Coldfusion Cookbook

I’m always in between both worlds (coldfusion & php). I must say I absolutely do love Coldfusion. A lot of people knock Cold Fusion, but let me tell you how much easier it is to write scripts with it. Mind you, this discussion can go either way. For what I do, Coldfusion makes my life a lot easier. It’s as simple as just writing <cfoutput> around your data to display all of your data. Where as with PHP you have to put loops and do a lot of extra work. I’m sure many people would argue with me, which is fine, but I honestly am not a big fan of PHP. I don’t mind coding in it, and I love that there is a lot of open source for the language, however, it CAN be very tedious and annoying. On top of that, some of the error messages aren’t descriptive enough and can sometimes be very confusing.

One issue I found extremely frustrating was that I couldn’t use CFdump. For those of you who don’t know Coldfusion CFdump is one of the handiest tools of them all. Basically, it will dump out all of your data from a form or table so you can see all of your data. Displaying the data helps me in a lot of different scenarios. A few time’s my insert statement was throwing errors so I did a CFdump to notice that the variable was undefined because I didn’t put anything into it (this was way in the beginning of my programming days promise!). Sometimes we are so wrapped up in the overall structure of our program that we make little mistakes and functions like CFdump are a huge help.

So to add to the interesting entry… I found a program that allows you to do CFdump in php! I tried it out and it works great! I am so exited! Now in PHP you have the option of doing print_r(), however it is no where as good as cfdump is for debugging.

dBug is a small PHP class that completely duplicates the cftag from coldfusion but in php. It’s very simple and easy to use also!

include_once("dBug.php");
new dBug($myVariable);

Go take a look and let me know what you think!!! http://dbug.ospinto.com