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" />

Adding an Auto Number to Oracle 10g Database

It is common to include a primary key when creating a database table. The primary key is a unique number or field that uniquely identifies each record. Using MS Access, the process of automatically incrementing the primary key for each new record added is a simple process, you simply choose "auto number" as the data type. In Oracle 10g, it's not quit that simple but is still an easy process. You simply need to create an individual sequence number for each table containing a primary key that you want to control. Using SQL, you can call [Sequence Number Name].nextval to generate an automated number you can insert into a new record. Read on to learn how I went about accomplishing this and to view a portion of C# code from an application I wrote, which accessed the Oracle database and inserted a record using an Oracle sequence number I created within Oracle 10g.

Assuming you already have Oracle 10g installed, you'll need to access the Database Home Page by clicking on Start --> All Programs --> Oracle Database 10g Express Edition --> Go To Database Home Page, and then log in using the user name and password you selected when you set up the database.

Once you have logged in, you'll see graphical buttons/selections labeled Administration, Object Browser, SQL, Utilities and Application Builder. On the right side of each graphic is a small triangle pointed down, indicating a drop down list of options is available. Click on the drop down list for Object Browser, using your mouse highlight Create, then select Sequence.

Type a name for the new sequence number such as: [table name]_seq. Then fill in the remaining fields. Note that if you already have existing records, you'll have to be sure you have the correct value in the Start With column. I left Number to Cache blank. After filling in the fields, select Next then Create. Your sequence number is now ready to be used.
I created a table called Components. The table contained the following headings and data.

Primary Key	PARTID	PARTNUM	PARTDESC
1	2889	G533044-1	RF Cable
2	3442	G533688-1	RF Cable Terminator

Using C#, I've pulled the below code snippets which interacted with this table and used the sequence number I created, which was called COMP_SEQ with a starting value of 3.

using Oracle.DataAccess.Client;
...
string oradb = "Data Source=172.xx.xxx.xx;User ID=mrb;Password=mrb;";

OracleConnection oraConn = new OracleConnection(oradb); 
oraConn.Open(); 

// Insert new record into the table Components using Oracle sequence number COMP_SEQ

OracleTransaction ora_addToComponents = oraConn.BeginTransaction();
OracleCommand oraCommand1 = oraConn.CreateCommand();
oraCommand1.CommandText = "INSET INTO ASR11COMPONENTS
                                                      values (COMP_SEQ.nextval, '222', '333', 'RF Tester')";
oraCommand1.ExecuteNonQuery();
ora_addToComponents.Commit();

oraConn.Close();