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>

Better CF documentation

If you don't like the adobe live doc's or using the PDF documentation this site will be your new best friend: http://coldfusiondocs.com/app/.   I don't remember exactly where I found this, but I immediately bookmarked and have been using it daily since.  

Another very nice about this site is that it includes both the Adobe CFML and Railo documentation in separate tabs!