Programming ColdFusion: Chapter 11: Advanced Database Techniques


	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>

<CFFLUSH>
 
<CFQUERY NAME="GetEmployees" DATASOURCE="ProgrammingCF">
SELECT *
FROM EmployeeDirectory
</CFQUERY>
 
<CFSET Stall=0>
<CFLOOP QUERY="GetEmployees">
  
  <CFFLUSH INTERVAL="100">
  
  <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>
 

<H2>Displaying a Limited Record Set</H2>
<CFOUTPUT>
<H3>#GetEmployeeInfo.RecordCount# total records - Displaying records 6-10</H3>
</CFOUTPUT>
 

<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

<CFQUERY NAME="GetEmployeeInfo" DATASOURCE="ProgrammingCF">
         SELECT Name
         FROM EmployeeDirectory
         ORDER BY Name
</CFQUERY>
 
<H2>Two column output sorted left to right</H2>

<CFSET StartNewRow = True>
 

<TABLE>
<CFPROCESSINGDIRECTIVE SUPPRESSWHITESPACE="Yes">
<CFOUTPUT QUERY="GetEmployeeInfo">

<CFIF StartNewRow IS True><TR></CFIF>
  <TD>#Name#</TD>

<CFSET StartNewRow = NOT(StartNewRow)>

<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


<CFQUERY NAME="GetEmployeeInfo" DATASOURCE="ProgrammingCF">
          SELECT Name,Email
          FROM EmployeeDirectory
          ORDER BY Name
</CFQUERY>
 

<CFSET Columns = 3>
<CFSET CurrentColumn = 0>
<CFSET RowCompleted = 0>

<CFSET Rows=Int(GetEmployeeInfo.RecordCount/Columns)>

<CFSET OddColumns = GetEmployeeInfo.RecordCount MOD Columns>

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

<CFLOOP FROM="1" TO="#rows#" INDEX="Row">
   <CFSET LeftOverIncrement = 0>
   <TR>
   
   <CFLOOP INDEX="Column" FROM="1" TO="#Columns#">
     <CFIF Column GT (OddColumns + 1) >
       <CFSET LeftOverIncrement = IncrementValue(LeftOverIncrement)>
     </CFIF>
     
         <CFSET CurrentRow = (Row + (Increment * (CurrentColumn) )  - 
LeftOverIncrement) >
         <CFSET CurrentColumn = IIf(CurrentColumn is (columns - 1),0 
,IncrementValue(CurrentColumn))>
     
     <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

<CFPARAM NAME="StartRow" DEFAULT="1">
<CFPARAM NAME="DisplayRows" DEFAULT="4">
 

<CFQUERY NAME="GetEmployeeInfo" DATASOURCE="ProgrammingCF" 
         CACHEDWITHIN="#CreateTimeSpan(0,0,15,0)#">
         SELECT Name,  Title, Department, Email, PhoneExt
         FROM EmployeeDirectory
</CFQUERY>
 

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

<CFOUTPUT>
<H4>Displaying records #StartRow# - #ToRow# from the 
#GetEmployeeInfo.RecordCount# total records in the database.</H4>
</CFOUTPUT>
 

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

<CFSET Next = StartRow + DisplayRows>
<CFSET Previous = StartRow - DisplayRows>
 

<CFOUTPUT>
<CFIF Previous GTE 1>
   <A HREF="NextPrevious.cfm?StartRow=#Previous#"><B>Previous #DisplayRows# 
      Records</B></A>
<CFELSE>
Previous Records  
</CFIF>
 
<B>|</B>
 

<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

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

<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

<CFQUERY NAME="GetEmployeeRecord" DATASOURCE="ProgrammingCF">
         SELECT Name, Title, Department, Email, PhoneExt, Salary
         FROM EmployeeDirectory
         WHERE ID = #URL.ID#
</CFQUERY>
 

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

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

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

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

<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

<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

<CFPARAM NAME="Form.Department" DEFAULT="">
 

<CFQUERY NAME="GetRecords" DATASOURCE="ProgrammingCF">
         SELECT Name, Title, Department, Email, PhoneExt, Salary
         FROM EmployeeDirectory
         WHERE 0=0

<CFIF Form.Name NEQ "">
    AND Name LIKE '%#Form.Name#%'
</CFIF>
 

<CFIF Form.Title NEQ "">
    AND Title LIKE '%#Form.Title#%'
</CFIF>
 

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

<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

<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

<CFQUERY NAME="MakeITDirectory" DATASOURCE="ProgrammingCF">
         SELECT Name, Title, Email, PhoneExt
         INTO ITDirectory
         FROM EmployeeDirectory
         WHERE Department = 'IT'
