My Favorite Things In ColdFusion Builder

After I read Sam Farmer's "5 Things I Love About ColdFusion Builder" article I started to think about why I love ColdFusion Builder so much. To put it in perspective exactly how much I love it, I told my CTO I'd be willing to have them take money out of my paycheck to purchase it if my company wouldn't outright buy it for me.  FYI they did end up purchasing it for me.  With that out of the way here is why I love ColdFusion Builder:

Debugging Perspective 

This has to be one of the most helpful tools for me in Builder.   I love the fact that we can now do line debugging and view variables in real time.  Most (but not all) of the time it sure beats the old cfdump cfabort method of debugging.  My only gripe about the line debugger is that it tends to run a bit slow.  The documentation states that using the "Step Over" command over the "Step Into" command will improve performance, however, this isn't very helpful when debugging a complex OO project.  


Ctrl+Click Shortcuts

When  I first read about the ctrl+click I had no idea how much I'd use it.   For those of you who don't know about it, when you create an object or include a file you can hover over the object definition or the file location and hit ctrl-click to open that file or object in the editor.  


Servers View and the Console View

I use these features hand in hand most of the time.  As long as your ColdFusion server is started using Builder, the console is very helpful when debugging, is most cases you get the full stack trace of the problem..  I also love the fact that I can open up my administrator and server monitor right in the Builder interface instead of having to switch to my web browser.   

CFC Method Insite

Even though our ColdFusion staff is small I still have have to frequently consume objects written by other people.  Because of method insite I don't need to know the method names or arguments of the public interface of an object before implementing it.  All I need to know where the CFC lives and CFBuilder will tell me what I need to know when referring to that object. 

While I this is definitely one of my favorite things about ColdFusion builder, it is also one that provides me much frustration.   For starters it doesn't work when you pass in an object as an argument to a function.  Second it seems somewhat hit or miss for me when inside a cfscript tag.  I haven't been able to track down exactly where and why it fails but I think it has something to do with error parser.    

Extensions

Last but certainly not least is the support for custom extensions written in ColdFusion!  What a brilliant idea, allow the community to enhance your product by extending it with the language we all know and love.   I can't wait to see what extensions the community is going to come up with.  I'll definately be keeping my eye on Ray Camden's ColdFusion Builder contest. 

A better alternate to the exec() function in sql server 2005 to run a dynamic SQL statement.

When creating a dynamic SQL statement in SQL Server 2000 and up avoid using the exec() function.  There is a system stored procedure that works better called sp_executesql().   Here is a link to Microsoft's documentation:  (SQL 2005) http://msdn.microsoft.com/en-us/library/ms188001(SQL.90).aspx  (SQL 2000)
http://msdn.microsoft.com/en-us/library/aa933299%28SQL.80%29.aspx

ColdFusion cfswitch bug not completely fixed...

I remember a bug in ColdFusion where a long cfswitch statement would throw an error.   This was thought to be fixed, however it still does exist when using switch and case statements inside of a cfscript tag.

