ColdFusion Console Display Terminal Style

One thing I loved about ColdFusion Builder was the console view. But I recently switched from ColdFusion Builder to Sublime Text 2, which doesn’t have that feature. So I needed a solution.

It seemed that running ColdFusion via a local service wouldn’t do the job, but running it from the command line would.

One approach was a simple batch file located on my desktop:

\ColdFusion9\runtime\bin\jrun -start coldfusion

You would then see a scrolling console display and press Crtl-C to stop ColdFusion. It was kind of annoying you’d have to press Y and enter afterwards too when prompting if you wanted to abort the batch file. I would always end up re-sizing the window as well to fit in with my email, Skype and TweetDeck on one dedicated screen.

On option I took note of after installing the Sublime Terminal plugin, was to run it inside Windows PowerShell with a default set of options. This works great and you don’t have to confirm aborting the batch file. I can even label the window.

I have the shortcut link and settings file in a Gist at


#coldfusion-2, #console, #powershell, #terminal

DateFormat() Add Day Shortcut

I ran into some old code that adds 7 days to a date and spits it out in a DateFormat().

Normally you’d do this:

dateFormat( dateAdd( 'd', 7, now() ), 'mm/dd/yyyy' );

But the code I ran into doesn’t use the dateAdd() method. It just simply adds days.

dateFormat( now() + 7, 'mm/dd/yyyy' );

Though I can’t find this shortcut documented anywhere it seems to be working well with at least ColdFusion v8 and v9.

#coldfusion-2, #dateadd, #dateformat, #shortcut

Getting the MS SQL Identity ID With a Complex UPDATE / INSERT Statement

As I posted in my blog entry MSSQL Update/Insert Based Upon Existing Record, it is better to use “IF @@ROWCOUNT=0” rather than “IF EXISTS ()” when updating or inserting a record based upon existence. This is because the preferred method only does a table/index scan once, while the later will do it twice.

What if you need the Identity Column ID whether the record is updated or inserted? Normally in ColdFusion you can use “queryResult[“GENERATEDKEY”]”, however what you will find is this variable only works on simple insert statements. If you try to use @@IDENTITY or SCOPE_IDENTITY, you will find it only returns the Identity Column ID when the insert is triggered.

Introducing MS SQL’s OUTPUT clause, introduced in MS SQL 2005. We can use the OUTPUT clause to return the Identity Column ID(s).

When we add this to the INSERT and UPDATE clauses, the problem we run into is that during an insert it will return an empty set and another set containing the Identity Column ID that was inserted. Therefore we use a temporary table to help us with this.

Now with a temporary table, we introduce back in the second table scan. However this table scan will have very limited rows, if not just one. Plus the temporary table will not need disk access. So the second table scan is of no consequence.

So with all this in mind here’s an example that returns the Identity column that was either inserted or updated. Keep in mind that multiple IDs will be returned as separate rows if more than one table row was updated.

<cfquery name="local.qSetCart">			
	    cartID INT NOT NULL
		dateModified = GETDATE(),
		isGift = <cfqueryparam value="#arguments.isGift#" cfsqltype="cf_sql_bit">
		userID = <cfqueryparam value="#arguments.userID#" cfsqltype="cf_sql_integer">
			INTO UserCart ( userID, isGift)
			<cfqueryparam value="#arguments.userID#" cfsqltype="cf_sql_integer">,
			<cfqueryparam value="#arguments.isGift#" cfsqltype="cf_sql_bit">
<cfreturn local.qSetCart.cartID>

#coldfusion-2, #identity, #sql

“Element GENERATEDKEY is undefined…” after ColdFusion SQL Statement

This is a followup post to “Returning IDENTITY ID via ColdFusion 9′s CFQuery“.

Today I created an Insert statement that I needed the new identity value returned. It’s pretty simple, calling the result.generatedkey as such:

<cfquery result="myResult">
    INSERT INTO users( username )
    VALUES( 'test' )
<cfreturn myResult["GENERATEDKEY"]>

The above code would normally result in the new identity value created for that record. However I received a “GENERATEDKEY is undefined…” error. Not sure what to think I dumped out the myResult variable, put the query into a file of itself and even tried using myResult.IDENTITY instead to no avail. After taking awhile figuring this out and ended up going home for the day and trying there again after a couple of hours. All of a sudden the standalone test worked!

So I’m thinking, okay this has to be some setting somewhere, and I was right. I narrowed it down to a query that runs before it during the login process for my website.