</CFQUERY>
 

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

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

<CFQUERY NAME="Add" DATASOURCE="ProgrammingCF">
         ALTER TABLE EmployeeDirectory
               ADD COLUMN DateHired Varchar(8)
</CFQUERY>
 

<CFQUERY NAME="Alter" DATASOURCE="ProgrammingCF">
         ALTER TABLE EmployeeDirectory
               ALTER COLUMN DateHired Date
</CFQUERY>
 

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

<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

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

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

<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

<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

<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

<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>
    
<CFOUTPUT QUERY="GetSalaries">
<TR BGCOLOR="##C0C0C0">
  <TD>#Name#</TD>
  <TD>#Title#</TD>
  <TD>#Department#</TD>
  <TD ALIGN="Right">#DollarFormat(Salary)#</TD>
</TR>    
</CFOUTPUT>
 

<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

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

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

<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

<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

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

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>
Example 11-20 introduces another technique known as table aliasing. If you look at the first query, you'll notice that we refer to the EmployeeDirectory table as employee and the IncentiveAwards table as incentive:
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
SQL lets us alias table names when working with multiple tables. To create an alias for a table name, simply specify the alias immediately following the table name in the FROM clause.

Outer joins

Outer joins differ from inner joins in that they let you query all the records from one table even if corresponding records don't exist in the other table. There are two types of outer joins: left and right.

As its name implies, a left outer join returns all the records from the left table (as specified in the WHERE or FROM clause) and only the records from the right table where the values in the joined fields match. All empty rows from the right table are assigned NULL values.

Example 11-21 uses a left outer join in the FROM clause to query the EmployeeDirectory and IncentiveAwards tables and return all records from the left table (EmployeeDirectory) and only those from the right table (IncentiveAwards) that match ID values.

Example 11-21: Using LEFT OUTER JOIN in the FROM Clause

<CFQUERY NAME="GetEmployeeInfo" DATASOURCE="ProgrammingCF">
         SELECT employee.ID, employee.Name, incentive.ID, 
                incentive.Category, incentive.DateAwarded, incentive.Amount
         FROM EmployeeDirectory employee 
         LEFT OUTER 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>
 

<CFOUTPUT QUERY="GetEmployeeInfo">
<TR BGCOLOR="##C0C0C0">
    <TD>#Name#</TD>
    <TD><CFIF Category EQ "">NULL<CFELSE>#Category#</CFIF></TD>
    <TD><CFIF DateAwarded EQ "">NULL<CFELSE>
        #DateFormat(DateAwarded, 'mm/dd/yyyy')#</CFIF></TD>
    <TD><CFIF Amount EQ "">NULL<CFELSE>#DollarFormat(Amount)#</CFIF></TD>
</TR>
</CFOUTPUT>
</TABLE>
Executing the template in Example 11-21 results in the output shown in Figure 11-8.

Figure 11-8. Results of using a left outer join in the FROM clause

A right outer join is the exact opposite of a left outer join. Right outer joins return all records from the right table and only the records from the left table where the values in the joined fields match. All empty rows from the left table are assigned NULL values.

Example 11-22 uses a right outer join to query the EmployeeDirectory and IncentiveAwards tables. A result set containing all records from the right table (IncentiveAwards) and only those from the left table (EmployeeDirectory) where the ID values are equal is returned.

Example 11-22: Using RIGHT OUTER JOIN in the FROM Clause

<CFQUERY NAME="GetEmployeeInfo" DATASOURCE="ProgrammingCF">
         SELECT employee.ID, employee.Name, incentive.ID, 
                incentive.Category, incentive.DateAwarded, incentive.Amount
         FROM EmployeeDirectory employee 
         RIGHT OUTER 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>
Amount</TH>
</TR>
 

<CFOUTPUT QUERY="GetEmployeeInfo">
<TR BGCOLOR="##C0C0C0">
    <TD><CFIF Name EQ "">NULL<CFELSE>#Name#</CFIF></TD>
    <TD><CFIF Category EQ "">NULL<CFELSE>#Category#</CFIF></TD>
    <TD><CFIF DateAwarded EQ "">NULL<CFELSE>#DateFormat(DateAwarded, 
        'mm/dd/yyyy')#</CFIF></TD>
    <TD><CFIF Amount EQ "">NULL<CFELSE>#DollarFormat(Amount)#</CFIF></TD>
</TR>
</CFOUTPUT>
</TABLE>
Executing the template in Example 11-22 results in the output shown in Figure 11-9.