<cfscript>
switch(fuse)
 {
  case "login":
  {
   ArrayAppend(IncludeArray,"DisplayFiles\dsp_Login.cfm");
   break;
  }
  case "logout":
  {
   include("ActionFiles\act_logout.cfm");   
   relocate("index.cfm?fuse=login&message=You have been successfully logged out.");
   break;
  }

I have a large controller (2500 lines or so) file that makes a lot of object calls within my switch statements and when using switch and case statements within cfscript I would get a "Invalid method Code length" error.   Once I converted the switch statements to markup everything worked fine...

<cfswitch expression="#fuse#">
  <cfcase value= "login">
  <cfscript>
   ArrayAppend(IncludeArray,"DisplayFiles\dsp_Login.cfm");
   </cfscript>
  </cfcase>
  <cfcase value= "logout">
  <cfscript>
   include("ActionFiles\act_logout.cfm");   
   relocate("index.cfm?fuse=login&message=You have been successfully logged out.");
   </cfscript>
  </cfcase>

Optimized ColdFusion Record Paging and Sorting using SQL Server 2005

While working on a large application with lots of data I began to think about optimizing my record paging.  I began working on an old school approach of using temporary tables and stored procedures to do my record paging when I decided to hit up google to find a better approach.   The simple search of "ColdFusion record paging" didn't bring up anything of use to me but then I searched "SQL Server 2005 record paging" and found something I could use.   In SQL 2005 a new function called row_number() was added which makes record paging much easier and much more efficient.

Using record_count my query looked something like this:

SELECT
*
FROM
(
SELECT 
row_number() over(order by S.name) as rowNum 
,S.siteId 
,S.code 
,S.name 
,S.roleBitMask 
,R.description as regionDescription 
,R.regionId 
FROM 
Site S 
INNER JOIN 
Region R ON R.regionID = S.regionId 
WHERE 
S.deleted = 0 
) AS List
WHERE
rowNum between 1 and 25

This query worked great, however it was leaving out one very important piece of information: the record count. I did not want to make another call to the db to get the record count so a few minutes of research brought me to this solution:

WITH List AS
(
 SELECT 
  row_number() over(order by S.name) as rowNum 
  ,S.siteId 
  ,S.code 
  ,S.name 
  ,S.roleBitMask 
  ,R.description as regionDescription 
  ,R.regionId 
 FROM 
  Site S 
 INNER JOIN 
  Region R ON R.regionID = S.regionId 
 WHERE 
  S.deleted = 0
),
ListRecordCount AS
(
 SELECT
  *
 FROM
   List
  ,(SELECT MAX(rowNum) AS recordCount FROM List) AS recordCount
)
SELECT
 *
FROM
 ListRecordCount
WHERE
 rowNum between 1 and 25

Now I'm only pulling 25 rows from the database at a time, and I'm getting a record count! All that is left is to use ColdFusion to pass in my start and end rows and to pass in different order by clauses as well.
Here is what my final code looks like:

<cfquery name="local.GetSiteListQuery" datasource="#variables.datasource#">
 WITH List AS
 (
  SELECT
    row_number() over(ORDER BY <cfif arguments.SiteListView.GetSortBy() neq "">
           #arguments.SiteListView.GetSortBy()#
          <cfelse>
           S.name
          </cfif> #arguments.SiteListView.GetSortOrder()#) as rowNum
   ,S.siteId
   ,S.code
   ,S.name
   ,S.roleBitMask
   ,R.description as regionDescription
   ,R.regionId
  FROM
   Site S
  INNER JOIN
   Region R ON R.regionID = S.regionId
   <cfif arguments.SiteListView..GetSiteView().GetRegionId() neq 0>
    AND
   R.regionId = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.SiteListView.GetSiteView().GetRegionId()#">
   </cfif>
  WHERE
   S.deleted = 0
  <cfif arguments.SiteListView..GetSiteView().GetCode() neq "">
   AND
    S.code = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.SiteListView.GetSiteView().GetCode()#">
  </cfif> 
  <cfif arguments.SiteListView..GetSiteView().GetSiteId() neq 0>
   AND
    S.siteId <> <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.SiteListView..GetSiteView().GetSiteId()#">
  </cfif>
  <cfif arguments.SiteListView.GetStartRow() neq 0>
   AND
    S.siteId > <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.SiteListView.GetStartRow()#">
  </cfif>
 ),
 ListRecordCount AS
 (
  SELECT
   *
  FROM
   List, (SELECT MAX(rowNum) AS recordCount FROM List) AS recordCount
 )
 SELECT 
  * 
 FROM
  ListRecordCount 
 WHERE
  rowNum BETWEEN <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.SiteListView.GetStartRow()#"> 
      AND <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.SiteListView.GetStartRow() + variables.recordsPage#">  
 
</cfquery>

Here's the proof that the optimized paging query works much, much faster  (click on the images for a full sized version that will show you the execution times better):

Be careful when using try/catch inside of a cftransaction tag.

While doing development on a somewhat large project for managing weatherization jobs I found a funny little thing about using the cftransaction tag outside of a try/catch statement. Take the chunk of code below for example:

<cftransaction>
<cfscript>
try
{
variables.LocationCodeDataDelegate.UpdateLocationCode(arguments.LocationCodeView.GetLocationCode());
arguments.Event.AddMessage("Location code updated successfully.");
arguments.Event.SetError(false);

}
catch(any ex)
{
arguments.Event.SetError(true);
arguments.Event.AddMessage("There was an error updating the location code.<br><br>#ex.message# The error occured on or around line #ex.TagContext[1].line# on file #ex.TagContext[1].template#<br>#ex.detail#",true);
}
</cfscript>
</cftransaction>

Now I had incorrectly assumed that if my UpdateLocationCode function would throw an error that the transaction would be rolled back automatically. It turns out that since that error is caught and normal processing continues after the catch tag that this transaction commits just fine.

The fix for this problem is a simple one. In my Event object I have a boolean property called Error that is defaulted to true, only when the code inside my try block completes is it set to false. All I need to do is check my Event object for an error condition right before my tag and rollback the transaction if the error condition is true as shown below:

<cftransaction>
<cfscript>
try
{
variables.LocationCodeDataDelegate.UpdateLocationCode(arguments.LocationCodeView.GetLocationCode());
arguments.Event.AddMessage("Location code updated successfully.");
arguments.Event.SetError(false);
}
catch(any ex)
{
arguments.Event.SetError(true);
arguments.Event.AddMessage("There was an error getting the location code.<br><br>#ex.message# The error occured on or around line #ex.TagContext[1].line# on file #ex.TagContext[1].template#<br>#ex.detail#",true);
}
</cfscript>
<cfif arguments.Event.GetError()>
<cftransaction action="rollback" />
</cfif>
</cftransaction>