Three Helpful ColdFusion Buider Tips

Here are a few quick tips to help increase your productivity using ColdFusion Builder. 


1) Use Ctrl + Click Shortcuts

When creating an object, using a cfinclude statement, or calling a function mouse over the object, template, or function call and hold down Ctrl key to activate a hyperlink for that definition.  While holding down the ctrl key left click the hyperlink to open the file in a new editor tab. This comes in really handy when tracking down a problem.  

Creating a Component:

Using a cfinclude:

Calling a function:

2) Start your development server from CFBuilder

Assuming that you've added your development server to the Servers View in Builder, always use the Server View to start your ColdFusion server instance.   This will bind the console view to your server instance and allow the server to display valuable information in the console.  Here is a quick example of the binding in action.   

Note: This will only work if you do not have the option "Use Windows Service to start/stop the server" selected in your server definition.

Lets start by throwing an error when the local ColdFusion server was running BEFORE ColdFusion Builder was started:

Now lets go back into builder and take a look at the console view:

Nothing is there but a generic server is available message.   Now I will go into my servers view and restart my ColdFusion server and rethrow the error.   Now the console view gives us some valuable information about the error that just occurred:

This gets even more helpful as your errors get more complex because the console view will show you the entire java stack trace.

3) Use the SQL Editor (This is not the same as the RDS Query Viewer)

The SQL Editor is a very different animal than the RDS Query Viewer.  The SQL Editor can be opened by clicking Ctrl + Alt + S or by right clicking in the code editor and selecting SQL Editor. The SQL Editor in CFBuilder will allow you to edit your query, preview your results, and insert the SQL code back into your template.  The biggest difference between the SQL Editor and the RDS Query View is that the SQL Editor will provide you with SQL Code insite!   

 

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):