Macromedia COLDFUSION MX7 User Manual

COLDFUSION®MX 7

Getting Started Building ColdFusion MX Applications

Trademarks

1 Step RoboPDF, ActiveEdit, ActiveTest, Authorware, Blue Sky Software, Blue Sky, Breeze, Breezo, Captivate, Central, ColdFusion, Contribute, Database Explorer, Director, Dreamweaver, Fireworks, Flash, FlashCast, FlashHelp, Flash Lite, FlashPaper, Flex, Flex Builder, Fontographer, FreeHand, Generator, HomeSite, JRun, MacRecorder, Macromedia, MXML, RoboEngine, RoboHelp, RoboInfo, RoboPDF, Roundtrip, Roundtrip HTML, Shockwave, SoundEdit, Studio MX, UltraDev, and WebHelp are either registered trademarks or trademarks of Macromedia, Inc. and may be registered in the United States or in other jurisdictions including internationally. Other product names, logos, designs, titles, words, or phrases mentioned within this publication may be trademarks, service marks, or trade names of Macromedia, Inc. or other entities and may be registered in certain jurisdictions including internationally.

This product includes code licensed from RSA Data Security.

Third-Party Information

This guide contains links to third-party websites that are not under the control of Macromedia, and Macromedia is not responsible for the content on any linked site. If you access a third-party website mentioned in this guide, then you do so at your own risk. Macromedia provides these links only as a convenience, and the inclusion of the link does not imply that Macromedia endorses or accepts any responsibility for the content on those third-party sites.

Copyright © 1999–2005 Macromedia, Inc. All rights reserved. U.S. Patents Pending. This manual may not be copied, photocopied, reproduced, translated, or converted to any electronic or machine-readable form in whole or in part without written approval from Macromedia, Inc. Notwithstanding the foregoing, the owner or authorized user of a valid copy of the software with which this manual was provided may print out one copy of this manual from an electronic version of this manual for the sole purpose of such owner or authorized user learning to use such software, provided that no part of this manual may be printed out, reproduced, distributed, resold, or transmitted for any other purposes, including, without limitation, commercial purposes, such as selling copies of this documentation or providing paid-for support services. Part Number ZCF70M200

Acknowledgments

Project Management: Randy Nielsen

Writer: Anne Sandstrom

Editors: Linda Adler, Noreen Maher

Production Management: Patrice O’Neill,

Media Design and Production: John Francis, Adam Barnett

Special thanks to Sawako Gensure, Seungmin Lee, Takashi Koto, Nozomi Kugita, Masayo Noda, Hiroshi Okugawa, Bowne Global Solutions

First Edition: January 2005

Macromedia, Inc. 600 Townsend St.

San Francisco, CA 94103

INTRODUCTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7

PART I: Welcome to ColdFusion

CHAPTER 1: Introducing ColdFusion MX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

About ColdFusion MX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

How ColdFusion processes pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12

Building applications with CFML. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13

CHAPTER 2: CFML Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

Working with ColdFusion pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

About CFML elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18

Working with CFML expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23

About conditional processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27

Processing form data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28

Commenting your code. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30

CHAPTER 3: Database Fundamentals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31

About database basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31

About SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34

Using SQL with ColdFusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36

PART II: Building a ColdFusion Application

CHAPTER 4: Lesson 1: Preparing to Build the Sample Application . . . . . . . . . . . 41

Application development steps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41

Exercise 1: Determining the application functional requirements . . . . . . . . . . . . . 42

Exercise 2: Determining the data requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . 43

Exercise 3: Designing the database for your application. . . . . . . . . . . . . . . . . . . . . 43

Exercise 4: Developing the sample application. . . . . . . . . . . . . . . . . . . . . . . . . . . . 46

CHAPTER 5: Lesson 2: Configuring Your Development Environment. . . . . . . . . 49

Exercise 1: Verifying the tutorial file structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49

Exercise 2: Setting up your development environment. . . . . . . . . . . . . . . . . . . . . . 50

Exercise 3: Configuring a database connection . . . . . . . . . . . . . . . . . . . . . . . . . . . 52

Exercise 4: Configuring debugging options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55

Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57

CHAPTER 6: Lesson 3: Retrieving Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59

Exercise 1: About the trip list. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60

Exercise 2: Retrieving the information for the trip list . . . . . . . . . . . . . . . . . . . . . . 61

Exercise 3: Displaying the query result using the cfoutput tag . . . . . . . . . . . . . . . . 62

Exercise 4: Writing structured, reusable code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63

Exercise 5: Creating additional queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66

Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69

CHAPTER 7: Lesson 4: Building Dynamic Queries. . . . . . . . . . . . . . . . . . . . . . . . 71

Exercise 1: Creating the trip search form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71

Exercise 2: Building a query that uses dynamic SQL . . . . . . . . . . . . . . . . . . . . . . . 75

Exercise 3: Constructing the initial Trip Search Results page . . . . . . . . . . . . . . . . . 79

Exercise 4: Testing the Trip Search Results page. . . . . . . . . . . . . . . . . . . . . . . . . . . 81

Exercise 5: Enabling the departure and price criteria on the Trip Search form . . . . 82

Exercise 6: Testing the revised Trip Search Results page . . . . . . . . . . . . . . . . . . . . . 83

Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83

CHAPTER 8: Lesson 5: Creating a Trip Detail Page. . . . . . . . . . . . . . . . . . . . . . . 85

Exercise 1: Creating a Trip Detail page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86

Exercise 2: Avoiding the potential security risk when using dynamic SQL. . . . . . . 91

Exercise 3: Linking the Trip Search Results page with the Trip Detail page . . . . . . 92

Exercise 4: Enhancing the look of the Trip Search Results and Trip Detail pages . . 93

Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95

CHAPTER 9: Lesson 6: Creating a Main Application Page . . . . . . . . . . . . . . . . . 97

Exercise 1: Creating the main application page from the Trip Detail page . . . . . . . 98

Exercise 2: Adding database maintenance buttons . . . . . . . . . . . . . . . . . . . . . . . . 100

Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101

CHAPTER 10: Lesson 7: Validating Data to Enforce Business Rules. . . . . . . . . 103

Exercise 1: Using an HTML form to collect data. . . . . . . . . . . . . . . . . . . . . . . . . 104

Exercise 2: Creating a simple action page. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107

Exercise 3: About data validation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108

Exercise 4: Providing server-side validation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109

Exercise 5: Validating data on the client using ColdFusion form tags. . . . . . . . . . 114

Exercise 6: Dynamically populating the list of event types . . . . . . . . . . . . . . . . . . 119

Exercise 7: Validating the existence of the trip photo file . . . . . . . . . . . . . . . . . . . 120

Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122

4 Contents

CHAPTER 11: Lesson 8: Implementing Browsing and Searching . . . . . . . . . . . . 123

Exercise 1: Enabling users to browse trip details . . . . . . . . . . . . . . . . . . . . . . . . . 123

Exercise 2: Determining actions based on which button a user clicks. . . . . . . . . . 127

Exercise 3: Enabling searching from the Trip Detail page . . . . . . . . . . . . . . . . . . 127

Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128

CHAPTER 12: Lesson 9: Enabling Database Maintenance . . . . . . . . . . . . . . . . 129

Exercise 1: Deleting the current trip record shown on the Trip Detail page . . . . . 129

Exercise 2: Adding trips with SQL INSERT statements . . . . . . . . . . . . . . . . . . . 131

Exercise 3: Adding data using the cfinsert tag . . . . . . . . . . . . . . . . . . . . . . . . . . . 135

Exercise 4: Updating a SQL row using the cfupdate tag. . . . . . . . . . . . . . . . . . . . 137

Exercise 5: Linking the Trip Edit page to the main page . . . . . . . . . . . . . . . . . . . 138

Exercise 6: Inserting values in the edit form. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139

CHAPTER 13: Lesson 10: Restricting Access to ColdFusion Applications . . . . 143

Exercise 1: About the Login Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143

Exercise 2: Requiring users to log in to the Compass Travel application. . . . . . . . 147

Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148

INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149

Contents 5

6 Contents

INTRODUCTION

Getting Started Building ColdFusion MX Applications is intended for anyone who needs to begin programming in the Macromedia ColdFusion MX 7 development environment. This manual includes a tutorial, which uses supporting files that are installed if you chose to install sample applications. If you did not install the ColdFusion MX sample applications, you can download the tutorial files from the ColdFusion Support center on the Macromedia website.

About Macromedia ColdFusion MX 7 documentation

The ColdFusion MX 7 documentation is designed to provide support for the complete spectrum of participants.

Documentation set

The ColdFusion MX 7 documentation set includes the following titles:

Book Description

Installing and Using ColdFusion MX

Configuring and Administering ColdFusion MX

ColdFusion MX Developer’s Guide

Getting Started Building ColdFusion MX Applications

Describes system installation and basic configuration for Microsoft Windows, Solaris, and Linux. To see this manual, go to http:// www.macromedia.com/go/livedocs_cfmx7docs_installing.

Part I describes how to manage the ColdFusion environment, including connecting to your data sources and configuring security for your applications. Part II describes Verity search tools and utilities that you can use for configuring the Verity K2 Server search engine, as well as creating, managing, and troubleshooting Verity collections. To see this manual, go to

www.macromedia.com/go/livedocs_cfmx7docs_configadmin.

Describes how to develop your dynamic web applications, including retrieving and updating your data, and using structures and forms. This manual includes two volumes. To see this manual, go to

www.macromedia.com/go/livedocs_cfmx7docs_dev.

Contains an overview of ColdFusion features and application development procedures. This manual includes a tutorial that guides you through the process of developing a sample ColdFusion application. To see this manual online, go to www.macromedia.com/go/livedocs_cfmx7docs_gs.

Book Description

CFML Reference Provides descriptions, syntax, usage, and code examples for all ColdFusion

CFML Quick Reference Shows the syntax of ColdFusion tags, functions, and variables in a brief

tags, functions, and variables. This manual includes two volumes. To see this manual, go to www.macromedia.com/go/

livedocs_cfmx7docs__cfml_reference.

guide.

Viewing online documentation

All ColdFusion MX documentation is available online in HTML and Adobe Acrobat Portable Document Format (PDF) files. Go to the documentation home page for ColdFusion MX on the Macromedia website: www.macromedia.com. In addition, you can view the documentation in LiveDocs, which lets you add comments to pages and view the latest comments added by Macromedia, by going to www.macromedia.com/go/livedocs_cfmx7docs.

8 Introduction:

PART I

Welcome to ColdFusion

Part I provides an introduction to Macromedia ColdFusion MX 7. It defines ColdFusion MX 7 and provides an overview of the ColdFusion Markup Language (CFML). It also provides generic database concepts.

The following chapters are included in Part I:

Chapter 1: Introducing ColdFusion MX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

Chapter 2: CFML Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

Chapter 3: Database Fundamentals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31

PART I

CHAPTER 1

Introducing ColdFusion MX

This chapter introduces the core technologies that are the foundation for Macromedia ColdFusion MX. In addition, it introduces the basic concepts about ColdFusion MX, how it works, and the various components that comprise it.

About ColdFusion MX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

How ColdFusion processes pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12

Building applications with CFML . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13

About ColdFusion MX

ColdFusion MX is a powerful web application server that lets you create robust sites and applications without a long learning curve. ColdFusion MX does not require coding in traditional programming languages (for example, C, C++, Java, XML), although it supports these traditional programming languages.

ColdFusion MX consists of the following core components:

• ColdFusion application server

• ColdFusion Markup Language (CFML)

• ColdFusion MX Administrator

The following sections describe these core components in more detail. In addition, the Getting Started Experience, which you can view by opening the Macromedia ColdFusion MX Administrator and clicking Getting Started, provides introductory information about ColdFusion and code snippets that highlight the new features of ColdFusion MX 7.

The ColdFusion application server

The ColdFusion application server is itself a web application that typically resides on the same computer as your web server software. It is the program that parses (reads and interprets) and processes supplied instructions. These instructions are passed to ColdFusion through ColdFusion pages, which use a .cfm or .cfc filename extension. A ColdFusion page looks like an HTML page, but contains special tags that instruct the ColdFusion server to perform specific operations.

The ColdFusion Markup Language

ColdFusion Markup Language (CFML) is a tag-based language similar to HTML that uses special tags and functions. With CFML, you can enhance your standard HTML files with database commands, conditional operators, and high-level formatting functions, and rapidly produce easyto-maintain web applications.

CFML looks similar to HTML: it includes start and end tags, and each tag is enclosed in angle brackets. All ending tags are preceded with a forward slash (/) and all tag names are preceded with cf.

The ColdFusion MX Administrator

You use the ColdFusion MX Administrator to configure and maintain the ColdFusion application server. It is a web-based application that you can access using any web browser, from any computer with an Internet connection.

You can manage the following configuration options with the ColdFusion MX Administrator:

• ColdFusion data sources

• Debugging output

• Server settings

• Application security

For further details about the ColdFusion MX Administrator, see Installing and Using ColdFusion MX or Configuring and Administering ColdFusion MX.

How ColdFusion processes pages

The following steps explain how the ColdFusion server processes a ColdFusion page:

The ColdFusion server looks at the content of the page and searches for the following ColdFusion instructions:

■ Tag names that begin with cf.

