Programming ColdFusion MX | O'Reilly Media

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In 1989, two chemists, Martin Fleischmann and Stanley Pons, claimed that their research had uncovered a phenomenon that promised to solve the world's energy problems. What they claimed to have accomplished was nothing short of astonishing: that they had achieved nuclear fusion at room temperature. They called their discovery cold fusion. Unfortunately, the scientific community at large dismissed their findings because no one was ever able to reproduce the results claimed in the original experiment. Oh wait, wrong book...

In 1995, J.J. and Jeremy Allaire introduced a product they believed would revolutionize application development for the Web. They too called their creation ColdFusion. The two brothers formed the Allaire Corporation and began selling ColdFusion. Unlike its infamous namesake, ColdFusion has delivered on the promises put forth by its creators. In 2001, Macromedia acquired Allaire, and along with it, ColdFusion. ColdFusion MX represents the second ColdFusion product release under the Macromedia banner.

According to Macromedia's marketing materials, ColdFusion is "the rapid server scripting environment for creating rich internet applications." Internet applications exist as a collection of pages, also known as templates, which work together to allow users to perform such tasks as reading email, buying books, or tracking packages. Internet applications often act as the front-end to back-end services, such as legacy applications and databases, and can be accessed through various means such as web browsers, cell phones, and other Internet-enabled devices. Some examples of web sites and applications built using ColdFusion include Autobytel.com's application for researching and purchasing a car (http://www.autobytel.com/), Williams-Sonoma's storefront application (http://www.williams-sonoma.com/), and the online reservation system at the Broadmoor Hotel's web site (http://www.broadmoor.com/).

One key aspect of an Internet application is that it is dynamic; it is not just a static collection of pages. The benefits of dynamically driven design are obvious. If you think of it in practical terms, which would you rather do each time a new press release has to be added to your web site? Would you rather the marketing department send you the text for the new press release so you can convert it to an HTML page, upload the page to your server, then go add a link to the menu of available press releases? Or, would you rather provide an online form to the marketing department so they can enter the text from the press release themselves and store it in a database that can then be queried to dynamically build the press release menu and associated pages? ColdFusion allows you to create just this kind of application.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In 1995, J.J. and Jeremy Allaire introduced a product they believed would revolutionize application development for the Web. They too called their creation ColdFusion. The two brothers formed the Allaire Corporation and began selling ColdFusion. Unlike its infamous namesake, ColdFusion has delivered on the promises put forth by its creators. In 2001, Macromedia acquired Allaire, and along with it, ColdFusion. ColdFusion MX represents the second ColdFusion product release under the Macromedia banner.

According to Macromedia's marketing materials, ColdFusion is "the rapid server scripting environment for creating rich internet applications." Internet applications exist as a collection of pages, also known as templates, which work together to allow users to perform such tasks as reading email, buying books, or tracking packages. Internet applications often act as the front-end to back-end services, such as legacy applications and databases, and can be accessed through various means such as web browsers, cell phones, and other Internet-enabled devices. Some examples of web sites and applications built using ColdFusion include Autobytel.com's application for researching and purchasing a car (http://www.autobytel.com/), Williams-Sonoma's storefront application (http://www.williams-sonoma.com/), and the online reservation system at the Broadmoor Hotel's web site (http://www.broadmoor.com/).

One key aspect of an Internet application is that it is dynamic; it is not just a static collection of pages. The benefits of dynamically driven design are obvious. If you think of it in practical terms, which would you rather do each time a new press release has to be added to your web site? Would you rather the marketing department send you the text for the new press release so you can convert it to an HTML page, upload the page to your server, then go add a link to the menu of available press releases? Or, would you rather provide an online form to the marketing department so they can enter the text from the press release themselves and store it in a database that can then be queried to dynamically build the press release menu and associated pages? ColdFusion allows you to create just this kind of application.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

There are several components that make up the ColdFusion MX environment, from which you can develop ColdFusion applications. As I mentioned earlier, a ColdFusion MX application is simply a collection of templates (pages) that work together to allow a user to perform a task. By template, I mean a file that contains a combination of hard-coded HTML (or other structure/layout code) along with sections of ColdFusion code that replace otherwise static sections of content with "dynamic" content pulled from a database or other data source. These templates don't exist in a vacuum, however. To get a better idea of how a ColdFusion application is constructed, you need to understand the components that make up the ColdFusion environment:

ColdFusion MX Application Server: TheColdFusion Application Server processes all the CFML code in the templates passed to it by the web server. It then returns the dynamically generated results to the web server, so that the output can be sent to the user's browser. The ColdFusion Application Server integrates with a number of popular web servers via native APIs and is also capable of running with ColdFusion MX's standalone web server (recommended for development only). Once the ColdFusion Application Server is set up, it works silently in the background, so we won't be talking much about it in this book.
Integrated Development Environment (IDE): DreamweaverMX is the Integrated Development Environment (IDE) designed for the ColdFusion MX Application Server. Dreamweaver MX provides developers with a visual environment for developing, testing, debugging, and deploying ColdFusion MX applications. Although ColdFusion MX applications can be written using any text editor capable of saving ASCII output, Dreamweaver MX offers many advantages that make it worth considering. Prior to the introduction of Dreamweaver MX, many ColdFusion developers used an IDE called ColdFusion Studio (later HomeSite +). Many of the features from Studio were merged into the Dreamweaver MX IDE; however, a large number of ColdFusion developers continue to use ColdFusion Studio as their IDE of choice. This book doesn't concern itself with the method you use to create your ColdFusion MX applications, so these tools aren't given much coverage.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Obviously, to use this book, you need access to a ColdFusion MX server. If your company is already developing web applications with ColdFusion, the server should already be available to you. Or, if you are developing for a remote server, you should be all set. In either case, you just need to know where to put your templates; check with your system administrator or webmaster.

If you don't have access to a ColdFusion server, your first step is to pick an edition of ColdFusion. There are currently four editions of ColdFusion available to support the needs of various sized projects and organizations; all of them are available at Macromedia's web site, http://www.macromedia.com (as of this writing, the latest release is ColdFusion MX 6.1):

ColdFusion MX Standard (Windows and Linux only): Formerly called ColdFusion MX Professional (through ColdFusion MX 6.0), the standard edition is designed for departmental and small group use. It contains access to all CFML language features, a 125KB document limit on Verity searches, and database drivers for MS Access (Windows only), MS SQL Server, and MySQL. Email handling in ColdFusion MX 6.1 Standard has been improved as well. The underlying engine is now capable of generating approximately 33KB emails per hour, an improvement over previous versions.
ColdFusion MX Enterprise (Windows, Linux, Solaris, HP-UX, and AIX): Contains all the functionality of ColdFusion MX Standard and adds server clustering, additional Type IV JDBC database drivers for most popular databases, a 250KB document limit on Verity searches, the ability to host JSP pages, servlets, EJBs, and import JSP tag libraries, as well as additional security, management, deployment, and performance features for hosting large-scale applications. Email handling in ColdFusion MX 6.1 Enterprise has be greatly improved. The engine is capable of generating approximately 1 million messages per hour, and contains additional features such as multi-threading, connection pooling, and the ability to use backup mail servers. ColdFusion MX Enterprise can be installed in one of two configurations:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Part of what makes developing web applications with ColdFusion so easy is the simplicity of the ColdFusion Markup Language (CFML). Because CFML is a tag-based language like HTML, it is simple to write and easy to understand. All ColdFusion code is written inside tags or within the boundaries of paired tags (just like HTML). There are over 90 tags and 265 functions in the CFML language that you can use to accomplish virtually any task. ColdFusion tags wrap complex functionality, such as database connectivity and data manipulation, into simple tags that can be invoked with a minimum of coding. CFML functions offer even more power, as they provide access to common operations, such as string manipulation and mathematical functions, that aren't possible using HTML alone.

Because CFML is a programming language, we need to start with some basics about the language. In this chapter, I cover how to create and save ColdFusion applications, as well as the major aspects of the language, such as datatypes, variables, expressions, conditional processing, and more.

To write a ColdFusion application, you can use virtually any text editor or an HTML authoring tool that allows you to directly edit the code. As we discussed in Chapter 1, a ColdFusion application is a collection of web pages, also called templates or pages that work together to allow a user to perform a task. When you create a CFML template, you typically embed the CFML code within standard HTML (although it is also possible to create files that contain only CFML, as you'll see later in the chapter). A ColdFusion application can be as simple as a single page. Consider the following example, which outputs the current date to the browser:

<html>
<head>
  <title>CFML Example</title>
</head>
   
<body>
   
<cfoutput>
<h2>Today's date is #DateFormat(Now( ),'mm/dd/yyyy')#</h2>
</cfoutput>
   
</body>
</html>

At first glance, this template looks just like an HTML template. If you look closer, however, you'll see embedded CFML code right in the middle of the template. The code here uses a single tag (

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

To write a ColdFusion application, you can use virtually any text editor or an HTML authoring tool that allows you to directly edit the code. As we discussed in Chapter 1, a ColdFusion application is a collection of web pages, also called templates or pages that work together to allow a user to perform a task. When you create a CFML template, you typically embed the CFML code within standard HTML (although it is also possible to create files that contain only CFML, as you'll see later in the chapter). A ColdFusion application can be as simple as a single page. Consider the following example, which outputs the current date to the browser:

<html>
<head>
  <title>CFML Example</title>
</head>
   
<body>
   
<cfoutput>
<h2>Today's date is #DateFormat(Now( ),'mm/dd/yyyy')#</h2>
</cfoutput>
   
</body>
</html>

At first glance, this template looks just like an HTML template. If you look closer, however, you'll see embedded CFML code right in the middle of the template. The code here uses a single tag (<cfoutput>) and two functions (DateFormat( ) and Now( )) to output the current date to the browser. We'll get to what this tag and the functions do in a bit. For now, we're just concerned with running the template and understanding how CFML and HTML coexist.

To execute this template, you need to save the file on the machine that is running your web server and the ColdFusion Application Server. You can either type in this example and save it to a file or you can copy the file from the book's example archive (available at http://www.oreilly.com/catalog/coldfusion/). You should put the file in a directory accessible under the root directory of your particular web server. For example, if the root directory for your web server is c:\inetpub\wwwroot, you can create a subdirectory two levels down such as c:\inetpub\wwwroot\examples\chapter2 and save the template there as 2-1.cfm. Now if you use your web browser to view this file (

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

ColdFusion supports a number of datatypes for use in building expressions. These datatypes can be broken into four categories: simple, complex, binary, and object. The simple datatypes are made up of Booleans, strings, numbers (both integer and floating point), and date/time objects:

Boolean

ColdFusion uses the Boolean datatype to store the value generated by a logical operation. Boolean values are stored as either true or false. In numeric operations, Boolean values evaluate to 1 for true and 0 for false. When dealing with strings, Boolean values are set to Yes for true and No for false. Note that no quotes are necessary to delimit Boolean values, nor are Boolean values case sensitive.

Strings

ColdFusion stores text values in strings delimited by a set of single or double quotes. For example, "This is a string." and 'So is this!' are both strings. "1000" and '1000' are also strings so long as the numbers are delimited by a set of quotes. The empty string can be written as either '' or "". There are certain special characters that must be escaped within strings. These special characters are the single quote ('), double quote ("), and pound sign (#). These characters may be escaped by doubling up on them as in the following examples:

<cfset String1 = "This is a ""good"" use of escaped double quotes">
<cfset String2 = "This is a ''good'' use of escaped single quotes">
<cfset String3 = "What is the ##1 team in the league?">

As we'll discuss in the next section, <cfset> is a CFML tag you use to set a variable to a particular value.

Numbers

ColdFusion supports both floating-point (decimal) and integer (whole number) values. Numbers don't need to be delimited by quotes when referenced within expressions. The range for floating-point values in ColdFusion is 10

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

A variable is a name associated with a data value; it is common to say that a variable stores or contains a value. Variables allow you to store and manipulate data in your applications; they are called variables because the values they represent can change throughout the life of an application. In ColdFusion, you don't have to explicitly declare your variables, as you do in a language such as C++. Additionally, ColdFusion variables are typeless, meaning that you can assign a value of any datatype (string, numeric, Boolean, object, etc.) to any variable.

The following rules and guidelines apply to variable names in CFML:

Variable names must begin with a letter, an underscore, or the Unicode currency symbol ($) and can contain only letters, numbers, underscores, and Unicode currency symbols. Variable names can't contain spaces. For example, Test, MyVariable, My_variable, MyVariable1, and MyDescriptive_var2 are all valid ColdFusion variables, while 4C, My Variable, Phone#, and A/P aren't. Note that the addition of the Unicode currency symbol is new in ColdFusion MX.
In ColdFusion MX, you cannot use compound variable names delimited with a period, such as Employee.Name. In previous versions of ColdFusion, compound variable names were allowed. In ColdFusion MX, the same syntax is used to automatically create a structure with a specific key and assign a value to that key. For example, <cfset Employee.Name="Pere Money"> creates a structure named Employee and assigns "Pere Money" to a key called Name. There are two exceptions to this rule: cookie names and client variable names may contain periods.
Avoid using variable names that are the same as ColdFusion variable scopes, scope structures, return values, tag names, function names, operators, or SQL keywords.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Expressions are the building blocks of the CFML language. In its most basic form, an expression is nothing more than a single element such as 1, test, MyVar, or Chr(54). Compound expressions let you to evaluate data that is acted upon by operators. For example, 1*10 is a mathematical expression that evaluates to 10. The values 1 and 10 are both data, while the asterisk (*) is considered an operator. On the other end of the spectrum, expressions can be complex, consisting of one or more subexpressions:

<cfset x = 1+(10 mod (3 * (11 - acos(-1))))>

Operators allow you to perform calculations and make comparisons between expressions. In other words, they allow you to combine simple expressions to form ones that are more complex. For example,

<cfset x =
10*(3+2)>

uses the asterisk (*) as an operator to multiply 10 by the sum of another expression that uses the plus (+) operator to add 3 and 2. There are four types of operators available in ColdFusion:

Arithmetic: Performs arithmetic operations such as sign changes, addition, subtraction, etc., on numeric values.
Comparison: Compares two values and returns a Boolean true/false.
String: There is only one string operator in the CFML language. The ampersand (&) concatenates strings.
Boolean: Also known as logical operators, Boolean operators perform connective and negation operations and return Boolean true/false values.

Table 2-2 lists the operators available in ColdFusion by order of precedence (P).

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

To output the contents or results of a ColdFusion expression, you use the <cfoutput> tag. cfoutput is a paired tag, which means that it has both start and end tags. cfoutput tells ColdFusion to parse any text found between the tag pairs for variables and expressions that need to be evaluated. We'll use the cfoutput tag in a variety of ways throughout the book; it is one of the most commonly used CFML tags. For now, let's focus on how the cfoutput tag outputs simple variable values.

The following example creates a number of variables using cfset tags and then outputs the values of the variables within cfoutput tags:

<!--- Assign values to variables --->
<cfset x = 1>
<cfset y = x+2>
<cfset Name = "Rob">
<cfset z = Name>
<cfset Authenticated = true>
<cfset TheDate = DateFormat(Now( ),'mm/dd/yyyy')>
   
<!--- Output the variable values --->
<h2>Writing Output</h2>
<cfoutput>
x = #x#<br>
y = #y#<br>
Name = #Name#<br>
z = #z#<br>
TheDate = #TheDate#<br>
Authenticated = #Authenticated#<br>
</cfoutput>

Executing this template causes the value assigned to each variable to be output to the browser, as shown in Figure 2-3. Note the use of pound signs (#) in this example. ColdFusion uses pound signs to separate expressions from literal text. When ColdFusion encounters an expression surrounded by pound signs, it attempts to evaluate it.

Figure 2-3: Writing output using the cfoutput tag

The most common usage of pound signs occurs when evaluating expressions within cfoutput tags. Use pound signs around variable names when you want to substitute the variable's value within your output:

<cfoutput>
Hello #Name#, how are you today?
</cfoutput>

When you have multiple variables, each variable should be delimited by its own set of pound signs:

<cfoutput>
Hello #FirstName# #LastName#, how are you today?
</cfoutput>

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Conditional processing makes it possible to implement flow control and decision making within your ColdFusion templates. ColdFusion provides three techniques for applying conditional processing in your programs: if/elseif/else functionality with the <cfif>, <cfelseif>, and <cfelse> tags; a C-style switch statement with the <cfswitch>, <cfcase>, and <cfdefaultcase> tags; and the

IIF(
)

function for inline conditionals.

If/elseif/else processing allows you to add logical decision-making and flow control to your ColdFusion applications, so you can evaluate an expression and take different actions based upon the results. Basic if/elseif/else processing takes the following form:

<cfif expression>
   HTML and CFML...
<cfelseif expression>
   HTML and CFML...
<cfelse>
   HTML and CFML...
</cfif>

The cfif statement can evaluate any expression capable of returning a Boolean value. If the statement evaluates true, ColdFusion processes the code associated with the cfif statement. The cfelseif statement provides an alternate expression in the event that the expression in the cfif statement evaluates false. Any number of cfelseif statements can provide additional decision-making options. The cfelse statement provides a default option in the event that both the cfif statement and any cfelseif statements all evaluate false. The following example uses if/elseif/else logic to evaluate a URL variable called Action:

<cfif URL.Action IS "Add">
   <cfinclude template="AddRecord.cfm">
<cfelseif URL.Action IS "Edit">
   <cfinclude template="EditRecord.cfm">
<cfelseif URL.Action IS "Delete">
   <cfinclude template="DeleteRecord.cfm">
<cfelse>
   You have chosen an invalid action!
</cfif>

Depending on the value of Action, one of three additional templates is called via a <cfinclude> tag (which we'll discuss shortly). If the value of the URL variable doesn't match one of the values in the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Looping allows you to repeat specific blocks of code (both HTML and CFML) within your CFML templates. ColdFusion supports a variety of looping constructs with the <cfloop> tag, including index (for) loops, conditional (while) loops, collection loops, list loops, and query loops. For now, we are just going to cover basic index and conditional loops. Query loops are covered in Chapter 4, while collection and list loops are covered in Chapter 6.

Also known as a for loop, an index loop repeats a number of times specified as a range of values:

<cfloop index="index_name" from="number" to="number" step="increment">
   HTML and CFML...
</cfloop>

The index attribute of the loop specifies a variable name to hold the value corresponding to the current iteration of the loop. The from attribute initializes the starting value for the loop. The to attribute refers to the value at which iteration should stop. step specifies the increment value for each iteration of the loop. step may be either a positive or a negative number. Here is an example that uses an index loop to output all the numbers between 10 and 100 in increments of 10, with each number on its own line:

<h2>Calling the loop...</h2>
   
<cfoutput>
<cfloop index="i" from="10" to="100" step="10">
  #i#<br>
</cfloop>
</cfoutput>
   
<h2>We are now outside of the loop</h2>

Here, index is set to i. Since we want to begin the count at 10, we assign that value to the from attribute. The to attribute is set to 100 because that is where we want the loop to stop iterating. In order to get the loop to increment by multiples of 10, the step attribute is set to 10. Executing the template results in the output shown in Figure 2-4.

Figure 2-4: Use an index loop to output the numbers between 10 and 100 in multiples of 10

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

ColdFusion allows you to embed references to other ColdFusion templates, HTML documents, and plain-text files in your ColdFusion applications via the <cfinclude> tag. cfinclude is the ColdFusion equivalent of Server Side Includes (SSI). cfinclude takes a single attribute, template, which specifies a logical path to the file to be included. The logical path must be either a virtual directory or a directory that has been explicitly mapped in the ColdFusion Administrator:

<cfinclude template="MyIncludedFile.cfm">

Or:

<cfinclude template="/MyDirectory/MyIncludedFile.txt">

Including files allows you to use repetitive code without having to cut and paste it into your template every time you want to use it. A good example of this is header and footer files that contain things such as site navigation, legal notices, and copyright information. By including header and footer files with each of your templates, you can make changes to the header or footer once and have that change instantly available to every template that includes the files. To understand how this works, consider the following ColdFusion template that includes both a header and footer file:

<!--- Set the title for the page --->
<cfset Title="My Page">
   
<!--- Include the header for the page --->
<cfinclude template="_header.cfm">
   
<h2>Hello World!</h2>
I'm just some regular text.
   
<!--- Include the footer --->
<cfinclude template="_footer.cfm">

You can save this template under any name you want. For this example, I saved the template as mypage.cfm. The template works by assigning a title for the page to a variable appropriately named Title. Next, a template called _header.cfm is included using the cfinclude tag. I use the underscore as the first character of any include files so that I can differentiate include files from other templates. The next part of the template constitutes the body of the page. In this example, we just output some simple text. The last line of code in the template uses another

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

While web applications might seem to be all about individual web pages, it is the passing of data between pages that is key to making an application work. You need to be able to pass data between ColdFusion templates for any form of interactivity. For example, if the user fills out an online form requesting additional information about a product, your application needs a mechanism for passing the data entered in the form fields to another page (or itself) that is capable of taking that data and acting on it. Likewise, if the user clicks on a hyperlink that initiates a parameter-driven query, the application needs a way to pass the parameters that make up the choice to the next template in the application for processing.

There are three methods for passing data between application templates in ColdFusion. You can pass data in the form of URL parameters, by posting it as form-field variables, or via persistent variables. This section covers the first two methods of passing data between application templates. The use of persistent variables is covered at length in Chapter 7.

One way to pass data from one template to the next is through hyperlinks containing special URL parameters. The HTTP specification allows you to append parameters to the end of a URL in the format:

            filename.cfm?param1=value1&param2=value2&param3=value3

The question mark immediately following the extension of the template in the URL specifies the beginning point for appending URL parameters. Each URL parameter consists of a parameter name followed by an equal sign, then the value assigned to the parameter. You can append more than one URL parameter to a URL by delimiting them with ampersands. Note that no spaces may appear in the URL string unless they are escaped first (see "Dealing with Special Characters").

When you click on a hyperlink containing one or more URL parameters, those parameters are automatically sent to the template specified in the URL of the link and thus are available to the template as URL variables. The following example illustrates how URL variables are passed from template to template in ColdFusion. The first template, shown in Example 3-1, creates several ColdFusion variables and appends them and their associated values to a URL.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

One way to pass data from one template to the next is through hyperlinks containing special URL parameters. The HTTP specification allows you to append parameters to the end of a URL in the format:

            filename.cfm?param1=value1&param2=value2&param3=value3

The question mark immediately following the extension of the template in the URL specifies the beginning point for appending URL parameters. Each URL parameter consists of a parameter name followed by an equal sign, then the value assigned to the parameter. You can append more than one URL parameter to a URL by delimiting them with ampersands. Note that no spaces may appear in the URL string unless they are escaped first (see "Dealing with Special Characters").

When you click on a hyperlink containing one or more URL parameters, those parameters are automatically sent to the template specified in the URL of the link and thus are available to the template as URL variables. The following example illustrates how URL variables are passed from template to template in ColdFusion. The first template, shown in Example 3-1, creates several ColdFusion variables and appends them and their associated values to a URL.

Example 3-1. Creating URL parameters

<!--- Set variables to be passed to another template --->
<cfset x=1>
<cfset color="green">
<cfset Pass = True>
   
<h2>Passing Data via URL Parameters</h2>
   
<!--- Create a hyperlink containing URL parameters --->
<cfoutput>
<a href="receiveurlparameters.cfm?x=#x#&color=#color#&pass=#pass#">Click 
this link to pass the URL parameters</a>
</cfoutput>

Executing this template results in a page that contains a single hyperlink. If you move your mouse over the hyperlink and look in the status window of your browser, you should see a URL that looks like this:

http://myserver.com/receiveurlparameters.cfm?x=1&color=green&Pass=True

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Another method for passing data from template to template involves sending the data via the HTTP Post method as form-field data. When you create an HTML form that posts to another ColdFusion template, each form field is automatically available as a form variable within the template specified in the action attribute of the form tag. Within the receiving template, each form field can be referenced by prefixing the field name with form, as in form. MyField. To see just how this works, let's look at an example. The contactform.cfm template, shown in Example 3-6, creates a simple HTML form that collects basic contact information and posts it to the displaycontactinfo.cfm template, shown in Example 3-7.

Example 3-6. Template for collecting and posting contact information

<html>
<head>
  <title>Passing Variables via Form Fields</title>
</head>
   
<body>
   
<h2>Employee Contact Information</h2>
<form action="displaycontactinfo.cfm" method="post">
   
<table>
  <tr>
    <td>Name:</td>
    <td><input type="text" name="Name" size="25" maxlength="50"></td>
  </tr>
  <tr>
    <td>Title:</td>
    <td><input type="text" name="Title" size="25" maxlength="50"></td>
  </tr>
  <tr>
    <td>Department:</td>
    <td><input type="text" name="Department" size="25" maxlength="50"></td>
  </tr>
  <tr>
    <td>E-mail:</td>
    <td><input type="text" name="Email" size="25" maxlength="255"></td>
  </tr>
  <tr>
    <td>Phone Ext.:</td>
    <td><input type="text" name="PhoneExt" size="6" maxlength="4"></td>
  </tr>
  <tr>
    <td colspan="2"><input type="submit" name="submit" value="submit"></td>
  </tr>
</table>
</form>
   
</body>
</html>

Filling in the form fields (Figure 3-5) and clicking on the "submit" button posts the form-field information to the displaycontactinfo.cfm template shown in Example 3-7.

Figure 3-5: HTML form for collecting employee contact information

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The final thing to consider when passing data between templates is how to check for and handle missing parameters. When dealing with data passed via URL or form field, it is entirely possible that an application template won't receive a parameter it is expecting. You should build mechanisms into your application templates to account for this possibility. As always, there are a few ways you can go about this.

The first method involves using the cfparam tag to assign a default value to any parameters expected by your application template. That way, if an expected parameter isn't passed, a default value is automatically assigned, and the application can continue processing. If you remember, the cfparam tag was covered earlier in Chapter 2. Additional information on the cfparam tag can be found in Appendix A.

The cfparam tag is great for assigning a default value if one doesn't exist; however, it doesn't allow you to display an error message or perform an alternate action if an expected variable doesn't exist. The next method uses the IsDefined( ) function to check for the existence of a variable before allowing processing of the template to continue. If the expected variable exists, the template is processed. If not, an error message is written to the browser. For example, if you have a template that is expecting a URL parameter named ArticleID to be passed in, you can use the following code to output an error message if the parameter isn't present:

<!--- If the parameter is present, output it to the screen --->
<cfif IsDefined('URL.ArticleID')>
<cfoutput>
The article ID is: #URL.ArticleID#
</cfoutput>
   
Additional program code...
   
<!--- Otherwise, output an error message --->
<cfelse>
A required parameter, <b>ArticleID</b> was not supplied!
</cfif>

In this example, the IsDefined( ) function checks for the existence of a URL parameter called ArticleID. If it is present, its value is output to the browser, and any additional program code for the page is executed. If the parameter isn't present, an error message to that effect is written to the browser.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Many people begin their relationship with ColdFusion out of a need to provide web-based access to data stored in a database. This might be as simple as wanting to output the contents of a single database table as an HTML table or as sophisticated as a multipage report generated from several related tables in a database. Whatever your requirements, the methods for querying the data from the database and outputting the results to a user's web browser using ColdFusion remain the same.

ColdFusion doesn't stop with allowing you to query data from a database. Using ColdFusion, you can perform a wide range of database operations including adding, updating, and deleting records; adding new columns to existing tables; and creating, altering, and dropping existing tables. In this chapter, we cover the basics you need to know in order to use ColdFusion to interact with a database. Included in the discussion are configuring data sources, an introduction to SQL, and techniques for retrieving and displaying data.

In ColdFusion, the term data source refers to a connection between ColdFusion and an information source, such as a database, LDAP server, flat file, etc. This chapter focuses on connecting with one specific type of data source: databases. Before you can use ColdFusion to interact with a database, the database has to be set up as a data source that the ColdFusion server can recognize. Unlike previous version of ColdFusion, which used ODBC, OLEDB, and native drivers to connect to databases, ColdFusion MX handles all database connections via JDBC (Java Database Connectivity). There are currently four distinct types of JDBC drivers, appropriately named type 1, 2, 3, and 4. Each JDBC type works in a slightly different way:

JDBC to ODBC Bridge (Type 1): JDBC calls are translated to appropriate ODBC calls and sent through an ODBC driver.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In ColdFusion, the term data source refers to a connection between ColdFusion and an information source, such as a database, LDAP server, flat file, etc. This chapter focuses on connecting with one specific type of data source: databases. Before you can use ColdFusion to interact with a database, the database has to be set up as a data source that the ColdFusion server can recognize. Unlike previous version of ColdFusion, which used ODBC, OLEDB, and native drivers to connect to databases, ColdFusion MX handles all database connections via JDBC (Java Database Connectivity). There are currently four distinct types of JDBC drivers, appropriately named type 1, 2, 3, and 4. Each JDBC type works in a slightly different way:

JDBC to ODBC Bridge (Type 1): JDBC calls are translated to appropriate ODBC calls and sent through an ODBC driver.
Native-API Partly Java Technology-Enabled Driver (Type 2): A JDBC call is converted to a client-specific API call.
Net-Protocol Fully Java Technology-Enabled Driver (Type 3): A JDBC call is first translated to an independent net protocol, then to a database-specific call.
Native-Protocol Fully Java Technology-Enabled Driver (Type 4 ): A JDBC call is converted to the network protocol used by the specific database.

ColdFusion MX comes with JDBC drivers for connecting to several popular databases. Depending on the version of ColdFusion MX you have, drivers may include MS Access, MS SQL Server, Oracle, various flavors of DB2, Informix, MySQL, and Sybase. Additionally, you may connect to almost any other database for which you have a third-party JDBC driver or an ODBC driver (through a JDBC Type 3 socket connection). ColdFusion MX ships with Type 2, Type 3, and Type 4 JDBC drivers. For more information on JDBC driver types, see the book

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The cfquery tag is the main tag used by ColdFusion to interact with databases. Using cfquery, you can pass any Structured Query Language (SQL) statement to a data source registered with the ColdFusion Administrator. The content of your SQL statements determines what action is performed against the data source. The next section provides a quick primer on SQL.

The cfquery tag works by establishing a connection with the specified data source, passing a series of SQL commands, and returning query variables that contain information about the operation. The basic syntax for using the cfquery tag is as follows:

<cfquery name="query_name"
         datasource="datasource_name">
SQL statements
</cfquery>

Each attribute in the opening cfquery tag specifies information about the data source and how ColdFusion should access it. The name attribute assigns a name to the query. Valid query names must begin with a letter and can contain only letters, numbers, and underscore characters. name is required when passing an SQL SELECT statement and is optional for all other SQL operations. The datasource attribute is required in all circumstances except when dbtype is Query (covered later in Chapter 11) and specifies the name of the data source (as it appears in the ColdFusion Administrator) to connect to when executing the query. There are a number of additional attributes that can be used with the cfquery tag. For a complete list, see its tag reference in Appendix A.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Before we go any further, a quick primer on SQL is in order. If you are already an SQL guru, feel free to skip this section. If, however, you are new to SQL, this section quickly covers the basic elements that go into creating an SQL statement. This primer is by no means a substitute for a thorough lesson on SQL. You may want to consult additional SQL references before proceeding, as a good understanding of SQL is an essential element in ColdFusion application design. One of the surest ways to bottleneck your applications is with poorly written SQL. Additionally, SQL is implemented in slightly different ways across various RDBMS platforms. For this reason, it is important to consult the documentation specific to your database to understand these differences.

With the disclaimer out of the way, let's move on and look at the elements that go into creating an SQL statement for use in a cfquery tag. If you don't completely understand everything we are about to cover, don't worry. Every aspect (and more) of the SQL we cover in the primer is covered in more detail throughout this and the next chapter.

Most database transactions in a web application can be grouped into one of four categories: selecting, inserting, updating, and deleting data. Not surprisingly, there are four commands in SQL that handle theses tasks. They are SELECT, INSERT, UPDATE, and DELETE, respectively:

SELECT: Retrieves data from a data source
INSERT: Inserts new data in a data source
UPDATE: Updates existing data in a data source

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

When you query records from a database, the results (known as a record set) are returned in a special ColdFusion data type called a query object. A query object stores the records within it in rows and columns—just like a spreadsheet. Throughout this book, I'll use the terms record and row interchangeably. Column name and field are also used interchangeably. Before we get into the specifics of querying databases and working with query objects, we need to create a database with some sample data to work with.

The majority of examples in this chapter (and throughout the book) use a data source called ProgrammingCF that contains several database tables including one called EmployeeDirectory. The schema and sample data for this database are listed in Appendix C. For simplicity, I've chosen to use a Microsoft Access database for all the examples; you can download the sample Access database from O'Reilly's catalog page for this book (http://www.oreilly.com/catalog/coldfusion2/). Of course, you can use any database you choose. To get started, you need to create a new database and save it as ProgrammingCF. Next, create a new table and add the fields shown in Table 4-2.

Table 4-2: Employee directory table within the ProgrammingCF database
Field name	Field type	Max length
`ID` (primary key)	AutoNumber	N/A
Name	Text	255

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

When you use a basic SQL SELECT statement to retrieve records from a database, those records are returned in the order in which they are stored. If you want to change the order in which the records are displayed (which you probably do), you need to use an ORDER BY clause, as shown in Example 4-2.

Example 4-2. Sorting query results using the SQL ORDER clause

<cfquery name="GetEmployeeInfo" datasource="ProgrammingCF">
  SELECT Name, Title, Department, Email, PhoneExt
  FROM EmployeeDirectory
  ORDER BY Name ASC
</cfquery>
   
<html>
<head>
  <title>Sorting Query Results Using the SQL ORDER Clause</title>
  <style type="text/css">
    th {
      background-color : #888888;
      font-weight : bold;
      text-align : center;
    }
    td {
      background-color : #C0C0C0;
    }
  </style>          
</head>
   
<body>
<table cellpadding="3" cellspacing="1">
  <tr>
    <th>Name</th>
    <th>Title</th>
    <th>Department</th>
    <th>E-mail</th>
    <th>Phone Extension</th>
  </tr>    
  <cfoutput query="GetEmployeeInfo">
  <tr>
    <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 ORDER BY clause specifies which column or columns to use in ordering the query results. Sorting can be either ASC (ascending) or DESC (descending). Example 4-2 sorts the result set by name column, in ascending order. The output is shown in Figure 4-1.

Figure 4-1: Sorting a result set using the ORDER BY clause

Multicolumn sorts can be performed by specifying a comma-delimited list of column names and sort orders for the

ORDER
BY

clause as in:

<cfquery name="GetEmployeeInfo" datasource="ProgrammingCF">
  SELECT Name, Title, Department, Email, PhoneExt
  FROM EmployeeDirectory
  ORDER BY Title ASC, Name ASC
</cfquery>

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The cfoutput tag has an attribute called group that lets you to group output from your record sets before displaying it to the browser. There are two ways to use the group attribute of the cfoutput tag. The first method uses group to remove any duplicate rows from the query result set. This is useful in situations where the result set you return from a query contains duplicate rows of data but you want to display only unique records.

Example 4-3 demonstrates what happens when you query a table containing duplicate values and output the results without using the group attribute of the cfoutput tag.

Example 4-3. Failing to use the group attribute results in duplicate values in the output

<cfquery name="GetDepartment" datasource="ProgrammingCF">
  SELECT Department
  FROM EmployeeDirectory
  ORDER BY Department
</cfquery>
   
<html>
<head>
  <title>Failing to Use the group Attribute Results in Duplicate Values in the 
         Output </title>
</head>
   
<body>
<h2>Departments:</h2>
   
<cfoutput query="GetDepartment">
#Department#<br>
</cfoutput>
   
</body>
<

Table of Contents