Programming ColdFusion | O'Reilly Media

Content preview·Buy reprint rights for this chapter

In 1989, two physicists, 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. Unlike its infamous namesake, Allaire's ColdFusion has delivered on the promises put forth by its creators.

ColdFusion is a rapid application development platform for creating and deploying dynamic server-based web applications. Web applications exist as a collection of web pages, also known as templates, that work together to allow users to perform such tasks as reading email, buying books, or tracking packages. Web applications often act as the frontend to back-end services, such as legacy applications and databases. Some examples of web 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 Infonautics's Company Sleuth application for tracking publicly traded companies (http://www.sleuth.com/).

One key aspect of a web application is that it is dynamic; it is not just a static collection of web 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 HTML 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 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. Unlike its infamous namesake, Allaire's ColdFusion has delivered on the promises put forth by its creators.

ColdFusion is a rapid application development platform for creating and deploying dynamic server-based web applications. Web applications exist as a collection of web pages, also known as templates, that work together to allow users to perform such tasks as reading email, buying books, or tracking packages. Web applications often act as the frontend to back-end services, such as legacy applications and databases. Some examples of web 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 Infonautics's Company Sleuth application for tracking publicly traded companies (http://www.sleuth.com/).

One key aspect of a web application is that it is dynamic; it is not just a static collection of web 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 HTML 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.

Of course, there are a lot of different technologies you can use to create dynamic web applications, from open source technologies such as Perl/CGI scripts or PHP, to such commercial options as Java Server Pages and Java servlets or Microsoft's Active Server Pages. With all these choices, why use ColdFusion?

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 reprint rights for this chapter

There are several components that make up the ColdFusion environment, from which you can develop ColdFusion applications. As I mentioned earlier, a ColdFusion application is simply a collection of templates (web pages) that work together to allow a user to perform a task. 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 Application Server: The ColdFusion 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 in CGI mode. 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.
ColdFusion Studio (or other text-editing software): ColdFusion Studio is the Integrated Development Environment (IDE) for the ColdFusion Application Server. Studio provides developers with a visual environment for developing, testing, debugging, and deploying ColdFusion applications. Although ColdFusion applications can be written using any text editor capable of saving ASCII output, ColdFusion Studio offers many advantages that make it worth considering. This book doesn't concern itself with how you create your ColdFusion applications, so ColdFusion Studio isn't covered.
ColdFusion Markup Language (CFML) templates: The ColdFusion Markup Language (CFML) is the language that you use to create ColdFusion applications. CFML is a tag-based language, just like HTML. You use it in conjunction with HTML and other client-side languages, such as JavaScript and VBScript, to create the templates that make up a ColdFusion application. CFML is used to determine what to display, while HTML specifies

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 reprint rights for this chapter

Obviously, to use this book, you need access to a ColdFusion 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 Allaire's web site, http://www.allaire.com (as of this writing, the latest release of ColdFusion is Version 5.0):

ColdFusion Express (Windows and Linux only): A free, bare bones edition of ColdFusion designed to introduce you to the ColdFusion development environment. ColdFusion Express supports only a subset of the full CFML language and provides limited database support and limited server administration. ColdFusion Express can be downloaded for free from Allaire's web site.
ColdFusion Professional (Windows and Linux only): The professional edition gives full access to the CFML language as well as full ODBC database support, administration, and advanced security services.
ColdFusion Enterprise (Windows, Solaris, Linux, and HP-UX): Contains all the functionality of ColdFusion Professional but adds server clustering, native database drivers for certain databases, upgraded Merant ODBC drivers, and complete sandbox security for securing ColdFusion in multisite hosted environments.
ColdFusion Single User (Windows only): This is a single-user edition of ColdFusion Enterprise that comes bundled with ColdFusion Studio and limits access to one IP address per session. ColdFusion Single User allows you to build and test applications without having to purchase a full ColdFusion Enterprise license. As of ColdFusion 5.0, the trial version of ColdFusion Enterprise automatically becomes the single-user version once the 30-day trial period expires.

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 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 80 tags and 255 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, 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 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, 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 (http://127.0.0.1/examples/chapter2/2-1.cfm), you'll see the web page displayed in Figure 2-1.

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 reprint rights for this chapter

ColdFusion supports a number of datatypes for use in building expressions. These datatypes can be broken into two categories, simple and complex. 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.

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³⁰⁰ (1 followed by 300 zeros). The range for integer values is -2,147,483,648 to 2,147,483,647. ColdFusion automatically converts numbers outside this range to floating-point values.

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 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 and can contain only letters, numbers, and the underscore character. 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.
Avoid using variable names that may be reserved words in SQL, such as Time, Date, and Order.
Avoid using variable names that are the same as ColdFusion variable scopes: Application, Attribute, Caller, CGI, Client, Cookie, Form, Variable, Request, Server, Session, URL, and Query.
Avoid choosing variable names that end in _date, _eurodate, _float, _integer, _range, _required, or _time, as these are reserved suffixes for server-side form validation variables and can cause naming conflicts.
ColdFusion variable names aren't case-sensitive. In the interest of good style and readability, however, you should keep the case of your variable names consistent.
Always try to use descriptive terms for your variables. It might seem like a pain, but you will be grateful when it comes time to debug or add a new feature.
If your application interacts with a database, you can make your code clearer by using ColdFusion variable names that match the corresponding fields in the database.

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 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 return 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).

