Programming ColdFusion By Rob Brooks-Bilson August 2001 1-56592-698-6, Order Number: 6986 974 pages, $49.95

Chapter 11
Advanced Database Techniques

In this chapter:
Display Techniques
Drill-Down Queries
Query Caching
Advanced SQL
CFSQL
Calling Stored Procedures
Transaction Processing

This chapter attempts to strengthen the concepts we have already covered while adding several advanced techniques to your bag of ColdFusion tricks. These techniques include advanced ways to display query results, query-caching strategies, and advanced SQL topics. The advanced display techniques we'll cover allow you to enhance the way you display dynamically generated data beyond simple HTML table dumps. Taking advantage of ColdFusion's query-caching abilities allows you to shave precious processing time off your frequently run queries. The advanced SQL topics cover the essentials necessary for building dynamic, highly scalable applications.

Display Techniques

This section focuses on techniques you can use to enhance the display of dynamic data. Some of these techniques include displaying limited record sets, creating dynamic HTML tables with alternating row colors, working with various multicolumn output displays, and browsing records with next/previous. You will also learn several methods for controlling whitespace in dynamic pages in order to optimize page-download times.

Flushing Page Output

A complaint often heard regarding web applications is the amount of time it takes to return data to a user once a page is requested, be it by a form submission or a URL the user clicks on. Often this is due to the large amount of data a particular operation must sift through and return to the user. In situations such as this, it is often desirable to present the user with a "Please Wait" message while their request processes or to provide incremental amounts of data as results from a large query result set become available. ColdFusion lets you handle these tasks with a new tag introduced in Version 5.0 called CFFLUSH. The CFFLUSH tag provides a means to send incremental amounts of data from your ColdFusion server to a user's browser as they become available.

The first time a CFFLUSH tag is encountered on a page, it sends all the HTTP headers for the request along with any generated content up to the position in the template where the tag is encountered. Successive CFFLUSH tags return any content generated since the previous flush. Because of this, CFFLUSH is usually used within loops or output queries to send results back to the browser in incremental chunks. The following example shows the CFFLUSH tag used to incrementally return the results of a database query:

<H1>Outputting Query Results</h2>
Please be patient as this may take a few moments...
<P>
<!--- flush the output up to this point --->
<CFFLUSH>
 
<CFQUERY NAME="GetEmployees" DATASOURCE="ProgrammingCF">
SELECT *
FROM EmployeeDirectory
</CFQUERY>
 
<CFSET Stall=0>
<CFLOOP QUERY="GetEmployees">
  <!--- flush the rest of the output as it is generated in chunks of 100 
        bytes --->
  <CFFLUSH INTERVAL="100">
  <!--- use this loop to exaggerate the processing time --->
  <CFLOOP INDEX="i" FROM="1" TO="3500">
    <CFSET Stall = Stall+1>
  </CFLOOP>
  
  <CFOUTPUT>
  #Name#<BR>
  </CFOUTPUT> 
</CFLOOP>

If you run this example, you'll notice all of the content before the first CFFLUSH tag is output almost immediately. After that, the next CFFLUSH tag is used in a loop and specifies that the rest of the content generated by the page should be sent to the browser in chunks of 100 bytes. This is achieved by setting the INTERVAL attribute of CFFLUSH to 100. The code in the example contains an index loop that essentially ties up processing time for 3,500 iterations between the output of each name returned by the query. This results in an artificially inflated amount of time required to output the query results, which is perfect to demonstrate how the CFFLUSH tag incrementally sends data back to the browser in chunks as it becomes available. This is done because the EmployeeDirectory table doesn't contain enough records to effectively demonstrate the CFFLUSH tag.

Once a CFFLUSH tag has been used in a template, you can't use any other CFML tags that write to the HTTP header; doing so causes ColdFusion to throw an error because the header has already been sent to the browser. These tags include CFCONTENT, CFCOOKIE, CFFORM, CFHEADER, CFHTMLHEAD, and CFCONTENT. In addition, attempting to set a variable in the cookie scope with the CFSET tag results in an error. This is because cookies are passed from the server to the browser in the HTTP header.

Displaying Limited Record Sets

You may decide that for a given application, it's more effective not to display the contents of an entire record set. For these applications, you can use two additional optional attributes of the CFOUTPUT tag to display a subset of the full record set returned by a query:

STARTROW

Specifies what query row to begin outputting from

MAXROWS

Specifies the maximum number of rows to output

Example 11-1 uses the STARTROW and MAXROWS attributes of the CFOUTPUT tag to output a subset of a full record set.

Example 11-1: Displaying a Limited Record Set Using CFOUTPUT

<CFQUERY NAME="GetEmployeeInfo" DATASOURCE="ProgrammingCF">
         SELECT Name, Title, Department, Email, PhoneExt
         FROM EmployeeDirectory
</CFQUERY>
 
<!--- display the total number of records returned by the query --->
<H2>Displaying a Limited Record Set</H2>
<CFOUTPUT>
<H3>#GetEmployeeInfo.RecordCount# total records - Displaying records 6-10</H3>
</CFOUTPUT>
 
<!--- output rows 6-10 of the query result set --->
<TABLE CELLPADDING="3" CELLSPACING="0">
<TR BGCOLOR="#888888">
  <TH>Record Number</TH>
  <TH>Name</TH>
  <TH>Title</TH>
  <TH>Department</TH>
  <TH>E-mail</TH>
  <TH>Phone Extension</TH>
</TR>    
<CFOUTPUT QUERY="GetEmployeeInfo" STARTROW="6" MAXROWS="5">
<TR BGCOLOR="##C0C0C0">
  <TD>#CurrentRow#</TD>
  <TD>#Name#</TD>
  <TD>#Title#</TD>
  <TD>#Department#</TD>
  <TD><A HREF="Mailto:#Email#">#Email#</A></TD>
  <TD>#PhoneExt#</TD>
</TR>    
</CFOUTPUT>
</TABLE>

In Example 11-1, a query is performed against the EmployeeDirectory table. Setting the STARTROW attribute to 6 and the MAXROWS attribute to 5 results in records 6 to 10 of the query result set getting output to the browser.

Alternating Row Color in HTML Tables

Another popular way to display tabular data is to alternate the background color of the rows being displayed. The technique is easy to implement and offers an attractive way to display tabular data, so that it stands out. Example 11-2 generates an HTML table of alternating row color from a database query. The results can be seen in Figure 11-1.

Example 11-2: Alternating Row Color in HTML Tables

<CFQUERY NAME="GetEmployeeInfo" DATASOURCE="ProgrammingCF">
         SELECT Name, Title, Department, Email, PhoneExt
         FROM EmployeeDirectory
</CFQUERY>
 
<TABLE CELLPADDING="3" CELLSPACING="0">
<TR BGCOLOR="#888888">
   <TH>Name</TH>
   <TH>Title</TH>
   <TH>Department</TH>
   <TH>E-mail</TH>
   <TH>Phone Extension</TH>
</TR>    
 
<CFOUTPUT QUERY="GetEmployeeInfo">
<TR BGCOLOR="###IIF(GetEmployeeInfo.currentrow MOD 2, DE('E6E6E6'), 
DE('C0C0C0'))#">
   <TD>#Name#</TD>
   <TD>#Title#</TD>
   <TD>#Department#</TD>
   <TD><A HREF="Mailto:#Email#">#Email#</A></TD>
   <TD>#PhoneExt#</TD>
</TR>    
</CFOUTPUT>
</TABLE>

Figure 11-1. A dynamically generated table with alternating row colors

