Macromedia Getting Started Building ColdFusion MX Applications User Manual

Getting Started Building

ColdFusion MX Applications

Trademarks

Afterburner, AppletAce, Attain, Attain Enterprise Learning System, Attain Essentials, Attain Objects for Dreamweaver, Authorware, Authorware Attain, Authorware Interactive Studio, Authorware Star, Authorware Synergy, Backstage, Backstage Designer, Backstage Desktop Studio, Backstage Enterprise Studio, Backstage Internet Studio, ColdFusion, Design in Motion, Director, Director Multimedia Studio, Doc Around the Clock, Dreamweaver, Dreamweaver Attain, Drumbeat, Drumbeat 2000, Extreme 3D, Fireworks, Flash, Fontographer, FreeHand, FreeHand Graphics Studio, Generator, Generator Developer's Studio, Generator Dynamic Graphics Server, JRun, Knowledge Objects, Knowledge Stream, Knowledge Track, Lingo, Live Effects, Macromedia, Macromedia M Logo & Design, Macromedia Flash, Macromedia Xres, Macromind, Macromind Action, MAGIC, Mediamaker, Object Authoring, Power Applets, Priority Access, Roundtrip HTML, Scriptlets, SoundEdit, ShockRave, Shockmachine, Shockwave, Shockwave Remote, Shockwave Internet Studio, Showcase, Tools to Power Your Ideas, Universal Media, Virtuoso, Web Design 101, Whirlwind and Xtra are 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, servicemarks, or tradenames of Macromedia, Inc. or other entities and may be registered in certain jurisdictions including internationally.

This product includes code licensed from RSA Data Security.

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.

Apple Disclaimer

APPLE COMPUTER, INC. MAKES NO WARRANTIES, EITHER EXPRESS OR IMPLIED, REGARDING THE ENCLOSED COMPUTER SOFTWARE PACKAGE, ITS MERCHANTABILITY OR ITS FITNESS FOR ANY PARTICULAR PURPOSE. THE EXCLUSION OF IMPLIED WARRANTIES IS NOT PERMITTED BY SOME STATES. THE ABOVE EXCLUSION MAY NOT APPLY TO YOU. THIS WARRANTY PROVIDES YOU WITH SPECIFIC LEGAL RIGHTS. THERE MAY BE OTHER RIGHTS THAT YOU MAY HAVE WHICH VARY FROM STATE TO STATE.

Copyright © 1999–2003 Macromedia, Inc. All rights reserved. This manual may not be copied, photocopied, reproduced, translated, or converted to any electronic or machine-readable form in whole or in part without prior written approval of Macromedia, Inc. Part Number ZCF61M300

Acknowledgments

Project Management: Randy Nielsen

Writer: Randy Nielsen

Editor: Linda Adler, Noreen Maher

First Edition: May 2002 Second Edition: August 2003

Macromedia, Inc. 600 Townsend St. San Francisco, CA 94103

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

About Macromedia ColdFusion MX documentation . . . . . . . . . . . . . . . . . . . . . . . 7

Documentation set . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7

Viewing online documentation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8

PART I: Welcome to ColdFusion

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

What is ColdFusion MX? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12

The ColdFusion application server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12

The ColdFusion Markup Language . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12

The ColdFusion MX Administrator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13

Using ColdFusion MX with Macromedia Flash MX . . . . . . . . . . . . . . . . . . . . . . . 14

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

Working with ColdFusion pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16

Creating a ColdFusion page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16

Understanding CFML elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18

Tags . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18

Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19

Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21

Creating variables with the cfset tag . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21

Displaying variable output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23

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

Building expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24

Understanding conditional processing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26

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

Form processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28

Commenting your code. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29

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

Understanding database basics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32

What is a relational database? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32

Understanding relational tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33

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

Using SQL to interact with a database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34

Using SQL with ColdFusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34

About data sources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34

Writing SQL and CFML statements to interact with a data source. . . . . . . . . . 35

CFML tags that interact with a database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35

CHAPTER 4: Configuring Your Development Environment . . . . . . . . . . . . . . . . . 37

Verifying the tutorial file structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38

Configuring database connection and debugging options . . . . . . . . . . . . . . . . . . . 39

Configuring the connection to the sample database file . . . . . . . . . . . . . . . . . . 39

Enabling debugging options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42

Macromedia development environment tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44

The Dreamweaver MX environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45

Configuring Dreamweaver MX for ColdFusion development. . . . . . . . . . . . . . 46

Configuring HomeSite+ for ColdFusion development . . . . . . . . . . . . . . . . . . . 46

PART II: Building a ColdFusion Application

CHAPTER 5: Lesson 1: Preparing to Build the Sample Application . . . . . . . . . . . 49

Application development steps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49

Determining the application functional requirements . . . . . . . . . . . . . . . . . . . . . . 50

Determining the data requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51

Designing the database for your application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52

Developing the sample application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54

How to proceed . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55

CHAPTER 6: Lesson 2: Writing Your First ColdFusion Application . . . . . . . . . . . 57

Creating your first ColdFusion application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58

Application development steps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59

Using a web page to list trips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60

Converting to a dynamic web page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60

Using SQL with cfquery to dynamically retrieve information . . . . . . . . . . . . . . 62

Creating a dynamic web page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63

Developing a search capability. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66

Designing the search criteria page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66

Building the Search Results page. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69

Completing the Trip Search Results page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73

Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75

In the next lesson . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75

4 Contents

CHAPTER 7: Lesson 3: Creating a Main Application Page . . . . . . . . . . . . . . . . . 77

Enhancing the Trip Maintenance application . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78

Showing additional trip details . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80

Avoiding the potential security risk when using dynamic SQL . . . . . . . . . . . . . 84

Linking the Search Results page to the Trip Detail page . . . . . . . . . . . . . . . . . . 85

Enhancing the look of the search results and detail pages . . . . . . . . . . . . . . . . . 86

Creating the main application page from the Trip Detail page . . . . . . . . . . . . . 89

Adding navigation buttons to browse database . . . . . . . . . . . . . . . . . . . . . . . . . 89

Adding database maintenance buttons . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91

Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92

In the next lesson . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92

CHAPTER 8: Lesson 4: Validating Data to Enforce

Business Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93

Enhancing the Trip Maintenance application . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94

Using an HTML form to collect data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94

Developing code to validate data and enforce business rules . . . . . . . . . . . . . . . . . 95

Validating data using a server-side action page . . . . . . . . . . . . . . . . . . . . . . . . . 96

Validating data on the client using ColdFusion form tags . . . . . . . . . . . . . . . . . 99

Using cfselect tag to present valid event types . . . . . . . . . . . . . . . . . . . . . . . . . 102

Using other client-side script to reduce edits on the server . . . . . . . . . . . . . . . 104

Validating the existence of the trip photo file . . . . . . . . . . . . . . . . . . . . . . . . . 106

Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108

In the next lesson . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108

CHAPTER 9: Lesson 5: Implementing the Browsing and Maintenance Database

Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109

Enhancing the Trip Maintenance application . . . . . . . . . . . . . . . . . . . . . . . . . . . 110

Application development steps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111

Using dynamic SQL to browse (navigate) the Trips table . . . . . . . . . . . . . . . . 111

Building the maintenance action page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114

Linking the Trip Detail page to the Trip Search page . . . . . . . . . . . . . . . . . . . 114

Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116

In the next lesson . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116

CHAPTER 10: Lesson 6: Adding and Updating SQL Data . . . . . . . . . . . . . . . . . 117

Completing the Trip Maintenance application . . . . . . . . . . . . . . . . . . . . . . . . . . 118

Writing code to save new trips to the database . . . . . . . . . . . . . . . . . . . . . . . . 118

Updating a SQL row using cfupdate. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124

Linking the Trip Edit page to the main page. . . . . . . . . . . . . . . . . . . . . . . . . . 125

SQL Update . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128

Updating multiple records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129

Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130

INDEX. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131

Contents 5

6 Contents

INTRODUCTION

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

About Macromedia ColdFusion MX documentation

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

Documentation set

The ColdFusion documentation set includes the following titles:

Book Description

Installing and Using ColdFusion MX

Configuring and Administering ColdFusion MX

Developing ColdFusion MX Applications

Getting Started Building ColdFusion MX Applications

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

CFML Quick Reference A brief guide that shows the syntax of ColdFusion tags, functions, and

Describes system installation and basic configuration for Windows, Solaris, Linux, and HP-UX.

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.

Describes how to develop your dynamic web applications, including retrieving and updating your data, using structures, and forms.

Contains an overview of ColdFusion features and application development procedures. Includes a tutorial that guides you through the process of developing an example ColdFusion application.

tags, functions, and variables.

variables.

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.

8 Introduction

PART I

Welcome to ColdFusion

Part I provides an introduction to ColdFusion. It defines ColdFusion and provides an overview of the ColdFusion Markup Language (CFML). It also provides generic database concepts, and information about how to prepare your development environment for using the tutorial in Part II of this book.

Introducing ColdFusion MX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

CFML Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

Database Fundamentals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31

Configuring Your Development Environment . . . . . . . . . . . . . . . . . . . . . . 37

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.

Contents

What is ColdFusion MX? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12

Using ColdFusion MX with Macromedia Flash MX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14

What is 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 server

• ColdFusion Markup Language (CFML)

• ColdFusion Administrator

The following sections describe these core components in more detail.

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 file extension. A ColdFusion page looks like an HTML page but contains special tags that instruct the ColdFusion server to perform specific operations.