Note that there are various shorthand techniques you can use to specify left outer joins and right outer joins in the WHERE clause. The syntax varies from database to database. Refer to your specific database's documentation for guidelines on specific syntax for outer joins.

Figure 11-9. Using a Right Outer Join in the FROM clause

There are several other types of joins that can be performed in SQL. Most of these are variations on the joins we already covered. Many are highly specialized and rarely employed. Some are exclusive to specific database platforms. For more information on joins, see your database documentation or pick up a good SQL reference book, such as O'Reilly & Associates' SQL in a Nutshell.

Data Binding

You can use the CFQUERYPARAM tag to check the datatype of a query parameter and optionally validate it against a specific SQL type. You can also use the CFQUERYPARAM tag to update long text fields. The CFQUERYPARAM tag must be nested within a CFQUERY tag and appears on the right side of the = sign in the WHERE clause:
<CFQUERY NAME="MyQUERY" DATASOURCE="MyDatasource">
         SELECT *
         FROM MyTable
         WHERE MyValue =
               <CFQUERYPARAM VALUE="parameter_value"
                             CFSQLTYPE="parameter_data_type"
                             MAXLENGTH="number"
                             SCALE="number_of_decimal_places"
                             SEPARATOR="seperator_character"
                             LIST="yes/no"
                             NULL="Yes/No">
</CFQUERY>
The VALUE attribute is required and specifies the value that ColdFusion should pass to the right of the comparison operator in the WHERE clause. CFSQLTYPE is also required and specifies the SQL type the parameter should be bound to. Possible entries are listed in Table 11-1. The default value for CFSQLTYPE is CF_SQL_CHAR.

Table 11-1: Values for CFSQLTYPE

CF_SQL_BIGINT

CF_SQL_IDSTAMP

CF_SQL_REFCURSOR

CF_SQL_BIT

CF_SQL_INTEGER

CF_SQL_SMALLINT

CF_SQL_CHAR

CF_SQL_LONGVARCHAR

CF_SQL_TIME

CF_SQL_DATE

CF_SQL_MONEY

CF_SQL_TIMESTAMP

CF_SQL_DECIMAL

CF_SQL_MONEY4

CF_SQL_TINYINT

CF_SQL_DOUBLE

CF_SQL_NUMERIC

CF_SQL_VARCHAR

CF_SQL_FLOAT

CF_SQL_REAL

The MAXLENGTH attribute specifies the maximum length of the parameter being passed and is an optional attribute. SCALE is also optional and, if you use a numeric datatype, specifies the number of decimal places for the parameter. The SEPARATOR attribute is optional and specifies the character used to delimit the list of values when the LIST attribute is set to Yes. The default is the comma (,). The LIST attribute is optional and accepts a Yes/No value indicating whether the VALUE attribute of the CFQUERYPARAM tag should be treated as a list of values separated by the character specified in the SEPARATOR attribute. If set to Yes, a SQL parameter is generated for each value in the list. Each list item is validated separately. If a value is specified for the MAXLENGTH attribute, the MAXLENGTH applies to each item in the list as opposed to the list as a whole. If the value passed is NULL, it is treated as a single NULL value. The default is No.

The final attribute is NULL. NULL is optional and specifies a Yes/No value indicating whether the value passed is a NULL. If Yes, ColdFusion ignores the VALUE attribute. The default value for NULL is No.

If the database being used doesn't support the binding of parameters (such as the Sybase 11 native driver), validation is still performed with the validated parameter being written back to the string. If for any reason validation fails, ColdFusion throws an exception. The following rules determine the validation performed:

CF_SQL_SMALLINT, CF_SQL_INTEGER, CF_SQL_REAL, CF_SQL_FLOAT, CF_SQL_DOUBLE, CF_SQL_TINYINT, CF_SQL_MONEY, CF_SQL_MONEY4, CF_SQL_DECIMAL, CF_SQL_NUMERIC, and CF_SQL_BIGINT can be converted to numbers.

CF_SQL_DATE, CF_SQL_TIME and CF_SQL_TIMESTAMP can be converted to a valid date format.

If the MAXLENGTH attribute is used, the length of the value for the specified parameter can't exceed the specified length.

The actual SQL that is generated by the CFQUERYPARAM tag is dependent on the database used. Example 11-23 uses the CFQUERYPARAM tag to validate the value of a variable called ID. Changing the value of ID from numeric to a text character causes ColdFusion to throw an error.

Example 11-23: Using CFQUERYPARAM for Data Binding and Validation