We alternate the row color by using the IIF( ) and DE( ) functions along with the MOD operator to determine whether or not the row number for the current record is odd or even. Depending on the outcome of the evaluation, one color or the other is used as the background color for the current row. Because hex color codes are supposed to begin with a pound sign (#), we have to create an escape sequence before we call the IIF( ) function. This is done by doubling up on the first pound sign.

Multicolumn Output

Another popular formatting technique involves outputting a query result set in more than one column, similar to how a newspaper story is printed. There are several techniques you can use to achieve multicolumn output. Two of the more popular methods are covered in the following sections.

Sorting multicolumn output from left to right

One technique for outputting a result set in more than one column involves sorting the results from left to right, then top to bottom. You can see the technique for sorting multicolumn output from left to right in Example 11-3.

Example 11-3: Sorting Multicolumn Output from Left to Right

<!--- retrieve a list of employee names from the employeedirectory table --->
<CFQUERY NAME="GetEmployeeInfo" DATASOURCE="ProgrammingCF">
         SELECT Name
         FROM EmployeeDirectory
         ORDER BY Name
</CFQUERY>
 
<H2>Two column output sorted left to right</H2>
<!--- initialize the STARTNEWROW variable as True --->
<CFSET StartNewRow = True>
 
<!--- The CFPROCESSING tag suppresses extra whitespace as much as possible.  
      To remove the max amount of whitespace, remove these comments as 
      well. --->
<TABLE>
<CFPROCESSINGDIRECTIVE SUPPRESSWHITESPACE="Yes">
<CFOUTPUT QUERY="GetEmployeeInfo">
<!--- Add a TR if we are supposed to start a new row.  Otherwise, continue
      adding TDs --->
<CFIF StartNewRow IS True><TR></CFIF>
  <TD>#Name#</TD>
<!--- set STARTNEWROW to the opposite of its currnet True/False value --->
<CFSET StartNewRow = NOT(StartNewRow)>
<!--- if STARTNEWROW is True, add a /TR to close the row --->
<CFIF StartNewRow IS True>
  </TR>
</CFIF>
</CFOUTPUT>
</CFPROCESSINGDIRECTIVE>
</TABLE>

After the query is performed, a variable called StartNewRow is initialized and set to True. CFPROCESSINGDIRECTIVE helps limit the amount of whitespace created by ColdFusion during the generation of the table. Using this tag helps reduce the overall size of the file generated by ColdFusion and sent to the browser. The CFOUTPUT tag loops over the result set specified in the QUERY attribute. If the value of StartNewRow is still True, a <TR> tag is dynamically inserted, beginning a new row in the table. Next, we output an employee name in a table cell using <TD>#Name#</TD>. The value of StartNewRow is then set to the opposite of its current value (either False or True). A CFIF statement determines the value of StartNewRow. If StartNewRow evaluates True, a </TR> tag is dynamically inserted into the table, ending the current row. If StartNewRow is False, another <TD>#Name#</TD> is inserted into the table, adding another employee name to the current row. This process continues until there are no more rows of data in the result set for the CFOUTPUT tag to loop over. The two-column output generated by this example is shown in Figure 11-2.

Figure 11-2. Multicolumn result set display sorted left to right

Sorting multicolumn output from top to bottom

It is also possible to sort multicolumn output from top to bottom, then left to right, as opposed to the sequence in the previous section. In this example, we also specify the number of columns to display on the page. Example 11-4 shows how to sort multicolumn output from top to bottom.

Example 11-4: Sorting Multicolumn Output from Top to Bottom

<!--- Thanks to Sean Clairmont of Team Allaire for helping to refine the
       original code --->
<!--- query the employeedirectory table for a list of employee names --->
<CFQUERY NAME="GetEmployeeInfo" DATASOURCE="ProgrammingCF">
          SELECT Name,Email
          FROM EmployeeDirectory
          ORDER BY Name
</CFQUERY>
 
<!--- Columns sets the total number of output columns. --->
<CFSET Columns = 3>
<CFSET CurrentColumn = 0>
<CFSET RowCompleted = 0>
<!--- Set the total number of rows equal to the number of records divided by
       the number of columns. --->
<CFSET Rows=Int(GetEmployeeInfo.RecordCount/Columns)>
<!--- Set a variable to hold the number of columns with extra records --->
<CFSET OddColumns = GetEmployeeInfo.RecordCount MOD Columns>
<!--- if there are columns with extra records, increase the number of rows by
       one --->
<CFIF OddColumns NEQ 0>
   <CFSET Rows = IncrementValue(Rows)>
</CFIF>
<CFSET Increment = IncrementValue(Int(GetEmployeeInfo.RecordCount/Columns))>
 
<H2>Multicolumn Query Output Sorted Top to Bottom</H2>
 
<TABLE BORDER=0>
<CFPROCESSINGDIRECTIVE SUPPRESSWHITESPACE="Yes">
<!--- create a loop that iterates a number of times equal to the total number
       of output rows needed --->
<CFLOOP FROM="1" TO="#rows#" INDEX="Row">
   <CFSET LeftOverIncrement = 0>
   <TR>
   <!--- create an inner loop for handling each column --->
   <CFLOOP INDEX="Column" FROM="1" TO="#Columns#">
     <CFIF Column GT (OddColumns + 1) >
       <CFSET LeftOverIncrement = IncrementValue(LeftOverIncrement)>
     </CFIF>
     <!--- Set the current row and column --->
         <CFSET CurrentRow = (Row + (Increment * (CurrentColumn) )  - 
LeftOverIncrement) >
         <CFSET CurrentColumn = IIf(CurrentColumn is (columns - 1),0 
,IncrementValue(CurrentColumn))>
     <!--- Output the current row --->
     <CFOUTPUT>
     <TD><CFIF (Row lt Increment or OddColumns gt 0) AND (CurrentRow LTE 
GetEmployeeInfo.Recordcount)>
                             #GetEmployeeInfo.name[CurrentRow]#
         <CFELSE>
            &nbsp;
                 </CFIF></TD>
     </CFOUTPUT>
 
                 <CFIF Row is Increment>
                         <CFSET RowCompleted = IncrementValue(RowCompleted)>
                 </CFIF>
                 <CFIF RowCompleted is OddColumns>
                         <CFSET OddColumns = 0>
                 </CFIF>
   </CFLOOP>
   </TR>
</CFLOOP>
</CFPROCESSINGDIRECTIVE>
</TABLE>

Before the individual columns are created and populated with data, a number of variables are initialized. You specify the number of columns to display the result set using the Columns variable. You may display the result set using any number of columns you desire, up to the total number of records returned. If you specify a number greater than the total number of records returned by the query, ColdFusion throws an exception. In Example 11-4, we display the result set in three columns.

We set the next variable, CurrentColumn, to specify a starting point for our output. RowCompleted is created and assigned an initial value of 0. We'll get back to the purpose for this variable in a moment. Rows is set to the total number of rows containing a record for each column. This is calculated by taking the total number of records and dividing it by the number of columns we want to use to display the output. The next variable we initialize is OddColumns. If a remainder is present when we calculate Rows, we use OddColumns to store the value. The value is important in determining how many cells need to be populated with data in the last row of the table. The final variable we initialize is Increment. Increment is used in the calculation that determines the index position of the next record to be output. It's initial value is set by adding 1 to the integer value of the total number of records divided by the number of columns to be output.

Once all of the variables have been initialized, an HTML table is started. A loop generates the appropriate number of rows for the table based on the value of the Rows variable. A second loop iterates over each column in the current row and populates it with the appropriate value from the query. After each table cell is built, the index position in the Rows loop is compared to the Increment value. If they are the same, the value of the RowCompleted variable is incremented by 1. This only happens in the last row of data generated for the table. Next, the value of RowCompleted is compared to the value of OddColumns. If they are the same, we set the value of OddColumns to 0. When the number of records is evenly divisible by the number of desired columns, OddColumns is always 0. Otherwise, it is 0 only for odd cells within the last row.

Next/Previous Record Browsing

One question of great concern to most CF developers is how to implement next/previous record browsing in ColdFusion. When building web applications with ColdFusion, you will inevitably create an application that queries a database and returns a record set with too many rows to display in a single browser window. To display thousands of rows of data in the browser at once is an unrealistic task, for a number of reasons. Sending thousands of rows of data to the browser eats up a lot of bandwidth. And no one likes to sit around waiting for a browser to download and render a 1-MB web page when the probability of the end user reading through thousands of rows of data is slim. So, what are our options? The solution is to break up the record sets returned to the browser into manageable chunks that allow the user to browse through the query results one chunk at a time. This type of interface is known as next/previous record browsing.

Implementing next/previous record browsing in ColdFusion might seem tricky at first glance. However, thanks to ColdFusion's query caching and the partial record set display capabilities we just covered, implementing a next/previous solution is a lot simpler than you might think.

The NextPrevious.cfm template in Example 11-5 shows how to build a next/previous record browser that can easily be modified to work with any query. The larger the query, the more benefit to this type of interface.

Example 11-5: Creating a Next/Previous Record Browser

<!--- StartRow is the default starting row for the output.
      DisplayRows determines how many records to display at a time --->
<CFPARAM NAME="StartRow" DEFAULT="1">
<CFPARAM NAME="DisplayRows" DEFAULT="4">
 
<!--- query the EmployeeDirectory table. Cache the result set for 15 
      minutes. --->
<CFQUERY NAME="GetEmployeeInfo" DATASOURCE="ProgrammingCF" 
         CACHEDWITHIN="#CreateTimeSpan(0,0,15,0)#">
         SELECT Name,  Title, Department, Email, PhoneExt
         FROM EmployeeDirectory
</CFQUERY>
 
<!--- Set a variable to hold the record number of the last
      record to output on the current page. --->
<CFSET ToRow = StartRow + (DisplayRows - 1)>
<CFIF ToRow GT GetEmployeeInfo.RecordCount>
    <CFSET ToRow = GetEmployeeInfo.RecordCount>
</CFIF>
 
<HTML>
<HEAD>
    <TITLE>Next/Previous Record Browsing</TITLE>
</HEAD>
 
<BODY>
 
<!--- Output the range of records displayed on the page as well as the total 
      number of records in the result set --->
<CFOUTPUT>
<H4>Displaying records #StartRow# - #ToRow# from the 
#GetEmployeeInfo.RecordCount# total records in the database.</H4>
</CFOUTPUT>
 
<!--- create the header for the table --->
<TABLE CELLPADDING="3" CELLSPACING="0">
<TR BGCOLOR="#888888">
  <TH>Name</TH>
  <TH>Title</TH>
  <TH>Department</TH>
  <TH>E-mail</TH>
  <TH>Phone Extension</TH>
</TR>    
<!--- dynamically create the rest of the table and output the number of
      records specified in the DisplayRows variable --->
<CFOUTPUT QUERY="GetEmployeeInfo" STARTROW="#StartRow#"
          MAXROWS="#DisplayRows#">
<TR BGCOLOR="##C0C0C0">
  <TD>#Name#</TD>
  <TD>#Title#</TD>
  <TD>#Department#</TD>
  <TD><A HREF="Mailto:#Email#">#Email#</A></TD>
  <TD>#PhoneExt#</TD>
</TR>    
</CFOUTPUT>
</TABLE>
 
<!--- update the values for the next and previous rows to be returned --->
<CFSET Next = StartRow + DisplayRows>
<CFSET Previous = StartRow - DisplayRows>
 
<!--- Create a previous records link if the records being displayed aren't the
      first set --->
<CFOUTPUT>
<CFIF Previous GTE 1>
   <A HREF="NextPrevious.cfm?StartRow=#Previous#"><B>Previous #DisplayRows# 
      Records</B></A>
<CFELSE>
Previous Records  
</CFIF>
 
<B>|</B>
 
<!--- Create a next records link if there are more records in the record set 
      that haven't yet been displayed. --->
<CFIF Next LTE GetEmployeeInfo.RecordCount>
    <A HREF="NextPrevious.cfm?StartRow=#Next#"><B>Next 
    <CFIF (GetEmployeeInfo.RecordCount - Next) LT DisplayRows>
      #Evaluate((GetEmployeeInfo.RecordCount - Next)+1)#
    <CFELSE>
      #DisplayRows#
    </CFIF>  Records</B></A>
<CFELSE>
Next Records   
</CFIF>
</CFOUTPUT>
 
</BODY>
</HTML>

The first thing Example 11-5 does is initialize two variables. StartRow specifies the starting row for the record set being displayed. The default value is set to 1. DisplayRows specifies the number of rows of data to display per page. We set DisplayRows to 4. Next, a query is run to retrieve all the records from the EmployeeDirectory table. The query is then cached for 15 minutes using the CACHEDWITHIN attribute of the CFQUERY tag. If you feel your users will use the record browser for more or less than 15 minutes on average, feel free to change this value.

Note that every cached query in ColdFusion takes up some of the server's memory. Depending on the amount of RAM on your server and the number of cached queries you allow (configurable in the ColdFusion Administrator), you may run into memory issues when dealing with cached queries. If you plan to use cached queries extensively, you should add additional RAM to your server so it can handle the anticipated load.

Next we set a variable called ToRow to hold the record number of the last record to be output on the current page. If ToRow is greater than the total number of records in the result set, it is set equal to the total number of records.

The next part of the template uses the CFOUTPUT tag to output the first chunk of records to the browser. The STARTROW and MAXROWS attributes determine the starting row and number of rows to output, respectively. These values are dynamically populated by the StartRow and DisplayRows variables we set in the beginning of the template. The results are shown in Figure 11-3. If you turn on debugging in the ColdFusion Administrator, you should be able to see that the query is being cached.

Figure 11-3. Implementing a next/previous record browser

The final section of Example 11-5 calculates the starting and previous row number for the next or previous batch of records to output. Depending on how many records have already been displayed, appropriate Next and Previous links are created for the user to click on to retrieve the next or previous set of records.[1] Clicking on one of the Next or Previous links causes the template to call itself and pass the starting row number for the next/previous chunk of records as a URL variable. When the template calls itself, instead of performing a fresh query and potentially wasting a lot of time waiting for the database to generate and return a record set, the template uses the cached query and returns the next set of records almost instantaneously! If it weren't for the cached query, you would have to query the database for the full record set every time you clicked on a Next or Previous link. It doesn't take much to imagine the enormous amount of wasted overhead just to display a few records at a time.

Controlling Whitespace

If you have ever looked at the HTML generated by a ColdFusion template (by viewing the source within your web browser), you may have noticed a lot of extraneous whitespace. Though whitespace in the HTML won't affect the display of your page, it can affect the overall file size of the page that is sent to the browser. File size can have a considerable impact on the time it takes for a page generated by ColdFusion to download.

When a CFML template is requested by a web browser, the web server that fulfills the request first passes the CFML template to the ColdFusion Application Server. Before the ColdFusion Application Server can process the instructions in the CFML template, the language processor within the application server must parse the CFML template into a special "language" called p-code that can be executed by the application server. It is during the conversion of CFML and HTML code into p-code that the extraneous whitespace is generated. The specifics of how this all happens aren't important for our purposes. What is important is realizing that there are several techniques you can use to suppress whitespace in the pages generated by your ColdFusion application.

Optimizing output

The CFSETTING tag is a sort of "catch-all" tag when it comes to optimizing output. There are three different functions currently handled by the tag: controlling whitespace, enabling/disabling the display of debug information, and overriding ColdFusion's default error and exception-handling mechanism. To accomplish these tasks, the CFSETTING tag has three attributes:

<CFSETTING ENABLECFOUTPUTONLY="yes/no"
           SHOWDEBUGOUTPUT="yes/no"
           CATCHEXCEPTIONSBYPATTERN="yes/no>

The first attribute, ENABLECFOUTPUTONLY, suppresses all HTML output, including whitespace, within a CFSETTING block. When ENABLECFOUTPUTONLY is set to Yes, only HTML code generated within a CFOUTPUT block is output to the browser:

<CFSETTING ENABLECFOUTPUTONLY="Yes">
 
 
 
 
<CFOUTPUT>
You should be able to see this...
</CFOUTPUT>
But not this...
<CFSETTING ENABLECFOUTPUTONLY="No">
Of course you can see this!

When the ENABLECFOUTPUTONLY attribute is used, CFSETTING tags must occur in matched pairs, where the first tag turns on the output suppression (Yes) and the second tag turns it off (No). CFSETTING tags may be nested any number of levels as long as there are always matching tag pairs.

The second attribute you can use with the CFSETTING tag is SHOWDEBUGOUTPUT. This optional attribute takes a Yes/No value that indicates whether to suppress debugging information normally output to ColdFusion templates when debugging is turned on in the ColdFusion Administrator. The default value for SHOWDEBUGOUTPUT is Yes. You don't need to use paired CFSETTING tags with the SHOWDEBUGOUTPUT attribute unless you are using it in combination with an ENABLECFOUTPUTONLY attribute.

The final attribute, CATCHEXCEPTIONSBYPATTERN, is also optional. This attribute takes a Yes/No value and indicates whether to override structured exception handling. This attribute was introduced in ColdFusion 4.5 to handle incompatibility issues arising from changes to ColdFusion's structured exception handling in Version 4.5. In Version 4.0.x of ColdFusion, exceptions caught with CFTRY and CFCATCH were handled by the first CFCATCH block capable of dealing with the type of exception generated. In ColdFusion 4.5, exceptions are handled by the CFCATCH block that is best able to deal with the exception. The default value is No. Structured exception handling is discussed in Chapter 9. You don't need to use paired CFSETTING tags with the CATCHEXCEPTIONBYPATTERN attribute unless it is used in combination with an ENABLECFOUTPUTONLY attribute.

Suppressing output

The CFSILENT tag suppresses all output produced between CFSILENT tag pairs. CFSILENT can suppress generated whitespace in Application.cfm templates as well as in instances where your template does a lot of looping but doesn't produce any output. CFSILENT is similar to the CFSETTING tag except that it doesn't allow any content to be generated. The following code shows how the tag can be used with a typical loop to suppress whitespace:

<CFSILENT>
<CFLOOP INDEX="i" FROM="1" TO="1000">
  <CFSET i = i+1>
</CFLOOP>
</CFSILENT>
 
<CFOUTPUT>
#i#
</CFOUTPUT>

If you execute this template and view the source in your web browser, notice that the number 1001 appears at the top of the page. If you remove the CFSILENT tags, rerun the template, and view the source again, notice that an awful lot of whitespace appears at the top of the page, resulting in the need to scroll considerably to reach the number 1001 in the source code.

Although it may appear to make more sense to use the CFSETTING tag instead of CFSILENT, the CFSILENT tag is better at eliminating whitespace than CFSETTING and should be used when output generation isn't a factor.

Suppressing whitespace

The CFPROCESSINGDIRECTIVE tag specifies a p-code compiler processing option to suppress all whitespace produced by the ColdFusion server (spaces, tabs, carriage returns, and linefeeds) within an executing CFML template. CFPROCESSINGDIRECTIVE tags must always occur in matched pairs and may be nested. CFPROCESSINGDIRECTIVE settings don't apply to templates called via CFINCLUDE, CFMODULE, or as custom tags. The syntax for using the CFPROCESS-INGDIRECTIVE tag is as follows:

<CFPROCESSINGDIRECTIVE SUPPRESSWHITESPACE="yes/no">
CFML...
</CFPROCESSINGDIRECTIVE>

The SUPPRESSWHITESPACE attribute is required and indicates whether ColdFusion should suppress all whitespace between CFPROCESSINGDIRECTIVE tag pairs. The ColdFusion Administrator contains an option in the Settings section that allows you to enable this suppression of whitespace by default. If this option is enabled, it may be overridden by setting a CFPROCESSINGDIRECTIVE tag pair to No within a CFML template.

Drill-Down Queries

A drill-down query is one that starts by retrieving and displaying a relatively broad or general result set. Then, hyperlinks from one or more columns in the result set are used to call another template that performs a query based on URL parameters passed by the hyperlinks. This process is designed to narrow the number of records returned until a desired level of granularity is achieved, hence the name drill-down query.

For drill-down queries, you usually need two templates, but it is possible to use as many as you want to achieve the level of granularity you need. In a two-template drill-down application, the first template queries a data source and displays a summary (usually just a few fields) of every record in the data source meeting the user's criteria. Hyperlinks from some of the fields in these results pass the primary key values of records to the second template. The second template then performs a query using the primary key value passed in via URL in the WHERE clause of the SELECT statement. The results (usually the full record) are then output to the browser. Example 11-6 demonstrates how a two-template drill-down query works by querying the EmployeeDirectory table and generating an HTML table containing the Name, Title, and Department of each employee.

Example 11-6: Initial Screen Listing Partial Information About Each Record

<!--- retrieve a list of all employees in the EmployeeDirectory table --->
<CFQUERY NAME="GetEmployeeList" DATASOURCE="ProgrammingCF">
         SELECT ID, Name, Title, Department 
         FROM EmployeeDirectory
</CFQUERY>
 
<HTML>
<HEAD>
    <TITLE>Drilldown Example</TITLE>
</HEAD>
 
<BODY>
 
<H2>Drilldown Query Example</H2>
<B>Click on an employee's name to retrieve the full employee record as well 
as a list of all incentive awards granted to the employee<B>
<P>
<TABLE CELLPADDING="3" CELLSPACING="0">
<TR BGCOLOR="#888888">
  <TH>Name</TH>
  <TH>Title</TH>
  <TH>Department</TH>
</TR>
 
<!--- dynamically build an HTML table containing the list of employees from
      the GetEmployeeList query.  Create a hyperlink for the Name field
      that points to a template called DrillDown.cfm and pass the value
      of the ID field as a query parameter, identifying the record --->
<CFOUTPUT QUERY="GetEmployeeList">
<TR BGCOLOR="##C0C0C0">
  <TD><A HREF="Drilldown.cfm?ID=#ID#">#Name#</A></TD>
  <TD>#Title#</TD>
  <TD>#Department#</TD>
</TR>
</CFOUTPUT>
</TABLE>
 
</BODY>
</HTML>

Example 11-6 queries the EmployeeDirectory table and returns a result set containing the Name, Title, and Department of every employee in the table. The result set dynamically generates an HTML table with a row for each record. Each name is displayed as a hyperlink that points to a template called Drilldown.cfm (shown in Example 11-7). Clicking on any one of the names calls the Drilldown.cfm template and passes the ID value (the primary key) associated with the name as a URL parameter so that the Drilldown.cfm template knows which record to drill down on. The initial results screen is shown in Figure 11-4.

When the Drilldown.cfm template in Example 11-7 is called, the first thing it does is execute a query to retrieve all the fields in the record whose ID matches the value specified by the URL.ID parameter. These values can also have been passed by form field, but for our example we'll do it this way. Just for fun, a second query is made to the IncentiveAwards table to retrieve any awards the employee has been granted.

Figure 11-4. Initial results screen with hyperlink to drill-down template

Example 11-7: Drill-Down Screen for Displaying Detail Information

<!--- retrieve the full record of the employee whose ID was passed in 
      as a URL parameter. --->
<CFQUERY NAME="GetEmployeeRecord" DATASOURCE="ProgrammingCF">
         SELECT Name, Title, Department, Email, PhoneExt, Salary
         FROM EmployeeDirectory
         WHERE ID = #URL.ID#
</CFQUERY>
 
<!--- query the IncentiveAwards table and retrieve all records for
      for the ID passed in as a URL parameter.  This query can return
      0 or more records --->
<CFQUERY NAME="GetIncentiveAwards" DATASOURCE="ProgrammingCF">
         SELECT DateAwarded, Category, Amount
         FROM IncentiveAwards
         WHERE ID = #URL.ID#
</CFQUERY>
 
<HTML>
<HEAD>
     <TITLE>Drilldown Example</TITLE>
</HEAD>
 
<BODY>
 
<H2>Employee Profile</H2>
 
<TABLE CELLPADDING="3" CELLSPACING="0">
<TR BGCOLOR="#888888">
  <TH>Name</TH>
  <TH>Title</TH>
  <TH>Department</TH>
  <TH>E-mail</TH>
  <TH>Phone Extension</TH>
  <TH>Salary</TH>
</TR>    
<!--- generate an HTLML table containing the employee record from the 
      GetEmployeeRecord query. --->
<CFOUTPUT>
<TR BGCOLOR="##C0C0C0">
  <TD>#GetEmployeeRecord.Name#</TD>
  <TD>#GetEmployeeRecord.Title#</TD>
  <TD>#GetEmployeeRecord.Department#</TD>
  <TD><A HREF="Mailto:#GetEmployeeRecord.Email#">
      #GetEmployeeRecord.Email#</A></TD>
  <TD>#GetEmployeeRecord.PhoneExt#</TD>
  <TD>#GetEmployeeRecord.Salary#</TD>
</TR>    
</CFOUTPUT>
</TABLE>
 
<H3>Incentive Awards</H3>
 
<!--- only display the table if 1 or more awards are found in the database --->
<CFIF GetIncentiveAwards.RecordCount GT 0>
<TABLE CELLPADDING="3" CELLSPACING="0">
<TR BGCOLOR="#888888">
    <TH>Date Awarded</TH>
    <TH>Incentive Type</TH>
    <TH>Amount</TH>
</TR>
 
<!--- generate an HTML table listing the awards granted the employee --->    
<CFOUTPUT QUERY="GetIncentiveAwards">
<TR BGCOLOR="##C0C0C0">
    <TD>#DateFormat(DateAwarded, 'mm/dd/yyyy')#</TD>
    <TD>#Category#</TD>
    <TD>#DollarFormat(Amount)#</TD>
</TR>
</CFOUTPUT>
</TABLE>
 
<CFELSE>
<B>No incentive awards granted.</B>
</CFIF>
 
</BODY>
</HTML>

Next, two HTML tables are generated from the query results. The first table contains all the information about the employee stored in the EmployeeDirectory table. The second table lists any awards the employee has earned. If no awards are found for the employee, a message to that effect is output in lieu of the table. The results of the Drilldown.cfm template are shown in Figure 11-5.

Figure 11-5. Drill-down template displaying entire employee record

Query Caching

If you want to squeeze every last bit of performance out of your ColdFusion applications (and who doesn't?), you might want to consider query caching. Query caching allows you to retrieve query result sets from memory as opposed to requiring a round trip to the database. This can significantly reduce the amount of time it takes to return a result set in your application. Query caching is implemented using the CFQUERY tag and one of two optional attributes:

CACHEDAFTER

Specifies a date for using cached query data. Cached query data is used only if the date of the original query is after the date specified in CACHEDAFTER.

CACHEDWITHIN

Specifies a time span (using the CreateTimeSpan( ) function) for using cached query data.

Query caching is especially useful in situations where you repeatedly execute the same query to obtain a result set that remains static for a known period of time. Some examples of queries that are candidates for caching include:

• A query that retrieves a "what's new" list that is updated once a day
• A query that retrieves a company's closing stock price on a heavily trafficked Intranet site that is updated once a day
• A query that retrieves a list of users to use in an administration application

Regardless of the type of query you want to cache, one guideline is absolute: the CFQUERY statement (including the SQL) that references the cached data must be exactly the same every time. For this reason, queries that use dynamic SQL aren't candidates for caching, unless you can created a cached query for every possible query combination.

Because cached queries take up server memory, the ColdFusion Administrator has a setting under Caching that allows you to specify the maximum number of cached queries to keep in memory. Cached queries are managed in a FIFO (first in, first out) manor so that when the threshold for allowable queries is reached, older queries are pushed out as newer ones are brought in. To disable query caching, set the maximum number of cached queries to 0.

Example 11-8 queries the EmployeeDirectory table of the ProgrammingCF database. If a cached query exists that is less than one hour old, the cached result set is used. If not, a live query is performed, and the result set is then cached.

Example 11-8: Using CACHEDWITHIN to Cache a Query

<!--- query the employeedirectory table.  If a cached result set that is less 
      than 1 hour old, use it.  Otherwise, perform a new query and cache the 
      result set. --->
<CFQUERY NAME="GetEmployeeInfo" DATASOURCE="ProgrammingCF"
         CACHEDWITHIN="#CreateTimeSpan(0,1,0,0)#">
         SELECT Name, Title, Department, Email, PhoneExt
         FROM EmployeeDirectory
</CFQUERY>
 
<HTML>
<HEAD>
    <TITLE>Cachedwithin Example</TITLE>
</HEAD>
 
<BODY>
 
<!--- output the result set.  If you have debugging turned on, you will be able
      to see whether the query was live or cached --->
<TABLE CELLPADDING="3" CELLSPACING="0">
<TR BGCOLOR="#888888">
  <TH>Name</TH>
  <TH>Title</TH>
  <TH>Department</TH>
  <TH>E-mail</TH>
  <TH>Phone Extension</TH>
</TR>    
<CFOUTPUT QUERY="GetEmployeeInfo">
<TR BGCOLOR="##C0C0C0">
  <TD>#Name#</TD>
  <TD>#Title#</TD>
  <TD>#Department#</TD>
  <TD><A HREF="Mailto:#Email#">#Email#</A></TD>
  <TD>#PhoneExt#</TD>
</TR>    
</CFOUTPUT>
</TABLE>
 
</BODY>
</HTML>

The CACHEDWITHIN attribute of the CFQUERY tag handles all the caching. Use the CreateTimeSpan( ) function to specify the amount of time the cached query should persist. In our example, we set the cached query to persist for one hour. Every time the query is called, the ColdFusion checks to see if the time associated with the cached query is more than one hour older than the time associated with the current request. If not, the cached data is used. If, however, it is older, a new query is run, the results are cached, and the timer refreshed.

We can easily rewrite this example to use CACHEDAFTER instead of CACHEDWITHIN. Instead of providing a time span for the cached query, CACHEDAFTER provides a date after which all queries should be cached:

<CFQUERY NAME="GetEmployeeInfo" DATASOURCE="ProgrammingCF" 
         CACHEDAFTER="06/15/2000">

Persistent queries created with CACHEDAFTER don't expire automatically as do those created with CACHEDWITHIN.

To see the difference between a normal query and a cached query, run the template in Example 11-8 (make sure debugging is turned on in the ColdFusion Administrator). The first time you run the template, you will see a processing time associated with the query of approximately 30 milliseconds. This is to be expected, as the query wasn't actually cached until after you executed it for the first time. If you hit the reload button on your browser and refresh the page, you should see something different (as shown in Figure 11-6).

Figure 11-6. Reloading the template retrieves cached data

This time, the words "Cached Query" should appear where the processing time for the query was previously displayed. This lets you know that the query you just ran came from the cache and not from the data source.

Advanced SQL

This section moves beyond the basic database manipulation techniques we discussed in earlier chapters. Here, we'll cover methods for creating dynamic SQL, creating and modifying database tables using SQL, using aggregate and scalar functions, performing table unions and joins, and several other database-manipulation techniques. These are the kinds of operations that allow you to interact with databases at a higher level. Most advanced applications such as shopping carts, threaded discussion lists, and business-to-business applications use one or more of the techniques described in this section.

Dynamic SQL

An extremely powerful feature of ColdFusion is the ability to generate dynamic SQL queries based on a variety of inputs. In Chapter 3, you learned how to pass a single dynamic value in an SQL statement:

SELECT Name, Title, Department
FROM EmployeeDirectory
WHERE ID = #ID#

We extend this concept a bit in Example 11-9 to allow for completely dynamic SQL in the WHERE clause of a SQL statement.

Example 11-9: HTML Form for Searching Database Records

<!--- query the EmployeeDirectory table for a list of departments --->
<CFQUERY NAME="GetDepartments" DATASOURCE="ProgrammingCF">
         SELECT DISTINCT Department
         FROM EmployeeDirectory
         ORDER BY Department
</CFQUERY>
 
<H2>Locate a User</H2>
<FORM ACTION="Search.cfm" METHOD="post">
 
<TABLE>
<TR>
  <TD>Name:</TD>
  <TD><INPUT TYPE="text" NAME="Name" SIZE="20" MAXLENGTH="80"></TD>
</TR>
<TR>
  <TD>Title:</TD>
  <TD><INPUT TYPE="text" NAME="Title" SIZE="20" MAXLENGTH="80"></TD>
</TR>
<TR>
  <TD>Department:</TD>
  <TD><SELECT NAME="Department" SIZE="5" MULTIPLE>
      <CFOUTPUT QUERY="GetDepartments">
        <OPTION VALUE="#Department#">#Department#</OPTION>
      </CFOUTPUT>
      </SELECT>
</TR>
</TABLE>
<INPUT TYPE="submit" VALUE="Submit">
</FORM>

Example 11-9 generates an HTML form for the user to specify search criteria when constructing a dynamic SQL statement to retrieve matching records from the EmployeeDirectory table. The form contains a field where the user can enter a name, another for title, and a multiple select list where he can choose one or more departments to narrow down the search. The list of department names in the multiple select list is obtained by performing a query using the DISTINCT keyword against the Department column in the EmployeeDirectory table. The DISTINCT keyword is covered in more detail later in this chapter. Note that the variable name in the VALUE attribute of the OPTION tag is enclosed in a set of single quotes. Because we'll be passing the values from the Department field as a delimited list of text values, it is necessary to enclose each value in the list in single quotes. If the values were numeric, this wouldn't be necessary.

Once you fill out and submit the search form, the search criteria are posted to the Search.cfm template shown in Example 11-10.

Example 11-10: Searching Database Records Using Dynamically Generated SQL

<!--- set a default of "" for Form.Department since the parameter isn't passed
      if no department is selected --->
<CFPARAM NAME="Form.Department" DEFAULT="">
 
<!--- query the EmployeeDirectory table with an SQL statement dynamically
      generated by the parameters passed in as form field values. --->
<CFQUERY NAME="GetRecords" DATASOURCE="ProgrammingCF">
         SELECT Name, Title, Department, Email, PhoneExt, Salary
         FROM EmployeeDirectory
         WHERE 0=0
<!--- if a value is passed for Name, use the SQL LIKE command and the %
      wildcard to include a wildcarded search for the Name, including it
      in the SQL statement using the AND operator. --->
<CFIF Form.Name NEQ "">
    AND Name LIKE '%#Form.Name#%'
</CFIF>
 
<!--- if a value is passed for Title, use the SQL LIKE command and the %
      wildcard to include a wildcarded search for the Title, including it
      in the SQL statement using the AND operator. --->
<CFIF Form.Title NEQ "">
    AND Title LIKE '%#Form.Title#%'
</CFIF>
 
<!--- if the value passed for Department is "", omit the AND statement 
      for Department, removing Department as a criteria.  If Department 
      contains any values other than "", use those values to construct 
      the dynamic AND for Department. --->
<CFIF Form.Department NEQ "">
    AND Department IN (#ListQualify(Form.Department, "'")#)
</CFIF>
</CFQUERY>
             
<CFOUTPUT>
<B>#GetRecords.RecordCount# records matched your search criteria:</B><BR>
Name like: #Form.Name#<BR>
Title like: #Form.Title#<BR>
Department: #Form.Department#
</CFOUTPUT>
 
<P>
<TABLE CELLPADDING="3" CELLSPACING="0">
<TR BGCOLOR="#888888">
    <TH>Name</TH>
    <TH>Title</TH>
    <TH>Department</TH>
    <TH>E-mail</TH>
    <TH>Phone Extension</TH>
    <TH>Salary</TH>
</TR>    
<!--- generate an HTLML table containing all of the records matching the
      search criteria --->
<CFOUTPUT QUERY="GetRecords">
<TR BGCOLOR="##C0C0C0">
    <TD>#Name#</TD>
    <TD>#Title#</TD>
    <TD>#Department#</TD>
    <TD><A HREF="Mailto:#Email#">#Email#</A></TD>
    <TD>#PhoneExt#</TD>
    <TD>#Salary#</TD>
</TR>    
</CFOUTPUT>
</TABLE>

Example 11-10 queries the EmployeeDirectory table with a dynamically generated SQL statement. The exact contents of the SQL statement may vary depending on the form-field values passed into the template. Note that the WHERE 0=0 clause is necessary in the CFQUERY tag, in order to build the dynamic WHERE statement. 0=0 is another way of saying True, and provides the initial condition to which to attach any dynamically generated AND statements. If no search parameters are passed, the 0=0 prevents ColdFusion from throwing an error.

CFIF statements evaluate the form-field values and generate the necessary SQL. If values are passed for Name and Title, the SQL LIKE clause adds wildcarded searches to the WHERE statement. Finally, we have to deal with the Department. Because the Department form control is a multiple selection list, we have to use the PreserveSingleQuotes( ) function to keep the single quotes around the values we passed in, so they can be used in the IN statement. Once we've built up the query, it is a simple matter to output the results to the browser.

Note that in general, it is preferable not to use the SQL LIKE operator to perform wildcard searches because of the amount of database overhead associated with full-text searching. SQL (as a query language) was never meant to handle full-text searching (there are very few operators that facilitate text searches). For serious full-text indexing and searching, consider the Verity search interface included with ColdFusion and discussed in more detail in Chapter 16. Additionally, some databases such as MS SQL Server 7.0 contain extensions to the database to facilitate full-text searching. Consult your database documentation for more information on how full-text searching is handled by your database.

Creating and Modifying Tables

It is possible to use SQL to handle such tasks as creating a new table, creating a new table and populating it with data from an existing table, modifying the design of a table, and deleting a table. These options are especially useful for developers working remotely without physical access to their data sources. Four SQL commands are available to facilitate these tasks: CREATE TABLE, SELECT INTO, ALTER TABLE, and DROP TABLE. The descriptions and examples given in this section are meant to provide a general overview of each function. Actual implementation and syntax varies from database to database. Consult your database documentation for specific information on how your particular database handles each function.

Creating new tables

The CREATE TABLE statement creates a new database table in the specified data source. Example 11-11 creates a new table called EmployeeDirectory2 with the same structure as the EmployeeDirectory table referred to throughout this book.

Example 11-11: Creating a New Table Using CREATE TABLE

<!--- create the EmployeeDirectory table --->
<CFQUERY NAME="CreateTable" DATASOURCE="ProgrammingCF">
CREATE TABLE EmployeeDirectory2 (
             ID counter,
             Name varchar(255),
             Title varchar(255),
             Department varchar(255),
             Email varchar(255),
             PhoneExt integer,
             Salary numeric,
       CONSTRAINT ID PRIMARY KEY (ID)
)
 
</CFQUERY>
 
Employee Directory table created.

Each column you wish to add to the newly created table takes the syntax:

column_name data_type[(length)] [constraint]  

Datatypes vary depending on the database you are using. Some of the more common datatypes are: Bit, Byte, Char, Character, Dec, Date, DateTime, Decimal, Float, Int, Integer, Long, Memo, Numeric, Real, Short, SmallInt, Text, Time, TimeStamp, TinyInt, and Varchar. Constraints also vary from database to database. Some common constraints are: CHECK, DEFAULT, FOREIGN KEY, IDENTITY, INDEX, PRIMARY KEY, [NOT] NULL, and UNIQUE.

Populating new tables with existing data

The SELECT INTO statement creates a new database table and populates it with data from an existing table. Example 11-12 demonstrates the SELECT INTO statement by selecting the name, title, email address, and phone extension for each employee in the EmployeeDirectory table who belongs to the IT department. The resulting record set is then used to populate a new table called ITDirectory.

Example 11-12: Using SELECT INTO to Create a Copy of a Table

<!--- Select the name, title, email, and phone ext for each employee in
      the EmployeeDirectory table that belongs to the IT department and  
      use it to populate a new table called ITDirectory --->
<CFQUERY NAME="MakeITDirectory" DATASOURCE="ProgrammingCF">
         SELECT Name, Title, Email, PhoneExt
         INTO ITDirectory
         FROM EmployeeDirectory
         WHERE Department = 'IT'
</CFQUERY>
 
<!--- retrieve all of the records from the ITDirectory table we just 
      created --->
<CFQUERY NAME="GetEmployees" DATASOURCE="ProgrammingCF">
         SELECT Name, Title, Email, PhoneExt
         FROM ITDirectory
         ORDER BY Name
</CFQUERY>
 
<H2>ITDirectorty table successfully created and populated with data:</H2>
 
<TABLE CELLPADDING="3" CELLSPACING="0">
<TR BGCOLOR="#888888">
  <TH>Name</TH>
  <TH>Title</TH>
  <TH>E-mail</TH>
  <TH>Phone Ext.</TH>
</TR>
 
<!--- dynamically generate a table containing all of the records returned 
      by the query --->
<CFOUTPUT QUERY="GetEmployees">
<TR BGCOLOR="##C0C0C0">
  <TD>#Name#</TD>
  <TD>#Title#</TD>
  <TD>#Email#</TD>
  <TD>#PhoneExt#</TD>
</TR>
</CFOUTPUT>
</TABLE>

Altering table design

The ALTER TABLE statement alters the design of an existing database table. You can use ALTER TABLE to add, modify the properties of, or delete a column from a specified table. The syntax for using ALTER TABLE is similar to the syntax used by CREATE TABLE as shown in the following code fragments:

<!--- add new column called DateHired to the Employee Directory Table --->
<CFQUERY NAME="Add" DATASOURCE="ProgrammingCF">
         ALTER TABLE EmployeeDirectory
               ADD COLUMN DateHired Varchar(8)
</CFQUERY>
 
<!--- modify the datatype of the DateHired column from varchar to date --->
<CFQUERY NAME="Alter" DATASOURCE="ProgrammingCF">
         ALTER TABLE EmployeeDirectory
               ALTER COLUMN DateHired Date
</CFQUERY>
 
<!--- drop (remove) the DateHired column from the table --->
<CFQUERY NAME="AddDateHired" DATASOURCE="ProgrammingCF">
         ALTER TABLE EmployeeDirectory
               DROP COLUMN DateHired
</CFQUERY>
 
Column added, altered, and deleted!

As you can see, you can perform three different actions with ALTER TABLE. You can choose to ADD, ALTER, or DROP a particular column to/from your table. Some databases let you use an additional clause called DROP CONSTRAINT to remove a named constraint from your schema.

Deleting tables

The DROP TABLE statement deletes an existing table (including all data) from a database:

<CFQUERY NAME="DropITDirectory" DATASOURCE="ProgrammingCF">
         DROP TABLE ITDirectory
</CFQUERY>

You should exercise caution when using the DROP TABLE statement. Once a table has been dropped, it is permanently deleted from the database. Before we go on, you should also note that DROP TABLE, ALTER TABLE, and CREATE TABLE can be used with stored procedures, triggers, views, and any other objects supported by your database.

Retrieving Unique Values

The DISTINCT keyword is used in a SELECT statement to retrieve all unique values stored in a specified column. Any duplicate values in the specified column are discarded. For example, if you want to retrieve all the unique department names stored in the Departments column of the EmployeeDirectory table, you can use the DISTINCT keyword:

<!--- query the employeedirectory table for a list of departments --->
<CFQUERY NAME="GetDepartment" DATASOURCE="ProgrammingCF">
         SELECT DISTINCT Department
         FROM EmployeeDirectory
         ORDER BY Department
</CFQUERY>

Using Column Aliases

Aliases allow you to provide an alternate name to reference a particular query column. Aliases have three general uses:

• In situations where the field names used in a database aren't descriptive
• To deal with nonsupported column names, such as those that contain spaces or special characters
• With scalar and aggregate functions (covered later in this chapter)

To create an alias for a field name you use the AS operator in a SELECT statement:

SELECT ItmN AS ItemNumber
FROM MyTable

You specify the original name of the column to retrieve, in this case ItmN, followed by the AS operator and the alias name, ItemNumber.

To get a better idea of how an alias works, consider the code in Example 11-13.

Example 11-13: Creating Aliases for Query Column Names

<!--- retrieve all records from the database.  Provide aliases for some of the
      field names.  Note that the Name field (not the alias) is used in the SORT 
      BY clause. --->
<CFQUERY NAME="GetEmployeeInfo" DATASOURCE="ProgrammingCF">
         SELECT Name AS EmployeeName, Title, Department, 
                Email AS EmailAddress, PhoneExt AS PhoneExtension
         FROM EmployeeDirectory
         ORDER BY Name
</CFQUERY>
 
<H3>Using Column Aliases</H3>
<!--- create an HTML table for outputting the query results.  This section 
      creates the first row of the table - used to hold the column 
      headers --->
<TABLE CELLPADDING="3" CELLSPACING="0">
<TR BGCOLOR="#888888">
    <TH>Employee Name</TH>
    <TH>Title</TH>
    <TH>Department</TH>
    <TH>E-mail Address</TH>
    <TH>Phone Extension</TH>
</TR>
 
<!--- Output the query results. Use the new field names to refer to the
      aliases column names. --->     
<CFOUTPUT QUERY="GetEmployeeInfo">
<TR BGCOLOR="##C0C0C0">
   <TD>#EmployeeName#</TD>
   <TD>#Title#</TD>
   <TD>#Department#</TD>
   <TD><A HREF="Mailto:#EmailAddress#">#EmailAddress#</A></TD>
   <TD>#PhoneExtension#</TD>
</TR>    
</CFOUTPUT>
</TABLE>

Example 11-13 queries the EmployeeDirectory table of the ProgrammingCF data source and assigns aliases for the Name, Email, and PhoneExt fields. Next, an HTML table is dynamically generated from the query results. The aliased column names are used in place of the original column names to generate the output.

To escape nonsupported column names (such as those containing spaces, characters, and especially pound signs), you can use the back quote (`) character. In this case, the SQL AS keyword is used to alias column name as in the following example:

SELECT `Item Number` AS ItemNumber
FROM MyTable

You can also use the back quote (`) character to escape field names containing pound signs. To keep ColdFusion from throwing an error, be sure to escape the pound sign by doubling it up as in this example:

SELECT `Item ##` AS ItemNumber
FROM MyTable

Depending on the database you are working with, you may need to use a character or characters other than the back quote to identify special fields. If you get an error using the back quote, try surrounding the field name in square brackets as in [Item Number] or in parentheses with double quotation marks, ("Item Number").

Scalar Functions

Scalar functions let you format record-set data at the database level before it is returned to your ColdFusion application. Many scalar functions have equivalent functions in ColdFusion (even identical names for some). For example, the scalar function Left( ) is the same as the Left( ) function in ColdFusion.

The ODBC specification (as outlined in the Microsoft ODBC SDK Programmer's Reference, available online at http://msdn.microsoft.com/downloads/sdks/platform/database.asp) contains a list of driver-independent ODBC scalar functions for use with SQL. Because support for these functions varies from driver to driver, it is important that you consult your database driver's documentation for a list of supported scalar functions.

At this point, you may be asking yourself why bother using scalar functions in your SQL statements when you can just code the functions in CFML. There is an inherent advantage to using scalar functions on the database side, as opposed to waiting until the data has been transferred. If you think in terms of performance, it makes sense to let the database handle any data manipulation and formatting so that ColdFusion is free to process other tasks. To illustrate the point, let's consider two examples.

The following example retrieves all the article titles from a table called News, then outputs the first 50 characters using the CFML Left( ) function:

<CFQUERY NAME="GetTitles" DATASOURCE="ProgrammingCF">
         SELECT Title
         FROM News
</CFQUERY>
 
<CFOUTPUT QUERY="GetTitles">
#Left(Title, 50)#<BR>
</CFOUTPUT>

Now look at the same example using the scalar function Left( ) instead:

<CFQUERY NAME="GetTitle" DATASOURCE="ProgrammingCF">
         SELECT {fn Left(Title, 50)} As ShortTitle
         FROM News
</CFQUERY>
 
<CFOUTPUT QUERY="GetTitle">
#ShortTitle#<BR>
</CFOUTPUT>

In this case, using the Left( ) scalar function saves processing time and memory on the ColdFusion server, since the result set returned by the query contains only the first 50 characters of each title as opposed to the entire title as in the previous example. Note the use of {fn ...} around the ODBC Left( ) function. While this notation isn't always necessary (it depends on your ODBC driver), I recommend you use it to help visually separate ODBC functions from CFML functions in your code and avoid any confusion. This scenario obviously shows just a simple example of how to use scalar functions to improve performance. It all comes down to one thing--returning the minimum amount of data possible in the most useful format.

Aggregate Functions

You can use aggregate functions to summarize data within a database. Aggregate functions are most often used to create reports that answer such questions as the following. How many employees are in each department? How many widgets were sold in the month of March? Can you break down widget sales by region? What was the date of the first press release issued by the company? What is the average employee salary?

Here are the aggregate functions commonly associated with most databases:

MIN(Fieldname)

Returns the minimum value (numeric, date, or character) in a column

MAX(fieldname)

Returns the maximum value (numeric, date, or character) in a column

AVG(Fieldname)

Returns the average value in a column of numeric values

SUM(Fieldname)

Returns the sum of all values in a column of numeric values

COUNT(Fieldname)

Returns the number of rows for a given column name that don't contain null values. To count the number of unique row values for a given column, use the DISTINCT keyword as in COUNT(DISTINCT Fieldname)

COUNT(*)

Returns the total number of rows in a table. If you use a WHERE clause, this function provides the number of rows returned in the result set

Although ColdFusion has its own functions that can provide the same functionality as the aggregate functions, you should let the database handle calculations whenever possible. From a performance standpoint, databases are optimized to manipulate data whereas ColdFusion is less so. For this reason alone, it makes sense to offload as much processing as you can from ColdFusion to your database. To see how simple aggregate functions can make life easier, consider the following code, which uses the COUNT(*) function to retrieve the total number of records in the EmployeeDirectory table:

<!--- Retrieve a count of the total number of records in the EmployeeDirectory
      table of the database.  You should use COUNT(*) as opposed to COUNT for
      this operation as it is faster.  --->
<CFQUERY NAME="GetTotalRecords" DATASOURCE="ProgrammingCF">
         SELECT Count(*) AS TotalRecords
         FROM EmployeeDirectory
</CFQUERY>
 
<H3>Using COUNT(*)</H3>
<CFOUTPUT>
Total Records in the EmployeeDirectory Table: #GetTotalRecords.TotalRecords#
</CFOUTPUT>

Of course, you can do the same thing by querying an arbitrary column using the CFQUERY tag, then using the queryname.RecordCount variable to output the total number of records retrieved by the query. As simple as this seems, it actually wastes a fair amount of resources. Using the CFQUERY tag to query a single field returns all the data associated with that field. So, if you query a table that happens to have one million rows of data in it, you are going to get a result set back that contains one million records. From a performance standpoint, not only will the process take forever, but your server will most likely run out of memory as ColdFusion attempts to store the entire result set. By using the COUNT(*) method instead, the database does all the work and returns only a single record back to ColdFusion that contains the total number of rows in the table.

You can also use aggregate functions to provide summarization on groups of related data. For example, if you want to know how many employees are in each department, you could use the COUNT (not COUNT(*)) function along with the GROUP BY clause to find out, as shown in Example 11-14.

Example 11-14: Counting Employees in Each Department

<!--- Retrieve a count of the number of employees for each department in 
      the EmployeeDirectory table of the database --->
<CFQUERY NAME="GetDepartment" DATASOURCE="ProgrammingCF">
         SELECT COUNT(Name) AS TotalEmployees,
                Department
         FROM EmployeeDirectory
         GROUP BY Department
</CFQUERY>
 
<H3>Using COUNT and GROUP BY to return the Total Number of Employees for 
    each Department</H3>
<TABLE BORDER="0" CELLPADDING="3">
<TR BGCOLOR="#C0C0C0">
  <TH>Department</TH>
  <TH>Total Employees</TH>
</TR>
 
<CFOUTPUT QUERY="GetDepartment">
<TR BGCOLOR="##E3E3E3">
  <TD>#Department#</TD>
  <TD>#TotalEmployees#</TD>
</TR>
</CFOUTPUT>
</TABLE>

You can provide additional filtering of grouped data with a HAVING clause. HAVING works just like the WHERE clause except the filtering takes place after the data has been grouped. In addition, HAVING allows you to specify an aggregate function, whereas the WHERE statement doesn't. Example 11-15 modifies the code in from Example 11-14 so that only departments that have two or more employees are returned by the query.

Example 11-15: Displaying Departments That Have Two or More Employees

<!--- Retrieve a count of the number of employees for each department in 
      the EmployeeDirectory table of the database where the total number
      of employees is greater than or equal to two. --->
<CFQUERY NAME="GetDepartment" DATASOURCE="ProgrammingCF">
         SELECT COUNT(Name) AS TotalEmployees,
                Department
         FROM EmployeeDirectory
         GROUP BY Department
         HAVING COUNT(Name) >= 2
</CFQUERY>
 
<H3>Using COUNT, GROUP BY, and HAVING to return the Total Number of Employees 
    for each Department where the total number of employees is greater than or 
    equal to two </H3>
<TABLE BORDER="0" CELLPADDING="3">
<TR BGCOLOR="#C0C0C0">
  <TH>Department</TH>
  <TH>Total Employees</TH>
</TR>
 
<CFOUTPUT QUERY="GetDepartment">
<TR BGCOLOR="##E3E3E3">
  <TD>#Department#</TD>
  <TD>#TotalEmployees#</TD>
</TR>
</CFOUTPUT>
</TABLE>

Subqueries

As the name implies, a subquery is a query that exists within another query. Subqueries can be used inside the SELECT, INSERT, UPDATE, and DELETE queries. In the case of SELECT queries, subqueries are often used along with aggregate functions to create a summarized column from data contained in the other columns. Subqueries can also associate data from different tables (much like a join, which we'll cover shortly).

Example 11-16 uses a subquery along with an aggregate function to calculate the average salary of all employees in the EmployeeDirectory table.

Example 11-16: Using a Subquery Along with an Aggregate Function

<!--- Retrieve employee records from the EmpoloyeeDirectory.  Employ a
      subquery to obtain the average salary of all employees  --->
<CFQUERY NAME="GetSalaries" DATASOURCE="ProgrammingCF">
         SELECT Name, Title, Department, 
                Salary, (SELECT AVG(Salary)
                         FROM EmployeeDirectory) AS AverageSalary
         FROM EmployeeDirectory
         ORDER BY Name
</CFQUERY>
 
<H3>Average Salary Report</H3>
 
<TABLE CELLPADDING="3" CELLSPACING="0">
<TR BGCOLOR="#888888">
  <TH>Name</TH>
  <TH>Title</TH>
  <TH>Department</TH>
  <TH>Salary</TH>
</TR>
<!--- output the employee records --->    
<CFOUTPUT QUERY="GetSalaries">
<TR BGCOLOR="##C0C0C0">
  <TD>#Name#</TD>
  <TD>#Title#</TD>
  <TD>#Department#</TD>
  <TD ALIGN="Right">#DollarFormat(Salary)#</TD>
</TR>    
</CFOUTPUT>
 
<!--- output the average employee salary.  Note that the QUERY attribute of
      the CFOUTPUT tag was not used.  The query name is prepended to the 
      AverageSalary salary variable --->
<CFOUTPUT>
<TR BGCOLOR="##808080">
  <TD>Average Salary</TD>
  <TD COLSPAN="3" ALIGN="Right">#DollarFormat(GetSalaries.AverageSalary)#</TD>
</TR>
</CFOUTPUT>
</TABLE>

In Example 11-16, a query is run to retrieve the Name, Title, Department, and Salary of each employee in the EmployeeDirectory table. A subquery is used within the SELECT statement to obtain the average of all salaries using the AVG aggregate function. Subqueries returning more than one record can be used only in the WHERE clause. The results are written to the browser in an HTML table that contains a listing of all the employees in the table along with their salaries. The average salary is given at the bottom of the table.

You can also include subqueries in the WHERE clause of a query. One way to do this is with the EXISTS keyword. EXISTS is used only with subqueries and tests for a nonempty record set (you can test for an empty record set by using NOT EXISTS). To see how this is useful, consider Example 11-17, in which we retrieve a list of all employees from the EmployeeDirectory table who have received individual incentive awards of less than $5,000 each in 2000.

Example 11-17: Using EXISTS with a Subquery

<!--- Retrieve a list of employees who received individual incentive awards
      of less than $5000 each in 2000 --->
<CFQUERY NAME="GetSalaries" DATASOURCE="ProgrammingCF">
         SELECT ID, Name, Title, Department
         FROM EmployeeDirectory
         WHERE EXISTS
           (SELECT ID
            FROM IncentiveAwards
            WHERE IncentiveAwards.ID = EmployeeDirectory.ID
            AND Amount < 5000
            AND {fn YEAR(DateAwarded)} = 2000)
         ORDER BY Name
</CFQUERY>
 
<H3>Incentive Awards under $5000 Granted in 1998</H3>
 
<TABLE CELLPADDING="3" CELLSPACING="0">
<TR BGCOLOR="#888888">
  <TH>Name</TH>
  <TH>Title</TH>
  <TH>Department</TH>
</TR>
 
<!--- output the employee records --->    
<CFOUTPUT QUERY="GetSalaries">
<TR BGCOLOR="##C0C0C0">
  <TD>#Name#</TD>
  <TD>#Title#</TD>
  <TD>#Department#</TD>
</TR>    
</CFOUTPUT>
</TABLE>

Example 11-17 uses a few of the advanced techniques we covered so far. In plain English, the query works by saying "select the employees from the Employee-Directory table where a matching record exists in the IncentiveAwards table that meets the criteria set in the subquery".

Subqueries can also be used in the WHERE clause of a SELECT query by using the equal sign (=) or the [NOT] IN operator. Use the equal sign when only one record will be returned by the subquery. If more than one record can be returned, use IN. Here are some example WHERE clauses:

WHERE MyField = (SELECT SomeField
                 FROM SomeTable
                 WHERE OtherField = Value)
 
WHERE MyField IN (SELECT SomeField
                  FROM SomeTable
                  WHERE OtherField = Value)
 
WHERE MyField NOT IN (SELECT SomeField
                  FROM SomeTable
                  WHERE OtherField = Value)

Unions

The UNION clause is used with a SELECT statement to merge result sets from two or more queries into a single result set. In order to use the UNION clause, each result set must contain the same number of columns, with each matching column being of the same datatype. Additionally, each column must have been SELECT'ed in the same order during the formation of the original result sets. Example 11-18 demonstrates how this works.

Example 11-18: Using the UNION Clause to Merge Two Result Sets

<!--- retrieve records from both the ITDirectory and HRDirectory tables
      and merge them using the UNION clause.  This example is somewhat
      impractical as the user's department is not stored in the
      database --->
<CFQUERY NAME="GetEmployees" DATASOURCE="ProgrammingCF">
         SELECT Name, Title, Email, PhoneExt
         FROM ITDirectory
         UNION
         SELECT Name, Title, Email, PhoneExt
         FROM HRDirectory
</CFQUERY>
 
<H2>Combined IT Directory and HR Directory:</H2>
 
<TABLE CELLPADDING="3" CELLSPACING="0">
<TR BGCOLOR="#888888">
  <TH>Name</TH>
  <TH>Title</TH>
  <TH>E-mail</TH>
  <TH>Phone Ext.</TH>
</TR>
 
<!--- dynamically generate a table containing all of the records returned 
      by the query --->
<CFOUTPUT QUERY="GetEmployees">
<TR BGCOLOR="##C0C0C0">
  <TD>#Name#</TD>
  <TD>#Title#</TD>
  <TD>#Email#</TD>
  <TD>#PhoneExt#</TD>
</TR>
</CFOUTPUT>
</TABLE>

In order to get Example 11-18 to work, you need to go back to the code in Example 11-12 and modify it to create a new table called HRDirectory. Simply substitute HRDirectory for ITDirectory in the FROM clause and 'HR' for 'IT' in the WHERE clause and execute the template. You should now have an ITDirectory and an HRDirectory table containing IT employees and HR employees respectively.

Once you have these two tables in your database, go ahead and execute Example 11-18. The template retrieves a list of all employees from both tables and merges the result sets using the UNION clause. An HTML table containing the records from the merged result set is dynamically generated.

Joins

Relational database design allows you to create database tables that maintain relationships. These relationships are usually defined in terms of primary and foreign key values. For example, in our EmployeeDirectory table, the ID value for each employee is the primary key value. The IncentiveAwards table contains a field named ID as well. The ID field in the IncentiveAwards table is known as a foreign key. Records in the IncentiveAwards table are related to records in the EmployeeDirectory table by their ID values. Each record in the Incentive-Awards table should have a corresponding record in the EmployeeDirectory table.

A join operation lets you select records from two or more tables where a relationship between primary key and foreign key values exists. Most joins fall into one of two categories: inner or outer joins.

Inner joins

Inner joins are the most common type of join and are used to retrieve records from two or more tables where values in the joined columns match. There are many ways to implement inner joins in SQL, and support for these methods varies from database to database. Consult your database documentation to find out which methods are supported by your database.

One common method involves using the equal sign (=) in the WHERE statement to join the tables by a related column, as shown in Example 11-19.

Example 11-19: Inner Join Performed in the WHERE Statement

<!--- query the EmployeeDirectory and IncentiveAwards tables and only return
      records where the ID from the EmployeeDirectory table matches the ID in 
      the IncentiveAwards table,  The inner join is performed by the equal
      sign. --->
<CFQUERY NAME="GetEmployeeInfo" DATASOURCE="ProgrammingCF">
         SELECT EmployeeDirectory.ID, EmployeeDirectory.Name, 
                IncentiveAwards.ID, IncentiveAwards.Category, 
                IncentiveAwards.DateAwarded, IncentiveAwards.Amount
         FROM EmployeeDirectory, IncentiveAwards
         WHERE EmployeeDirectory.ID = IncentiveAwards.ID
</CFQUERY>
 
<H3>Incentive Awards</H3>
 
<TABLE CELLPADDING="3" CELLSPACING="0">
<TR BGCOLOR="#888888">
  <TH>Name</TH>
  <TH>Award Type</TH>
te Awarded</TH>
  <TH>Amount</TH>
</TR>
 
ically generate a table containing all of the records returned 
      by the query --->
<CFOUTPUT QUERY="GetEmployeeInfo">
<TR BGCOLOR="##C0C0C0">
ame#</TD>
  <TD>#Category#</TD>
  <TD>#DateFormat(DateAwarded, 'mm/dd/yyyy')#</TD>
  <TD>#DollarFormat(Amount)#</TD>
</TR>
</CFOUTPUT>
</TABLE>

Example 11-19 queries the EmployeeDirectory and IncentiveAwards tables and returns only records in which the ID value from the EmployeeDirectory table matches the ID value in the IncentiveAwards table. The inner join is performed using the equal sign in the WHERE clause. Executing this template results in the output shown in Figure 11-7.

You can also perform an inner join using INNER JOIN in the FROM clause of the SELECT statement, as shown in Example 11-20. The results of this query will mimic the results of Example 11-19 (shown in Figure 11-7).

Figure 11-7. Inner join performed using the equal sign in the WHERE clause

Example 11-20: Inner Join Performed in the FROM Statement

<!--- query the EmployeeDirectory and IncentiveAwards tables and only return
      records where the ID from the EmployeeDirectory table matches the ID in 
      the IncentiveAwards table.  The inner join is performed by the INNER
      JOIN operator. --->
<CFQUERY NAME="GetEmployeeInfo" DATASOURCE="ProgrammingCF">
         SELECT employee.ID, employee.Name, incentive.ID, 
                incentive.Category, incentive.DateAwarded, incentive.Amount
         FROM EmployeeDirectory employee 
         INNER JOIN IncentiveAwards incentive
         ON employee.ID = incentive.ID
</CFQUERY>
 
<H3>Incentive Awards</H3>
 
<TABLE CELLPADDING="3" CELLSPACING="0">
<TR BGCOLOR="#888888">
  <TH>Name</TH>
  <TH>Award Type</TH>
  <TH>Date Awarded</TH>
  <TH>Amount</TH>
</TR>
 
<!--- dynamically generate a table containing all of the records returned 
      by the query --->
UERY="GetEmployeeInfo">
<TR BGCOLOR="##C0C0C0">
  <TD>#Name#</TD>
  <TD>#Category#</TD>
  <TD>#DateFormat(DateAwarded, 'mm/dd/yyyy')#</TD>
  <TD>#DollarFormat(Amount)#</TD>
</TR>
</CFOUTPUT>
</TABLE>