How ColdFusion processes pages

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

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

ColdFusion instructions:

■ Tags that begin with cf.

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

2 If the ColdFusion server finds any HTML or plain text in the page, the ColdFusion server

returns it to the web server untouched.

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

The ColdFusion Markup Language

ColdFusion Markup Language (CFML) is a 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 easy-to-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; for example:

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 JSP tag libraries).

12 Chapter 1: Introducing ColdFusion MX

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

Development tools

While you can code your ColdFusion application with NotePad or any HTML editor, 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 4, “Configuring Your Development

Environment,” on page 37.

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.

What is ColdFusion MX? 13

Using ColdFusion MX with Macromedia Flash MX

Macromedia Flash MX is designed to overcome the many limitations of HTML and solve the problem of providing efficient, interactive, user interfaces for Internet applications. ColdFusion MX is designed to provide a fast, efficient environment for developing and providing data-driven Internet applications.

By using ColdFusion MX and Flash MX together, you can easily create complete visually interactive applications for the Internet. ColdFusion MX provides a native Flash connectivity that ensures visual applications created with Flash MX can easily and securely interact with ColdFusion. Flash MX clients communicate efficiently with ColdFusion by using an Action Message Format protocol over HTTP. This protocol provides fast, lightweight, binary transfer of data between the Flash client and ColdFusion.

By using the following features of ColdFusion MX and Flash MX, you can create efficient datadriven Internet applications with visually interactive user interfaces:

• Flash MX ActionScript commands Lets you connect to ColdFusion components (CFC)

and ColdFusion pages.

• Flash MX development application debugger Lets you trace your application logic as it

executes between Flash and ColdFusion.

• ColdFusion MX Server-Side ActionScript Lets Flash programmers familiar with

ActionScript create ColdFusion services, such as SQL queries, for use by Flash clients.

For more information about using Server-Side ActionScript and developing Flash applications in ColdFusion, see Developing ColdFusion MX Applications. For more information about using Flash MX, go to Macromedia on the web (www.macromedia.com).

14 Chapter 1: Introducing ColdFusion MX

CHAPTER 2

CFML Basics

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

Contents

Working with ColdFusion pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16

Understanding CFML elements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18

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

Understanding conditional processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26

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

Commenting your code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29

Working with ColdFusion pages

As discussed in Chapter 1, “Introducing ColdFusion MX,” on page 11, 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 4,

“Configuring Your Development Environment,” on page 37.

Note: This book shows 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

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

16 Chapter 2: CFML Basics

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

To create a ColdFusion page:

1 Open your editor and create a blank file. 2 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>

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), then you can save the pages locally; if you are using a remote server, then 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 using the J2EE configuration with JRun, you save pages under jrun_root/servers/cfusion/cfusion- ear/cfusion-war. When using 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 you typed to create a ColdFusion page:

1 Create a new directory called test under the web_root or web_application_root directory. 2 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:

1 Open a web browser and go to the following URL:

http://127.0.0.1/test/cfpage.cfm The address 127.0.0.1 refers to the localhost and 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, 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://127.0.0.1/cfmx/test/cfpage.cfm.

Working with ColdFusion pages 17

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

2 Do the following tasks:

a View the source code that was returned to the browser. In most browsers, you can view the

source by right-clicking on page then selecting View Source.

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

Understanding 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 you want to accomplish with ColdFusion will involve using tags and functions.

You will use another important element known as a variable. Variables are an important part of most programming languages and are equally important with CFML. Va ri ab les 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

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 containing a value in dollars, the code to write this statement might look like this:

#DollarFormat(price)#

Understanding CFML elements 19

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

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

Nesting functions

DateFormat() or TimeFormat() 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.

Functions and pound signs

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

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

The following figure shows the output of this example:

20 Chapter 2: CFML Basics

If you did not include the pound signs around the DateFormat(Now(), "mm/ddyyy") 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 pound signs with functions, see Developing ColdFusion MX Applications.

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 a variable 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. The syntax for this tag is:

Understanding 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 (') or double (") quotation marks. Numeric or Boolean values assigned to a variable do not require single or double quotation marks.

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

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

ColdFusion supports many types of variables. Each type 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 are named based on the column names that you select

Variables Variables created using

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

cfinclude.

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. Most often you define the

You will use these other types of variables in Part II of this book. 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

<cfoutput> {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 pound 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:

<cfset my_first_name = "Kaleigh"> <cfset my_last_name = "Smith"> <cfset my_full_name = variables.my_first_name & " " & variables.my_last_name> <cfoutput> #variables.my_full_name# </cfoutput>

The following is the output:

Kaleigh Smith

cfoutput tag. The cfoutput

cfoutput start and end tags. The

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)

Many examples of expressions were shown in this chapter; 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 program.

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

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

• Character case consistency

• When to use the pound (#) sign

• 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 pound signs to denote functions or variables

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

The following list identifies some common ways to use pound signs:

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

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

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

• In the following example, you do not include the pound signs because you are using cfset to

assign one variable’s value to another value:

<cfset my_full_name = variables.my_first_name & " " &

variables.my_last_name>

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

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

The result is the following output: #1. Your name.

For more information and examples on using pound signs in expressions, see Developing ColdFusion MX Applications.

24 Chapter 2: CFML Basics

Specifying quotation marks around values

When assigning literal values to variables, you must surround the literal value with double quotation marks or single 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; for example:

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

" and my age is " & variables.my_age>

My name is

references quotation marks. ColdFusion uses the values of the referenced variables ( respectively) when 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

Kaleigh and 5,

the_string.

To display quotation marks on a page as literal characters, you must double the quotation marks; for example:

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

room."> <cfoutput> #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 using 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 these operators as the start or end of a tag.

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

Nonsupported logical operator

= IS, EQUAL, EQ Tests for equality.

< LT, LESS THAN Tests for less than.

<= LTE, LE,

>GT

Equivalent ColdFusion decision operator Description

Tests for less than or equal to.

LESS THAN OR EQUAL TO

Tests for greater than.

GREATER THAN

Working with CFML expressions 25

Nonsupported logical operator

>= GTE,

< > IS NOT, NEQ,

Equivalent ColdFusion decision operator Description

Tests for greater than or equal to

GREATER THAN OR EQUAL TO

Tests for nonequality.

NOT EQUAL

CONTAINS Tests whether a value is contained within a

second value.

DOES NOT CONTAIN Tests whether a value is not contained

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 either positive or

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

\ Divides two integer values. Use the \ (trailing slash) to separate the integers. 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.

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.

String operator

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

Operator Description

& Concatenates strings.

Understanding conditional processing

To this point, all the coding examples shown 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 if a user entered a value in a form field.

• Display results based on user input.

• Display messages based on the time of day.

26 Chapter 2: CFML Basics

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

if, then, and else.

Conditional processing tags

ColdFusion provides several tags that let you control how a page is processed. When 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 book, 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 Developing ColdFusion MX Applications.

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

<cfif expression> HTML and CFML tags executed if expression is True. </cfif>

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

In the previous 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 tag. For example, if the following

cfelse tag and the cfif tag is processed:

HTML and CFML tags executed if expression is True. <cfelse>

HTML and CFML tags executed if expression is False. </cfif>

cfif expression evaluates to False, then the code between the

cfelse

Using cfelseif to evaluate multiple expressions

To evaluate multiple expressions in a cfif statement, you can use cfelseif and cfelse in your statement; for example:

HTML and CFML tags executed if expression 1 is True. <cfelseif expression 2>

HTML and CFML tags executed if expression 2 is True. <cfelse>

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. In this example, you created a form in which users can enter their state to determine their state tax:

#form.state# State Tax: 8.5% <cfelseif form.state IS "VA">

#form.state# State Tax: 8.2% <cfelse>

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

Understanding conditional processing 27

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:

1 Creating the layout for the form itself. 2 Writing the code to process the submitted information.

Form processing

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.

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

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

Submit

form.var1=value1 form.var2=value2

Action

page

ColdFusion server & web server

Database

28 Chapter 2: CFML Basics

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 processes 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 book, you will use ColdFusion form tags to create forms and write collected values to a database.

Commenting your code

As in other programming languages, it is important to include comments in the 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:

<!--- This is a CFML comment --- >

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.

Commenting your code 29

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.

Contents

Understanding database basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32

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

Using SQL with ColdFusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34

Understanding 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 get you started with 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 address, city, state, zip, and salary are all stored in their own columns. 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

Understanding relational tables

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

Because you have multiple departments for employees, but you would not store the information about the departments in every employee row for several 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.

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

Using SQL to interact with a database

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 these SQL commands:

Command Description

SELECT Use to retrieve (query) information in a database.

INSERT Use to add records to a database.

UPDATE Use to update information in a database.

DELETE Use to delete information in a database.

In Part II of this book, you will be introduced to the syntax of these commands when you use them to build a ColdFusion application that interacts with a database. For additional information about SQL, consult any SQL primer.

Using SQL with ColdFusion

ColdFusion communicates with your data source through a database interface called 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 administrator. Chapter 4,

“Configuring Your Development Environment,” on page 37 discusses how to configure the

sample data source file that is supplied for use with Part II of this book. For more information about configuring a data source in ColdFusion, see Installing and Using ColdFusion MX or Developing ColdFusion MX Applications.

34 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 need to include SQL statements in your CFML statements; for example:

SELECT FirstName, LastName, DepartmentID

From Employee </cfquery>

In the previous example, the first attribute of cfquery is the name of the query. The second attribute of (columns) to be retrieved from a tabled named

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

Employee.

CFML tags that interact with a database

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

Command Description

cfquery To retrieve (query) information in a database.

cfinsert To add records to a database.

cfupdate To update information in a database.

In Part II of this book, you will be introduced to these tags when you use them to interact with the sample database. For more information about interacting with a database, see Developing ColdFusion MX Applications or CFML Reference.

Using SQL with ColdFusion 35

36 Chapter 3: Database Fundamentals

CHAPTER 4

Configuring Your Development Environment

This chapter describes how to set up your development environment for the tutorial in Part II of this book. It specifies the tutorial file structure, and how to configure the database connection and debugging options in the ColdFusion MX Administrator. Additionally, it provides a brief overview of using Macromedia Dreamweaver MX or Macromedia HomeSite+ for ColdFusion development.

Contents

Verifying the tutorial file structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38

Configuring database connection and debugging options . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39

Macromedia development environment tools. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44

Verifying the tutorial file structure

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

The files required to complete the Compass Travel tutorial (in Part II of this book) 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 using a third-party web server (such as IIS or Apache), the files are installed in:

web_root\cfdocs\getting_started.

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

cf_root\webroot\cfdocs\getting_started. The getting_started directory structure is as follows:

getting_started

-db

-my_app

-images

-new_user_database

-photos

-solutions

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.

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 Part II of this book.

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 Part II of this book) 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.

38 Chapter 4: Configuring Your Development Environment

Configuring database connection and debugging options

Prior to ColdFusion development, use the ColdFusion MX Administrator to define the connection to the sample database file and any optional debugging options.

To access the ColdFusion Administrator, do either of the following:

• Select Start > Programs > Macromedia ColdFusion MX > ColdFusion MX 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

Note: If you are ceasing 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 using Part II of this book to build 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:

1 In the ColdFusion MX Administrator, select Data & Services > Data Sources. 2 In the Add New Data Source dialog box, specify the following:

Field Action

Data Source name text box

Driver drop-down list box

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 Microsoft Access. Windows 98/ME users should select Microsoft Access with Unicode.

3 Click Add to configure the data source name and driver.

The Microsoft Access Data Source dialog box appears.

Configuring database connection and debugging options 39

4 Specify the following:

Field Action

Database File text box Specify the location of the CompassTravel.mdb file. Click Browse to

Description text box Enter the following:

locate and select the CompassTravel.mdb file. By default, ColdFusion MX installs the CompassTravel.mdb file in one of

the following locations:

• For third-party web server configurations: web_root\cfdocs\getting_started\db

• For built-in ColdFusion web server configurations: cf_root\wwwroot\getting_started\db

Database file for Compass Travel tutorial

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

(checked).

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

7 Ensure that OK appears in the Status column for CompassTravel.

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

a 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, then repeat the steps in this procedure to reconfigure this data source.

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

To define the sample PointBase database file:

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

The Add New Data Source dialog box appears.

2 Specify the following:

Field Action

Data Source name text box

Driver drop-down selection box

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.

3 Click Add to configure the data source name and driver.

The Data Source dialog box appears.

40 Chapter 4: Configuring Your Development Environment

4 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/coldfusionmx/wwwroot/cfdocs/getting_started/db

Driver Class Enter the following driver class:

Driver Name Specify PointBase.

Username Specify PBPUBLIC.

Password Specify PBPUBLIC.

Description Enter the following:

com.pointbase.jdbc.jdbcUniversalDriver

Database file for Compass Travel tutorial

5 Click Show Advanced Settings to make the following settings:

Field Action

Maintain Connection Disabled (clear)

CLOB Enabled (checked)

BLOB Enabled (checked)

6 Click Submit to complete the data source configuration.

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

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

a Verify that the name of the data source file does not contain a space. If it does contain a

b Verify that the JDBC URL is correct.

Configuring database connection and debugging options 41

Enabling debugging options

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.

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

42 Chapter 4: Configuring Your Development Environment

The appended debugging information

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.

If you are using a development server to build the sample application in Part II of this book, 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:

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

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

2 Select the Enable Debugging check box. The debugging service is enabled for all options already

selected on the page.

3 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 the tutorial in Part II of this book, 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

4 Click Submit Changes when you are done.

Displays variable information in the debugging output.

Configuring database connection and debugging options 43

Sending debugging information to remote clients

If you are using a remote client to perform the tutorial in Part II of 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), this procedure is not necessary.