<CFSET ID = 1>
 

<CFQUERY NAME="GetEmployeeRecord" DATASOURCE="ProgrammingCF">
         SELECT Name, Title, Department, Email, PhoneExt, Salary
         FROM EmployeeDirectory
         WHERE ID = <CFQUERYPARAM VALUE="#ID#"
                                  CFSQLTYPE="CF_SQL_INTEGER">
</CFQUERY>
 
<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>    

<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>
CFSQL

Now that you have an understanding of advanced SQL, it's time to introduce a new concept known as "query of a query" or CFSQL (ColdFusion SQL). Introduced in ColdFusion 5.0, CFSQL allows you to use the CFQUERY tag to query an already existing query using a subset of ANSI SQL 92. This feature makes it easy to program functionality previously difficult or impossible to implement in ColdFusion. Some potential uses for CFSQL include:

Manipulate query objects (sort, summarize, group, etc.) returned by other ColdFusion tags such as CFHTTP, CFFTP, CFLDAP, CFPOP, CFSEARCH, or CFSTROEDPROC as well as the various query functions

Perform joins and unions between tables from different data sources

Resorting a query result set without having to go back to the data source

Moving entire tables into memory (Macromedia recommends no more than 10,000 rows), effectively speeding up query times because ColdFusion no longer has to make a round trip to the database for each query performed

Regardless of what you decide to use CFSQL for, you need to know what SQL constructs are available. For starters, you can perform only an SQL SELECT in CFSQL. This means that CFSQL can be used only to select records, not INSERT, UPDATE, or DELETE them. This makes sense, as the purpose of CFSQL is to allow you to perform a query against an already existing query. When you perform your SELECT, a number of SQL keywords are available to help you construct your query. These keywords are FROM, WHERE, ORDER BY, GROUP BY, HAVING, JOIN, and UNION. A number of comparison and Boolean operators are also available: =, <>, <, >, <=, >=, AND, OR, NOT, IN, BETWEEN, LIKE, and EXISTS. In addition, CFSQL supports several aggregate functions including COUNT, SUM, AVG, MAX, and MIN. You should note that CFSQL doesn't support nested aggregate functions nor does it support ODBC dates in comparison operations.

To perform a query of a query, you set the DBTYPE attribute of the CFQUERY tag to Query and reference the name of one or more existing queries in the FROM clause of your SQL statement. The DATASOURCE attribute isn't used when performing a query of a query. Example 11-24 shows a simple query of a query.

Example 11-24: Using CFSQL to Perform a Query of a Query

<CFQUERY NAME="GetAll" DATASOURCE="ProgrammingCF">
SELECT *
FROM EmployeeDirectory
</CFQUERY>
 
 

<CFQUERY NAME="GetDepartmentSummary" DBTYPE="query">
         SELECT COUNT(Name) AS TotalEmployees,
                Department
         FROM GetAll
         GROUP BY Department
         ORDER BY Department
</CFQUERY>
 
<H3>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="GetDepartmentSummary">
<TR BGCOLOR="##E3E3E3">
  <TD>#Department#</TD>
  <TD>#TotalEmployees#</TD>
</TR>
</CFOUTPUT>
</TABLE>
In this example, the first CFQUERY retrieves all the records in the EmployeeDirectory table in a query called GetAll. In the second CFQUERY, the DBTYPE is set to Query, indicating that the query should use an existing query as its data source. The SQL used is the same aggregate function from Example 11-14. It retrieves a count of the number of employees in each department. However, instead of going to the database for this information, the query is performed against the GetAll query, as specified in the FROM clause. While you probably wouldn't use ColdFusion's query of a query for such a trivial task in a real application, this example shows how simple it is to implement this technique.

Calling Stored Procedures

Most enterprise-level databases (MS SQL Server, DB2, Oracle, Informix, Sybase) support creating special programs within the database called stored procedures. Stored procedures allow you to encapsulate SQL and other database-specific functions in a wrapper that can be called from external applications. There are several reasons to use stored procedures whenever possible in your applications:

Stored procedures execute faster than identical code passed using the CFQUERY tag because they are precompiled on the database server.

Stored procedures support code reuse. A single procedure needs to be created only once and can be accessed by any number of templates, even different applications and those written in other languages.

Stored procedures allow you to encapsulate complex database manipulation routines, often utilizing database-specific functions.

Security is enhanced by keeping all database operations encapsulated within the stored procedure. Because ColdFusion passes parameters only to the stored procedure, there is no way to execute arbitrary SQL commands.