Table 2-2: ColdFusion Operators
Operator	Operation	Type	P
Unary +, unary -	Sign change

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

In addition, expressions also need to be delimited by pound signs:

<CFOUTPUT>
The absolute value of -1 is #ABS(-1)#.
</CFOUTPUT>

Be careful when trying to use compound expressions. The following example produces an error because ColdFusion doesn't allow you to evaluate compound expressions directly within

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 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 CFIF or CFELSEIF statements, a default message contained within the CFELSE tag is displayed.

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

<CFLOOP INDEX="i" 
        FROM="10" 
        TO="100"
        STEP="10">
        
<CFOUTPUT>
#i#<BR>
</CFOUTPUT>

</CFLOOP>

<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

We can easily modify this example to output all of the numbers between

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 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 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 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 you can pass data 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.

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

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>
<AHREF="ReceiveURLParameters.cfm?x=#x#&color=#color#&pass=#pass#">Clickthis 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:

ReceiveURLParameters.cfm?x=1&color=green&Pass=True

As you can see, the URL contains the parameters you appended in the original code. Clicking on the hyperlink requests the template in Example 3-2, called ReceiveURLParameters.cfm, and passes along the URL parameters.

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

Example 3-7. Displaying Submitted Form-Field Values

Name: #Form.Name#
Title: #Form.Title#
Department: #Form.Name#
E-mail: #Form.Email#
Phone Ext.: #Form.PhoneExt#

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 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 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. There are three types of database connections supported by ColdFusion:

ODBC: Open Database Connectivity (ODBC) is arguably the most common format for accessing databases in both client-server and Internet-based applications. ODBC drivers are available for virtually every RDBMS in existence. If you already have ODBC data sources set up on the system you installed ColdFusion on, they are automatically available for use by ColdFusion. Depending on the platform you are running ColdFusion on, any number of ODBC drivers may be installed by default. For specifics, consult the documentation for your particular flavor of ColdFusion.

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 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. There are three types of database connections supported by ColdFusion:

ODBC: Open Database Connectivity (ODBC) is arguably the most common format for accessing databases in both client-server and Internet-based applications. ODBC drivers are available for virtually every RDBMS in existence. If you already have ODBC data sources set up on the system you installed ColdFusion on, they are automatically available for use by ColdFusion. Depending on the platform you are running ColdFusion on, any number of ODBC drivers may be installed by default. For specifics, consult the documentation for your particular flavor of ColdFusion.
OLE DB: OLE DB is a relatively new driver technology from Microsoft designed to replace ODBC as the preferred method for accessing a variety of data sources. OLE DB is available only with the Windows version of ColdFusion and requires a special set of drivers (called providers) to implement. ColdFusion comes with OLE DB providers for accessing MS Access and MS SQL Server. Other third-party OLE DB drivers are available from a number of vendors.
Native drivers: The Enterprise version of ColdFusion comes with native driver support for DB2 5.0 and 6.1, Informix 7.x and 9.x, Oracle 7.3, 8.0, and 8i, and Sybase System 11 and 12 databases. Native drivers sometimes offer better performance than ODBC drivers and often include support for features not implemented in the ODBC version of the driver. In order to use a native driver, you must have client software from the particular database vendor installed on your ColdFusion server.

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 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"
         DBTYPE="dbtype"
         CONNECTSTRING="connection_string">
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 or Dynamic and specifies the name of the data source (as it appears in the ColdFusion Administrator) to connect to when executing the query. The DBTYPE attribute is optional and specifies the type of database driver to use when connecting to the data source. Possible entries are:

ODBC (the default): Connect to the data source using an ODBC driver.
OLEDB: Make the connection using an OLEDB driver.
Oracle73: Connect using the Oracle 7.3 native driver. This requires the 7.3.4.0.0 or later client libraries be installed on the ColdFusion server.
Oracle80: Connect using the Oracle 8 native driver. This requires the 8.0 or later client libraries be installed on the ColdFusion server.

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 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
DELETE: Deletes data from a data source