To receive debugging information when using a remote client:

1 In the ColdFusion MX Administrator, select Debugging and Logging > Debugging IP

Addresses.

The Debugging IP Address page appears.

2 In the IP Address text box, enter the IP address of your remote client. 3 Click Add.

Macromedia development environment tools

Macromedia Dreamweaver MX is the preferred development environment for building ColdFusion MX applications. It combines the best code editing features of ColdFusion Studio with the visual design features of Dreamweaver.

Dreamweaver MX window

Dreamweaver MX supports the latest ColdFusion MX features and tags. It also includes Macromedia HomeSite+, which combines all the features of ColdFusion Studio and HomeSite 5, along with support for the latest ColdFusion MX tags.

With Dreamweaver MX or HomeSite+, you can author and test your application code from a local or remote client. Both of these tools let you 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 and HomeSite+ for ColdFusion development.

44 Chapter 4: Configuring Your Development Environment

The Dreamweaver MX environment

As a ColdFusion developer, you can build ColdFusion MX applications by writing the code manually or generating the code by using one of the code-generating tools provided with Dreamweaver MX.

Features for ColdFusion developers

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

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

• Code hints for writing CFML tag attributes.

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

• Tag chooser with integrated reference material for inserting ColdFusion tags.

• Snippets panel for reusing code.

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

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

• Dreamweaver MX online Help includes ColdFusion MX documentation.

If you plan to use Dreamweaver MX or HomeSite+ to build the sample ColdFusion application in Part II of this book, see the following sections for information about configuring these tools for ColdFusion development.

Macromedia development environment tools 45

Configuring Dreamweaver MX for ColdFusion development

Before you use Dreamweaver MX to create the sample application in Part II of this book, you must configure Dreamweaver to recognize the tutorial files and data sources.

To configure Dreamweaver MX to create the sample application:

1 Create a site that contains the tutorial files. 2 Specify ColdFusion as the application server document type. 3 Specify ColdFusion MX as the site application server.

For information about how to perform these steps, see the Dreamweaver MX online Help or Using Dreamweaver MX.

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

Configuring HomeSite+ for ColdFusion development

Before you use Macromedia HomeSite+ to create the sample application in Part II of this book, you must configure HomeSite+ to recognize the tutorial files and data sources.

To use HomeSite+ to create the sample application:

1 Establish a secure connection to the ColdFusion server environment where the tutorial files are

installed. Establishing a connection to the ColdFusion server is a prerequisite for accessing the ColdFusion data sources.

For information, see the online Help or product documentation for HomeSite+.

2 Enable internal browsing to view and process the ColdFusion application files.

For information, see the online Help or product documentation for HomeSite+.

3 Determine how you want to work with the tutorial files. You can view and access the tutorial

files using the Resource Area in the HomeSite + window. You can also set up a project to manage the files more efficiently.

For more information, see the online Help or the product documentation for HomeSite+.

Note: The tutorial provided in this book does not describe how to use HomeSite +. The purpose of this tutorial is to teach you how to build ColdFusion applications using ColdFusion Markup Language (CFML). For more information about HomeSite +, see the product documentation or online Help.

46 Chapter 4: Configuring Your Development Environment

PART II

Building a ColdFusion Application

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

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

Lesson 2: Writing Your First ColdFusion Application . . . . . . . . . . . . . . . . . 57

Lesson 3: Creating a Main Application Page . . . . . . . . . . . . . . . . . . . . . . . . 77

Lesson 4: Validating Data to Enforce Business Rules . . . . . . . . . . . . . . . . . 93

Lesson 5: Implementing the Browsing and Maintenance Database Functions. 109

Lesson 6: Adding and Updating SQL Data. . . . . . . . . . . . . . . . . . . . . . . . 117

CHAPTER 5

Lesson 1: Preparing to Build the Sample

Application

In this tutorial, you will build a simple ColdFusion web application 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 made available to the public. You will build the sample tutorial application to assist 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 in determining the application design. This lesson provides an overview of these application design steps, while the remainder of this book guides you through the lessons on constructing the sample application.

Application development steps

Most software applications perform three major functions:

• A user interface to capture data

• Logic to validate the captured data

• A database to store the validated data

The process steps to develop these major functions varies from project to project. In this tutorial you will review or participate in the following application development steps to build the Compass Travel Trips 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.

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

the trip information.

4 Develop the ColdFusion application pages.

An overview of each of these application development steps is explored in greater detail in the following sections.

Determining the application functional requirements

Before you can build the sample application, you must understand the functional requirements underpinning its design. The design of the sample application centers around the daily tasks performed by Compass Travel’s trip coordinators. These tasks are listed in the following table:

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

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

Functional requirement

1 The ability to generate trip listings

2 A trip query facility based on user supplied criteria

3 Trip browsing functionality

4 The ability to add a new trip

5 The ability to delete an existing trip

6 The ability to edit an existing trip

7 A mechanism to validate new or updated trips against Compass business rules

To help Compass Travel agents take trip reservations over the phone 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.

In the lessons that follow, you will build ColdFusion pages to address each of these functional requirements. Central to every requirement is the notion 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.