There are two ways to call stored procedures in ColdFusion. You can use the CFQUERY tag (which is now outdated) or the CFSTOREDPROC tag (which is new as of ColdFusion Version 4.0). Unfortunately, material on writing stored procedures is beyond the scope of this book. For more information on creating stored procedures, consult the documentation for your specific database.

Using CFSTOREDPROC

The preferred method for calling stored procedures in ColdFusion is via the CFSTOREDPROC tag. This tag takes several attributes that allow you to specify information about the data source on which you want to execute the stored procedure, as well as the stored procedure itself. The three most commonly used attributes are:
<CFSTOREDPROC PROCEDURE="procedure_name"
              DATASOURCE="datasource_name"
              RETURNCODE="Yes/No">
</CFSTOREDPROC>
PROCEDURE is a required attribute and specifies the name of the stored procedure on the database server that you want to execute. The DATASOURCE attribute is also required and specifies the data source that contains the stored procedure. The final attribute, RETURNCODE is optional and accepts a Yes/No value. If set to Yes, populates CFSTOREDPROC.STATUSCODE with the status code returned by the stored procedure. The default value for RETURNCODE is No. There are a number of additional attributes that can be used with the CFSTOREDPROC tag. For a complete list, see Appendix A.

The CFSTOREDPROC tag calls only the stored procedure you want to execute. You still need a way to pass values in and receive data back from the stored procedure. These functions are handled by two child tags of the CFSTOREDPROC tag, the CFPROCPARAM and CFPROCRESULT tags respectively.

When a stored procedure is executed using the CFSTOREDPROC tag, two return values are automatically created by ColdFusion. These variables are:

CFSTOREDPROC.STATUSCODE

Returned when RETURNCODE is set to Yes; contains the status code returned by the stored procedure

CFSTOREDPROC.EXECUTIONTIME

The number of milliseconds it took for the stored procedure to execute

Passing parameters using CFPROCPARAM

The CFPROCPARAM tag specifies parameter information to send to the stored procedure named in the CFSTOREDPROC tag. You may specify multiple CFPROCPARAM tags within a single CFSTOREDPROC tag. CFPROCPARAM tags must be nested within the CFSTOREDPROC tag and use the following syntax:
<CFPROCPARAM TYPE="In/Out/InOut"
             VARIABLE="variable_name"
             DBVARNAME="database_variable_name"
             VALUE="parameter_value"
             CFSQLTYPE="parameter_data_type"
             MAXLENGTH="length"
             SCALE="decimal_places" 
             NULL="yes/no">
The TYPE attribute is optional and specifies whether the variable being passed is an input (In), output (Out), or input/output (InOut) variable. The default TYPE is input (In). VARIABLE is required when TYPE is Out or InOut and specifies the name of the ColdFusion variable used to reference the value returned by the output parameter after the stored procedure is called. DBVARNAME is required if named notation is used and specifies the name of the parameter within the stored procedure. If you are using positional notation in your stored procedure, you must pass the CFPROCPARAM tags in the order expected by the stored procedure. The VALUE attribute is required when TYPE is In or InOut. It specifies the value to pass to the stored procedure.

The next attribute, CFSQLTYPE, is required and specifies the SQL type of the parameter being passed to the stored procedure. Possible values are listed back in Table 11-1.The default value for CFSQLTYPE is CF_SQL_CHAR. The MAXLENGTH attribute specifies the maximum length of the parameter being passed and is an optional attribute. SCALE is also optional and specifies the number of decimal places for the parameter should it be a numeric datatype. The final attribute is NULL. NULL is optional and specifies a Yes/No value indicating whether the value passed is a NULL. If Yes, ColdFusion ignores the VALUE attribute. The default value for NULL is No.

Specifying result sets using CFPROCRESULT

The CFPROCRESULT tag specifies the name for a given result set returned by the CFSTOREDPROC tag. This allows other ColdFusion tags to reference the result set returned by the stored procedure. Because stored procedures can return more than one result set, the CFPROCRESULT tag allows you to specify which result set to use. Because of this feature, it is possible to nest multiple CFPROCRESULT tags within a CFSTOREDPROC tag, provided you assign a different NAME for each CFPROCRESULT set:
<CFPROCRESULT NAME="query_name"
              RESULTSET="1-n" 
              MAXROWS="number">
The NAME attribute is required and specifies a name for the query result set returned by the stored procedure. RESULTSET is an optional attribute and specifies the result set to use if the stored procedure returns more than one result set. The default value for RESULTSET is 1. The final attribute is MAXROWS. MAXROWS is also optional and specifies the maximum number of rows to return with the result set. By default, all rows are returned.

