Debugging SQL in ColdFusion 9 & 10

There may be times you would like to see the actual SQL statement that is being sent to the database for execution, especially when you are trying to diagnose a problem. When dynamically creating SQL statements and using CFQUERYPARAM within the SQL, the resulting SQL will have question marks as place holders of parameter values attached to the end of the SQL statement. In order to see both the SQL statement and the parameters use "queryName.getMetaData().getExtendedMetaData()". See the example below:

<cfquery name="myQuery" datasource="myDatabase">
   INSERT INTO Contacts (name, birthdate)
   VALUES(
       '#form.name#',
       <CFQUERYPARAM VALUE="#form.birthDate#" null="#NOT IsNumericDate(form.birthDate)#");
</cfquery>

<cfoutput>
   <cfdump var="#myQuery.getMetaData().getExtendedMetaData()#"/>
</cfoutput>

ColdFusion 10's CGI PATH_INFO Variable is Empty

A couple years ago I wrote a web app using ColdFusion 8 development server. I had wrote a method that I used to store the page name and query string of the current page into a session variable called "returnPage". I recently upgraded my developent server to ColdFusion 10 and I noticed my "returnPage" session variable was never being populated with any text. Doing some investigating, I discovered that the CGI variable named PATH_INFO (which in ColdFusion 8 stored the page name) is an empty string in ColdFusion 10. Doing a quick Google search, appears other's have ran into this same issue and reported it as a bug with ColdFusion 10. For the workaround, I chose to use ColdFusion's function GetFileFromPath() and passed in the CGI variable CF_TEMPLATE_PATH. Now the session variable "returnPage" contains the desired page name. Below is the old code and modified code:

Old Code:

<cfcomponent>

   <cffunction name="setReturnPage">

      <!--- This function sets the session variable returnPage to the current URL address --->

      <cfif cgi.QUERY_STRING IS NOT "">
         <cfset session.returnPage = "#cgi.PATH_INFO#" & "?" & "#cgi.QUERY_STRING#" >
      <cfelse>
         <cfset session.returnPage = "#cgi.PATH_INFO#">
      </cfif>

   </cffunction>

</cfcomponent>

Updated Code:

<cfcomponent>

   <cffunction name="setReturnPage">

      <!--- This function sets the session variable returnPage to the current URL address --->

      <cfif cgi.QUERY_STRING IS NOT "">
         <cfset session.returnPage = "#GetFileFromPath(cgi.CF_TEMPLATE_PATH)#" & "?" & "#cgi.QUERY_STRING#" >
      <cfelse>
         <cfset session.returnPage = "#GetFileFromPath(CF_TEMPLATE_PATH)#">
      </cfif>

   </cffunction>

</cfcomponent>

ColdFusion's Infline IF (IIF) function

If I have a CFM page that has several CFIF/CFELSE statements, most of the time I'll replace them using ColdFusion's Infline IF (IIF) Function. This decreases the amount of code I have to scroll through within the CFM page.

Take the following CFIF/CFELSE example:

<cfquery name="qName" datasource="dbName">
        INSERT INTO tableName (
                                                           firstName
                                                           lastName
                                                           member)
        VALUES ('#form.firstName#',
                          '#form.lastName#',                         
                          <cfif isDefined("URL.value")>
                                 member = ''#URL.value#"
                          <cfelse>
                                 member = 'Non-Member'
                          </cfif> )
</cfquery>

This can be cleaned up by using ColdFusion's IIF function. Note the IIF function requires the DE function to prevent ColdFusion from evaluating literal strings:

<cfquery name="qName" datasource="dbName">
        INSERT INTO tableName (
                                                           firstName
                                                           lastName
                                                           member)
        VALUES ('#form.firstName#',
                          '#form.lastName#',                         
                          '#IIF(isDefined("URL.value"), DE("#URL.value#"), DE("Non-Member"))#' )
</cfquery>

I've also used the IIF function to alter the row colors of an HTML table:

<table border="1">
    <cfoutput query="qName">
         <tr bgcolor="#IIF(qName.currentRow mod 2 eq 0, DE("white"), DE("gray"))#">
               <td>
                      #qName.firstName#  #qName.lastName#
               </td>
         </tr>
     </cfoutput>
</table>

Center CFGRIDCOLUMN Data Within a CFGRID

Problem:
I am displaying data in a CFGRID of format HTML and I want the data in two of the columns centered. I can't use the CFGRIDCOLUMN attribute dataAlign to center the data, as it only works with a CFGRID format of type flash when using ColdFusion 8.0. Since upgrading to ColdFusion 9.0 is not an option right now, I needed to find an alternate method.

Solution:
My CFM file containing the CFGRID is called testGrid.cfm. Within this file I linked an external CSS file named styles.css.

<link href="styles.css" rel="stylesheet" type="text/css" media="all">

I added the following CSS to styles.css:

#customgrid  .x-grid-hd-1 {text-align: center;}
#customgrid  .x-grid-col-1 {text-align: center;}
#customgrid  .x-grid-hd-3 {text-align: center;}
#customgrid  .x-grid-col-3 {text-align: center;}

I can now enclose the CFGRID tag with a <div id="customgrid"> tag and the 1st and 3rd heading and column data will be centered within their columns.

Passing a Value From Selected CFGRID Record To Another File

Problem Description:
Using ColdFusion 8 and MS-SQL server 2008, I was displaying data within a CFGRID. The data was being queried from a table called Sites. This table had over 10 columns. I was limited to a display area on the web page of 800px. Therefore, I did not have enough space to display all the columns of data for a given record. I would like the user to be able to select a record from the CFGRID and all the data for that record is displayed below the CFGRID.

Solution:
The first thing I needed to do was to make sure I was getting the primary key ID from the Sites table. Next, I had to add a new CFGRIDCOLUMN to the CFGRID to include the primary key from the database query. I didn't want the ID to show in the CFGRID so I set the display attribute equal to "no".

I then placed the CFGRID within a CFFORM tag so that I could use CFINPUT to bind the selected record's primary key ID to hidden variable called "selectedSite".

Finally, I used the CFDIV tag and bound the CFDIV tag's URL variable to a file called showSiteDetail.cfm and passed the bound value of "selectedSite" using a URL variable called "SiteID". The showSiteDetail.cfm file simply re-quiries the Site table based on the URL variable "SiteID" and displays the data in a table. Now every time the user selectes a new record in the CFGRID, the CFDIV tag is refreshed and displays all the available data for the selected record.

Code:

<!--- viewSites.cfm --->
  <cfform style="margin-bottom: 10px;">
     <cfgrid name="SitesGrid" format="HTML" height="200" width="320" rowheaders="no" colHeaders="yes"
                              autoWidth="No" query="getSites" selectMode="single">
          <cfgridcolumn name="site_id" display="No">
          <cfgridcolumn name="site_name" header="Site Name" width="150">
          <cfgridcolumn name="location" header="Location" width="150">
     </cfgrid>
     <cfinput name="selectedSite" bindOnLoad="true" type="hidden" bind="{SitesGrid.site_id}"/>
  </cfform>

  <cfdiv bind="url:SSM_Mod/showSiteDetail.cfm?SiteID={selectedSite}" bindOnLoad="true" />