50 Chapter 5: Lesson 1: Preparing to Build the Sample Application

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

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

• Trip description

• Trip location

• An indicator of whether a deposit is required

• Departure date

• Return date

• Total number of people who can attend the trip

• Price

• Base cost

• Assigned trip leader

• Trip photograph

By collecting the preceding information about each trip, the Compass Travel website can market its trips online to the general public. Customers 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. Cost is not shown on the public website.

Determining the data requirements 51

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.

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

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

52 Chapter 5: 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:

Designing the database for your application 53

Developing the sample application

Given the application functional requirements and the database provided, you are ready to use ColdFusion to develop the Trips Maintenance application. The remaining lessons in the tutorial will 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:

• Develop a trip search facility (Lesson 2: Writing Your First ColdFusion Application).

• Build the main application page (Lesson 3: Creating a Main Application Page).

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

Business Rules).

• Implement browsing and trip maintenance functions (Lesson 5: Implementing the Browsing

and Maintenance Database Functions).

• Develop the trip add and update pages (Lesson 6: Adding and Updating SQL Data).

54 Chapter 5: Lesson 1: Preparing to Build the Sample Application

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 exercise(s).

Depending on your programming experience, you can read the entire lesson then proceed to the hands-on exercises, or you can skip some information in the lesson and proceed directly to the exercises.

Working directories

The following table describes the working directories for this tutorial:

Directory Description

my_app You will save all your source code in this directory.

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

db You will use this directory as the working directory for the Compass Travel

photos You will use this directory to access existing trip photographs.

images You will use this directory to access application image files.

database. Note: As discussed earlier in Chapter 4, “Configuring Your Development

Environment,” on page 37, you should verify that the Compass Travel

database file residing in the db directory is the original file supplied. This file should have the same date as the file in the new_user_db directory. If date on the file in the db directory is different, replace the file in db directory with a copy of the Compass Travel database file that is located in the new_user_db directory.

Locating the working directories

You can locate the working directories for this tutorial under your web root directory. For example, the directory path on your computer might be:

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

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

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

Developing the sample application 55

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

Requirements

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

• ColdFusion MX For information on how to install ColdFusion MX, 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 MS 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 “Configuring Your Development Environment” on page 37. For additional information about configuring a data source, see Installing and Using ColdFusion MX.

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

later).

• Text editor or IDE (Interactive Development Environment) Macromedia recommends that

you use Dreamweaver MX. However, you can use HomeSite+, ColdFusion Studio, any text editor, or IDE. In the exercises in this tutorial, the term editor means Dreamweaver MX, HomeSite+, ColdFusion Studio, or any text editor or IDE of your choice.

Note: The default file extension used for ColdFusion application pages is .cfm.

56 Chapter 5: Lesson 1: Preparing to Build the Sample Application

CHAPTER 6

Lesson 2: Writing Your First ColdFusion

Application

In this lesson, you begin the construction of a ColdFusion web application for the fictitious company, Compass Travel. The exercises in this lesson guide you through the steps of creating queries and forms to search for and display trip offering information from the Compass Travel relational database.

This lesson explains how to do the following tasks:

• Construct a query to retrieve information from a database.

• Develop a search form to accept user criteria.

• Use dynamic SQL to build a flexible search query.

• Develop a results form to display the result of the search.

ColdFusion tags and functions introduced in this lesson

The following table identifies the ColdFusion tags and functions that you use in this lesson to build your first ColdFusion application:

Element Type Description

PreserveSingleQuotes

cfoutput

cfquery

cfif

cfset

Function Enables you to use single quotation marks in variables used in

Tag Instructs the server to show the results of variables, functions, or

Tag Submits SQL statements to a JDBC data source.

Tag Creates conditional statements.

Tag Defines a ColdFusion variable. If the variable already exists,

SQL statements.

text that is specified between the

cfset resets it to the specified value.

cfoutput start and end tags.

Creating your first ColdFusion application

As you recall from Lesson 1: Preparing to Build the Sample Application, 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 lesson.

The following list identifies the components that you will create in this lesson:

• Dynamic Trip List page The purpose of the Trip List page is to present an up-to-date lists of

trips on the Compass Travel website.

• Trip Search form 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.

58 Chapter 6: Lesson 2: Writing Your First ColdFusion Application

• Trip Search Results page The purpose of the Trip Search Results page is to display the

results of a trip search.

The primary users of these components are the Compass Travel coordinators and agents, not the general public.

Application development steps

You will review or participate in the following application construction steps:

Steps Description

1 Create a dynamic web page that displays a list of trips.

2 Design the constraints for the search interface.

3 Develop ColdFusion pages to capture user search criteria and display the results.

Creating your first ColdFusion application 59

Using a web page to list trips

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

Converting to a dynamic web page

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, since Compass Travel has built a database that contains a list of trips, you can build a more accurate and timely solution for the trip coordinator. To accomplish this, you must understand how to issue a SQL SELECT statement to retrieve the data from the Trips table in the Compass Travel database.

Understanding basic SQL SELECT statements

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

60 Chapter 6: Lesson 2: Writing Your First ColdFusion Application

Consider a table named Clients to hold information about people with the following rows:

LastName FirstName Address City

Jones Tom 12 State St Boston

Adams Anita 521 Beacon St Boston

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

Jones Tom

Adams Anita

Green Peter

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 resulting 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 "%" sign to

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

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

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

Using a web page to list trips 61

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

LastName FirstName

Jones Tom

Adams Anita

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 listed are True. The OR operator displays a row if ANY of the conditions listed are True. An example of a WHERE clause with multiple subclauses follows:

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

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

LastName

Adams

Note: The preceding SQL SELECT examples use single quotation marks around the value. SQL uses single quotation marks around text values. Most database systems will also accept double quotation 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 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 book. For more information, consult a SQL reference.

Using SQL with cfquery to dynamically retrieve information

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 which database manager to send a specific SQL string for evaluation. In CFML, the

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

statement and the cfquery tag to create a dynamic version of the Trip List page presented earlier in this lesson. In this example, you use

cfquery to return all the trip names found in the

tripName column within 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 as follows:

SELECT tripName FROM trips

</cfquery>

62 Chapter 6: Lesson 2: Writing Your First ColdFusion Application

Displaying the query result using cfoutput

In Chapter 2, “CFML Basics,” on page 15, you learned that the ColdFusion easy mechanism to display literal text and the contents of variables. Additionally, the

cfoutput tag is an

cfoutput

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 QUERY attribute of the

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

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>

Displaying the column contents from the SQL statement

In CFML you surround variables with pound 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 instance, when you want to display the trip names from the SQL query, you

would simply use

#tripname#

</cfoutput>

#tripName# within the output code block.

For additional information about using SQL with cfquery and cfoutput, see Developing ColdFusion MX Applications.

Creating a dynamic web page

In the following exercises you will build a dynamic Trip Listing web page that is always current. The first exercise guides you through constructing a query to retrieve information from the database. In the second exercise, you will enhance the query to sort the query results and to display other pertinent trip information.

For your convenience, the following figure shows the Compass Travel Trips table. You can refer to this table to verify the names of the columns you use in the queries in the exercises.

Using a web page to list trips 63

Exercise: building a query using SQL, cfquery, and cfoutput

Follow these steps to build a query that lists the current trips from the Compass Travel database.

To build the query:

1 Open an editor and create a new ColdFusion page (.cfm). 2 At the top of the file, enter the following code to dynamically retrieve the names of the current

trips listed in the Compass Travel database:

SELECT tripName FROM trips </cfquery> <html>

<head>

<title>Trip Listing</TITLE>

</head> <body>

<cfoutput query="TripResult">#tripName#<BR></cfoutput>

</body>

</html>

3 Save the file as triplisting.cfm in the my_app directory. 4 View the triplisting.cfm page in a browser. The page lists all the trip names retrieved from

Compass Travel database.

Reviewing the code

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

Code Explanation

<cfquery name="TripResult"

datasource="CompassTravel">

SELECT tripName FROM trips

#tripName#<BR></cfoutput>

ColdFusion query named "TripResult". Submits any SQL statement between the source specified in the datasource attribute.

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

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

cfquery start and end tags to the data

Exercise: enhancing the query

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

To enhance the trip listing query to meet these new requirements, you will modify the query you created in the previous exercise.

Follow these steps to enhance the query to meet the new requirements. Display the triplisting.cfm page in the browser after each step to ensure the corresponding requirement was met.

64 Chapter 6: Lesson 2: Writing Your First ColdFusion Application

To enhance the query results:

1 To sort the trip names in alphabetical order in the triplisting.cfm page, modify the SQL

SELECT statement within the

SELECT tripName FROM trips ORDER BY tripName

cfquery tags as follows:

2 To display the departure, return date, and price for each trip, modify the same SQL statement.

a Modify the SQL SELECT statement, as follows:

SELECT tripName, departureDate, returnDate, price FROM trips ORDER BY tripName

b Change the output block (the code immediately preceding the </cfoutput> tag) from just

#tripName# to include all three selected fields, as follows: #tripName# departs: #departureDate# returns: #returnDate# price:

#price#<BR>

3 Create the Budget Trip List report by doing the following:

a Modify the SQL SELECT statement, as follows:

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

b Change the heading tag from <h1>Trip List</h1> to <h1>Budget Trip List</h1>.

4 View the triplisting.cfm in a browser and verify that all the new requirements were met. The

revised TripListing.cfm page looks like this:

Note that the dates and prices in the preceding listing are not formatted. In Lesson 3: Creating