Example 11-25 uses the CFSTOREDPROC tag, several CFPROCPARAM tags, and the CFPROCRESULT tag to execute a stored procedure called sp_AddEmployee that adds a new employee record to a database.

Example 11-25: Executing a Stored Procedure Using CFSTOREDPROC

<CFPARAM NAME="Form.Name" DEFAULT="">
<CFPARAM NAME="Form.Title" DEFAULT="">
<CFPARAM NAME="Form.Department" DEFAULT="">
<CFPARAM NAME="Form.Email" DEFAULT="">
<CFPARAM NAME="Form.PhoneExt" DEFAULT="">
<CFPARAM NAME="Form.Salary" DEFAULT="">
 

<CFSTOREDPROC PROCEDURE="sp_AddEmployee" 
              DATASOURCE="ProgrammingCF" 
              RETURNCODE="Yes">
              
<CFPROCPARAM TYPE="In" CFSQLTYPE="CF_SQL_VARCHAR" DBVARNAME="Name" 
             VALUE="#Form.Name#" MAXLENGTH="255" 
             NULL=#IIF(Form.Name IS "", 1, 0)#>
<CFPROCPARAM TYPE="In" CFSQLTYPE="CF_SQL_VARCHAR" DBVARNAME="Title" 
             VALUE="#Form.Title#" MAXLENGTH="255" 
             NULL=#IIF(Form.Title IS "", 1, 0)#>
<CFPROCPARAM TYPE="In" CFSQLTYPE="CF_SQL_VARCHAR" DBVARNAME="Department"
             VALUE="#Form.Department#" MAXLENGTH="255" 
             NULL=#IIF(Form.Department IS "", 1, 0)#>
<CFPROCPARAM TYPE="In" CFSQLTYPE="CF_SQL_VARCHAR" DBVARNAME="Email" 
             VALUE="#Form.Email#" MAXLENGTH="255" 
             NULL=#IIF(Form.Email IS "", 1, 0)#>
<CFPROCPARAM TYPE="In" CFSQLTYPE="CF_SQL_DECIMAL" DBVARNAME="PhoneExt" 
             VALUE="#Form.PhoneExt#" NULL=#IIF(Form.PhoneExt IS "", 1, 0)#>
<CFPROCPARAM TYPE="In" CFSQLTYPE="CF_SQL_DECIMAL" DBVARNAME="Salary" 
             VALUE="#Form.Salary#" NULL=#IIF(Form.Salary IS "", 1, 0)#>
 

<CFPROCRESULT NAME="InsertRecord" RESULTSET="1">
</CFSTOREDPROC>       
 
 

<CFIF CFSTOREDPROC.StatusCode IS 1>
  <CFOUTPUT>
  Record inserted successfully.  The employee ID assigned is: #InsertRecord.ID#
  <BR>
  The stored procedure executed in #CFSTOREDPROC.ExecutionTime# milliseconds.
  </CFOUTPUT>
<CFELSE>
  There was an error inserting the record!
</CFIF>
 
</BODY>
</HTML>
In Example 11-25, if the stored procedure returns a StatusCode of 1, the record was inserted successfully, and you can output the ID value assigned to the newly inserted record. Returning this value is part of the stored procedure. If it returns any other StatusCode, an error occurred. The value of StatusCode is set inside of the stored procedure, allowing you to assign any status codes you intend.