SET COL1 = 'myVal'

Notice that I didn’t set “SET NOCOUNT OFF”. There lies the issue. Apparently when you set NOCOUNT off and keep it off, ColdFusion doesn’t retrieve the new Identity value. So the following code fixed my issue:


SET COL1 = 'myVal'


#cfquery, #coldfusion-2, #generatedkey, #identity, #nocount, #sql

ColdFusion CFQuery Zero Records and the “Element x is undefined in x” Error

When you run a CFQuery tag in ColdFusion you “will always” get a query object returned whether or not any results where found. But recently, after running a complicated query in ColdFusion 9.0.1, I found this to not be true. When there where results, everything worked as expected. However, when there where no results then I received a “Element x is undefined in x.” error when calling the CFQuery’s name variable. Here’s a simplified example of my code:

<cfquery name="local.products">
SELECT P.product
FROM Products P
    SELECT PC.productID, MIN( PC.price ) )
    FROM ProductsChildren PC
    GROUP BY PC.productID
) price ON P.productID = price.productID
<cfdump var="local.products">

After banging my head and doing some research I ran through this thread that helped me resolve the issue: . They mention a couple of bug ID’s, but Adobe updated their bug-base and I can’t find them. So at some point I’ll create a new ticket for this issue.

The problem lies in the fact that my SQL was generating a warning message inside the joined sub-query and CF wasn’t handling that very well. One fix is to fix why this message is being generated in the first place by adding a ISNULL() or COALESCE() around the column the MIN() method is reading. For example:

<cfquery name="local.products">
SELECT P.product
FROM Products P
    SELECT PC.productID, MIN( COALESCE( PC.price, 0 ) ) )
    FROM ProductsChildren PC
    GROUP BY PC.productID
) price ON P.productID = price.productID
<cfdump var="local.products">

The second method would be to add “SET ANSI_WARNINGS OFF;” to the beginning of the SQL statement. For example:

<cfquery name="local.products">

SELECT P.product
FROM Products P
    SELECT PC.productID, MIN( PC.price, 0 ) )
    FROM ProductsChildren PC
    GROUP BY PC.productID
) price ON P.productID = price.productID
<cfdump var="local.products">

The first fix is probably the best method to choose, however both of these resolved my issue.

There are other workarounds such as using the JDBC driver, but this approach seems to be the most practical.

#coldfusion-2, #join, #query, #sql, #sub-query, #undefined

Handling Expired Sessions via AJAX & FW/1