a Main Application Page, you will enhance the look of this page.

Using a web page to list trips 65

Developing a search capability

The dynamic listings developed in the previous exercise 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 results of the user’s criteria selection are then posted to a search results page. The logic contained within the search results page builds the SQL SELECT statement contained in a ColdFusion string manipulation. Finally, the action page displays the result using the tag. This approach of building and executing SQL statements on the fly is called dynamic SQL.

Dynamic SQL is a term used to refer to SQL code 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.

Designing the search criteria page

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

cfquery tag using

• List the trips located in Hawaii.

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

• Show the trips departing after 11/11/2002 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. Two of the most important considerations are as follows:

• 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 lesson, the Compass Travel trip coordinator will search the trips based on tripLocation, departureDate, and price. These queryable columns, therefore, will be the only ones contained in the WHERE clause of the generated SQL Statement. Further, the coordinator will have no control over which columns are returned in the record set. The query will always return the same columns to identify a trip:

• tripName

• tripLocation

• departureDate

• returnDate

• price

• tripID

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

cfquery in the search action page.

cfoutput

66 Chapter 6: Lesson 2: Writing Your First ColdFusion Application

Understanding search query operators

Now that you decided on the queryable columns (tripLocation, departureDate, and price), you can build a simple form that allows the user to 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'

But, what if 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. When designing the Search Criteria page, you must decide which operators to support for each of the queryable columns. The operators that you use depends on the data type of the SQL column.

For example, 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

Unlike trip location, it is not semantically correct to consider whether a price begins with “B”. Typical SQL string operators are equals, starts with, contains, and ends with.

While many more operators are permissible, for simplification, you can use the following the operators for the Compass Travel queryable columns:

Queryable column Query operators

tripLocation is, begins with

departureDate is, before, after

price is, greater than, less than

Developing a search capability 67

Using SQL operators to create a search criteria page

A simple design for a search criteria page presents an operator list and data entry field for each of the queryable columns. Following this pattern, a page to collect the Compass Travel Trip search criteria looks like this:

Since all the code used to produce the search criteria page is HTML, you are not requested to build this page. You will, however, use this page (tripsearch.cfm) later in this lesson to test the search action page. The source code for the Trip Search form (tripsearch.cfm) is as follows:

<html> <head> <title>Trip Maintenance - Search Form</title> </head> <body> <img src="images/tripsearch.gif">  <form action="tripsearchresult.cfm" method="post"> <table>

<tr>

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

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

</select>

</td> <td>

</td> </tr>  <tr>

<td>Departure Date

</td>

<td>

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

68 Chapter 6: Lesson 2: Writing Your First ColdFusion Application

</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"> </form> </body> </html>

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.

Input text control to capture value to test. There is one text control for each queryable column.

Building the Search Results page

Based on the queryable columns identified earlier, 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.

Developing a search capability 69

When the user enters the search criteria on the Trip Search form and clicks the Search button, the form fields are then 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 can be generated depending on the criteria set on the search page:

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

AND departureDate > 1/1/2001

AND price < 1500

In the previous 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.

The search action page uses a SQL SELECT statement to display an HTML table with the results of the user query using the

cfoutput block.

Building the WHERE Clause with the cfif and cfset

The WHERE clause in a SQL SELECT 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 new 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 variable is equal to

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

red, and other code execute if it is not, you use the following pseudocode:

color

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 = "Dylan"> <cfset LastName = "Smith"> <cfset FullName = FirstName & " " & LastName> <cfoutput>My name is #FullName#.</cfoutput>

results in the following text:

My name is Dylan Smith.

70 Chapter 6: Lesson 2: Writing Your First ColdFusion Application

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 using the cfif tag; for

example

• 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 & "'" > <cfelse>

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

PreserveSingleQuotes function. The PreserveSingleQuotes function

Constructing the initial Trip Search Results page

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.

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

form.tripLocationValue & "'" >

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

form.tripLocationValue & "%'" >

</cfif>

</cfif>

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

Developing a search capability 71

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

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

Reviewing the code

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

Code Explanation

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

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

The cfif tag tests to see if user entered anything in the Value input box for tripLocation criterion.

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

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

72 Chapter 6: Lesson 2: Writing Your First ColdFusion Application

Completing the Trip Search Results page

In the following exercises you will test and modify tripsearchresults.cfm. In the first exercise, you will test the Trip Search Results page by entering criteria on the Trip Search form and inspecting the results. In the second exercise, you will finish the code to construct the complete WHERE clause for all three queryable columns from the Trip Search form.

Exercise: testing the Trip Search Results page

Follow these steps to test the Trip Search Results page:

1 Copy the tripsearch.cfm and tripsearchresult.cfm files from the solutions directory to the

my_app directory.

2 View the tripsearch.cfm from the my_app directory in your browser and do the following:

a In the Trip Location drop-down list box select the Begins With option, and enter the value

C in the text box.

b Click Search.

The Trip Results page displays several entries, as follows:

c Notice in the Trip Results page that only one trip has a trip location of China. d Click the Back button in your browser to return to the Trip Search page.

3 In the Trip Location drop-down list box of the Trip Search page, select the Is option, enter the

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

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

follows:

a Click the Back button in the browser to return to the Trip Search page. b 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. Obviously, either of these conditions would produce a results page with no rows.

c Click the Search button.

The Search Result page should be identical to step 3 because the code to build the WHERE clause in the Trip Results page does not include departure date and price.

Developing a search capability 73

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

1 In an editor, open the tripsearchresult.cfm page in the my_app directory, then locate the

following comment line:

2 To build the departureDate WHERE subclause, enter the code in the following table

immediately following the comment line.

For Enter this code

Windows users, using the MS Access database file

UNIX users, using the PointBase database file

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

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

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

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>

3 To build the price WHERE subclause, enter the following code after the code you entered in

step 2.

<cfif Form.priceValue GT "">

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

</cfif>

74 Chapter 6: Lesson 2: Writing Your First ColdFusion Application

4 Verify that the price and departureDate are now considered in the query, as in step 4 in the

previous exercise:

a Open the tripsearch.cfm page in the my_app directory in your browser. b In the Departure Date drop-down list box, select Before, enter 1/1/1900 as the date (specify

1900-1-1 on UNIX), and select Smaller Than 0 for the price.

c 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 then the one presented here. This more extensible approach is beyond the scope of this tutorial, however.

Summary

This lesson described how to access a relational database using ColdFusion. You used the SQL SELECT statement and the tool that dynamically builds a WHERE clause of the SQL SELECT statement using

cfset tags. To ensure that the SQL statement remains intact, you used the PreserveSingleQuotes CFML function.

In the next lesson

In the next lesson, you will build the main navigation page for the Trip Maintenance application. This page will display detail information about the currently selected trip. Additionally, it will provide buttons to do trip maintenance and browsing through the trips table. You will also link the trip search facility that you built in this lesson to the main page that you will build in the next.

cfquery and cfoutput tags to display trip lists. You built a search

cfif and

Summary 75

76 Chapter 6: Lesson 2: Writing Your First ColdFusion Application

CHAPTER 7

Lesson 3: Creating a Main Application Page

In this lesson you will enhance the Compass Travel Trip Maintenance application. The exercises in this lesson guide you through the following steps:

• Transforming the search facility that you built in the previous lesson into a drill-down facility

for trip queries

• Creating a Trip Detail page to display complete details of a trip

• Implementing a link from Trip Detail page to the search facility

• Converting the Trip Detail page into the main application page by including buttons for

navigating to other pages and browsing trip records in the database

This lesson explains how to do the following tasks:

• Use URLEncodedFormat to safely link the Search Results page to the Trip Detail page.

• Use the DateFormat and DollarFormat functions to properly format date and dollar

variables.

• Use the MOD function and a query variable to properly alternate the back color of rows in the

Trip Search Results table.

ColdFusion tags and functions introduced in this lesson

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

Element Type Brief description

IsDefined

DollarFormat

DateFormat

URLEncodedFormat

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 Returns a number as a string formatted with two decimal

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

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

DateFormat returns the date value in the dd-mmm-yy format. DateFormat supports dates that have the U.S. date format.

Function Converts a text string into a string that can safely be used in a

URL.

Enhancing the Trip Maintenance application

In this lesson you will enhance the Trip Maintenance application that you created in Lesson 2:

Writing Your First ColdFusion Application. You will modify the application to include a main

application page that lets Compass Travel employees do these tasks:

• View additional details about a trip.

• Scan records in the Trips database table.

• Modify or search for records in the Trips database table.

The following list identifies the application components that you will create in this lesson:

• Trip Detail Page The Trip Detail page displays additional information about a trip that was

initially selected from the Trip Search Results page.

78 Chapter 7: Lesson 3: Creating a Main Application Page

• Enhanced Trip Search Results page The original purpose of the Trip Search Results page

in Lesson 2: Writing Your First ColdFusion Application was to display the results of a trip search. In this lesson, you will enhance this page to provide a useful drill-down mechanism for accessing additional information about trips that meet the search criteria.

• Main Application page In the beginning of this lesson, you will develop the Trip Detail

page (see the following figure). Later in this lesson, you will convert the Trip Detail page into the main Trip Maintenance application page. The main application page will include 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

Enhancing the Trip Maintenance application 79

The primary users of these components will be the Compass Travel coordinators and agents, not the general public.

Showing additional trip details

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 displayed, the user should be able to click on any row to display the detailed trip data.