The following Allaire Knowledge Base articles (http://www.allaire.com/support/knowledgebase/SearchForm.cfm) provide additional information on using stored procedures with ColdFusion:

Article 13754

"New Features and Enhancements in ColdFusion Server 4.5" (Page 2)

Article 8622

"How to Execute a DB2 Stored Procedure in ColdFusion"

Using CFQUERY

Prior to the introduction of the CFSTOREDPROC tag in ColdFusion 4.0, the only way to call a stored procedure from ColdFusion was with the CFQUERY tag. The syntax for calling a stored procedure using CFQUERY is:
<CFQUERY NAME="MyQuery" DATASOURCE="MyDataSource">
{Call MyDB.dbo.sp_mysp (#var1#, '#Var2#')}
</CFQUERY>
There are several drawbacks to using the Call statement with CFQUERY to call stored procedures. For example, parameters must be passed in the order in which they appear in the stored procedure, and you can't specify input parameters by name or bind parameters to datatypes. There is no way to explicitly pass a NULL, and you can't specify the length for a given parameter. There is also no way to access return codes or output parameters created by the stored procedure.

Depending on your database, you may be able to use a native function to call a stored procedure with the CFQUERY tag. Several databases allow you to call stored procedures using the Execute or Exec keyword instead of Call as in:
<CFQUERY NAME="MyQuery" DATASOURCE="MyDataSource">
{Execute MyDB.dbo.sp_mysp 
 @Var = #var#,
 @Var2 = '#Var2#'}
</CFQUERY>
Using a native database function for calling a stored procedure allows you to overcome some of the problems associated with using the Call method such as referring to input parameters by name. However, this in and of itself isn't justification for using the CFQUERY tag to call stored procedures. Unless you are running a version of ColdFusion prior to Version 4.0, you should call your stored procedures using the CFSTOREDPROC tag.

Transaction Processing

ColdFusion provides support for database transaction processing using the CFTRANSACTION tag. The CFTRANSACTION tag lets you treat all query operations with the <CFTRANSACTION> and </CFTRANSACTION> tags as a single transaction. Changes to the database aren't committed until all queries in the transaction have executed successfully. In the event a query within the transaction fails, all previous queries are automatically rolled back. The exception to this occurs when the database itself is changed, as in the case when a table or column is created or deleted.

The CFTRANSACTION tag accepts two optional attributes for controlling how transactions are processed, ACTION and ISOLATION:

ACTION

Specifies the transaction action to take. Valid options include:

Begin (default)

Specifies the beginning of the block of code to execute

Commit

Commits a pending transaction

Rollback

Rolls back a pending transaction

ISOLATION

Specifies the ODBC lock type to use for the transaction. The following ODBC lock types are supported: Read_Uncommitted, Read_Committed, Repeat-able_Read, and Serializable.

Note that not all databases and/or database drivers support isolation levels. Many support only a subset of those listed. Refer to your particular database/driver's documentation for more information on the isolation levels supported.

Example 11-26 shows how to use the CFTRANSACTION tag with two queries that delete records from different tables within the same data source. You need to use the CFTRANSACTION tag to ensure that both queries are treated as a single transaction. If either query fails, any changes made are automatically rolled back.

Example 11-26: Simple CFTRANSACTION Usage

<CFTRANSACTION>

<CFQUERY NAME="DeleteEmployee" DATASOURCE="ProgrammingCF">
       DELETE FROM EmployeeDirectory
       WHERE ID = #Form.ID#
</CFQUERY>
 

<CFQUERY NAME="DeleteBonusRecords" DATASOURCE="ProgrammingCF">
       DELETE FROM IncentiveAwards
       WHERE ID = #Form.ID#
</CFQUERY>
</CFTRANSACTION>
In Example 11-25, the CFTRANSACTION tag ensures that both DELETE queries are treated as a single transaction. If either query fails, any changes made are automatically rolled back. Note that both queries are made to the same data source, but not to the same table.

A particularly useful feature of the CFTRANSACTION tag is that it can be nested to allow portions of a transaction to be committed or rolled back within the main CFTRANSACTION block as the code executes. The syntax for a nested transaction differs slightly from the syntax used to call most other tags. To commit a transaction within a nested CFTRANSACTION tag, use the following syntax:
<CFTRANSACTION ACTION="Commit"/>
Note the trailing forward slash at the end of the tag. This lets ColdFusion know you have nested the CFTRANSACTION tag and no end tag is necessary. Rolling back a transaction uses similar syntax:
<CFTRANSACTION ACTION="Rollback"/>
Using nested CFTRANSACTION tags and exception handling with CFTRY/CFCATCH gives you full control over how queries are committed and rolled back within CFTRANSACTION blocks. This technique also lets you write to more than one database within a single CFTRANSACTION block, if each transaction is committed or rolled back prior to writing a query to the next database. Example 11-27 demonstrates how to use nested CFTRANSACTION tags to create a new table populated with data from an existing table (using SELECT INTO) and then add another record to the table.

Example 11-27: Using Nested CFTRANSACTION Tags with Multiple Queries

<CFSET Continue = "Yes">
 

<CFTRANSACTION ACTION="BEGIN">
 

<CFTRY>

<CFQUERY NAME="MakeSalesDirectory" DATASOURCE="ProgrammingCF">
         SELECT Name, Title, Email, PhoneExt
         INTO SalesDirectory
EmployeeDirectory
         WHERE Department = 'Sales'
</CFQUERY>
 

<CFCATCH TYPE="Database">
    <CFTRANSACTION ACTION="ROLLBACK"/>
    <CFSET ProblemQuery = "MakeSalesDirectory">
    <CFSET Continue = "No">
</CFCATCH>
</CFTRY>
 

<CFIF Continue EQ "Yes">
<CFTRY>
<CFTRANSACTION ACTION="COMMIT"/>
<CFQUERY NAME="InsertRecord" DATASOURCE="ProgrammingCF">
         INSERT INTO SalesDirectory(Name, Title, Email, PhoneExt)
         VALUES('Lynda Newton', 'Account Manager', 'lynda@example.com', 1261)
</CFQUERY>         
  

<CFCATCH TYPE="DATABASE">
    <CFTRANSACTION ACTION="ROLLBACK"/>
    <CFSET ProblemQuery = "InsertRecord">
    <CFSET Continue = "No">
</CFCATCH>
</CFTRY>
</CFIF>
 

<CFIF Continue EQ "Yes">
    <CFTRANSACTION ACTION="COMMIT"/>
</CFIF>
</CFTRANSACTION> 
 

<CFIF Continue EQ "Yes">
 

<CFQUERY NAME="GetEmployees" DATASOURCE="ProgrammingCF">
         SELECT Name, Title, Email, PhoneExt
         FROM SalesDirectory
         ORDER BY Name
</CFQUERY>
 
<H2>All queries in the transaction executed successfully</H2>
<H3>Below is the data from the new table:</H3>
 
<TABLE CELLPADDING="3" CELLSPACING="0">
<TR BGCOLOR="#888888">
  <TH>Name</TH>
  <TH>Title</TH>
  <TH>E-mail</TH>
  <TH>Phone Ext.</TH>
</TR>
 

<CFOUTPUT QUERY="GetEmployees">
<TR BGCOLOR="##C0C0C0">
  <TD>#Name#</TD>
  <TD>#Title#</TD>
  <TD>#Email#</TD>
  <TD>#PhoneExt#</TD>
</TR>
</CFOUTPUT>
</TABLE>
 
<CFELSE>
<CFOUTPUT>
<H2>An Error has occurred.  All queries have been rolled back</H2>
<B>The query that caused the error is: <I>#ProblemQuery#</I>.</B>
</CFOUTPUT>
</CFIF>
In Example 11-27, the Continue variable controls the transaction. At the start of the template, this variable is initialized to Yes. As long as queries within the CFTRANSACTION block execute successfully, Continue keeps the value of Yes. Each subsequent query checks the status of this variable before executing. If at any point Continue is No, the transaction is rolled back, and no other queries with the CFTRANSACTION block are executed.

Executing the template in Example 11-27 results in the output shown in Figure 11-10. If you execute the template a second time, however, you should see the error message generated by the template.

Figure 11-10. Successful completion of queries within a nested CFTRANSACTION block

1. This example can easily be modified to use HTML Form buttons. If you prefer to use buttons instead of links, simply pass the StartRow value as a hidden form field and have the form post to itself.

**Table 11-1:** **Values for CFSQLTYPE**
CF_SQL_BIGINT	CF_SQL_IDSTAMP	CF_SQL_REFCURSOR
CF_SQL_BIT	CF_SQL_INTEGER	CF_SQL_SMALLINT
CF_SQL_CHAR	CF_SQL_LONGVARCHAR	CF_SQL_TIME
CF_SQL_DATE	CF_SQL_MONEY	CF_SQL_TIMESTAMP
CF_SQL_DECIMAL	CF_SQL_MONEY4	CF_SQL_TINYINT
CF_SQL_DOUBLE	CF_SQL_NUMERIC	CF_SQL_VARCHAR
CF_SQL_FLOAT	CF_SQL_REAL

Back to: Programming ColdFusion

Programming ColdFusion

Chapter 11 Advanced Database Techniques

Display Techniques

Flushing Page Output

Displaying Limited Record Sets

Alternating Row Color in HTML Tables

Multicolumn Output

Sorting multicolumn output from left to right

Sorting multicolumn output from top to bottom

Next/Previous Record Browsing

Controlling Whitespace

Optimizing output

Suppressing output

Suppressing whitespace

Drill-Down Queries

Query Caching

Advanced SQL

Dynamic SQL

Creating and Modifying Tables

Creating new tables

Populating new tables with existing data

Altering table design

Deleting tables

Retrieving Unique Values

Using Column Aliases

Scalar Functions

Aggregate Functions

Subqueries

Unions

Joins

Inner joins

Outer joins

Data Binding

CFSQL

Calling Stored Procedures

Using CFSTOREDPROC

Passing parameters using CFPROCPARAM

Specifying result sets using CFPROCRESULT

Using CFQUERY

Transaction Processing

Chapter 11
Advanced Database Techniques