■ Variables and functions that are always surrounded by number signs (#).

If the ColdFusion server finds any HTML or plain text in the page, the ColdFusion server returns it to the web server unchanged.

The ColdFusion server processes all the ColdFusion instructions found, and returns any remaining results to the web server. The web server then sends the entire output to the browser.

12 Chapter 1: Introducing ColdFusion MX

Building applications with CFML

You build ColdFusion applications as a series of pages that use CFML. Developers can extend this language by creating their own custom tags or user-defined functions (UDF), or by integrating COM, C++, and Java components, such as Java Server Page (JSP) tag libraries.

Interacting with data sources

ColdFusion applications can interact with any database that supports a JDBC technology-based driver. A JDBC technology-based driver uses an Application Programming Interface (API) to execute SQL statements to databases on most platforms. However, ColdFusion is not limited to JDBC data sources. You can also interact with existing Open Database Connectivity (ODBC) data sources by using ODBC Socket, a driver that interacts with an existing ODBC driver.

Interacting with mobile devices

ColdFusion MX 7 lets you create applications that can respond to events and messages from diverse sources, including instant messaging, mobile phone Short Message Service (SMS) text messages, Internet socket requests, and system events. For information, see Chapter 42, “Using

Event Gateways” in ColdFusion MX Developer’s Guide.

Development tools

You can code your ColdFusion application with NotePad or any HTML editor; however, Macromedia recommends that you build your applications using Macromedia Dreamweaver MX. Dreamweaver MX offers features and wizards that enhance ColdFusion development. For more information about Dreamweaver MX, see Chapter 5, “Lesson 2: Configuring Your

Development Environment,” on page 49.

Building applications with CFML 13

14 Chapter 1: Introducing ColdFusion MX

CHAPTER 2

CFML Basics

This chapter introduces the basic elements of CFML, including how to create Macromedia ColdFusion pages, and use variables, functions, conditional processing, and form processing.

Working with ColdFusion pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

About CFML elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18

Working with CFML expressions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23

About conditional processing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27

Processing form data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28

Commenting your code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30

Working with ColdFusion pages

ColdFusion pages are plain text files that you use to create web applications. You can create your ColdFusion applications by writing all the code manually or by using wizards (provided with some editors) to generate the majority of the code for you.

You can use the following editors to create your ColdFusion pages:

• Macromedia Dreamweaver MX

• Macromedia HomeSite+

• Macromedia ColdFusion Studio

• Any HTML editor

• Windows Notepad

• Vi or Emacs (UNIX systems)

The best choice for creating ColdFusion pages is Macromedia Dreamweaver MX. Dreamweaver MX includes many CFML features for building applications, such as rapid visual development, robust CFML editing, and integrated debugging. Dreamweaver MX also includes a copy of HomeSite+ for users who are familiar with developing their application code using ColdFusion Studio or HomeSite 5. HomeSite+ combines all the features of ColdFusion Studio and HomeSite 5, along with support for the latest ColdFusion MX tags. For more information, see

Chapter 5, “Lesson 2: Configuring Your Development Environment,” on page 49.

Note: This manual describes how to create ColdFusion applications by writing your code manually. It does not address how to create ColdFusion pages by generating code with wizards. For information about using wizards to generate CFML code, see the product documentation for Dreamweaver MX and HomeSite+.

Creating a ColdFusion page

Creating a ColdFusion page involves using tags and functions. The best way to understand this process is to create a ColdFusion page.

In the following procedure, you will create a simple ColdFusion page by using HTML tags, one ColdFusion tag, and two ColdFusion functions. The following table briefly explains the ColdFusion tags and functions:

Element Description

Now()

DateFormat()

cfoutput

A function supported in CFML that you can use to retrieve information from your system. You will use the that is retrieved from your system.

A function that instructs ColdFusion to format the date returned by the Now() function.

A ColdFusion tag that you use to return dynamic data (data retrieved from a database) to a web page.

You will use the retrieved from your system.

Now() function in the following procedure to return the current date

cfoutput tag in the following procedure to display the current date

Note: ColdFusion tags and functions are considered primary elements of CFML. You will learn more about these elements and others later in this manual.

To create a ColdFusion page:

Open your editor and create a blank file.

Enter the following code on the page:

<html> <head> <title>A ColdFusion Page</title> </head> <body> <strong>Hello world, this is a ColdFusion page.</strong> <br> <cfoutput> Today’s date is #DateFormat(Now())# </cfoutput> </body> </html>

16 Chapter 2: CFML Basics

Saving your ColdFusion page

In order for the ColdFusion server to process the page, you must save the ColdFusion page on a computer where the ColdFusion MX is installed. If you are creating your pages on a local server (on which ColdFusion is running), you can save the pages locally; if you are using a remote server, you must save your pages on that server.

If you are using the ColdFusion MX J2EE configuration, you typically save ColdFusion pages under the ColdFusion web application root. For example, in the default directory structure when you use the J2EE configuration with JRun, you save pages under jrun_root/servers/cfusion/ cfusion-ear/cfusion-war. When you use JRun, you can also run the Web Server Configuration Tool and save pages under your web root directory.

Tip: ColdFusion MX on Macintosh OS X uses the J2EE configuration.

To save the code that you typed to create a ColdFusion page:

Create a directory called test under the web_root or web_application_root directory.

In the test directory, save the file as cfpage.cfm.

Browsing your code

To ensure that the code you wrote is working as expected, you must view the ColdFusion page in a browser. The following procedure describes how to view the ColdFusion page that you created earlier.

To view the ColdFusion page:

Open a web browser and go to the following URL:

http://localhost/test/cfpage.cfm

Note: If you are using the built-in web server, specify the port to use in the URL as follows:

http://localhost:8500/test/cfpage.cfm.

The address localhost is only valid when you view pages locally. The URL for a remote site would include the server name or IP address of the server where ColdFusion is installed; for example, http://<serveripaddress>/test/cfpage.cfm.

If you are using the ColdFusion MX J2EE configuration, you may also need to include a context root in the URL; for example, http://<server>/<context-root>/<page>.cfm. For example, if you deploy an EAR file and use the default context root of cfmx, you specify http:/ /localhost/cfmx/test/cfpage.cfm.

Working with ColdFusion pages 17

The following figure shows the cfpage.cfm page in the browser:

Do the following tasks:

View the source code that was returned to the browser. In most browsers, you can view the source by right-clicking on the page, and then selecting View Source.

Compare the browser source code with the source code that appears in your editor. Notice that the CFML tags were processed on the page but did not appear in the source that was returned to your browser.

As described in Chapter 1, “Introducing ColdFusion MX,” on page 11, ColdFusion processes all the instructions (CFML tags and functions) it receives on a page, and then returns the results of the instructions that your browser can interpret and display.

About CFML elements

CFML consists of two primary language elements: tags and functions. Ta g s let you perform operations such as accessing a database. Functions can return data and do other operations like retrieving the system date. Almost everything that you want to accomplish with ColdFusion involves using tags and functions.

You use another important element known as a variable. Variables are an important part of most programming languages and are equally important with CFML. Var ia bl es let you store information in memory and enable you to pass data.

The following sections describe how to use these three elements.

Functions

Typically, a function acts on data. It can generate a value or a set of values, usually from some input. You can perform the following operations (actions) with functions:

• Manipulate data and time values

• Examine a value or variable

• Display and format information

• Manipulate string data

• Retrieve system information and resources

• Perform mathematical operations

About CFML elements 19

Functions and number signs

You use number signs (#) with functions to display the results of a function on the page. Number signs tell the ColdFusion server to evaluate the content between the number signs and display the value, for example:

Hello world, <br> Today’s date is #DateFormat(Now(), "mm/dd/yyyy")#

</cfoutput>

The following figure shows the output of this example:

If you did not include the number signs around the

DateFormat(Now(), "mm/dd/yyyy")

function, ColdFusion would not evaluate the function and the previous example would display your source code, as follows:

For more information about how to use number signs with functions, see ColdFusion MX Developer’s Guide.

Functions and parentheses

All functions have parentheses, regardless of whether the function acts on data. Consider the following function:

#Now()#

If you put anything inside the parentheses of the Now() function, an error would occur. The

Now() function returns an unformatted date and time. However, you can format the results of

this function with other functions, such as the

DateFormat() or TimeFormat() functions.

20 Chapter 2: CFML Basics

Using functions on values

Usually, a function performs an operation on a value, and the value can include the value of a variable. For example, to format the value of a variable that contains a value in dollars, the code to write this statement might look like this:

#DollarFormat(price)#

The DollarFormat function returns a value as a string and formats that value with two decimal places, a thousands separator, and a dollar sign. The number signs (#) around the function instruct ColdFusion to evaluate the content between the number signs and display the value.

Nesting functions

Functions can generate data, as well as act on data. Consider the following example:

#DateFormat(Now(), "mm/dd/yyyy")#

In this example, the Now() function generates the date, and then the DateFormat function formats the date.

Variables

Variables let you store data in memory on the server. Variables always have a name and a value. You can assign a value to a variable, or you can instruct ColdFusion to assign variable values based on data that it retrieves from a data source, such as a database table.

Naming variables

You must use the following rules for naming ColdFusion variables:

• Names are case-insensitive (uppercase, lowercase, or mixed case).

• Names can contain only letters, numbers, and underscore characters.

• Each name must begin with a letter.

• Special characters (such as double-quotation marks ("), reserved names (such as functions and

tags), and spaces are not allowed.

Ways to use variables

You can use variables for the following purposes:

• Store data collected from a form.

• Store results of a calculation (such as the number of database records returned).

• Use as input to a function.

Creating variables with the cfset tag

ColdFusion lets you create variables as you need them. You create the variable (name and value) using the

cfset tag. This tag has the following syntax:

About CFML elements 21

In the following examples, the variables are assigned a string literal value. All string literal values are surrounded by double-quotation marks.

In the next example, ColdFusion uses the values of the my_first_name and my_last_name variables to set the value for the (

&) string operator joins the variables, and the space surrounded by double-quotation marks (" ")

my_full_name variable in the last line of code. The ampersand

adds a space between the variables.

Tip: String values assigned to a variable must be enclosed in single-quotation marks (') or doublequotation marks ("). Numeric or Boolean values assigned to a variable do not require single- or double-quotation marks.

So far, all the variable examples have shown local variables. Local variables are variables that you can use only on the current ColdFusion page. The previous example used a variables prefix to reference an existing variable on the page. Using a prefix when referencing a variable is important because ColdFusion supports many types of variables. Use the following syntax to reference a local variable:

variables.variablename

Because ColdFusion lets you use the same name with variables of more than one type, ColdFusion relies on scope referencing. In scope referencing, you preface the variable’s name with the scope when you refer to that variable.

Other variables and their scope

Each type of variable that ColdFusion supports has it own scope, or where it can be referenced, and its own way of referencing that variable type. The following table identifies some of the more common types of variables and their prefixes:

Scope Prefix Description

Variables (local variable)

Form Form Data entered in tags in an HTML form or ColdFusion form and

URL URL Variables passed to a page as URL string parameters.

Query QueryName Variables that have names based on the column names that you select

Variables Variables created using a

the variable on the current page or on a page that you include using the

cfinclude tag.

processed on the action page.

in the database table. The values are created when you execute the query that selects data from the database.

cfset or cfparam tag. Most often you define

You will use these other types of variables in Part II of this manual. For additional information about variables, see CFML Reference.

22 Chapter 2: CFML Basics

Displaying variable output

Output is what remains after the ColdFusion server processes the CFML tags on a page. Usually the output has two parts:

• Information that the user sees (for example, a confirmation message)

• Information that is stored by the server as a result of processing (for example, user input

collected from a form)

One of the tags that ColdFusion provides to display output is the tag instructs ColdFusion to process all the code between the syntax for the

{normal html, text, and ColdFusion processing instructions}

</cfoutput>

cfoutput tag looks like this:

To return the value of a variable, you must always surround the variable name with number signs (#) and place the variable name between the

cfoutput start and end tags. For example, the

following code creates a variable and instructs the ColdFusion server to return the value of the variable:

#variables.my_full_name#

</cfoutput>

The following text is the output:

cfoutput tag. The cfoutput

cfoutput start and end tags. The

Kaleigh Smith

Working with CFML expressions

Expressions are an important part of the ColdFusion language. Expressions are a collection of different elements, ColdFusion variables, functions, and operators. You can think of them as strings of text that consist of one or more of the following elements:

• Literal text (string), numbers, dates, and other values

• Variables

• Functions

• Operators (& for joining statements, + for addition, and so on)

This chapter includes many examples of expressions; for example:

• #variables.my_full_name#

• DateFormat(Now())

• my_first_name= "Kaleigh"

When you build expressions in ColdFusion, you can include simple and complex elements; how you represent these elements determines how ColdFusion processes your application.

Working with CFML expressions 23

Building expressions

In ColdFusion, you build expressions as you need them. The expressions can include simple elements, such as the expressions shown previously, or they can include complex elements, such as arithmetic functions, strings, and decision operators. (You build some complex expressions in Part II of this manual.)

As mentioned, it is important that elements are identified properly in your expression so that ColdFusion processes them as expected, and you avoid unnecessary errors. When you write expressions, consider the following coding practices:

• Use of consistent character case

• When to use number signs (#)

• When quotation marks are needed

Specifying a consistent character case

Because the ColdFusion server is case-insensitive, you can write expressions using all uppercase, all lowercase, or mixed case. However, for code readability and consistency, you should use the same character case in all your programs. If you write your programs using the same case rules, you might prevent errors from occurring when you combine CFML on a page with case-sensitive languages, such as JavaScript.

Specifying number signs to denote functions or variables

In ColdFusion, you specify number signs (#) to denote functions and variables within a string of text. You use number signs to show the results of the function or variable on the page. Number signs instruct the ColdFusion server to evaluate the function (or variable) between the number signs and display the value. The value of the function (or variable) appears in the browser as a result.

The following are some common ways to use number signs:

• In the following example, you include the number signs to return the value to a page:

<cfoutput> Hello #variables.my_first_name# </cfoutput>

If you omit the number signs, the text, not the value, appears on the page.

• In the following example, you do not include the number signs because you are using the

cfset tag to assign one variable’s value to another value:

<cfset my_full_name = variables.my_first_name & " " &

variables.my_last_name>

• To display a number sign on a page, you must designate the number sign as a literal character.

You do this by using two number signs (##); for example:

##1: Your name.

</cfoutput>

The result is the following output:

#1. Your name.

24 Chapter 2: CFML Basics

For more information and examples on using number signs in expressions, see ColdFusion MX Developer’s Guide.

Specifying quotation marks around values

When you assign literal values to variables, you must surround the literal value with single- or double-quotation marks. ColdFusion interprets the content between the quotation marks as a literal value and assigns that value to the variable; for example:

ColdFusion instantiates the variable my_first_name to the string literal Kaleigh. Further, Smith is assigned to the variable

my_last_name and 5 is assigned to age.

When referencing a variable by its name, you do not surround the name with quotation marks. In the following example, when you concatenate literal text and variables using the & operator, you don’t surround the variable references with quotation marks:

<cfset the_string = "My name is " & variables.my_first_name &

" and my age is " & variables.my_age>

My name is

references marks. ColdFusion uses the values of the referenced variables ( assigning the value to the variable

is literal text, and you, therefore, surround it with quotation marks. The variable

variables.my_first_name and variables.my_age are not surrounded by quotation

Kaleigh and 5, respectively) when

the_string.

To display quotation marks on a page as literal characters, you must use two consecutive quotation marks; for example:

<cfset mystring = "We all shouted ""Happy Birthday"" when he entered the

room.">

#mystring#

</cfoutput>

The result is the following output:

We all shouted "Happy Birthday" when he entered the room.

Specifying operators in expressions

In ColdFusion, you use operators to test conditions; for example, you use the

IS operator to test

for equality. When you use operators in expressions, you must only use supported logical operators that ColdFusion can interpret properly. For example, if you use the greater than operator (>) or the less than operator (<), ColdFusion interprets them as the start or end of a tag rather than as an operator.

Working with CFML expressions 25

The following table lists the unsupported logical operators and their equivalent ColdFusion operators:

Unsupported logical operator

= IS, EQUAL, EQ Tests for equality.

< LT, LESS THAN Tests for less than.

<= LTE, LE,

>GT

>= GTE,

< > IS NOT, NEQ,

CONTAINS Tests whether a value is contained within a

DOES NOT CONTAIN Tests whether a value is not contained

Equivalent ColdFusion decision operator

LESS THAN OR EQUAL TO

GREATER THAN

GREATER THAN OR EQUAL TO

NOT EQUAL

Description

Tests for less than or equal to.

Tests for greater than.

Tests for greater than or equal to.

Tests for nonequality.

second value.

within a second value.

Arithmetic operators

The following table lists the arithmetic operators that ColdFusion supports:

Operators Description

+, -, *, / The basic arithmetic operators: addition, subtraction, multiplication, and division.

+, - Unary arithmetic operators for setting the sign of a number as either positive or

Mod Returns the remainder (modulus) after a number is divided by a divisor. The result

\ Divides two integer values. Use the \ (backslash character) to separate the

^ Returns the result of a number raised to a power (exponent). Use the ^ (caret) to

In the case of division, the right operand cannot be zero.

negative (+ or -).

has the same sign as the divisor. The right operand cannot be zero. For example, 11 MOD 4 is 3.

integers. The right operand cannot be zero. For example, 9 \ 4 is 2.

separate the number from the power. The left operand cannot be zero. For example, 2 ^ 3 is 8.

26 Chapter 2: CFML Basics

String operator

The following table describes the one ColdFusion string operator that is a concatenation operator:

Operator Description

& Concatenates strings.

About conditional processing

So far, all the coding examples shown in this chapter are considered linear coding examples. Linear code is when ColdFusion executes code starting with the first line on the page, and processes every line in order. Although you will use linear code in your applications, you will often write code that performs various actions based on conditions, such as the following:

• Determine whether a user entered a value in a form field.

• Display results based on user input.

• Display messages based on the time of day.

You use conditional processing to customize the behavior of your application. Conditional processing facilitates decision making and lets you control how the code on a page is processed.

In ColdFusion, you implement conditional processing with flow control tags. These tags are similar to other programming language control elements, such as using these tags, you can facilitate decision making in your code. The most fundamental tags used to control code execution are the

cfif, cfelse, and cfelseif tags. Because you will see and use

these tags in Part II of this manual, the following sections provide a basic introduction on how to use these tags. For more information about other conditional processing tags, including tags for looping, see ColdFusion MX Developer’s Guide.

if, then, and else. When

Using the cfif tag to evaluate True or False conditions

To create statements that let you evaluate conditions and perform an action based on the result, you use the

HTML and CFML tags executed if expression is True.

</cfif>

cfif tag to create a cfif statement. The basic syntax is as follows:

In this example, ColdFusion only executes the code inside the cfif statement if the expression evaluates to True. To perform actions if the expression is False, you can use the example, if the following the ending

HTML and CFML tags executed if expression is True.

HTML and CFML tags executed if expression is False.

</cfif>

cfif tag is processed:

cfif expression evaluates to False, the code between the cfelse tag and

About conditional processing 27

cfelse tag. For

Using the cfelseif tag to evaluate multiple expressions

To evaluate multiple expressions in a

cfif statement, you can use cfelseif and cfelse tags in

your statement; for example:

HTML and CFML tags executed if expression 1 is True.

HTML and CFML tags executed if expression 2 is True.

HTML and CFML tags executed for expression(s) that is False.

</cfif>

The following example shows you how you can evaluate multiple expressions using these tags. This example uses a form in which users can enter their state to determine their state tax:

#form.state# State Tax: 8.5%

#form.state# State Tax: 8.2%

#form.state# State Tax Unknown </cfif> </cfoutput>

The output of this cfif statement is based on the value entered by the user. If the user enters MA in the state form field, the state tax results returned is 8.5%. If the user enters VA in the state form field, the state tax results returned is 8.2%. If the user enters any other state in the state form field, State Tax Unknown is returned.

Processing form data

Virtually all web applications that gather and write information to a database use a form to accomplish that task. Forms let you collect information from a user (using an order form, registration form, and so on) and write that information to a database. Like HTML, there are two independent steps for creating a form in ColdFusion:

Creating the layout for the form itself.

Writing the code to process the submitted information.

Every form that you create in ColdFusion consist of two parts: the form page and the action page. These two pages work together to process user input. The form page contains the user interface elements, such as input fields and radio buttons. The action page handles the processing of the form page data.

28 Chapter 2: CFML Basics

When a user submits a form, the form values are stored in form variables and sent to the action page for processing. The following figure shows the relationship between the form page and action page:

Form page

Name

E-mail

Submit

The action page contains the code for form processing and interaction with database.

form.var1=value1 form.var2=value2

ColdFusion server & web server

Action

page

Database

In order for the form page to find its corresponding action page, the action statement in the form tag must be correct. The form tag includes the information that tells the server where to send the data that it collects. It also tells the server how to send it. To process these instructions to the server, the form tag uses the following syntax:

HTML and CFML form tags </form>

The first attribute (action) in the form tag lets you specify where to send the data. The page that you specify where to send the data is the name of the action page. The second attribute in the form tag is must set the method attribute to

method. The only method that ColdFusion supports is post. All ColdFusion forms

post.

In Part II of this manual, you use ColdFusion form tags to create forms and write collected values to a database.

Processing form data 29

Commenting your code

As in other programming languages, it is important to include comments in your code. You should comment your code for the following reasons:

• Commented code is easier to debug than code that is not commented.

• If you describe the code on the page, it is easier to make modifications.

• Commented code tends to be better organized.

Comment tag

The ColdFusion comment tag is similar to the HTML comment tag, except that it has three dashes instead of two:

ColdFuions comments can wrap to more than one line. ColdFusion comments are not returned to the browser because the ColdFusion server processes and omits the comments from the page. The user will never be able to read your comments.

30 Chapter 2: CFML Basics

CHAPTER 3

Database Fundamentals

This chapter provides a quick overview of relational database concepts and terms. It describes what a database is and how it is organized. It also discusses the Structured Query Language (SQL) that you use to interact with databases.

About database basics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31

About SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34

Using SQL with ColdFusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36

About database basics

Even though you do not need a thorough understanding of database management systems to create ColdFusion applications, you must understand some basic concepts and techniques about databases. The information in this chapter will help you get started using ColdFusion.

What is a relational database?

A relational database is a structured collection of information that is related to a particular subject or purpose, such as an inventory database or a human resources database. You use databases to manage information. Information, such as product name, cost, and on-hand inventory, is stored in a database. Within the database, you organize the data into storage containers called tables. Ta b l e s are made up of columns and rows. Columns represent individual fields in a table. Rows represent records of data in a table. You can think of database tables as grids, as in the following example:

Field (column)

Record (row)

Each field in the table contains one piece of information. In an employee table, for example, one column contains the employee name, another contains the employee phone number, and the other columns each store one piece of information, such as the address, city, state, zip, and salary information. Each record represents one set of related information. For example, an employee table might store information about one employee per row. The number of rows in a table represents the total number of table records.

32 Chapter 3: Database Fundamentals

About relational tables

In a database, you can organize data in multiple tables. For example, if you manage a database for the Human Resources department, you might have one table that lists all the information about employees and another table that lists all the departments:

You have multiple departments for employees, but you would not store the information about the departments in every employee row for the following reasons:

• The department information is the same for each employee in a given department; however,

repeating the department information for each employee is redundant. Storing redundant data

takes up more disk space.

• If the department information changes, you can update one occurrence. All references to that

department are updated automatically.

Storing multiple occurrences of the same data is rarely a good thing. Good relational database design separates application entities into their own tables. Key values from one table are often stored in a related table rather than repeating the information. The key value is used to join the data between the tables to return the complete set of data required.

About database basics 33

About SQL

SQL (Structured Query Language) is a language that lets you communicate with databases. For example, you can use SQL to retrieve data from a database, add data to a database, delete or update records in a database, change columns in multiple rows, add columns to tables, and add and delete tables.

Unlike other computer languages, SQL is made up of a small number of language elements that let you interact efficiently with a database. Some of the more frequently used elements include the following SQL commands:

Command Use

SELECT Retrieve (query) information in a database.

INSERT Add records to a database.

UPDATE Update information in a database.

DELETE Delete information in a database.

Understanding basic SQL SELECT statements

One of the most widely used SQL statements is the SELECT statement. The SQL SELECT statement retrieves columns of data from a database. The tabular result is stored in a result table (called the record set).

You use the following SELECT statement to retrieve information from a table:

SELECT column_name(s) FROM table_name

Consider a table named Clients that contains the following rows:

LastName FirstName Address City

Brown Marie 12 State St Boston

Adams Russell 521 Beacon St Boston

Carter Joan 1 Broadway New York

To select the columns named LastName and FirstName, use the following SELECT statement:

SELECT LastName, FirstName FROM Clients

The result of this SQL statement contains the following data:

LastName FirstName

Brown Marie

Adams Russell

Carter Joan

34 Chapter 3: Database Fundamentals

Using the SQL WHERE clause to limit the rows returned

To conditionally select data from a table, you can add a WHERE clause to the SELECT statement, which results in the following syntax:

SELECT column_name FROM table_name WHERE column condition value

With the WHERE clause, you can use any of the following operators:

Operator Description

=Equal

<> Not equal

> Greater than

< Less than

>= Greater than or equal

<= Less than or equal

BETWEEN Between an inclusive range

AND Joins one or more conditions

OR Joins one or more conditions

LIKE Specifies a search for a pattern in a column. You can use a percent sign

(%) to define wildcards (missing letters in the pattern) before and after the pattern.

For example, to select the columns named LastName and FirstName for Clients whose City is Boston, use the following SELECT statement:

SELECT LastName, FirstName FROM Clients Where City = 'Boston'

The result of the preceding SQL statement contains the following data:

LastName FirstName

Brown Marie

Adams Russell

You can compose a WHERE clause with one or more conditions; these are called subclauses. You join subclauses using the operators AND and OR. The AND operator displays a row if all conditions that are listed are True. The OR operator displays a row if any of the conditions listed are True. The following statement shows an example of a WHERE clause with multiple subclauses:

SELECT LastName, FirstName FROM Clients Where City = 'Boston' AND FirstName =

'Marie'

About SQL 35

The result of the preceding SQL statement contains the following data:

LastName FirstName

Brown Marie

Note: The preceding SQL SELECT examples use single-quotation marks around the value. SQL uses single-quotation marks around text values. Most database systems also accept doublequotation marks. Do not enclose numeric values in quotation marks.

Sorting the results

You use the ORDER BY clause to sort the result rows. The following SQL statement returns an alphabetic list of people sorted by last name, and then first name, from the Clients table:

SELECT * FROM Clients Order By LastName, FirstName

The default is to return the results in ascending order (top to bottom). If you include the DESC keyword in the ORDER BY clause, the rows are returned in descending order (bottom to top).

The following statement returns a reverse alphabetic list of the Clients table:

SELECT * FROM Clients Order By LastName, FirstName DESC

Note: The SQL SELECT statement is quite powerful. There are several other options for retrieving data from a SQL database using the SELECT statement, which are not described in this manual. For more information, consult a SQL reference.

Using SQL with ColdFusion

ColdFusion communicates with your data source through a database interface called Java Database Connectivity (JDBC). JDBC is a standard application programming interface (API) for accessing information from different database systems and different storage formats.

About data sources

A data source is a complete database configuration that uses a JDBC driver to communicate with a specific database. In ColdFusion, you must configure a data source for each database file that you want to use. After you configure a data source, the ColdFusion server is then capable of communicating with that data source through the JDBC driver.

You configure data sources in ColdFusion by using the ColdFusion MX Administrator. For information about how to configure the sample data source file that is supplied for use with Part II of this manual, see “Lesson 2: Configuring Your Development Environment” on page 49. For more information about configuring a data source in ColdFusion, see Installing and Using ColdFusion MX or ColdFusion MX Developer’s Guide.

36 Chapter 3: Database Fundamentals

Writing SQL and CFML statements to interact with a data source

After ColdFusion makes a connection to the data source, you can interact with that database by using SQL and ColdFusion.

To interact with an established data source, you must include SQL statements in your CFML statements; for example:

SELECT FirstName, LastName, DepartmentID

From Employee </cfquery>

In this example, the first attribute of the cfquery tag is the name of the query. The second attribute of the

cfquery tag defines the name of the data source. The SELECT statement defines

the fields (columns) to be retrieved from a table named Employee.

CFML tags that interact with a database

The following table lists the CFML tags that you can use to interact with a database:

Tag Description

cfquery

cfinsert

cfupdate

To retrieve (query) information in a database.

To add records to a database.

To update information in a database.

Part II of this manual introduces these tags when you use them to interact with the sample database. For more information about interacting with a database, see ColdFusion MX Developer’s Guide or CFML Reference.

Using SQL with ColdFusion 37

38 Chapter 3: Database Fundamentals

PART II

Building a ColdFusion Application

Part II provides a tutorial that steps you through building a sample Macromedia ColdFusion application. It consists of the following lessons:

Lesson 1: Preparing to Build the Sample Application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41

Lesson 2: Configuring Your Development Environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49

Lesson 3: Retrieving Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59

Lesson 4: Building Dynamic Queries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71

Lesson 5: Creating a Trip Detail Page. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85

Lesson 6: Creating a Main Application Page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97

Lesson 7: Validating Data to Enforce Business Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103

Lesson 8: Implementing Browsing and Searching. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123

Lesson 9: Enabling Database Maintenance. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129

Lesson 10: Restricting Access to ColdFusion Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . 143

PART II

CHAPTER 4

Lesson 1: Preparing to Build the

Sample Application

This tutorial guides you through the process of building a simple Macromedia ColdFusion web application. By the end of the tutorial, you will be familiar with how to use Macromedia ColdFusion MX 7 to query and update a database, validate data, generate a report, and require user authentication.

The application that you will build in this tutorial is for a fictitious travel company called Compass Travel. Compass Travel markets a wide range of adventure trips to the public through its website. Trip coordinators at Compass Travel are responsible for maintaining the trip information. The sample tutorial application assists the trip coordinators in maintaining trip information in the Compass Travel database.

ColdFusion development is the emphasis of the tutorial; therefore, you will not need to design or build the Compass Travel database. It is important, however, for you to be familiar with the layout of the database. Additionally, you must understand the functional requirements that will help determine the application design. This lesson provides an overview of these application design steps, while the remainder of this manual guides you through the lessons on constructing the sample application.

Application development steps

Most software applications contain three major functions:

• A user interface to capture data

• Logic to validate the captured data

• A database to store the validated data

The steps to develop these major functions vary from project to project. In this tutorial, you will review or participate in the following application development steps to build the Compass Travel Trip Maintenance application:

Step Description

1 Determine the application functional requirements.

2 Determine the data requirements by identifying the information required for the Trip

Maintenance application.

Step Description

3 Design the database for your application by exploring the database tables that will store

4 Develop the ColdFusion application pages.

the trip information.

The following sections explore an overview of each of these application development steps.

Exercise 1: Determining the application functional requirements

Before you can build the sample application, you must understand the functional requirements that influence its design. The design of the sample application centers around the daily tasks performed by Compass Travel’s trip coordinators. The following table describes these tasks:

Trip coordinator task Description

Produce current trip listing

Provide trip information On an ad hoc basis, Compass Travel management asks the trip

Maintain trip information The trip coordinator is responsible for keeping all trip information up to

Ensure the quality of trip information

To help Compass Travel agents take trip reservations on the telephone and in person, the trip coordinator maintains a list of current trip offerings.

coordinator to develop lists of trips that meet specific criteria.

date. To do this, the coordinator needs to locate a trip to edit it or delete it. Additionally, the coordinator must be able to add a new trip.

The trip coordinator is responsible for periodically browsing the current trip offerings to ensure that all the information is accurate. Additionally, when adding a new trip or editing an existing one, the trip coordinator must ensure that the data adheres to the Compass Travel business rules.

You can derive several functional requirements for the new application from the preceding table. For example, the sample application must provide the ability to do the following:

• Generate trip listings.

• Find trips based on user-supplied criteria.

• Browse trips.

• Add a new trip.

• Delete an existing trip.

• Edit an existing trip.

• Validate new or updated trips against Compass business rules.

In the lessons that follow, you will build ColdFusion pages to address each of these functional requirements. The basis for every requirement is the idea of a trip. Before you can build code to address any of these requirements, you must understand which attributes of a trip are important to Compass Travel. For this you must determine the data requirements for the application. Understanding the data requirements is essential to building the proper database to hold the application data.

42 Chapter 4: Lesson 1: Preparing to Build the Sample Application

Exercise 2: Determining the data requirements

Prior to creating the application pages to capture trip information, you must determine what type of data is required about each trip. For the example in this tutorial, the Compass Travel trip coordinator must maintain the following information about each trip:

• Tr i p n am e

• Type of event (surfing, mountain climbing, kayaking, and so on)

• Trip description

• Trip location

• Departure date

• Return date

• Price

• Base cost

• Assigned trip leader

• Total number of people who can attend the trip

• An indicator of whether a deposit is required

• Trip photograph

By collecting the preceding information about each trip, the Compass Travel website can market its trips online to the general public. Customers who are booking a trip need to know the trip name, when the trip begins and ends, the price, and a description. Additionally, the trip coordinator must identify the filename for a photograph of each trip. The Compass Travel website displays the photograph to further entice prospective customers into booking the trip. Finally, Compass Travel considers it important to store the base cost for each trip to help determine trip profitability. The cost must be captured, but it is for Compass Travel internal use only and is not shown on the public website.

Exercise 3: Designing the database for your application

After you identify the information to collect, you must consider where to store the data. Prior to creating the data collection form and instructing ColdFusion where to store the form data, you must have a database ready to accept the data.

Exercise 3: Designing the database for your application 43

If you had to create the Compass Travel database, you would create a table named trips to store the information that you plan to collect about each trip. The table would look something like the following table:

Recognizing the data types

Each field in the Trips table has a data type attribute that describes the type of data that can be stored in the column. For instance, the tripName column can contain text data, while the price column can only contain numeric data. It is important to know what type of data is valid for each column so that your data collection forms can validate against incorrect values entered by the user.

Looking closer, you might wonder why the eventType column is a number and not a text data type column. Recall that data requirements analysis identified the need for a type of event (surfing, mountain climbing, kayaking, and so on). The purpose of this column is to classify trips into various categories based on the trip activity. It is essential that the application classify the trips consistently. Therefore, it is important to offer a list of event types for the user to select, rather than to accept free text input.

To present a list of event types for user selection, the event types are stored in a separate table, the eventtypes table. This table is already populated and contains the following rows:

44 Chapter 4: Lesson 1: Preparing to Build the Sample Application

Establishing a relationship between the two tables

When the user selects an event type from the list obtained from reading the eventtypes table, the correct event type must be saved to the trips table with all the other trip-related data. The application could store the eventType (for example, mountain climbing) itself into the eventType column in the trips table. But if the name Mountain Climbing were later shortened to Climbing in the eventtypes table, new mountain climbing trips would be classified differently than ones saved before the change. For this reason and to save space in the database, the key to the eventtypes row (eventTypeID) is stored in the trips table instead.

The two tables are said to have a relationship. This relationship works by matching data in key fields. In this case, the matching fields consist of a primary key (eventTypeID) from the eventtypes table, which provides a unique identifier for each record, and a foreign key (eventType) in the trips table. The foreign key contains the same value as the primary key, pointing to a unique event type. The following figure shows this relationship:

Exercise 3: Designing the database for your application 45

Exercise 4: Developing the sample application

Using the application functional requirements and the database that is provided for this tutorial, you are ready to use ColdFusion to develop the Trip Maintenance application. The remaining lessons in the tutorial step you through the process of constructing this application. When you are done, the main page for the Trip Maintenance application will appear as follows:

The main application page is where users will come to view information about trips and to navigate to other ColdFusion pages to add, edit, or search for new trips.

The following lessons explain how to do these tasks:

• Configure your development environment (“Lesson 2: Configuring Your

Development Environment” on page 49).

• Retrieve data from a database (“Lesson 3: Retrieving Data” on page 59).

• Build dynamic queries (“Lesson 4: Building Dynamic Queries” on page 71).

• Build the trip detail page (“Lesson 5: Creating a Trip Detail Page” on page 85).

• Build the main application page (“Lesson 6: Creating a Main Application Page” on page 97).

• Write code to enforce Compass Travel business rules (“Lesson 7: Validating Data to Enforce

Business Rules” on page 103).

46 Chapter 4: Lesson 1: Preparing to Build the Sample Application

• Implement browsing and searching (“Lesson 8: Implementing Browsing and Searching”

on page 123).

• Enable database maintenance (“Lesson 9: Enabling Database Maintenance” on page 129).

• Restrict access to the Compass Travel application by requiring user authentication (“Lesson 10:

Restricting Access to ColdFusion Applications” on page 143).

How to proceed

Each lesson in the tutorial is designed to let you proceed at your own pace. At any time, you can stop and later return to that place in a lesson so that you can complete all the sections in the lesson.

Each lesson guides you through a scenario to enhance the Compass Travel Trip Maintenance application. Sections within a lesson present basic programming concepts that you need to understand before completing the section exercises.

Depending on your programming experience, you can read the entire lesson and then proceed to the hands-on exercises, or you can skip some information in the lesson and proceed directly to the exercises. For each exercise, there is a completed version of each file created or modified in that lesson. The completed solution files are in the directory cf_root/cfdocs/getting_started/solutions. Appended to each filename is the lesson and exercise number. For example, the version of the file tripdetail.cfm that you would have after performing the steps in Lesson 5, Exercise 4 is named tripdetail_lesson5_ex4.cfm. To use the solution file and continue with Lesson 5, Exercise 5, copy the tripdetail_lesson5_ex4.cfm file to the cf_root/cfdocs/my_app directory, and overwrite the tripdetail.cfm file if it already exists in the my_app directory.

Working directories

The following table describes the working directories for this tutorial:

Directory Description

my_app You save all your source code in this directory.

solutions You can find solutions to all the exercises in this directory.

db You use this directory as the working directory for the Compass Travel database.

photos You use this directory to access existing trip photographs.

images You use this directory to access application image files.

Locating the working directories

The working directories for this tutorial are under your web root directory. For example, the directory path on your computer might look like the following:

• (On Windows) web_root\cfdocs\getting_started

• (On UNIX) web_root/cfdocs/getting_started

Note: When using the J2EE configuration, the cfdocs directory is under the ColdFusion web application root. For more information, see “Saving your ColdFusion page” on page 17.

Exercise 4: Developing the sample application 47

You can view ColdFusion application pages on your local computer by opening a web browser and entering one of the following URLs:

Configuration URL

For local third-party web server configuration

For built-in ColdFusion web server configuration

http://localhost/cfdocs/getting_started/my_app/<pagename>.cfm

http://localhost:8500/cfdocs/getting_started/my_app/<pagename>.cfm

For more information about the tutorial file structure and the location of the getting_started subdirectories, see “Exercise 1: Verifying the tutorial file structure” on page 49.

Requirements

To use this tutorial, you must have the following components installed:

ColdFusion MX 7 For information on how to install ColdFusion MX 7, see Installing and Using ColdFusion MX.

Database Management System A database management system can be installed on the same

computer as ColdFusion MX or on a separate computer. For the purpose of this tutorial, a Microsoft Access database file for Microsoft Windows users and a PointBase database file for UNIX users have been provided. For information about how to configure the Compass Travel data source, see “Lesson 2: Configuring Your Development Environment” on page 49. For additional information about configuring a data source, see Installing and Using ColdFusion MX.

Web browser You can use Microsoft Internet Explorer (4.0 or later) or Netscape Navigator

(6.0 or later).

Text editor or IDE (Interactive Development Environment) In order to use the Macromedia

Dreamweaver Extensions described in this tutorial, you must use Dreamweaver MX 2004. However, you can use Macromedia HomeSite+, ColdFusion Studio, any text editor, or IDE, and copy and paste the code that the Dreamweaver Extensions create. In the exercises in this tutorial, the term editor means Dreamweaver MX 2004, HomeSite+, ColdFusion Studio, or any text editor or IDE of your choice.

48 Chapter 4: Lesson 1: Preparing to Build the Sample Application

CHAPTER 5

Lesson 2: Configuring Your

Development Environment

In this lesson, you set up your development environment for the tutorial. This lesson describes the tutorial file structure, and how to configure the database connection and debugging options. Additionally, it provides an overview of using Macromedia Dreamweaver MX for Macromedia ColdFusion development.

This lesson explains how to do the following tasks:

Exercise 1: Verifying the tutorial file structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49

Exercise 2: Setting up your development environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50

Exercise 3: Configuring a database connection. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52

Exercise 4: Configuring debugging options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55

Exercise 1: Verifying the tutorial file structure

Before you begin the tutorial, verify that the configuration of the computer where ColdFusion MX 7 is installed matches the file structure described in the following sections.

The files required to complete the Compass Travel tutorial are installed under the web server root directory. The location of this directory varies, depending on whether you chose to configure a local third-party web server (such as IIS) or the ColdFusion stand-alone web server during installation, as follows:

• When you use a third-party web server (such as IIS or Apache), the files are installed in:

web_root/cfdocs/getting_started.

• When you use the ColdFusion built-in web server, the files are installed in:

cf_root\webroot\cfdocs\getting_started.

The following is the getting_started directory structure:

getting_started

-db

-my_app

-images

-new_user_database

-photos

-solutions

Note: When you use the J2EE configuration, the cfdocs directory is under the ColdFusion web application root. For more information, see “Saving your ColdFusion page” on page 17.

ColdFusion MX installs two copies of the sample CompassTravel database file. The working copy is located in the db directory; a backup copy of the file is in the new_user_database directory.

To ensure that you are working with the original database file, verify that the file in the db directory has the same date as the backup file in the new_user_database directory. If the date of the file in the db directory is later than the backup file, replace the file in the db directory with a copy of the backup database.

Caution: Do not write to the database file in the new_user_database directory. The backup file lets multiple users perform the tutorial.

In each of the database subdirectories, the tutorial provides one sample database file for Microsoft Windows users and one sample database file for UNIX users. Windows users use a Microsoft Access file, and UNIX users use a PointBase file.

Note: The sample PointBase file consists of two files: compasstravel.dbn and compasstavel$1.wal. ColdFusion MX uses both of these files to work with the content in the sample PointBase database.

Save all the files that you create for the tutorial application in the my_app directory. This directory contains one subdirectory for images. The image subdirectory contains the required image files for the tutorial application.

The photos directory contains the required photo files for the tutorial application. The solutions directory provides sample application files that you can use when building the tutorial application.

Exercise 2: Setting up your development environment

If you are running ColdFusion in a Windows environment, Macromedia recommends that you use Macromedia Dreamweaver MX for ColdFusion development; however, if you are running ColdFusion in a UNIX environment, you should use the text editor that you prefer. Although Dreamweaver MX offers features that help you create ColdFusion applications quickly and easily, you can still follow the steps of the tutorial by entering or copying the code examples into your editor.

Note: To use the Dreamweaver Extensions included with ColdFusion MX 7, you must use Macromedia Dreamweaver MX 2004 version 7.0.1.

About Macromedia Dreamweaver MX

Macromedia Dreamweaver MX is the preferred development environment for building ColdFusion MX 7 applications. It supports the latest ColdFusion MX 7 features, and it combines superior code editing features with the visual design features of Dreamweaver. You can build ColdFusion MX 7 applications by writing the code manually or generating the code by using one of the code-generating tools provided with Dreamweaver MX. In addition, ColdFusion MX 7 provides extensions to Dreamweaver that let you perform some administrative tasks without leaving the Dreamweaver environment and generate code for some of the most common tasks you encounter when creating a ColdFusion application.

50 Chapter 5: Lesson 2: Configuring Your Development Environment

With Dreamweaver MX, you can author and test your application code from a local or remote client. You can save your code directly to the server computer where ColdFusion is installed. The following sections provide an overview of Dreamweaver MX, and information on how to configure Dreamweaver MX for ColdFusion development.

Features for ColdFusion developers

Dreamweaver MX provides a wide variety of code editing features for ColdFusion developers, including the following:

• Extensions that generate code for common ColdFusion application tasks.

• Extensions that let you perform administrative tasks without leaving the Dreamweaver

environment.

• Rich tag editors for quickly setting attributes and values for every CFML tag.

• Code hints for writing CFML tag attributes.

• A code validator for validating code readiness against other ColdFusion versions.

• A tag chooser with integrated reference material for inserting ColdFusion tags.

• A snippets panel for reusing code.

• An integrated debugging display for quickly pinpointing problem areas in the code.

• A remote ColdFusion server connection for browsing remote data sources and files.

• ColdFusion MX documentation included in the Dreamweaver MX online Help.

To use Dreamweaver MX to build the sample ColdFusion application in this tutorial, see the following sections for information about configuring Dreamweaver for ColdFusion development.

Configuring Dreamweaver MX for ColdFusion development

Before you use Dreamweaver MX to create the sample application in this tutorial, you must configure Dreamweaver to use the ColdFusion MX 7 Extensions for Dreamweaver, which are included with ColdFusion MX 7. You must also define the connection to the sample database file. The Extensions simplify the process of configuring database connections.

To configure Dreamweaver MX to create the sample application:

Install the ColdFusion MX 7 Extensions for Dreamweaver by double-clicking the

cfmx7dreamweaverextensions.mxp file, which is located in the cf_root/wwwroot/CFIDE/

installers directory.

Create a site that contains the tutorial files.

Specify ColdFusion as the application document type.

Specify ColdFusion as the site application server technology. For information about how to

perform these steps, see the Dreamweaver MX online Help or Using Dreamweaver MX.

Specify the RDS login information for the site.

Tip: If you are a new Dreamweaver MX user, you can perform the Dreamweaver MX tutorial before

using Dreamweaver MX to build the sample application. The tutorial in this book does not describe

how to use Dreamweaver. The purpose of this tutorial is to teach you how to build ColdFusion

applications using ColdFusion Markup Language (CFML).

Exercise 2: Setting up your development environment 51

Exercise 3: Configuring a database connection

One of the most commonly used and most powerful features of ColdFusion is the ability to connect to and manipulate data from a wide variety of databases. Like most ColdFusion developers, you will probably use this capability often. To be able to connect to a database, you must create a a data source in ColdFusion. A data source contains the information that ColdFusion needs to be able to recognize and communicate with a database.

ColdFusion MX 7 includes a Dreamweaver extension that lets you create a data source without having to leave the Dreamweaver environment. If you are not using Dreamweaver, you use the ColdFusion Administrator to create data sources; see“Configuring a data source in the

Administrator” on page 52 for more information.

Using Dreamweaver to configure a data source

To create a ColdFusion data source without leaving the Dreamweaver environment, you need the following information:

• The type of connection to use

• The name to call the data source

• The location of the database file(s)

To configure a database connection in Dreamweaver:

Ensure that the site that contains the tutorial files is the current site.

Select Create New > ColdFusion.

On the Databases tab, click the + button.

Select Microsoft Access Connection.

In the CF Data Source Name text box, enter CompassTravel.

Specify the Database File as

c:\CFusionMX7\wwwroot\cfdocs\getting_started\db\CompassTravel.mdb.

Select the Use Default Username check box.

When prompted for the password for this site, enter the ColdFusion Administrator password

you specified when you installed ColdFusion MX 7.

Click OK.

10.

Continue with “Exercise 4: Configuring debugging options” on page 55.

Configuring a data source in the Administrator

You can use the ColdFusion MX 7 Administrator to configure a data source. If you are running ColdFusion in a UNIX environment, you must use the ColdFusion MX 7 Administrator to connect to the PointBase database in the data source.

52 Chapter 5: Lesson 2: Configuring Your Development Environment

To access the ColdFusion MX 7 Administrator:

• Open a browser and go to one of the following URLs:

■ External web server users: http://localhost/CFIDE/administrator

■ Built-in web server users: http://localhost:8500/CFIDE/administrator

■ J2EE server users: http://localhost:portnumber/CFIDE/administrator. If you installed

ColdFusion Enterprise Edition with JRun, the default HTTP port is 8300.

Note: If you are accessing the ColdFusion MX Administrator from a remote client, you must replace localhost with the host name or IP address of the computer where ColdFusion MX is installed.

The following sections describe how to establish a connection to the sample tutorial database file and how to enable optional debugging settings.

Configuring the connection to the sample database file

The following procedures describe how to configure a connection to the sample database file (CompassTravel) using the ColdFusion MX Administrator. Prior to building the sample application, you must configure the Compass Travel database connection.

Perform one of the following procedures. The Microsoft Access procedure is for Windows users. The PointBase procedure is for UNIX and Macintosh OS X users.

To define the connection to the sample Microsoft Access database:

In the ColdFusion MX Administrator, select Data & Services > Data Sources.

In the Data Source Name box, enter CompassTravel.

In the Driver list, select Microsoft Access with Unicode.

Click Add.

Click Browse Server and navigate to the cf_root/cfdocs/getting_started/db/CompassTravel.mdb

file.

Click Apply.

Click Show Advanced Settings and ensure that the settings for CLOB and BLOB are enabled

(checked).

Click Submit to complete the data source configuration.

The ColdFusion MX Administrator verifies the data source connection and the name

CompassTravel appears in the Connected Data Sources table.

Ensure that OK appears in the Status column for CompassTravel.

If the connection to the CompassTravel data source fails, do the following:

Verify that the name of the data source file does not contain a space. If it does contain a space, delete the data source from the Connected Data Source dialog box. To do this, click the Delete action button associated with the CompassTravel data source name, and then repeat the steps in this procedure to reconfigure this data source.

Verify that the path specified for the Compass Travel database file is correct.

Exercise 3: Configuring a database connection 53

To define the sample PointBase database file:

In the ColdFusion MX Administrator, select Data & Services > Data Sources.

The Add New Data Source dialog box appears.

Specify the following:

Field Action

Data Source name text box

Driver drop-down selection box

Click Add to configure the data source name and driver.

Specify the name CompassTravel. Note: Ensure that the name of the data source file does not contain any spaces. If the name contains a space, the data source connection fails.

Select Other.

The Data Source dialog box appears.

Specify the following:

Field Action

JDBC URL Enter the following JDBC URL for the Compass Travel PointBase files:

jdbc:pointbase:compasstravel,database.home=/<home location>/ wwwroot/cfdocs/getting_started/db

The following is the default home location for stand-alone ColdFusion web server configurations:

/opt/coldfusionmx7/wwwroot/cfdocs/getting_started/db

Driver Class Enter the following driver class:

com.pointbase.jdbc.jdbcUniversalDriver

Driver Name Specify PointBase.

Username Specify PBPUBLIC.

Password Specify PBPUBLIC.

Description Enter the following:

Database file for Compass Travel tutorial

Click Show Advanced Settings to make the following settings:

Field Action

Maintain Connection Disabled (clear)

CLOB Enabled (checked)

BLOB Enabled (checked)

Click Submit to complete the data source configuration.

The name CompassTravel appears in the Connected Data Sources dialog box.

54 Chapter 5: Lesson 2: Configuring Your Development Environment

Click Verify All Connections to ensure that ColdFusion can access this file.

OK appears in the Status column for successful connections.

If the connection to the compass travel data source fails, do the following:

Verify that the JDBC URL is correct.

Exercise 4: Configuring debugging options

ColdFusion provides debugging information to make it easier to fix problems in your application. You can view debugging information for individual ColdFusion pages while editing them in Dreamweaver, or enable debugging throughout your application by using the ColdFusion MX 7 Administrator to set debugging options.

Viewing debugging information within Dreamweaver

Instead of enabling debugging for an application, you can view debugging information for any ColdFusion page in Dreamweaver.

To view debugging information for a ColdFusion page in Dreamweaver:

Open the page to debug in Dreamweaver.

On the Documents tab, click the Server Debug icon.

If an Exceptions category appears in the Results panel, click the + button to expand the category.

Fix the error and save the file, and click Browse.

To leave debug mode, switch to either Code view or Design view.

Enabling debugging in the ColdFusion MX Administrator

The ColdFusion MX Administrator provides a variety of debugging settings that let you enable debugging information on a server-wide basis. If you are working on a development system, you can have these options turned on all the time. However, if you are working on a production system, you most likely will not want to have these options turned on, because the debugging information can appear on the bottom of an application page or in a dockable tree in your browser.

Exercise 4: Configuring debugging options 55

The location of the debugging information or the type of debugging data shown varies, depending on the options that you enable on the Debugging page in the ColdFusion MX Administrator. In the following example, the debugging output includes general information about the ColdFusion server, the execution time of the application, and variable information:

The following figure shows an example of how debugging information can appear when appended to the bottom of a page in a browser:

The application form

The appended debugging information

If you are using a development server to build the sample application in this tutorial, you can enable some of these settings to help debug any unexpected problems.

Use the following steps to enable debugging options in the ColdFusion MX Administrator.

To enable debugging options:

In the ColdFusion MX Administrator, select Debugging & Logging > Debugging Settings.

A list of debugging options appears on the Debugging Settings page.

Select the Enable Debugging check box.

The debugging service is enabled for all options already selected on the page.

On the Debugging Settings page, view the description of each option that is enabled. If you do

not want to append debugging information for a specific option, clear the check box.

For the purpose of this tutorial, enable the following debugging options:

Option Description

Database Activity Identifies database activity related to SQL query events.

Exception Information Identifies ColdFusion exceptions raised in the debugging output.

Tracing Information Lets you trace event information reported in the debugging output.

Form, URL and Session Variables

Click Submit Changes when you are done.

56 Chapter 5: Lesson 2: Configuring Your Development Environment

Displays variable information in the debugging output.

Sending debugging information to remote clients

If you are using a remote client to perform the tutorial in this book, you must specify your IP address to receive debugging information. If you are working on a local client (the computer where ColdFusion is installed), you do not need to do this.

To receive debugging information when using a remote client:

In the ColdFusion MX Administrator, select Debugging & Logging > Debugging IP Addresses.

The Debugging IP Address page appears.

In the IP Address text box, enter the IP address of your remote client.

Click Add.

Summary

In this lesson, you set up your development environment, configured data sources, and set up debugging options.

In the next lesson

In the next lesson, you learn to retrieve data from a database, display the results of querying a database, and use ColdFusion components to write structured, reusable code.

Summary 57

58 Chapter 5: Lesson 2: Configuring Your Development Environment

CHAPTER 6

Lesson 3: Retrieving Data

In this lesson, you begin the construction of a Macromedia ColdFusion web application for the fictitious company, Compass Travel. The exercises in this lesson introduce you to the process of retrieving data from the Compass Travel relational database.

This lesson contains the following exercises:

Exercise 1: About the trip list. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60

Exercise 2: Retrieving the information for the trip list . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61

Exercise 3: Displaying the query result using the cfoutput tag . . . . . . . . . . . . . . . . . . . . . . . . . 62

Exercise 4: Writing structured, reusable code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63

Exercise 5: Creating additional queries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66

Exercise 1: About the trip list

As you recall from “Lesson 1: Preparing to Build the Sample Application” on page 41, one of the requirements for the Trip Maintenance application is the ability to generate trip listings. To help Compass Travel agents take trip reservations by telephone and in person, the trip coordinator maintains a list of current trip offerings. Years ago, the coordinator would type the list and fax it to the various Compass Travel offices in an effort to keep everyone informed. When Compass Travel built an intranet that was accessible by all offices, the trip coordinator added the following HTML web page to the site:

Each time the Trip List HTML page is rendered in a browser, it displays the same web page. Since the page always shows an identical trip list, it is considered a static web page. You should only use static web pages when you are creating a page that is not likely to change often.

Using the static web page approach, the Trip Coordinator needs to modify all the web pages that reference trip lists when trips are added, deleted, or trip names are changed. This manual process of updating each web page can lead to inaccurate or untimely information. Luckily, Compass Travel has built a database that contains a list of trips, so you can build a more accurate and timely solution for the trip coordinator.

In this lesson, you will create a dynamic Trip List page to present an up-to-date list of trips on the Compass Travel website. The primary users of this component are the Compass Travel coordinators and agents, not the general public.

To display the information on the page, you do the following:

Retrieve the data that you want to display.

Display the data on the page.

60 Chapter 6: Lesson 3: Retrieving Data

Exercise 2: Retrieving the information for the trip list

Relational database management systems process SQL instructions sent to them from various applications. ColdFusion sends SQL statements to database managers to manipulate data. ColdFusion needs a way to know to which database manager to send a specific SQL string for evaluation. In CFML, the statement and the

cfquery tag to create a dynamic version of the Trip List page described earlier

in this lesson. In this example, you use the tripName column in the Compass Travel Trips table. To use the SQL SELECT statement to dynamically retrieve this information, you must execute the SQL SELECT statement between the

cfquery start and end tags.

To retrieve the trip list:

If you are working in Dreamweaver, ensure that the site that you created in Lesson 1 is the

current site. For more information, see “Configuring Dreamweaver MX for ColdFusion

development” on page 51.

Open a new file and save it as triplisting.cfm in the my_app directory.

Note: If you are working in Dreamweaver, ensure that you select ColdFusion Templates in the

Save As Type list.

Enter the following code, or do the steps listed in the “Let Dreamweaver do it” section.

SELECT trips.tripName FROM trips

</cfquery>

Save the file.

cfquery tag serves this purpose. You will use the SQL SELECT

cfquery tag to return all the trip names found in the

Let Dreamweaver do it

Dreamweaver lets you create a query without having to enter the code.

To create a query in Dreamweaver:

Click the Bindings tab in the application panel.

Click the + button.

Select Recordset (Query).

In the Name text box, enter Tr i p L i s t .

From the Data source list, select CompassTravel.

From the Tables list, select trips.

Next to Columns, click Selected.

Select tripName.

Click OK.

10.

Save the file.

Your ColdFusion application page retrieves the information for the trip list. Next, you need to display the information.

Exercise 2: Retrieving the information for the trip list 61

Reviewing the code

The following table describes the code used to build the query:

Code Explanation

<cfquery name="TripList"

datasource="CompassTravel">

SELECT trips.tripName FROM

trips

ColdFusion query named TripList. Submits any SQL statement between the specified in the

SQL SELECT statement to retrieve all tripName(s) from the trips table.

cfquery start and end tags to the data source

datasource attribute.

Exercise 3: Displaying the query result using the cfoutput tag

In Chapter 2, “CFML Basics,” on page 15, you learned that the ColdFusion cfoutput tag is an easy mechanism to display literal text and the contents of variables. Additionally, the tag significantly simplifies displaying the results of queries. When used to display the results from a query, the the name of the query in the

cfoutput tag automatically loops through the record set for you. You simply specify

query attribute of the cfoutput tag:

All the code between the cfoutput start and end tags is the output code block. The output code block executes repeatedly, once for each row in the record set. However, if the query returns no rows, ColdFusion skips the code contained in the output code block.

...output code block... </cfoutput>

cfoutput

Displaying the column contents from the SQL statement

In CFML you surround variables with number signs (#) to display their contents using the

cfoutput tag. You also use this approach with column names specified in the SELECT statement

of a

cfquery. For example, when you want to display the trip names from the SQL query, you use

#tripName# within the output code block:

#tripname# </cfoutput>

For additional information about using SQL with cfquery and cfoutput, see ColdFusion MX Developer’s Guide.

To display the query results:

Modify the triplisting.cfm file by adding the highlighted code so that it appears as follows:

SELECT trips.tripName FROM trips

</cfquery>

<html>

<head>

<title>Trip Listing</title>

</head>

62 Chapter 6: Lesson 3: Retrieving Data

<body>

<h1>Trip List</h1> <cfoutput query="TripList">#tripName#<br></cfoutput>

</body>

</html>

Save the file as triplisting.cfm in the my_app directory.

View the triplisting.cfm page in a browser. The page lists all the trip names retrieved from the

Compass Travel database.

Reviewing the code

The following table describes the code used to display the query result set:

Code Explanation

#tripName#<br</cfoutput>

Output code block. Displays the value of the tripName column for each row in the result set from the "TripResult" query.

Exercise 4: Writing structured, reusable code

Generally, it is good coding practice to separate business logic from the display. The ColdFusion page that you just created contains both business logic (the database query) and presentation (the output code block). To separate them, you put the query in a ColdFusion component (CFC). Doing so separates business logic from presentation; it also makes it easy to reuse the query anywhere in your application. For more information, see Chapter 10, “Building and Using ColdFusion Components” in ColdFusion MX Developer’s Guide.

To move the query to a CFC:

Create the CFC file.

Copy the query to the CFC.

Call the method that contains the query.

Creating the CFC file

ColdFusion components (CFCs) are special files saved with the filename extension .cfc. They can contain data and functions. Within CFCs, functions are referred to as methods. Actions that you want ColdFusion to perform, such as querying a database, are contained in component methods. One CFC can contain many methods. Each method in a CFC can return only one variable. The following is the general syntax of a CFC:

</cffunction> <cffunction name="secondMethod">

</cffunction>

</cfcomponent>

Exercise 4: Writing structured, reusable code 63

To create the CFC file:

Create a directory named components as a subdirectory of the my_app directory.

Open a new blank file.

Note: If you are using Dreamweaver, select Dynamic page in the Category list, and select ColdFusion Component in the Dynamic Page list.

Enter the following code, or do the steps listed in the “Let Dreamweaver do it” section.

<cffunction name="basicList" displayName="List all trips"

hint="List trips in same order as in table" access="public"

returnType="query" output="false">

</cffunction> </cfcomponent>

Save the file as gettrips.cfc in the components directory.

Let Dreamweaver do it

Dreamweaver lets you create a ColdFusion component without having to enter the code.

To create a CFC in Dreamweaver:

Click the Components tab.

Click the + button.

In the Display name text box, enter Get Trips.

In the Hint text box, enter Get trip information.

In the Name text box, enter gettrips.

Click the Browse button, and make the components directory the current directory.

In the Section list, click Functions.

Click the + button.

In the Name text box, enter basicList.

10.

In the Display name text box, enter List all trips.

11.

In the Hint text box, enter List trips in same order as in the table.

12.

Select query as the Return type.

13.

Click OK.

14.

Save the file as gettrips.cfc in the components directory.

64 Chapter 6: Lesson 3: Retrieving Data

Copying the query to the CFC

To copy the query to the CFC, you copy the CFML to the CFC, between the opening and closing

cffunction tags.

To copy the query to the CFC:

Highlight the following code on the triplisting.cfm page:

SELECT trips.tripName FROM trips </cfquery>

Cut the highlighted code and copy it to the gettrips.cfc page so that it appears as follows:

<cffunction name="basicList"

displayName="List all trips" hint="List trips in same order as in table" access="public" returnType="query" output="false"> <cfquery name="TripList" datasource="CompassTravel">

SELECT trips.tripName FROM trips </cfquery> <cfreturn>

</cffunction>

</cfcomponent>

Modify the code by adding the f ollowing t ext so that the method returns the results of the query to the triplisting.cfm page:

Save the gettrips.cfc file.

Calling the query method

To perform the query that is now in a method in a ColdFusion component, you have to call (invoke) the method. To do so, you can use the

cfinvoke tag. Within the cfinvoke tag, you

specify the name of the ColdFusion component, the method to call, and the query to return to the calling page. The name of the component includes the package, "cfdocs.getting_started.my_app.components." The package looks very similar to the path, except that it contains periods instead of slashes. Like a path, it specifies the location of the component.

To invoke the method:

Go to the top of the triplisting.cfm file.

Enter the following code, or do the steps listed in the “Let Dreamweaver do it” section.

Save the file.

View the triplisting.cfm page in a browser and notice that the page lists the trip names, just as it did previously.

Exercise 4: Writing structured, reusable code 65

Let Dreamweaver do it

Dreamweaver lets you invoke a method in a CFC without having to write the code.

To invoke the method using Dreamweaver:

Click the Components tab in the Application panel.

Click the + button next to cfdocs.getting_started.my_app.components.

Click the + button next to gettrips.

Select query basicList() and drag it to the top of the triplisting.cfm file.

Change the value of the returnvariable to be the name of the query, TripList, as follows:

returnvariable="TripList"

Return to Step 3 in the “To invoke the method:” procedure.

Exercise 5: Creating additional queries

In this exercise, you will improve the Trip List page to make it easier for the Compass Travel agents to locate trips. You must make the following improvements:

• Sort the trip names in alphabetical order.

• Display the departure date, return date, and price for each trip.

• Develop a Budget Trip List report that identifies trips that are priced $1500 or less.

You could modify the existing query; however, you may need to use that query in the future. Instead, you can create an additional method in the gettrips.cfc component with a query that meets the preceding requirements. You can then call the new methods with the enhanced query from the triplisting.cfm page. Display the triplisting.cfm page in the browser after each step to ensure that the corresponding requirement was met.

To modify the application, you must:

• Create the query that meets the requirements.

• Invoke the method that contains the query.

• Display the results.

Creating the query

To continue writing structured, reusable code, you create the query in a method in the existing CFC.

To create a method with enhanced query results:

Open the gettrips.cfc file.

Enter the following code just before the closing cfcomponent tag, or do the steps in the “Let

Dreamweaver do it” section.

<cffunction name="getBudgetTrips" displayName="Budget trip list"

hint="List trips under $1500 alphabetically" access="public" returnType="query"

66 Chapter 6: Lesson 3: Retrieving Data

output="false"> <cfquery name="budgetTrips" datasource="CompassTravel">

SELECT tripName, departureDate, returnDate, price FROM trips WHERE price <= 1500 ORDER BY tripName

</cfquery> <cfreturn budgetTrips> </cffunction>

Save the file.

Let Dreamweaver do it

You can use the Dreamweaver Extensions to create a CFC query and invoke the method that contains the query from the triplisting.cfm page.

To create the enhanced CFC query in Dreamweaver:

Open the gettrips.cfc file and position the pointer before the closing cfcomponent tag.

Click the Bindings tab.

Click the + button.

Select Recordset (Query).

In the Name text box, enter budgetTrips.

Click the New Function button.

In the New function name text box, enter getBudgetTrips.

Click OK.

In the Data Source list, select CompassTravel.

10.

In the Table list, select trips.

11.

Click the Selected button.

12.

Select tripName, departureDate, returnDate, and price from the list of columns.

13.

In the Filter section, select price, select < from the list of operators, select Entered value, and enter 1500 in the text box.

14.

In the Sort section, select tripName, and select Ascending.

15.

Click OK.

16.

Save the file.

Exercise 5: Creating additional queries 67

Invoking the new method

To use the new query, you invoke the method that contains the query.

To invoke the new method with the enhanced query:

Open the triplisting.cfm file.

Modify the following code, or do the steps listed in the “Let Dreamweaver do it” section.

Save the file.

Let Dreamweaver do it

You can use the Dreamweaver Extensions to create the CFC query and invoke the method that contains the query from the triplisting.cfm page.

To invoke the enhanced CFC query in Dreamweaver:

Delete the cfinvoke code block.

Click the Components tab.

Click the + button next to cfdocs.getting_started.my_app.components, and click the + button next to gettrips.

Select query getBudgetTrips and drag it to the top of the file.

Change the value of the returnvariable to be the name of the query, budgetTrips, as follows:

returnvariable="budgetTrips"

Save the file.

Displaying the results

To display the results of the new query, you refer to the new query in the include all the columns that you want to display.

To display the results of the enhanced query

Change the output block in the triplisting.cfm file to output all three selected fields from the enhanced query, as follows:

#tripName# departs: #departureDate# returns: #returnDate# price: #price#<BR>

</cfoutput>

Change the heading tag in the triplisting.cfm file from <h1>Trip List</h1> to <h1>Budget

Trip List</h1>.

68 Chapter 6: Lesson 3: Retrieving Data

cfoutput block and

Save both the triplisting.cfm file and the gettrips.cfc file.

View the triplisting.cfm page in a browser and verify that all the new requirements were met. The revised TripListing.cfm page looks like this:

The dates and prices in the preceding listing are not formatted. In “Lesson 6: Creating a Main

Application Page” on page 97, you will enhance the look of this page.

Summary

This lesson described how to access a relational database using ColdFusion. You used the SQL SELECT statement and the use ColdFusion components to write structured, reusable code.

cfquery and cfoutput tags to display trip lists. You also learned to

In the next lesson

In the next lesson, you will build the search form to let users select which information to retrieve. To build the query based on what the user enters in the search form, you will use dynamic SQL. You will then display the results of the search on an action page.

Summary 69

70 Chapter 6: Lesson 3: Retrieving Data

CHAPTER 7

Lesson 4: Building Dynamic Queries

In this lesson, you develop a search form to allow users to select the information to retrieve, and then display, the search results.

This lesson contains the following exercises:

Exercise 1: Creating the trip search form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71

Exercise 2: Building a query that uses dynamic SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75

Exercise 3: Constructing the initial Trip Search Results page . . . . . . . . . . . . . . . . . . . . . . . . . . 79

Exercise 4: Testing the Trip Search Results page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81

Exercise 5: Enabling the departure and price criteria on the Trip Search form . . . . . . . . . . . . . 82

Exercise 6: Testing the revised Trip Search Results page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83

Exercise 1: Creating the trip search form

As you recall from “Lesson 1: Preparing to Build the Sample Application” on page 41, two of the requirements for the Trip Maintenance application are the ability to generate trip listings and a trip query facility. You will create a search interface that meets both of these requirements in this exercise.

About the trip search form

The dynamic listings that you developed in “Lesson 3: Retrieving Data” meet many of Compass Travel’s requirements for locating trips. However, what if the number of trips were in the thousands or tens of thousands? Locating the right trip for a customer might be difficult and certainly time consuming. Moreover, it is very hard, if not impossible, to anticipate all the ways that a user might want to search for trips.

A better solution is to provide an interface for the user to specify the search criteria. The purpose of the Trip search form is to enable Compass Travel employees to search and view brief details about existing trips on their website. The completed form should appear as follows:

Designing the search criteria page

When designing the search criteria page, it is a good idea to develop a list of possible queries that the user might issue when searching for the records. Most Compass Travel customers are primarily concerned with trip locations, departure dates, and price; the following is a list of the types of queries that the agents are likely to require at Compass Travel:

• List the trips located in Hawaii.

• Identify the trips with a price greater than $3,000.

• Show the trips departing after 11/11/2005 that are priced less than $2,000.

There are a number of considerations to take into account when you design a search page to capture the user’s search criteria. The following are two of the most important considerations:

• For which database columns will the user be allowed to specify a search condition?

• Should the user be allowed to identify which database columns to include in the record set?

In this exercise, the Compass Travel trip coordinator searches the trips based on tripLocation, departureDate, and price. Because these columns are the only ones that users can query, they are the only ones contained in the WHERE clause of the generated SQL statement. In addition, the coordinator has no control over which columns are returned in the record set. The query always returns the same columns to identify a trip:

• tripName

• tripLocation

• departureDate

• returnDate

• price

• tripID

72 Chapter 7: Lesson 4: Building Dynamic Queries

In later exercises, you will reference these columns when you build the SQL SELECT statement for the

cfquery in the search action page.

Understanding search query operators

Now that you have decided which columns can be queried (tripLocation, departureDate, and price), you can build a simple form that lets the user enter values for each of these fields. If the user enters a value (for example, Boston) for the tripLocation field and leaves the other two fields blank, the search results page constructs the following SQL statement:

SELECT tripName, tripLocation, departureDate,

returnDate, price, tripID FROM trips WHERE tripLocation = 'Boston'

When you design the Search Criteria page, you must decide which operators to support for each of the columns you can query. The operators that you use depend on the data type of the SQL column.

For example, suppose the user wants a list of all the trips where the trip location begins with a "B." SQL is well-suited for this type of query. Typical SQL string operators are equals, starts with, contains, and ends with.

However, price is a numeric data type. The user can specify any of the following:

• Price is 5000

• Price less than 600

• Price greater than 1500

Although many more operators are permissible, for simplification, you can use the following operators for the Compass Travel columns that you can query:

Column to query Query operators

tripLocation is, begins with

departureDate is, before, after

price is, greater than, less than

Creating the Trip Search page

A simple design for a search criteria page presents an operator list and data entry field for each of the columns that you can query. To create the search criteria page, you create an HTML form.

To create the search page:

Create a blank file.

Note: If you are using Dreamweaver, select Dynamic page in the Category list and ColdFusion in

the Dynamic Page list.

Enter, or copy and paste, the following code in the blank file:

<html>

<head>

Exercise 1: Creating the trip search form 73

<title>Trip Maintenance - Search Form</title>

</head>

<body>

<table>

<tr>

<td>Trip Location </td> <td>

<option value="EQUALS">is <option value="BEGINS_WITH">begins with

</select> </td> <td>

</tr>  <tr>

<td>Departure Date </td> <td>

<option value="EQUALS">is <option value="BEFORE">before <option value="AFTER">after

</select> </td> <td>

</td>

</tr>  <tr>

<td>Price </td> <td>

<option value="EQUALS">is <option value="GREATER">greater than <option value="SMALLER">smaller than

</select> </td> <td>

</tr> </table> <p> <input type="submit" value="Search">

74 Chapter 7: Lesson 4: Building Dynamic Queries

</form> </body> </html>

Save the file as tripsearchform.cfm in the my_app directory.

Reviewing the code

The following table describes the search criteria code and its function:

Code Explanation

<form action="tripsearchresult.cfm"

method="post">

<select name="tripLocationOperator"> <option value="EQUALS">is <option value="BEGINS_WITH">begins with </select>

Identifies tripsearchresult.cfm as the search action page. Results of user entry are passed to the search action page.

Builds a drop-down list offering the query operators for tripLocation. There must one operator list box for each queryable column.

Captures a value to test. There is one text control for each queryable column.

Exercise 2: Building a query that uses dynamic SQL

Dynamic SQL is a term that refers to SQL code that your program generates using variables before the SQL is executed. You can use dynamic SQL to accomplish tasks such as adding WHERE clauses to a search based on the fields that the user filled out on a search criteria page.

Based on the columns that you can query in this tutorial, the SQL query to display the search results would look like this:

SELECT tripName, tripLocation, departureDate, returnDate, price, tripID FROM trips

The purpose of the Trip Search form is to supply the data needed to build the WHERE clause to finish this SQL SELECT statement and constrain the query according to the user’s input.

When the user enters the search criteria on the Trip Search form and clicks the Search button, the form fields are posted to the Trip Search Results page. The posted field values compose the WHERE clause in the SQL SELECT statement. The following example lists the WHERE clauses that you can generate depending on the criteria set on the search page:

WHERE tripLocation = 'Aruba' WHERE tripLocation Like 'C%' WHERE tripLocation = 'China'

AND departureDate > 1/1/2001 AND price < 1500

In this example, the SQL AND operator joins the search condition clauses. To simplify the trip search example, you will use the SQL AND operator to combine all the search condition clauses. A more sophisticated search criteria page might present the user a choice of using AND or OR to connect one search criterion with the others.

Exercise 2: Building a query that uses dynamic SQL 75

The action page invokes a method that builds the WHERE clause so that the SQL SELECT statement retrieves the information that the user requests. Then, the action page displays an HTML table with the results of the user query using the

cfoutput block.

Building the WHERE Clause with the cfif and cfset tags

The WHERE clause in a SQL SELECT statement is a string. You use the CFML

cfset and cfif

tags to conditionally build the WHERE clause depending on values passed to the search action page. The

cfset statement creates a variable or changes the value of an existing variable. For

example, to create a variable named color and initialize its value to red, you use the following statement:

The cfif tag instructs the program to branch to different parts of the code depending on whether a test evaluates to True or False. For example, to have some code execute if the color variable is equal to red, and other code execute if it is not, you use the following pseudocode:

<cfif color EQ "red"> ... statements for color red <cfelse> ... statements for other than red </cfif>

Building a SQL WHERE clause in code is largely an exercise in string concatenation. The & operator combines two strings in ColdFusion. For example, the following code snippet:

<cfset FirstName = "Wilson"> <cfset LastName = "Gato"> <cfset FullName = FirstName & " " & LastName> <cfoutput>My name is #FullName#.</cfoutput>

results in the following text:

My name is Wilson Gato.

For each search criterion on the Trip Search form, the code within the Trip Search Results page must do the following:

• Verify that the user entered data in the search criterion’s value field. To do so, you use the cfif

tag; for example,

<cfif Form.tripLocationValue GT "">.

• If data was entered, construct a WHERE subclause by concatenating the following:

■ The SQL keyword AND

■ The corresponding SQL column name (in the Trip Search example, tripLocation) for the

search criterion

■ The SQL operator equivalent of the search query operator

■ The test value entered by the user

The following code shows the creation of the WHERE subclause:

<cfset WhereClause = WhereClause & " AND tripLocation = '" &

form.tripLocationValue & "'" >

76 Chapter 7: Lesson 4: Building Dynamic Queries

<cfset WhereClause = WhereClause & " AND tripLocation like '" &

form.tripLocationValue & "%'" >

</cfif>

When you test for a string column within the WHERE clause of the SQL SELECT statement, you must enclose the test value in quotation marks.

When you use a variable to construct a WHERE clause, you must preserve the quotation marks so that the database server does not return an error. To preserve the quotation marks, you must use the ColdFusion

PreserveSingleQuotes function. The PreserveSingleQuotes function

prevents ColdFusion from automatically escaping single-quotation marks contained in the variable string passed to the function.

Note: The cfqueryparam tag also escapes single-quotation marks. For more information, see CFML Reference.

Creating the CFC query

The following code shows how to construct the tripLocation SQL WHERE subclause. Specifically, it uses a dynamic SQL SELECT statement built from parameters from the Trip Search page to display the search results. To continue the good coding practice of separating business logic and presentation, you put the code to build the query using dynamic SQL in a function in the CFC that you have been working with.

To add the new query to the CFC:

Open the file gettrips.cfc file and position the pointer before the closing cfcomponent tag.

Enter the following code, or do the steps in the “Let Dreamweaver do it” section:

SELECT tripID, tripName, tripLocation, departureDate,

returnDate, price FROM trips </cfquery> <cfreturn TripResult>

</cffunction>

Add the logic for creating the WHERE clause dynamically by entering the highlighted code.

<cfset WhereClause = " 0=0 ">  <cfif Form.tripLocationValue GT "">

</cfif>

SELECT tripID, tripName, tripLocation, departureDate,

returnDate, price FROM trips

Exercise 2: Building a query that uses dynamic SQL 77

</cfquery> <cfreturn TripResult>

</cffunction>

Add the highlighted code to the cfquery block to use the dynamically built WHERE clause in the query:

SELECT tripID, tripName, tripLocation, departureDate,

returnDate, price FROM trips

WHERE #PreserveSingleQuotes(WhereClause)#

</cfquery>

Save the file.

Let Dreamweaver do it

You can use the Dreamweaver Extensions to create the CFC query.

To construct the query with Dreamweaver:

Click the Bindings tab.

Click the + button.

Select Record set (Query).

In the Name text box, enter Tr i p R e s u l t .

Click the New Function button.

In the New function name text box, enter getTripsFromForm and click OK.

From the Data source list, select CompassTravel.

From the Tables list, select trips.

Click the Selected radio button.

10.

Select tripName, tripLocation, departureDate, returnDate, price, and tripID.

11.

Click OK.

12.

Return to Step 3 in the “To add the new query to the CFC:” section.

78 Chapter 7: Lesson 4: Building Dynamic Queries

Exercise 3: Constructing the initial Trip Search Results page

After the user enters the search criteria and submits the form, the results are posted to the Trip Search Results page, as the following figure shows:

The logic contained in the search results page, also known as the action page, invokes the CFC method that builds the SQL SELECT statement contained in a ColdFusion string manipulation. The action page displays the result using the

To create the Trip Search Results page:

Create a blank document and save it as tripsearchresult.cfm in the my_app directory.

Note: If you are using Dreamweaver, select Dynamic page in the Category list, and ColdFusion in the Dynamic Page list.

Enter the following code, or do the steps in the “Let Dreamweaver do it” section.

<cfinvoke

component="cfdocs.getting_started.my_app.components.gettrips" method="getTripsFromForm" returnvariable="TripResult">

</cfinvoke>

Enter or copy and paste the following code after the closing cfinvoke tag:

<html> <head> <title>Trip Maintenance - Search Results</title> </head> <body>

<td>Trip Name

</td>

<td>Location

</td>

<td>Departure Date

</td>

<td>Return Date

</td>

<td>Price

cfquery tag by using

cfoutput tag.

Exercise 3: Constructing the initial Trip Search Results page 79

</td>

</tr> <cfoutput query="TripResult">

<tr>

<td>#tripName# </td> <td>#tripLocation# </td> <td>#departureDate# </td> <td>#returnDate# </td> <td>#price# </td>

</tr>

</cfoutput> </table> </body> </html>

Save the file.

Let Dreamweaver do it

As you have in previous exercises, you can let Dreamweaver generate the code to invoke the method.

To invoke the method using Dreamweaver:

Click the Components tab.

Click the + button next to cfdocs.getting_started.my_app.components.

Click the + button next to gettrips.

Select query getTripsFromForm() and drag it to the top of the triplisting.cfm file.

Change the value of returnvariable to be the name of the query, TripResult, as follows:

returnvariable="TripResult"

Return to Step 3 in the “To create the Trip Search Results page:” section.

Reviewing the code

The following table describes the code used to build the tripLocation WHERE subclause:

Code Explanation

80 Chapter 7: Lesson 4: Building Dynamic Queries

The cfset tag initializes the WhereClause variable to hold the WHERE clause to be constructed. The initial value is set to clause has at least one subclause in case the user enters no search criteria.

" 0=0 " so that the WHERE

Code Explanation

SELECT tripName, tripLocation, departureDate, returnDate, price, tripID FROM trips WHERE #PreserveSingleQuotes(WhereClause)#

The

cfif tag tests to see if the user entered anything

in the Value input field for tripLocation criterion.

SQL query to execute. The PreserveSingleQuotes function ensures that quotation marks are passed to the database server as intended.

The preceding code only builds the tripLocation subclause. In the following exercise, you will add code for the other two columns that you can query, departureDate and price.

Exercise 4: Testing the Trip Search Results page

In this exercise, you will test the tripsearchresults.cfm page. First, you will test the Trip Search Results page by entering criteria on the Trip Search form and inspecting the results. Next, you will finish the code to construct the complete WHERE clause for all three columns that you can query from the Trip Search form.

To test the Trip Search Results page:

View the tripsearchform.cfm page from the my_app directory in your browser and do the following:

In the Trip Location drop-down list, select the Begins With option, and enter the value C in the text box.

Click Search.

The Trip Results page displays several entries, as follows:

Notice in the Trip Results page that only one trip has a trip location of China.

Click the Back button in your browser to return to the Trip Search page.

In the Trip Location drop-down list of the Trip Search page, select the Is option, enter the value China, and then click Search.

The Trip Search Results page displays only one entry for the trip to China in the HTML table.

Exercise 4: Testing the Trip Search Results page 81

Verify that the other criteria (departure date and price) are not taken into consideration yet as follows:

Click the Back button in the browser to return to the Trip Search page.

In the Departure Date drop-down list box, select Before, enter 1/1/1900 as the date, and select Smaller Than 0 for the price.

Either of these conditions would produce a results page with no rows.

Click the Search button.

The Trip Search Results page should be identical to the one in Step 2, because the code to build the WHERE clause in the Trip Search Results page does not include departure date and price.

Exercise 5: Enabling the departure and price criteria on the Trip Search form

In this exercise, you will modify the Trip Search Results page to add the criteria needed for the departure and price query.

To enable the departure and price criteria:

Open the gettrips.cfc file.

In the getTripsFromForm method, position the pointer before the cfquery code block.

To build the departureDate WHERE subclause, enter or copy and paste the code in the following table immediately before the

cfquery code block:

For this database Enter this code

(Windows users) Microsoft Access database file

(UNIX users) PointBase database file

82 Chapter 7: Lesson 4: Building Dynamic Queries

<cfif Form.departureValue GT ""> <cfif Form.departureOperator EQ "EQUALS"> <cfset WhereClause = WhereClause & " and departureDate = " &

CreateODBCDate(Form.departureValue)> <cfelseif Form.departureOperator EQ "AFTER"> <cfset WhereClause = WhereClause & " and departureDate > " &

CreateODBCDate(Form.departureValue)> <cfelseif Form.departureOperator EQ "BEFORE"> <cfset WhereClause = WhereClause & " and departureDate < " &

CreateODBCDate(Form.departureValue)> </cfif> </cfif>

<cfif Form.departureValue GT ""> <cfif Form.departureOperator EQ "EQUALS"> <cfset WhereClause = WhereClause & " and departureDate = Date '" &

Form.departureValue & "'"> <cfelseif Form.departureOperator EQ "AFTER"> <cfset WhereClause = WhereClause & " and departureDate > Date '"

& Form.departureValue & "'"> <cfelseif Form.departureOperator EQ "BEFORE"> <cfset WhereClause = WhereClause & " and departureDate < Date '" &

Form.departureValue & "'"> </cfif> </cfif>

To build the price WHERE subclause, enter the following code after the code you entered in the previous step.

<cfif Form.priceValue GT "">

<cfset WhereClause = WhereClause & " and price > " & form.priceValue>

</cfif>

Save the file.

Exercise 6: Testing the revised Trip Search Results page

In this exercise, you will verify that the price and departureDate are now considered in the query.

To test the revised Trip Search Results page:

Open the tripsearchform.cfm page in the my_app directory in your browser.

In the Departure Date drop-down list box, select Before, enter 1/1/1900 as the date (specify 1900-1-1 on UNIX).

Select Smaller Than, and enter 0 for the price.

Click the Search button.

Because the departure date is considered in the query, there are no rows returned.

Note: If you planned to use many more fields as search criteria, the approach used to add departure date and price criteria to the Trip Search form is not the most elegant solution. A generic routine to handle WHERE clause string construction based on specific data types could reduce the code and be a more extensible solution than the one presented here. However, this more extensible approach is beyond the scope of this tutorial.

Summary

This lesson described how to build a search tool that dynamically builds a WHERE clause of the SQL SELECT statement using intact, you used the

PreserveSingleQuotes CFML function.

In the next lesson

In the next lesson, you will build the Trip Detail page, which will display detailed information about the currently selected trip.

cfif and cfset tags. To ensure that the SQL statement remains

Summary 83

84 Chapter 7: Lesson 4: Building Dynamic Queries

CHAPTER 8

Lesson 5: Creating a Trip Detail Page

In this lesson you will enhance the Compass Travel Trip Maintenance application. This lesson contains the following exercises:

Exercise 1: Creating a Trip Detail page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86

Exercise 2: Avoiding the potential security risk when using dynamic SQL . . . . . . . . . . . . . . . . 91

Exercise 3: Linking the Trip Search Results page with the Trip Detail page . . . . . . . . . . . . . . . 92

Exercise 4: Enhancing the look of the Trip Search Results and Trip Detail pages . . . . . . . . . . . 93

ColdFusion tags and functions introduced in this lesson

The following table describes the ColdFusion tags and functions that you use in this lesson to enhance the sample ColdFusion application:

Element Type Description

DollarFormat

DateFormat

IsDefined

URLEncodedFormat

Function Returns a number as a String formatted with two decimal

places, a thousands separator, and a dollar sign. If the specified number is negative, parentheses are used.

Function Returns a formatted date-time value. If no mask is specified,

the

DateFormat function returns the date value in the dd/

mmm/yy format. The

have the U.S. date format.

Function Evaluates a String value to determine whether it represents

an existing variable. Returns True if the variable is found, False if not found.

Function Converts a text string into a String that you can safely use in a

URL.

DateFormat function supports dates that

Exercise 1: Creating a Trip Detail page

By design, the Trip Search Results page displays a subset of the information about a trip. To get additional information about any of the trips, the user should be able to click any row to display the detailed trip data.

In this exercise, you build a Trip Detail page to provide all the information about a particular trip that is stored in the Compass Travel trips database. The following figure shows an example of the Trip Detail page that you build:

After you complete the Trip Maintenance application in this tutorial, you will use this Trip Detail page in several ways:

• You can call the Trip Detail page directly by typing in the address of the page with an ID. For

example, to view trip information for Rio Cahabon Rafting with tripID 24, you open a browser and enter the following URL:

http://localhost/cfdocs/getting_started/my_app/tripdetail.cfm?ID=24

• You can navigate to the Trip Detail page by creating a hyperlink from the trip name on the Trip

Search Results page. This will offer the user drill-down capability when searching for trips. You will link the Trip Search Results page and the Trip Detail page in one of the exercises in this lesson.

• You can use browse buttons on the Trip Detail page to navigate the Trips table row-by-row.

You will implement this navigational feature in “Lesson 6: Creating a Main Application Page”

on page 97.

Building the Trip Detail page

To build the Trip Detail page, you do the following:

• Create a CFC that queries the database.

• Create the Trip Detail page, which invokes the CFC query and displays the results of the query.

86 Chapter 8: Lesson 5: Creating a Trip Detail Page

To build the CFC query:

Create a blank ColdFusion page.

Save the file as tripdetail.cfm in the my_app directory.

Perform the following steps, or do the steps in the “Let Dreamweaver do it” section.

Enter the following code:

Save the file.

Create a file named displaytripdetail.cfc in the my_app/components directory.

Enter the following code:

<cfcomponent> <cffunction name="getTripDetails" access="public" returntype="query"> <cfset var tripDetails = "" > <cfquery name="tripDetails" datasource="CompassTravel" maxrows=1> SELECT * FROM trips

<cfif IsDefined("URL.ID")> WHERE tripID = #URL.ID#

</cfif> </cfquery> <cfreturn tripDetails> </cffunction> </cfcomponent>

Save the file.

Let Dreamweaver do it

You can use the Dreamweaver Extensions to build the CFC query.

To create the CFC query in Dreamweaver:

Open a new file in the my_app directory in Dreamweaver.

Save the file as tripdetail.cfm.

Click the Bindings tab.

Click the + button.

Select Recordset (Query).

In the Name text box, enter tripDetails.

In the Data sources list, select CompassTravel.

Click the CFC Query button.

In the Name text box, enter tripDetails.

10.

Click the Create New Component button.

11.

In the Name text box, enter displaytripdetail.

Exercise 1: Creating a Trip Detail page 87

12.

In the Component Location text box, enter /components/.

13.

In the Recordset name text box, enter tripDetails.

14.

In the Function text box, enter getTripDetails.

15.

In the Data source list, select CompassTravel.

16.

In the Table list, select trips.

17.

Click OK.

18.

When asked whether to include dependent files, click Yes.

19.

Click OK.

20.

In the displaytripdetail.cfc file, modify the cfquery code block by adding the highlighted text:

<cfquery name="tripDetails" datasource="CompassTravel" maxrows=1> SELECT * FROM trips

<cfif IsDefined("URL.ID")> WHERE tripID = #URL.ID# </cfif>

</cfquery>

21.

Save the file.

To display the results of the query on the Trip Detail page:

Open the tripdetail.cfm file.

Enter or copy and paste the following cfoutput code after the cfinvoke code block:

<table>

<tr>

<td valign="top">Trip Name: </td> <td>#tripName#

</td> </tr> <tr>

<td valign="top">Description:

</td>

<td>#tripDescription#

</td> </tr> <tr>

<td valign="top">Location:

</td>

<td>#tripLocation#

</td> </tr> <tr>

<td valign="top">Departure Date:

</td>

<td>#departureDate#

</td> </tr>

88 Chapter 8: Lesson 5: Creating a Trip Detail Page

<tr>

<td valign="top">Return Date:

</td>

<td>#returnDate#

</td> </tr> <tr>

<td valign="top">Price:

</td>

<td>#price#

</td>

</tr> <tr>

<td valign="top">Base Cost:

</td>

<td>#baseCost#

</td> </tr> <tr>

<td valign="top">Trip Leader:

</td>

<td>#tripLeader#

</td> </tr> <tr>

<td valign="top">Number People:

</td>

<td>#numberPeople#

</td> </tr> <tr>

<td valign="top">Deposit Required:

</td>

<td>#depositRequired#

</td> </tr> <tr>

<td valign="top">Photo File:

</td>

<td>#photo#

</td> </tr>

</table> </cfoutput>

To provide a title that appears in the browser window, insert the following HTML code before the

<cfoutput query = "TripQuery"> line:

<title> Trip Maintenance - View Record </title>

</head> <body>

Exercise 1: Creating a Trip Detail page 89

Add the following code at the end of the file:

</body> </html>

Save the file.

To view the Trip Detail page:

Open a browser.

Enter one of the following URLs, which use the tripID of 24, which specifies the Rio Cahabon Rafting trip:

http://localhost/cfdocs/getting_started/my_app/tripdetail.cfm?ID=24

Note: If you are using the built-in ColdFusion server, enter

localhost:8500 instead of localhost.

The following figure shows the expected result:

90 Chapter 8: Lesson 5: Creating a Trip Detail Page

Reviewing the code

The following table describes the ColdFusion code that you use to build the Trip Detail page:

Code Explanation

<cfcomponent> <cffunction name="getTripDetails"

access="public"

returntype="query"> ... <cfreturn tripDetails> </cffunction> </cfcomponent>

<cfquery name="TripQuery"

dataSource="CompassTravel"

maxRows=1>

WHERE tripID = #URL.ID# </cfif>

The cfcomponent tag creates a CFC. The cffunction tag creates a method named method returns the results of the query to the calling page.

The cfquery tag includes a maxRows attribute. This attribute limits the number of result rows brought back from the database. In the Trip Detail page, you want to show only a single row at a time; therefore, the to

The URL.ID specifies a parameter that you can include in the URL that requests this page. If the ID parameter is passed within the URL, it is used in the SQL query to identify the

IsDefined function to determine if a parameter is passed

within the URL. You can also use the user has entered data in form fields prior to the form post action.

tripID to SELECT. You can use the CFML

getTripDetails in the CFC; the

maxRows attribute is set

IsDefined to determine if

As you did in this exercise, you can build comprehensive database query applications using CFML and dynamic SQL. To further test the new Trip Detail page that you created, you will link it to the search facility that you built in Lesson 4: Building Dynamic Queries. However, before you link that search facility, you must understand a potential security risk using dynamic SQL. The following exercise describes this risk and how to avoid it.

Exercise 2: Avoiding the potential security risk when using dynamic SQL

To reduce round trips between the client and the database server, many SQL database servers permit the client to submit multiple SQL statements in a single request, separated by a semicolon (;). For these database managements systems, the following SQL request is valid:

DELETE from trips where tripLocation = 'China'; SELECT tripName from trips

This request might be an efficient way to list the trips that remain after the database management system removes the China trip. Problems arise when the SQL statement is built dynamically.

In the Trip Maintenance application, when the client program or user passes an ID in the URL that calls the Trip Detail page, the page displays the relevant trip information. The following code builds the correct WHERE clause that supports this behavior:

WHERE tripID = #URL.ID#

</cfif>

Exercise 2: Avoiding the potential security risk when using dynamic SQL 91

If a user called the Trip Detail page using the following statement:

http://localhost/cfdocs/getting_started/my_app/tripdetail.cfm?ID=24;DROP+trips

the SQL database management system executes the proper SQL SELECT statement, and then immediately erases the Trips table from the database.

Protecting your application

To ensure that your application is protected from such an attack, you can exploit the fact that the ID must be a numeric value. The CFML of a string expression. You can use the

WHERE tripID = #Val(URL.ID)#

</cfif>

Val function returns the numeric value at the beginning

Val function as follows:

If nonnumeric data is passed within the URL ID field, the Val function returns 0, and the trip

with ID 0 appears (if one exists). If the user enters the previously cited URL

(http://localhost/cfdocs/getting_started/my_app/tripdetail.cfm?ID=24;DROP+trips), the application ignores the non-numeric values and displays the trip information of trip ID 24.

Warning: The exercises in this tutorial ignore the dynamic SQL risk from attack. To eliminate this risk, you should use ColdFusion functions (such as For queries, you can also use the

cfqueryparam tag, which is explained in CFML Reference.

Val) to perform type checking on all URL parameters.

Exercise 3: Linking the Trip Search Results page with the Trip Detail page

In this exercise, you will modify the Trip Search Results page to let the user view the details of any trip. To do this, you will convert each trip name entry in the results page to a link, which will display the trip’s detailed information in the detail page.

Use the following steps to link the Trip Search Results page (tripsearchresult.cfm) to the Trip Detail page (tripdetail.cfm).

To create links between the Trip Search Results page and the Trip Detail page:

Open the tripsearchresult.cfm file from the my_app directory.

Replace #tripName# in the cfoutput block with the following code:

<a href="tripdetail.cfm?ID=#URLEncodedFormat(tripID)#">#tripName# </a>

Note: The are replaced with %20, and nonalphanumeric characters with equivalent hexadecimal escape sequences. The function lets you pass arbitrary strings within a URL, because ColdFusion automatically decodes URL parameters that are passed to the page.

Save the file.

92 Chapter 8: Lesson 5: Creating a Trip Detail Page

URLEncodedFormat is a ColdFusion function that returns a URL-encoded string. Spaces

To test the revised page:

View the tripsearchform.cfm page from the my_app directory in your browser.

In the Trip Location drop-down list, select Begins With, and enter the value C.

Click Search.

The Trip Search Results page displays a hyperlink for each trip name, as the following figure shows:

To view the Trip Detail page for a trip, click the trip name.

You might notice that the dates and prices in both the Trip Detail and Trip Search Results pages are unformatted. You will improve the appearance of the application in “Exercise 4: Enhancing

the look of the Trip Search Results and Trip Detail pages” on page 93.

Exercise 4: Enhancing the look of the Trip Search Results and Trip Detail pages

The Trip Maintenance application now provides a useful drill-down mechanism for locating trips. Although this application is functionally sound, its appearance could be improved, including formatting dates and dollar amounts, and making long lists easier to read.

Formatting dates and dollar amounts

ColdFusion provides several functions to improve application appearance. These include the

DateFormat and DollarFormat functions that format dates and currency variables.

To format the dates and dollar amounts:

To format the Trip Detail page dollar and date fields, open the tripdetail.cfm file in the my_app directory in your editor and make the following changes:

Existing code Replacement code

#departureDate# #DateFormat(departureDate, "mm/dd/yyyy")#

#returnDate# #DateFormat(returnDate, "mm/dd/yyyy")#

#price# #DollarFormat(price)#

#baseCost# #DollarFormat(baseCost)#

Exercise 4: Enhancing the look of the Trip Search Results and Trip Detail pages 93

Save the file.

To format the currency and date fields on the Trips Search Results page, open the tripsearchresult.cfm file in your editor and make the changes for and

price indicated in the table.

Save the file.

To view the application’s new appearance:

View the tripsearchform.cfm page in the my_app directory in your browser.

In the Trip Location drop-down list, select Begins With and enter the value C.

Click Search.

departureDate, returnDate,

The Trip Search Results page appears:

In the Trip Search Results page, click the link for Riding the Rockies.

The properly formatted Trip Detail page appears:

94 Chapter 8: Lesson 5: Creating a Trip Detail Page

Summary

In this lesson, you transformed the search facility that you built in “Lesson 4: Building Dynamic

Queries” into a drill-down facility for trip queries. You built a Trip Detail page to show more

information about a particular trip. You also formatted the Trip Search Results and Trip Detail pages using the CFML Results page with the Trip Detail page.

In the next lesson

In the next lesson, you will add navigation and maintenance buttons on the main Trip Maintenance application page.

DateFormat and DollarFormat functions. You linked the Trip Search

Summary 95

96 Chapter 8: Lesson 5: Creating a Trip Detail Page

CHAPTER 9

Lesson 6: Creating a Main Application Page

In this lesson you will enhance the Compass Travel Trip Maintenance application. So far, you created a very useful drill-down query facility. Compass Travel trip coordinators can produce lists required by management and easily locate and display information about any trip. There are several requirements that were identified in “Lesson 1: Preparing to Build the

Sample Application” on page 41 that you have not yet addressed:

• The ability to browse through the Trips table.

• The ability to add, delete, and edit trip information.

You will modify the Trip Detail page to accomplish this additional functionality. The Trip Detail page shows information about a single trip. You will convert the Trip Detail page into the main application page by adding the following functionality:

• Navigation buttons to browse the database

• Database maintenance buttons to edit, delete, and add new trips, as well as a button to search

This lesson contains the following exercises:

Exercise 1: Creating the main application page from the Trip Detail page . . . . . . . . . . . . . . . . 98

Exercise 2: Adding database maintenance buttons . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100

Exercise 1: Creating the main application page from the Trip Detail page

In this exercise, you convert the Trip Detail page into the main Trip Maintenance application page. The main application page includes additional buttons for navigating to other ColdFusion pages and browsing the trip database records.

Maintenance buttons

Trip detail information that you build initially

Navigation buttons

Adding navigation buttons to browse the database

The drill-down search function developed in “Lesson 5: Creating a Trip Detail Page” on page 85 is very useful when the user knows some search criteria to enter. Flipping back and forth between the results page and the detail page to navigate through a record set can be tedious. Moreover, on occasion the trip coordinator might want to browse the Trips database just to check for anomalies or to become familiar with its contents. In these cases, the trip coordinator does not know the criteria to search for in advance.

The following figure shows the navigation buttons. The label below each button does not appear in the application; it describes which row to display relative to the currently displayed row.

98 Chapter 9: Lesson 6: Creating a Main Application Page

To add navigation buttons to the Trip Detail page:

Open the tripdetail.cfm file in the my_app directory.

Insert the following code between the </table> and </cfoutput> tags:

</form>

Note: The current trip record ID ( action page with current record ID that it must have in order to build the query to access the appropriate record in the Trips database table.

Save the file.

To test the updated application:

View the updated tripdetail.cfm page in a browser.

tripID) is in a hidden field in the form code. This field provides the

The Trip Search Results page appears:

Test the buttons by clicking any navigation button.

An error occurs because the navigation action page (navigationaction.cfm) does not exist. The navigation action page processes the navigation button requests. You will build the navigation action page in “Lesson 7: Validating Data to Enforce Business Rules” on page 103.

Exercise 1: Creating the main application page from the Trip Detail page 99

Reviewing the code

The following table describes the navigation code for the Trip Detail page:

Code Explanation

<INPUT type="hidden" name="RecordID"

value="#tripID#">

<input type="image" name="btnFirst" src="images/

first.gif"> <input type="image" name="btnPrev" src="images/

prev.gif"> <input type="image" name="btnNext" src="images/

next.gif"> <input type="image" name="btnLast" src="images/

last.gif">

Form tag that identifies the navigationaction.cfm file to handle record navigation.

Hidden RecordID field with the value of the current

Navigation buttons that are image type HTML input tags.

tripID.

Exercise 2: Adding database maintenance buttons

The search and sequential navigation capabilities are features for locating Compass Travel trips. After locating a trip, the trip coordinator must be able to modify or delete it. Additionally, when viewing the detail for a trip, the trip coordinator must be allowed to add a new trip or use the search facility. To enable trip coordinators to do this, you add the following buttons to the Trip Detail page:

As described in “Exercise 1: Creating the main application page from the Trip Detail page”

on page 98, it is important to pass the current record ID (

tripID) to the action page to build the

proper SQL statement to process the navigation button requests. It is also important to pass the current record ID to the Maintenance Action page. Therefore, you use an HTML hide the current

To add maintenance buttons:

Open the tripdetail.cfm file from the my_app subdirectory.

Enter the following code immediately after the <cfoutput query="TripQuery"> tag:

</form>

Note: The current trip record ID ( action page with current record ID that it must have in order to build the query to access the appropriate record in the Trips database table.

Save the file.

100 Chapter 9: Lesson 6: Creating a Main Application Page

recordID and post it to the maintenanceaction.cfm page.

tripID) is in a hidden field in the form code. This field provides the

input tag to

Macromedia COLDFUSION MX7 User Manual

Specifications and Main Features

Frequently Asked Questions

User Manual

CONTENTS

INTRODUCTION

About Macromedia ColdFusion MX 7 documentation

Documentation set

Viewing online documentation

Welcome to ColdFusion

Introducing ColdFusion MX

Contents

About ColdFusion MX

The ColdFusion application server

The ColdFusion Markup Language

The ColdFusion MX Administrator

How ColdFusion processes pages

Building applications with CFML

Interacting with data sources

Interacting with mobile devices

Development tools

CFML Basics

Contents

Working with ColdFusion pages

Creating a ColdFusion page

Saving your ColdFusion page

Browsing your code

About CFML elements

Tags

Functions

Variables

Naming variables

Ways to use variables

Creating variables with the cfset tag

Displaying variable output

Working with CFML expressions

Building expressions

Specifying a consistent character case

Specifying number signs to denote functions or variables

Specifying quotation marks around values

Specifying operators in expressions

About conditional processing

Using the cfif tag to evaluate True or False conditions

Using the cfelseif tag to evaluate multiple expressions

Processing form data

Commenting your code

Database Fundamentals

Contents

About database basics

What is a relational database?

About relational tables

About SQL

Understanding basic SQL SELECT statements

Using the SQL WHERE clause to limit the rows returned

Sorting the results

Using SQL with ColdFusion

About data sources

Writing SQL and CFML statements to interact with a data source

CFML tags that interact with a database

Building a ColdFusion Application

Application development steps

Exercise 1: Determining the application functional requirements

Exercise 2: Determining the data requirements

Exercise 3: Designing the database for your application

Exercise 4: Developing the sample application

How to proceed

Working directories

Requirements

Exercise 1: Verifying the tutorial file structure

Exercise 2: Setting up your development environment

About Macromedia Dreamweaver MX

Features for ColdFusion developers

Configuring Dreamweaver MX for ColdFusion development

Exercise 3: Configuring a database connection

Using Dreamweaver to configure a data source

Configuring a data source in the Administrator

Configuring the connection to the sample database file

Exercise 4: Configuring debugging options

Viewing debugging information within Dreamweaver

Enabling debugging in the ColdFusion MX Administrator