In the following 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 will 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 TripDetail 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 Results page. This will offer the user drill-down capability when searching for trips. You will link the Trip 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 the next lesson.

80 Chapter 7: Lesson 3: Creating a Main Application Page

Exercise: building a Trip Detail page

Follow these steps to build a Trip Detail page.

To build a Trip Detail page:

1 Open your editor and create a new ColdFusion page. Remove any lines that your editor adds. 2 To create a query to select a single trip from the Trips table, enter the following code:

<cfquery name="TripQuery" dataSource="compasstravel" maxRows=1> SELECT tripID, tripName, tripDescription, tripLocation, departureDate, returnDate, price, tripLeader, photo, baseCost, numberPeople,

depositRequired FROM TRIPS <cfif IsDefined("URL.ID")> WHERE tripID = #URL.ID# </cfif> </cfquery>

3 To output the results from the query, append the following cfoutput code after the code you

added in step 2:

4 To display the data fields on the Trip Detail page, place the following code above the

</cfoutput> tag you added in step 3. Alternatively, you can copy this HTML code from the

tripdetail.txt file in the solutions directory.

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

<td valign="top">Return Date:

</td>

<td>#returnDate#

</td> </tr> <tr>

Enhancing the Trip Maintenance application 81

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

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

the

<cfoutput query = "TripQuery"> line:

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

</head> <body>

6 Insert the ending body and html tags at the end of the page:

</body> </html>

7 Save the file as tripdetail.cfm in the my_app directory. 8 The Rio Cahabon Rafting trip has an tripID of 24. To view the trip detail for the trip in your

browser enter one of the following URLs:

Web server configuration URL

For built-in ColdFusion web server configuration

For local third-party web server configuration

82 Chapter 7: Lesson 3: Creating a Main Application Page

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

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

The following page shows the expected result:

Reviewing the code

The following table describes the ColdFusion code used to build the Trip Detail page:

Code Explanation

<cfquery name="TripQuery"

dataSource="CompassTravel" maxRows=1>

</cfif>

WHERE tripID = #URL.ID#

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, we want to only show a single row at a time, therefore,

maxRows is set to 1.

The URL.ID specifies a parameter that can be contained within 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 tripID to SELECT. You can use the CFML function

IsDefined to determine if a parameter is passed

within the URL. It can also be used to determine if the user has entered data in form fields prior to the form post action.

Enhancing the Trip Maintenance application 83

As you can see, 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 2: Writing Your First ColdFusion Application. However, before you link that search facility, you need to understand a potential security risk using dynamic SQL. The following section describes this risk and how to code around it.

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 may 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 supporting this behavior:

WHERE tripID = #URL.ID#

</cfif>

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

Now if non-numeric data is passed within the URL ID field, the Val statement returns 0, and the trip with ID 0 displays (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 Val) to perform type checking on all URL parameters. For queries, you can also use the cfqueryparam tag, which is explained in CFML Reference.

84 Chapter 7: Lesson 3: Creating a Main Application Page

Linking the Search Results page to the Trip Detail page

In the next 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 the trip name entries in the results page to links, which will display the trip’s detailed information in the detail page.

Exercise: linking the Search Results page with the Trip 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 pages:

1 Open the tripsearchresult.cfm page from the my_app directory and replace the #tripName# in

the cfoutput block with the following code:

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

Note: The URLEncodedFormat is a ColdFusion function that returns a URL-encoded string. Spaces 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.

2 Save the file and view the tripsearch.cfm page from the my_app directory in your browser. 3 In the Trip Location drop-down list box, select Begins With and type the value C in the trip

location text box then click Search. The Trip Search Results page displays a hyperlink for each trip name listed, as the following

figure shows:

4 To view the Trip Detail page for a trip, click on 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 the next exercise.

Enhancing the Trip Maintenance application 85

Enhancing the look of the search results and detail pages

The Trip Maintenance search now provides a useful drill-down mechanism for locating trips. While this application is functionally sound, the appearance of the dates and dollar amounts could be improved.

ColdFusion provides several functions to improve the application appearance. The and

DollarFormat functions format dates and currency variables.

DateFormat

Another part of the application that could be improved is the Trip Search Results table. In a large list, it is sometimes difficult to correctly read the contents of a row in the middle of the table because it is sandwiched between two other rows. To avoid mistakes in reading the table, it would be helpful to alternate the background color of each row in the table.

The HTML table row

<tr> tag has a bgcolor attribute to change the background color for a

given row. To highlight every other row, the background color could be changed alternatively to yellow or white. To change the background color for each row, you must determine whether the row is an odd or even row. Therefore, you must obtain the sequence number of the current row and test if it is evenly divisible by 2.

As described in Chapter 2, “CFML Basics,” on page 15, ColdFusion offers a modulus function (

MOD) that returns the remainder (modulus) after a number is divided by a divisor; for example,10

MOD 3

is 1.

If the

MOD function returns a value greater than 0, a cfif test of its result evaluates to True. If the

MOD function returns 0, the cfif check evaluates to False.

The following example uses the

MOD function to alternate the background color of table rows:

Notice that the MOD function uses a variable called CurrentRow. Notice also that CurrentRow is not defined anywhere in tripsearchresult.cfm. The

CurrentRow variable is one of a few variables

that ColdFusion automatically exposes that provide information about the query. These are often called query variables.

variables

Query variables

In addition to using the use it to return information about a query. To do this, the

cfquery tag to return data from a ColdFusion data source, you also can

cfquery tag creates a query object,

providing information in query variables as described in the following table:

Variable Name Description

query_name.recordCount The number of records returned by the query.

query_name.currentRow The current row of the query being processed by cfoutput.

query_name.columnList A comma-delimited list of the query columns.

86 Chapter 7: Lesson 3: Creating a Main Application Page

When a query variable is referenced within a cfoutput block, the qualifying query_name is assumed to be the query identified in the QUERY attribute of the need the

qualifier query_name. That is why the CurrentRow variable is unqualified in the

cfoutput tag and does not

previous modulus code example.

For more information about using the modulus function or query variables in ColdFusion applications, see Developing ColdFusion MX Applications.

Exercise: formatting the display

In this exercise, you format the currency and date fields in the Trip Search Results page and the Trip Detail page. Additionally, you modify the Trip Search Results page to alternate the background color of the result table rows.

To format the table:

1 To format the Trip Detail page dollar and date fields, open the tripdetail.cfm in the my_app

directory in your editor and make the following changes:

Existing code Change to

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

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

#price# #dollarformat(price)#

#baseCost# #dollarformat(baseCost)#

2 To format the currency and date fields on the Trips Search Results page, open the

tripsearchresult.cfm in your editor and make the same changes for departureDate,

returnDate, and price as in step 1.

3 To alternate the background color of the rows of the search results table, delete the table row

tag

<tr> that immediately follows the <cfoutput query="TripResult">, and replace it with

the following code:

4 Save the files then open your browser and navigate to the tripsearch.cfm page in the my_app

directory. Again, enter Begins With and C in the location search criteria, and click Search.

Enhancing the Trip Maintenance application 87

The Trip Search Results page appears:

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

The properly formatted Trip Detail page appears:

88 Chapter 7: Lesson 3: Creating a Main Application Page

Creating the main application page from the Trip Detail page

To this point in the tutorial, 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, however, that you have not addressed: the ability to

browse through the Trips table, and the ability to add, delete, and edit trip information.

The trip coordinator must be viewing the details of a specific trip to edit trip information or delete a trip. The Trip Detail page provides this trip-specific detail information. Therefore, you will use the Trip Detail page as the main Trip Maintenance application page.

You will modify the Trip Detail page so that it can act as a main switchboard 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, or add new trips

• An additional button to launch the search facility

Adding navigation buttons to browse database

The drill-down search function developed in the last exercise is very useful when the user knows some search criteria to enter. Unfortunately, however, 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 user does not know the criteria to search for in advance.

For example, an anomaly may exist on any trip record, the trip coordinator would have no idea what search criteria to specify on the search form to find these problem records. To solve this problem, the browse function gives the coordinator the ability to navigate sequentially through the trips table using the single record trips display. The following figure shows the navigation buttons. The label under each button describes which row to display relative to the currently displayed row:

Exercise: adding navigation buttons to the Trip Detail page

In this exercise, you use the HTML

form and input tags to add the navigational buttons to the

Trips Detail page.

To add navigation:

1 Open the tripdetail.cfm in the my_app directory in your editor. 2 To implement the trip navigation buttons, insert the following code between the </table> and

</cfoutput> tags in the tripdetail.cfm file: <form action="navigationaction.cfm" method="post">

Enhancing the Trip Maintenance application 89

Note: Notice that the current trip record ID (tripID) is hidden within the form code. This is desirable because the action page must have the current record ID in order to build the query that navigates to the appropriate record in the trips database table.

3 Save the file and view the updated tripdetail.cfm page in a browser.

The Trip Search Results page appears:

4 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 the next lesson.

90 Chapter 7: Lesson 3: Creating a Main Application Page

Reviewing the code

The following table describes the navigation code in 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 identifying navigationaction.cfm to handle record navigation.

Hidden RecordID field with the value of the current

Navigation buttons are image type HTML input tags.

tripID.

Adding database maintenance buttons

The search and sequential navigation capabilities are features for locating Compass Travel trips. After the trip coordinator locates a trip, they must be able to modify or delete the trip. Additionally, when viewing the detail for a trip, they must be allowed to add a new trip or use the search facility. To enable trip coordinators to do this, you will add the following buttons to the Tr i p D e ta i l p ag e :