This is a followup to my “Framework One AJAX Method (FW/1)” post (


  1. You use the session scope to define if a user is logged in or not
  2. You use jQuery AJAX to pull JSON data from FW/1 action URL’s
  3. The user’s session has expired after x minutes of inactivity after login
  4. If the session is expired the user is directed to a login page after trying to navigate

So what happens in this scenario? Instead of the expected JSON data your AJAX call receives the HTML of a login page with a status of 200. Can’t do too much with this.

Here’s a code example that will pass the client a 403 error (Forbidden) in the header and return no content. jQuery will then redirect the user to a login screen when it sees this status code.

First here’s a simlified FW/1 Application.cfc setupRequest() method:

void function setupRequest() {

	var reqData = getHTTPRequestData();

	if( structKeyExists( reqData.headers, 'X-Requested-With' ) && reqData.headers[ 'X-Requested-With' ] == 'XMLHttpRequest' && !structKeyExists( session, 'user' ) ) {
		getpagecontext().getresponse().setstatus( 403 );

This code detects if the call came from an AJAX request ( getHTTPRequestData().headers.X-Requested-With = ‘XMLHttpRequest’ ) and if the session still knows about the user. If it is an AJAX request and the user is not known, then set the status code of the return page to 403 and stop processing any more code. If you try to use throw instead of abort, it will overwrite the status code to 500.

The second simple example is the jQuery piece:

$( document ).ready( function() {

	$( this ).ajaxError( function( e, jqXHR, settings, exception ) {
		if( jqXHR.status == 403 ) {
			location.href = '?logout';
			throw new Error( 'Login Required' );
		} else if( !jqXHR.statusText == 'abort' && jqXHR.getAllResponseHeaders() ) {
			alert( 'There was an error processing your request.\nPlease try again or contact customer service.\nError: ' + jqXHR.statusText );


Here we are globally looking at all AJAX requests. Since the status code 403 is in the error class it will throw an error. The .ajaxError() method picks up this error and handles it.

If the status code is detected as a 403 (which we set in our ColdFusion code) then we direct the user to a logout page (which in turn directs to a login page) and throws a JS error. The throw statement is supposed to stop all JS processing, however if you have an error handler attached to the specific AJAX call, then that will still fire. The error message will just be seen if you are viewing the JS console.

If there’s another error caught it first looks to see if the request was aborted or if the user navigated away from the page. In these two cases I don’t want to display an error. If anything else is caught, I display a generic message.

#ajax, #coldfusion-2, #fw1, #jquery, #session

Missing ColdFusion 9 Solr Collections List

After trying to track down a slow server issue, I noticed that the Solr Collections list in the ColdFusion Administrator did not have the usual list including “core0”. I’m not sure if this list ever existed as this is a new multi-server instance setup by another person. However I did know something was wrong.

So my first step was to locate the collections directory for this instance and delete everything. Wrong thing to do.

Taking a look at the “C:\JRun4\servers\cfusion\cfusion-ear\cfusion-war\WEB-INF\cfusion\solr\logsstderr-????_??_??.log” I see I’m now getting “SEVERE: java.lang.RuntimeException: Can’t find resource ‘solrconfig.xml’ in classpath or ‘C:\JRun4\servers\…\cfusion.ear\cfusion.war\WEB-INF\cfusion\collections\…’, cwd=C:\JRun4\servers\cfusion\cfusion-ear\cfusion-war\WEB-INF\cfusion\solr” when I run my page script. The script detects if the collection exists; if not then create it; then populate the collection.

So now I’m thinking that the collection list probably exists in an XML config file somewhere, but is somehow corrupted as the ColdFusion Administrator can’t read it.

After doing some searching I find the “core” list in “C:\JRun4\servers\cfusion\cfusion-ear\cfusion-war\WEB-INF\cfusion\solr\multicore\solr.xml”. The XML has multiple core children elements under the “solr\cores” path which include the standard “core0” and my missing collections. Because I can just delete those collections, recreating them problematically later, I decide to delete all core elements except “core0/”. Not sure if I need to restart anything or not, I restart the SOLR service and the ColdFusion instance.

I now see “core0” in the ColdFusion Collections list as normal and am able to create collections normally as well.

#coldfusion-2, #collection, #solr

ColdFusion Implicit Struct Case Sensitivity For JSON

I’ve always learned that in ColdFusion 9+ that dot notation and implicit structs will always convert the key into uppercase when returning it as a JSON string.

To get around this you would always use bracket notation.

However I learned today, by example, that if you enclose the defined key, in an implicit struct, inside quotes that the key case will be preserved.

// returns: {"MYKEY":"myValue"}
myStruct.myKey = "myValue";
return myStruct;

// returns: {"MYKEY":"myValue"}
myStruct = { myKey = 'myValue '};
return myStruct;

// returns: {"myKey":"myValue"}
myStruct[ "myKey" ] = "myValue";
return myStruct;

// returns: {"myKey":"myValue"}
myStruct = { 'myKey' = 'myValue '};
return myStruct;

#case, #coldfusion-2, #json

FW/1 and Service Beans in ColdSpring

Recently I started switching from a Model-Glue implementation with ColdSpring to FW/1 (Framework One) with ColdSpring on a project of mine.

An example service bean definition looks like this:

<bean id="geographic" class="geographicService" />

With the new FW/1 code, I tried calling a method in this service by using:

variables.fw.service ( 'geographic.getStatesByCountry', 'states' );

But ended up with this error:

Service ‘geographic.getStatesByCountry’ does not exist. To have the execution of this service be conditional based upon its existence, pass in a third parameter of ‘false’.

After hours of debugging, my team member finally found a solution; change the bean id from geographic to geographicService. Apparently FW/1 automatically appends “Service” to the bean ID it references so that it can cache services and controllers that way. This appears to be lacking in the FW/1 documentation, or at least clearly.

So the fix is:

<bean id="geographicService" class="geographicService" />

#beans, #coldfusion-2, #coldspring, #framework, #fw1

Looping Over Arrays in ColdFusion 9.01 (CFScript)

This is pretty much a “note to self”… but in ColdFusion 9.01 you can now loop over arrays using the  for – in loop while in cfscript.

Syntax is:

for(item in array) {

#array, #arrays, #coldfusion-2