Once you have determined the type of operation you want to perform, the next step is to refine the SQL statement by adding various clauses and operators. Depending on the action you want to perform, the syntax of the SQL statement varies. Here are some common SQL clauses:

FROM: The table name or names you want to perform 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 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/coldfusion/). 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
Title	Text	255
Department

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 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 were originally entered. If you want to change the order in which the records are displayed, 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>

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

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 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>Using GROUP to remove duplicate records</TITLE>
</HEAD>

<BODY>
<H2>Departments:</H2>
<CFOUTPUT QUERY="GetDepartment">
#Department#<BR>
</CFOUTPUT>

</BODY>
</HTML>

As you can see in Figure 4-2, executing the template results in many of the same values being output more than once.

Figure 4-2: Duplicate records are displayed because GROUP wasn't used

This is easy enough to fix. To remove the duplicates from the output, all you have to do is modify the line of code containing the CFOUTPUT tag to read like this:

<CFOUTPUT QUERY="GetDepartment" GROUP="Department" GROUPCASESENSITIVE="No">

Adding GROUP="Department" to the CFOUTPUT tag tells ColdFusion to discard any duplicate values in the result set and output only unique values. The GROUPCASESENSITIVE attribute indicates whether grouping should be case-insensitive or case-sensitive. This attribute is optional and defaults to Yes. For our example, set GROUPCASESENSITIVE to No in case someone enters the name of a department using the wrong case. The difference in output is shown in Figure 4-3.

Figure 4-3: Using GROUP to remove duplicate records

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 reprint rights for this chapter

As I mentioned briefly in Chapter 2, a query loop (CFLOOP tag with the QUERY attribute) performs essentially the same job as using a CFOUTPUT tag with the QUERY attribute. A query loop iterates over each row in a query object. Optionally, a start row and end row within the query may be specified:

<CFLOOP QUERY="query_name"
        STARTROW="row_number"
        ENDROW="row_number">
...
</CFLOOP>

The QUERY attribute specifies the name of a valid ColdFusion query object. STARTROW is optional and may be used to specify the row within the query object where the loop should begin. ENDROW is also optional and specifies the last row within a query object that should be included within the loop.

The query loop may be used instead of the QUERY attribute of the CFOUTPUT tag to display the contents of a query:

<CFQUERY NAME="GetEmployeeInfo" DATASOURCE="ProgrammingCF">
         SELECT Name, Title
         FROM EmployeeDirectory
</CFQUERY>

<CFLOOP QUERY="GetEmployeeInfo">
  <CFOUTPUT>#Name#, #Title#<BR></CFOUTPUT>
</CFLOOP>

Using a query loop allows you to work around limitations inherent in the CFOUTPUT tag such as the inability to nest additional output queries within a CFOUTPUT block. For example, the following code produces an error in ColdFusion because you can't nest CFOUTPUT tags without using the GROUP attribute:

<CFQUERY NAME="MyQuery1" DATASOURCE="MyDSN">
    SELECT *
    FROM MyTable
    WHERE Field = Value
</CFQUERY>

<CFOUTPUT QUERY="MyQuery1">
  <CFQUERY NAME="MyQuery2" DATASOURCE="MyDSN">
      SELECT *
      FROM MyTable
      WHERE Field = Value
  </CFQUERY>

  <CFOUTPUT QUERY="MyQuery2">
    Additional processing and output code here...
  </CFOUTPUT>
</CFOUTPUT>

You can get around this limitation by using a query loop within the CFOUTPUT block:

<CFQUERY NAME="MyQuery1" DATASOURCE="MyDSN">
    SELECT *
    FROM MyTable
    WHERE Field = Value
</CFQUERY>

<CFOUTPUT QUERY="MyQuery1">
  <CFQUERY NAME="MyQuery2" DATASOURCE="MyDSN">
      SELECT *
      FROM MyTable
      WHERE Field = Value
  </CFQUERY>

  <CFLOOP QUERY="MyQuery2">
    Additional processing and output code here...
  </CFLOOP>
</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 reprint rights for this chapter

Once you have query data in the form of a result set, you might want to massage it a bit before outputting it to the browser or to a file. ColdFusion provides several built-in functions for formatting a variety of datatypes. This section covers some of the more popular functions for formatting strings, HTML code, numbers, currency, dates, times, and Boolean values. For more information on all the functions covered in this section, see Appendix B.

ColdFusion provides functions for formatting text strings: ParagraphFormat( ), Ucase( ), Lcase( ),

Table of Contents