As described earlier, 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 will use an HTML

input tag to hide the current recordID and post it to the maintenanceaction.cfm page.

Exercise: add Maintenance Buttons to Trip Detail Page

Follow these steps to add the database maintenance buttons to the Trip Detail page.

To add maintenance buttons:

1 In your editor, open tripdetail.cfm from my_app subdirectory. 2 Enter the following code immediately after the <cfoutput query="TripQuery"> tag in the

tripdetail.cfm file:

Note: The current trip record ID (tripID) is in a hidden field in the form code. This field provides the 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.

Enhancing the Trip Maintenance application 91

3 Save the file and view the updated tripdetail.cfm page in a browser (http://localhost/cfdocs/

getting_started/my_app/tripdetail.cfm?ID=8). The page appears as follows:

4 Click Search or Delete to test the database maintenance buttons.

An error occurs because the Maintenance Action page does not exist. The Maintenance Action page is required to process the maintenance button requests. You will develop this page in the next lesson.

Summary

In this lesson, you transformed the search facility you built in Lesson 2: Writing Your First

ColdFusion Application 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 Trip Search Results page with the Trip Detail page. You used the function to ensure that data was correctly from one page to the other. Finally, by adding trip maintenance and navigation buttons, you converted the Trip Detail page into the main Trip Maintenance application page.

In the next lesson

In the next lesson, you will build the action pages required to implement the navigation and maintenance buttons on the main Trip Maintenance application page.

92 Chapter 7: Lesson 3: Creating a Main Application Page

DollarFormat and DateFormat functions. You linked the

URLEncodedFormat CFML

CHAPTER 8

Lesson 4: Validating Data to Enforce

Business Rules

In this lesson, you will enhance the Compass Travel Trip Maintenance application. The exercises in this lesson will guide you through the steps of enhancing the application to provide a page for the trip coordinator to add new trip offerings and update existing trips. Further, you will add logic to validate that data entered against Compass Travel business rules.

This lesson explains how to do the following tasks:

• Create a data entry form to capture user input information.

• Develop an action page to process posted form variables.

• Validate captured data in three ways.

ColdFusion tags and functions introduced in this lesson

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

Element Type Description

cfform

cfinput

cfselect

FileExists

Tag Builds a form with CFML custom control tags that provide more

Tag Use inside cfform to place radio buttons, checkboxes, or text boxes.

Tag Used inside cfform, cfselect lets you construct a drop-down list box form

Function Returns True if the file specified in the argument exists; otherwise it returns

functionality than standard HTML form input elements.

Provides input validation for the specified control type.

control. You can populate the drop-down list box from a query, or use the option tag. You further use the option tag is the same as for its HTML counterpart.

False.

option elements to populate lists. The syntax for

Enhancing the Trip Maintenance application

In this lesson and the next, you will create the code to implement the remaining maintenance buttons on the main Trip Maintenance application page. The remaining buttons are Add and Edit.

You will develop the data entry form to capture new trip information and validate the data entered against Compass Travel business rules. You will then modify the data entry form to edit existing trips. In the next lesson, you will continue to build on this application by adding logic to insert and update the data to the Compass Travel database.

The following list identifies the trip edit components that you will create in this lesson.

• Trip Edit page You will create the Trip Edit page (see the following figure) to add new trips

and edit existing trips. The page will be launched from the Add and Edit buttons on the main Trip Maintenance application page (tripdetail.cfm). The fields required to capture trip information are the same as those on the Trip Detail page that you used to display trip information in Lesson 3: Creating a Main Application Page.

• Trip Edit Action page You will develop an action page that will insert or update trip data

passed from the Trip Edit page into the trips table of the Compass Travel database. In this lesson you will only add the logic to validate the data entered on the Trip Edit page.

Using an HTML form to collect data

Based on the data requirements determined in Lesson 1: Preparing to Build the Sample

Application, the following figure shows the Trip Edit data collection page:

94 Chapter 8: Lesson 4: Validating Data to Enforce Business Rules

Exercise: view the source and test the Trip Edit page

To view the source and test the Trip Edit data collection form:

1 Open an editor, then locate and open the file tripedit1.cfm in the solutions directory

\cfdocs\getting_started\solutions under your web root directory.

2 Review the HTML source code used to create the Trip Edit page. If you are not fluent in

HTML, the following table explains the use of some of the HTML tags in the Trip Edit page. For more information on HTML, consult any HTML primer.

Tag Description

Form You create a data entry form by using the

Table You can format a data entry form and display its controls neatly, by using the table

Form Controls

attributes; for example:

Here, the action attribute specifies the name of the ColdFusion file that the web server will navigate to in response to the form’s submission. The specifies how data is returned to the web server. Submit all ColdFusion forms using the

Post method attribute.

tag,

table, table row tag, tr, and the table data tag, td.

The form requires controls to collect and submit user input. There are a variety of types of form controls you can use. For this lesson, you will use the following controls:

•

<input> Accepts text answers such as Trip Name and Trip Price.

•

<input type=checkbox> Asks yes or no questions, such as Deposit Required?

•

<select>,<option> Provides user with a list of possible answers such as the

event type (Mountain Biking, Surfing, and so on).

•

<textarea> Gathers user input on multiple lines such as for the Trip Description.

•

<input type=submit> Posts the information collected to the server.

form tag. The form tag takes two tag

method attribute

3 Save the file as tripedit.cfm to the my_app directory. 4 View the tripedit.cfm in a browser and test the form by entering a trip name in the Trip Name

field then clicking Save. An error occurs.

5 View the form source (tripedit.cfm) in an editor to try to determine the cause of the error.

Notice that the

<form> tag on line 6 of the source code has an action attribute. This attribute

indicates the page to receive the form values posted by the tripedit.cfm page. Since the page, tripeditaction.cfm, does not exist yet, ColdFusion MX sends an error.

At this point, this form does not store any information in the database and does not enforce any business rules of Compass Travel. In the next exercise, you will develop the action page to enforce the business rules.

Developing code to validate data and enforce business rules

As described in Lesson 1: Preparing to Build the Sample Application, it is important to define the right data type for each column on the tables in the database. A fundamental concern, therefore, is ensuring that the captured data is suitable for the column definitions in the Trips table. This type of validation on a single field is often referred to as a single-field edit.

Compass Travel has other operating policies that involve evaluating the values from more than one field. These validations, referred to as cross-field edits, are usually more difficult to program. To assure that new trips are uniformly captured, Compass Travel has published cross-field validations and single-field edits in its Compass Travel business rules.

Developing code to validate data and enforce business rules 95

The following table lists the Compass Travel business rules for capturing and editing trip information. This table identifies which rules require single-field or cross-field editing.

Compass Travel new trip policy Edit type

1 All trips must be named. Single-field

2 All trips must be accompanied by a full description. Single-field

3 Each trip must be categorized by event type. Only valid event types (1-surfing, 2-

mountain climbing, and so on) are permissible

4 Trip locations are required. Single-field

5 The maximum number of people permitted on the trip must be specified. Single-field

6 The trip departure and return dates must be specified for each trip.

All trip dates must be valid future dates. Departure date must precede return date.

7 The trip’s price and base cost are required. Both values are positive numeric

values. The trip price must have at least a 20% markup over base cost.

8 Any trip priced over $750 requires a deposit. Cross-field

9 A trip leader must be identified. Single-field

10 A photo must accompany all new trips. The photo image file must reside within

the images directory of the Compass Travel website.

Single-field

Single-field Cross-field

Cross-field

Single-field

Ways to validate data

ColdFusion provides special tags to simply the process of enforcing business rules. Using ColdFusion, it is possible to enforce business rules in several places. For example, you can enforce some validation edits on the client. Other validation edits, you can enforce on the server after the data entry form is submitted. You will explore these options in the following sections.

Validating data using a server-side action page

The first approach you will take to enforce Compass Travel business rules is to develop an action page to validate the data collected on the data entry form. The action page receives a form variable for every field on the form that contains a value. You use the

cfif tag to test the values of these

fields to ensure that they adhere to Compass Travel business policy.

Using a cfif tag to enforce business rules

The

cfif tag lets you create conditions that evaluate to either True or False. Therefore, to use the

cfif tag to test whether a trip name was entered (business rule 1)on the Trip Edit form, you code

the following cfif statement:

<cfoutput> Trip Name cannot be blank. </cfoutput>

</cfif>

In the previous example, the cfif statement tests to see if the value of the form variable tripName is blank. If the trip name condition evaluates to True, ColdFusion sends "Trip name cannot be blank" to the browser.

Note: The keyword EQ is an operator used to test for equality. For more information about the cfif tag and its operators, see Developing ColdFusion MX Applications.

96 Chapter 8: Lesson 4: Validating Data to Enforce Business Rules

Evaluating check box and radio button variables

Business rule 8 in the Compass Travel new trip policy requires you to test the value of the

depositRequired check box form variable. Check box and radio button variables are only passed

to the action page when the user selects these options on the form. Therefore, an error occurs if the action page tries to use a variable that was not been passed.

To insure an error does not occur, you will use the

IsDefined function in a cfif statement to

determine whether the user selected the Deposit Required check box option on the form:

</cfif>

The cfif statement and the IsDefined function evaluate the value of the form variable

depositRequired to determine if a value exists. The statement not IsDefined returns True if

the specified variable is not found and the

cfset statement sets the form variable to No. No

indicates a deposit is not required; Yes indicates a deposit is required.

Evaluating whether business rules fail

The purpose of the tripeditaction.cfm action page is to update the Compass Travel database, so it is important to make certain that all the business rules are passed successfully before the database insert is executed. Failure of any one of the rules negates the insert.

One approach to ensuring that the action page considers each business rule is to create a local variable with a

The

cfset tag lets you manipulate the value of a variable. For example, the following pseudocode

initializes a variable to a specific value and checks the value using the

<cfset isOk = "Yes"> if rule 1 fails then <cfset isOK = "No" ... if Rule n fails then <cfset isOk = "No"> ... <cfif isOk = "Yes"> update the database </cfif>

cfset tag within the action page that tests to see if any of the business rules failed.

cfif statement:

In the previous example, cfset initializes the local variable isOk to Yes. If any rule fails, the variable

isOK is set to No. The code then tests if isOk equals Yes, before executing the SQL insert

logic.

For more information about using the

cfset and cfif tags and the IsDefined function, see

Developing ColdFusion MX Applications or CFML Reference.

Exercise: create an action page with server-side validation

In this exercise you build an action page (tripeditaction.cfm)to validate the data passed to ColdFusion MX from the tripedit.cfm data entry page. You use the

cfif and cfset tags to build

edits that ensure the data passed is valid per the Compass Travel business rules. Additionally, you will use the ColdFusion

IsDefined function to check to see if data was entered in the data entry

form (tripedit.cfm).

Developing code to validate data and enforce business rules 97

To build trip edit action page and validate data passed:

1 Open an editor and create a new page called tripeditaction.cfm in the my_app directory. The

new page appears as follows:

<title>Untitled</title> </head>

2 To ensure that Compass Travel business rule 7 is met, insert the following code above the

<html> tag on the tripeditaction.cfm page. For your convenience, business rule 7 is repeated.

Business rule 7: The trip’s price and base cost are required. Both values are positive numeric

values. The trip price must have at least a 20% markup over base cost.

<cfif Form.baseCost EQ "" or IsNumeric(Form.baseCost) EQ False>

<cfoutput>Base cost must be a number and cannot be blank.</cfoutput> <cfelse>

<cfoutput>Price must be marked up at least 20% above cost.</ cfoutput><br> </cfif>

</cfif>

Note: The code for business rule 7 uses ColdFusion cfif and cfelse conditional processing tags. The code inside the cfif tags only executes when the condition evaluates to True. To perform other actions when the condition evaluates to False, the cfelse tag is used. For more information about using conditional processing tags, see Developing ColdFusion MX Applications.

3 Save the page. 4 Use the following steps to test the code to see if it meets the objective of business rule 7:

a View the tripedit.cfm page in the browser. b In the form, enter the number 500 in both the Price and Base cost fields. c Click the Save button.

The trip price error message displays: "Price must be marked up at least 20% above cost."

d Click the browser Back button to return to the tripedit.cfm page. e To avoid the error, enter the number 800 in the Price field and click Save.

5 Complete all the business rules using server-side validation. You must insert the code for each

business rule above the

<html> tag. As an example, see the tripeditaction1.cfm page in the

solutions directory.

Tip: You can either modify your new tripeditaction.cfm page to include the code necessary to meet all ten business rules or you can copy the tripeditaction1.cfm page from the solutions directory to tripeditaction.cfm in the my_app directory.

98 Chapter 8: Lesson 4: Validating Data to Enforce Business Rules

6 Test various combinations to make sure all the Compass Travel business rules are enforced by

filling out the fields on the form and clicking save. Testing recommendations:

■ Leave out required fields, such as trip name or location.

■ Enter a non-numeric value in Number of People, such as one.

■ Leave the entire form blank and click Save. The following messages appear:

Trip name cannot be blank. A trip leader must be specified. Photo filename must be specified. The number of people must be a number and cannot be blank. Trip location cannot be blank. Base cost must be a number and cannot be blank. Price must be a number and cannot be blank.

Drawbacks of validating data on the server-side

Validating data on the server-side has two drawbacks. First, since the action page is used for validation, the

form page is not in the browser context when the error is trapped. The user will,

therefore, not get immediate feedback from the page where the data was entered. Second, because data capture occurs on the client and validation on the server, the number of round-trips to the server is increased. This can cause increased traffic on the network and the server. If the data is validated on the client, then only valid data is posted to the server and traffic is reduced.

Validating data on the client using ColdFusion form tags

An alternative approach to server-side editing is to use client-side scripting. Client-side scripting lets you validate the form data entered on the client prior to posting it to the server. CFML provides alternative versions of standard HTML form tags that provide advantages of client-side data validation.These data input tags include

checkbox

, cfselect, and others.

cfinput text, cfinput radio, cfinput

Among the improvements over standard HTML tags, ColdFusion form tags offer the following attributes:

Attribute Description

validate The data type that the field tag validates against. Values include: integer, date, time,

message The error message displayed if validation fails.

range The range of permissible values for this tag.

required An indicator of whether data is required for the corresponding tag.

telephone, zipcode.

Examples of using the improved ColdFusion form tags

To use the improved form tags, you must replace the HTML form tag with the

cfform tag. The

following code snippets show the use of the improved ColdFusion form tags. The first code snippet shows how the duration field is validated on the server. The second code snippet shows how ColdFusion form tags simplify field validation on the client.

Developing code to validate data and enforce business rules 99

Server-side validation approach (no ColdFusion form tag)

The following code is on the server (tripeditaction.cfm page):

<cfif Form.numberPeople EQ "" or IsNumeric(Form.numberPeople) EQ False>

<CFSET IsOk = "No"> <cfoutput>The number of people must be a number and cannot be blank. </cfoutput>

</cfif>

Code Explanation

<cfif Form.numberPeople EQ "" or

IsNumeric(Form.numberPeople) EQ False>

The cfif tag evaluates the value of the form variable

numberPeople to determine if the user entered a value.

The

IsNumeric function checks whether the value

entered on the form was a numeric value.

Client-side validation approach using ColdFusion form tag

The following code is on the client (tripedit.cfm):

<cfinput name="duration" message="Duration must be a number and cannot be

blank." validate="integer" required="Yes" size="3" maxlength="3">

Code Explanation

<cfinput name="duration"

message="Duration must be a number and cannot be blank." validate="integer" required="Yes" size="3" maxlength="3">

Use the cfinput tag to create the duration input entry field within a field as an that the field must have an entry. If the data is not entered or data entered is not an attribute specifies that the message, "Duration must

be...." appears.

cfform. The validate attribute defines the

integer. The required attribute indicates

integer, the message

Exercise: modify Trip Edit page to exploit ColdFusion form tags

In this exercise, you will use the ColdFusion form tags to move the validation of many business rules from the server to the client. To do this, you will change the HTML form tags in the tripedit.cfm page to ColdFusion form tags that validate these fields on the client side. Next, you will remove the unneeded server-side single-field validation code from tripeditaction.cfm page. Finally, you will test the form to ensure the client side validation is working correctly.

To exploit the ColdFusion form tags on the Trip Edit page:

1 Open the tripedit.cfm in the my_app directory in your editor. 2 Locate and change the <form> and </form> tags to <cfform> and </cfform>, respectively. 3 Change the <input> tags to <cfinput> tags and <select> tags to <cfselect> tags. Note that

the input type for the Submit button must remain a standard input rather than

100 Chapter 8: Lesson 4: Validating Data to Enforce Business Rules

cfinput.

Macromedia Getting Started Building ColdFusion MX Applications User Manual

Specifications and Main Features

Frequently Asked Questions

User Manual

CONTENTS

INTRODUCTION

About Macromedia ColdFusion MX documentation

Documentation set

Viewing online documentation

What is ColdFusion MX?

The ColdFusion application server

The ColdFusion Markup Language

The ColdFusion MX Administrator

Using ColdFusion MX with Macromedia Flash MX

Working with ColdFusion pages

Creating a ColdFusion page

Understanding CFML elements

Tags

Functions

Variables

Creating variables with the cfset tag

Displaying variable output

Working with CFML expressions

Building expressions

Understanding conditional processing

Processing form data

Form processing

Commenting your code

Understanding database basics

What is a relational database?

Understanding relational tables

About SQL

Using SQL to interact with a database

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

Verifying the tutorial file structure

Configuring database connection and debugging options

Configuring the connection to the sample database file

Enabling debugging options

Macromedia development environment tools

The Dreamweaver MX environment

Configuring Dreamweaver MX for ColdFusion development

Configuring HomeSite+ for ColdFusion development

Application development steps

Determining the application functional requirements

Determining the data requirements

Designing the database for your application

Developing the sample application

How to proceed

Creating your first ColdFusion application

Application development steps

Using a web page to list trips

Converting to a dynamic web page

Using SQL with cfquery to dynamically retrieve information

Creating a dynamic web page

Developing a search capability

Designing the search criteria page

Building the Search Results page

Completing the Trip Search Results page

Summary

In the next lesson

Enhancing the Trip Maintenance application

Showing additional trip details

Avoiding the potential security risk when using dynamic SQL

Linking the Search Results page to the Trip Detail page

Enhancing the look of the search results and detail pages

Creating the main application page from the Trip Detail page

Adding navigation buttons to browse database

Adding database maintenance buttons

Summary

In the next lesson

Enhancing the Trip Maintenance application

Using an HTML form to collect data

Developing code to validate data and enforce business rules

Validating data using a server-side action page

Validating data on the client using ColdFusion form tags