MACROMEDIA COLDFUSION 5-DEVELOPING, ColdFusion 5 Develop Manual

Developing

ColdFusion Applications

MacroMedia ColdFusion® 5

Macromedia® Incorporated

be used or copied only in accordance with the terms of such license. The content of this manual is furnished for informational use only, is subject to change without notice, and shou ld not be con strued as a com mitment by Mac romedia, Inco rporated. Macromedia, Incorporated assumes no responsibility or liability for any errors or inaccuracies that may appear in this book.

Except as permitted by such license, no part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, recording, or otherwise, without the prior written permission of Macromedia, Incorporated.

ColdFusion, HomeSite, and Macromedia are registered trademarks of Macromedia Incorporation in the United States and internationally. Allaire Spectra, the Allaire Spectra logo, Generator, Macromedia Generation, and JRun are trademarks of Macromedia, Incorporated. Java is a trademark of Sun Microsystems, Inc. Microsoft, Windows, Windows NT , Windows 95, Microsoft Acce ss, and FoxPro are registered trademarks of Microsoft Corporation. PostScript is a trademark of Adobe Systems Inc. Solaris is a trademark of Sun Microsystems Inc. UNIX is a trademark of The Open Group. All other company names, brand names, a nd product names are trademarks of their respective holder(s).

Part number: ZCF50MDEV

Contents

About This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv

Intended Audience . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvi

New Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xvi

Developer Resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii

About ColdFusion Documentation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xviii

Getting Answers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xx

Contacting Macromedia . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xx

Printed and online documentation set . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix

Viewing online documentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix

Printing ColdFusion documentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix

Chapter 1 Introduction to ColdFusion . . . . . . . . . . . . . . 1

A Quick Web Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2

Before You Begin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3

HTML . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3

Relational database design and management . . . . . . . . . . . . . . . . . . . . . . . . . 3

SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3

What is ColdFusion?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4

Editions of ColdFusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4

ColdFusion Features and Components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5

About the features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5

About the components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6

How ColdFusion Server Works . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8

Chapter 2 Writing Your First ColdFusion Application . 9

The Development Process. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10

Working with ColdFusion Application Pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10

About applicaton pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10

Creating application pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10

Saving application pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

Viewing application pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

iv Contents

Working with Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13

About variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13

Adding more variables to the application . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

Development Considerations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16

Chapter 3 Querying a Database . . . . . . . . . . . . . . . . . . 17

Publishing Dynamic Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18

Understanding Database Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19

Understanding Data Sources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20

About Open Database Connectivity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20

Accessing Data Sources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21

Adding data sources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22

Specifying data sources dynamically . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24

Retrieving Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25

The cfquery tag . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25

The cfquery tag syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25

Writing SQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26

Basic SQL syntax elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26

SQL notes and considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27

Building Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28

Query notes and considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29

Outputting Query Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30

Query output notes and considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31

Getting Information About Query Results. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32

Query properties notes and considerations . . . . . . . . . . . . . . . . . . . . . . . . . . 33

Using Query Results in Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34

Query of query benefits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34

Creating queries of queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34

Performing a query on a query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35

Chapter 4 Retrieving and Formatting Data . . . . . . . . . 39

Using Forms to Specify the Data to Retrieve. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40

form tag syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40

Form controls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41

Form notes and considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44

Working with Action Pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45

Processing form variables on action pages . . . . . . . . . . . . . . . . . . . . . . . . . . . 45

Dynamically generating SQL statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45

Creating action pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46

Testing for a variable’s existence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47

Form variable notes and considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48

Working with Queries and Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49

Using HTML tables to display query results . . . . . . . . . . . . . . . . . . . . . . . . . . 49

Formatting individual data items . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50

Performing pattern matching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51

Filtering data based on multiple conditions . . . . . . . . . . . . . . . . . . . . . . . . . . 51

Creating table joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52

Building flexible search interfaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52

Returning Results to the User . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55

If there are no query results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55

Returning results incrementally . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56

Chapter 5 Graphing Data . . . . . . . . . . . . . . . . . . . . . . . . 59

Creating a Graph . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60

Graph types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60

Creating a basic graph . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61

Graphing Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62

Graphing a query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62

Graphing individual data points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65

Combining a query and data points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66

Controlling Graph Appearance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67

Common graph characteristics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67

Setting bar and horizontal bar chart characteristics . . . . . . . . . . . . . . . . . . . 68

Setting pie chart characteristics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71

Setting line and area graph characteristics . . . . . . . . . . . . . . . . . . . . . . . . . . . 74

Linking Dynamically from Graphs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77

Chapter 6 Making Variables Dynamic . . . . . . . . . . . . . 81

Dynamically Populating List Boxes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82

Creating Dynamic Check Boxes and Multiple-Selection List Boxes. . . . . . . . . . . 84

Check boxes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84

Multiple selection lists . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86

Ensuring that Variables Exist . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88

Using cfparam to test for variables and set default values . . . . . . . . . . . . . 88

Requiring users to enter values in form fields . . . . . . . . . . . . . . . . . . . . . . . . 89

Validating Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90

Using cfparam to validate the data type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90

Validating form field data types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91

Checking query parameters with cfqueryparam . . . . . . . . . . . . . . . . . . . . . . 94

Dynamic SQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96

Implementing dynamic SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96

Chapter 7 Updating Your Database . . . . . . . . . . . . . . 101

Inserting Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102

Creating an HTML insert form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102

Developing Web Applications with ColdFusionDRAFT 5/ 9/01 Path/Filen ame/Author

vi Contents

Data entry form notes and considerations . . . . . . . . . . . . . . . . . . . . . . . . . . 103

Creating an action page to insert data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103

Updating Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106

Creating an update form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106

Creating an action page to update data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109

Deleting Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112

Deleting a single record . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112

Deleting multiple records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113

Chapter 8 Handling Complex Data

with Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115

About Arrays. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116

Basic Array Techniques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118

Creating an array . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118

Adding elements to an array . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119

Referencing elements in dynamic arrays . . . . . . . . . . . . . . . . . . . . . . . . . . . 119

Populating Arrays with Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121

Populating an array with ArraySet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121

Populating an array with cfloop . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121

Using nested loops for 2D and 3D arrays . . . . . . . . . . . . . . . . . . . . . . . . . . . 122

Populating an array from a query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123

Array Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124

About Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125

Structure notation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126

Creating and Using Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127

Creating structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127

Adding data elements to structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127

Updating values in structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128

Getting information about structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129

Copying structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130

Deleting structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130

Structure example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130

Looping through structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132

Structure Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133

Chapter 9 Building Dynamic Forms . . . . . . . . . . . . . . 135

Creating Forms with the cfform Tag . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136

Using HTML and cfform . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136

cfform controls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137

Preserving input data with preservedata . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137

Browser considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138

Input Validation with cfform Controls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139

Validating with regular expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140

vii

Input Validation with JavaScript . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144

Handling failed validation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144

Example: validating an e-mail address . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144

Building Tree Controls with cftree. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147

Grouping output from a query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148

cftree form variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149

Input validation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149

Structuring Tree Controls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150

Image names in a cftree . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151

Embedding URLs in a cftree . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152

Specifying the tree item in the URL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153

Creating Data Grids with cfgrid . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153

Populating a grid from a query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154

Creating an Updateable Grid . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155

Navigating and entering data in a grid . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155

Controlling cell contents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156

How user edits are returned . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156

Editing data in cfgrid . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157

Updating the database with cfgridupdate . . . . . . . . . . . . . . . . . . . . . . . . . . . 159

Updating the database with cfquery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160

Building Slider Bar Controls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164

Building Text Entry Boxes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165

Building Drop-Down List Boxes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166

Embedding Java Applets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167

Registering a Java applet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167

Using cfapplet to embed an applet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169

Handling form variables from an applet . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169

Chapter 10 Reusing Code . . . . . . . . . . . . . . . . . . . . . . 171

Ways to Reuse Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172

Reusing Common Code with cfinclude . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172

Using Custom Tags . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173

Using existing custom tags . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174

Creating custom CFML tags . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174

Variable scopes and special variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174

Using tag attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175

Passing values to and from custom tags . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176

Passing custom tag attributes via CFML structures . . . . . . . . . . . . . . . . . . 177

Custom tag example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178

Nesting Custom Tags . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180

Passing Data Between Nested Custom Tags . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181

What data is accessible? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181

Developing Web Applications with ColdFusionDRAFT 5/ 9/01 Path/Filen ame/Author

viii Contents

Where is data accessible? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181

High-level data exchange . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181

Executing Custom Tags . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185

Tag instance data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185

Modes of execution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185

Specifying execution modes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185

Terminating tag execution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186

Access to generated content . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187

Installing Custom Tags. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187

Local tags . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187

Shared tags . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187

Managing Custom Tags . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188

Resolving filename conflicts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188

Securing custom tags . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 88

Encoding custom tags . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189

Chapter 11 Preventing and Handling Errors . . . . . . . 191

Debug Settings in the ColdFusion Administrator . . . . . . . . . . . . . . . . . . . . . . . . . 192

Generating debug information for an individual page . . . . . . . . . . . . . . . . 192

Generating debug information for an individual query . . . . . . . . . . . . . . . 192

Error messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192

CFML Code Validation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194

Runtime validation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194

The CFML syntax checker . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194

Troubleshooting Common Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195

ODBC data source configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195

HTTP/URL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195

CFML syntax errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195

Error Handling in ColdFusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197

Understanding ColdFusion errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197

Generating Custom Error Messages with cferror . . . . . . . . . . . . . . . . . . . . . . . . . 199

Creating an error application page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200

Logging Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202

Handling Exceptions in ColdFusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204

Types of recoverable exceptions supported . . . . . . . . . . . . . . . . . . . . . . . . . 205

Exception information in cfcatch . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206

Exception handling strategies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208

Exception handling example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208

Custom Exception Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210

Chapter 12 Using the Application Framework . . . . . 213

Understanding the Web Application Framework. . . . . . . . . . . . . . . . . . . . . . . . . . 214

Application-level settings and functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . 214

Client, Session, Application, and Server scope variables . . . . . . . . . . . . . . 214

Custom error handling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215

Web server security integration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215

Mapping an Application Framework . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216

Processing Application.cfm and OnRequestEnd.cfm . . . . . . . . . . . . . . . . . 216

Defining the directory structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217

Creating the Application.cfm File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 219

Naming the application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 19

Setting application default variables and constants . . . . . . . . . . . . . . . . . . 219

Managing the Client State. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221

About Client and Session variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221

About client cookies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222

Managing client state in a clustered environment . . . . . . . . . . . . . . . . . . . 222

Managing client state without cookies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222

Configuring and Using Client Variables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223

Setting up Client variable options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223

Using Client variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 224

Using Session Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228

Enabling Session variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228

What is a session? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228

Storing session data in Session variables . . . . . . . . . . . . . . . . . . . . . . . . . . . 229

Standard Session variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229

Getting a list of Session variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230

Using Application Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230

Storing application data in Application variables . . . . . . . . . . . . . . . . . . . . 230

Application variable timeouts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231

Tips for using Application variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231

Getting a list of Application variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231

Using Server Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 232

Locking Code with cflock. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233

Using cflock . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233

How cflock works . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233

Using administrative lock management . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236

Nesting locks and avoiding deadlocks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237

Examples of cflock. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 238

Chapter 13 Extending ColdFusion Pages with CFML

Scripting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243

About CFScript. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244

CFScript example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244

Supported statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244

The CFScript Language . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245

Comments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245

Developing Web Applications with ColdFusionDRAFT 5/ 9/01 Path/Filen ame/Author

x Contents

Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245

Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245

Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245

Reserved words . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 248

Differences from JavaScript . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 248

Interaction of CFScript with CFML. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249

Defining and Using Custom Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 250

Defining functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 250

Calling functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 251

Using arguments and variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 251

Identifying custom functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 253

Examples of custom functons . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 253

Using custom functions effectively . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254

Chapter 14 Using Regular Expressions in Functions . . . .

259

About Regular Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260

Basic Regular Expression Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 261

Character classes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 261

Multicharacter Regular Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263

Limiting input string size . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 264

Anchoring a regular expression to a string . . . . . . . . . . . . . . . . . . . . . . . . . . 264

Using Backreferences. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 265

Using backreferences in replacement strings . . . . . . . . . . . . . . . . . . . . . . . 265

Returning Matched Subexpressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 266

Regular Expression Examples. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267

Regular expressions in CFML . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267

Chapter 15 Indexing and Searching Data . . . . . . . . . 269

Searching a ColdFusion Web Site. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 270

Advantages of using Verity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 270

Supported File Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271

Support for International Languages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 272

Creating a Searchable Data Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 273

Creating a Collection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 273

Populating and indexing a collection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 276

Selecting an indexing method . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 277

Building a search interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279

Indexing Query Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 282

Indexing database query results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 282

Indexing cfldap query results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 283

Indexing cfpop query results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 284

Using Query Expressions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 285

Simple query expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 285

Explicit query expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 286

Expression syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 286

Composing search expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287

Searching with wildcards . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 288

Searching for special characters as literals . . . . . . . . . . . . . . . . . . . . . . . . . . 289

Operators and modifiers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290

Modifiers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297

Managing Collections. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298

Maintenance options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298

Securing a collection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298

Chapter 16 Sending and Receiving E-mail . . . . . . . . 301

Using ColdFusion with Mail Servers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302

Sending E-mail Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302

Sending SMTP mail with cfmail . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 303

Sample Uses of cfmail . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 304

Sending form-based e-mail . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 304

Sending query-based e-mail . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 304

Sending e-mail to multiple recipients . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 305

Customizing E-mail for Multiple Recipients. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 306

Attaching a MIME file . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 307

Advanced Sending Options. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 308

Sending mail as HTML . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 308

Error logging and undelivered messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . 308

Receiving E-mail Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 309

Using cfpop . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 309

cfpop query variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 310

Handling POP Mail . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 310

Retrieving only message headers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 310

Retrieving an entire message . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312

Retrieving attachments with messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313

Deleting messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 314

Chapter 17 Managing Files on the Server . . . . . . . . . 317

Using cffile . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 318

Uploading Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 319

Resolving conflicting filenames . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 321

Controlling the type of file uploaded . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 321

Setting File and Directory Attributes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 323

Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 323

UNIX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 323

Developing Web Applications with ColdFusionDRAFT 5/ 9/01 Path/Filen ame/Author

xii Contents

Evaluating the Results of a File Upload . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 324

Moving, Renaming, Copying, and Deleting Server Files. . . . . . . . . . . . . . . . . . . . 326

Reading, Writing, and Appending to a Text File . . . . . . . . . . . . . . . . . . . . . . . . . . . 327

Reading a text file . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 327

Writing a text file . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 328

Performing Directory Operations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 329

Returning file information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 329

Chapter 18 Interacting with Remote Servers . . . . . . 331

Using cfhttp to Interact with the Web. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 332

Using the cfhttp Get Method . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 332

Creating a Query from a Text File. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335

Using the cfhttp Post Method. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 337

Performing File Operations with cfftp . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 341

Caching connections across multiple pages . . . . . . . . . . . . . . . . . . . . . . . . 343

Connection actions and attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 344

Moving Complex Data Structures Across the Web with WDDX . . . . . . . . . . . . . 345

An overview of distributed data for the Web . . . . . . . . . . . . . . . . . . . . . . . . 345

WDDX and Web Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 346

WDDX components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 346

Working with application-level data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 347

Data exchange across application servers . . . . . . . . . . . . . . . . . . . . . . . . . . . 347

How WDDX works . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 348

Converting CFML Data to a JavaScript Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . 349

Transferring Data from Browser to Server. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 350

Storing Complex Data in a String. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 353

Chapter 19 Application Security . . . . . . . . . . . . . . . . . 355

ColdFusion Se cur ity Features. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 356

Remote Development Services (RDS) Security. . . . . . . . . . . . . . . . . . . . . . . . . . . . 356

Overview of User Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 357

Using Advanced Security in Application Pages. . . . . . . . . . . . . . . . . . . . . . . . . . . . 358

Using the cfauthenticate tag. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 359

Authentication and Authorization Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 360

Using the IsAuthenticated function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 360

Using the IsAuthorized function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 360

Catching Security Exceptions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 361

Using the cfimpersonate Tag . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 362

Example of User Authentication and Authorization . . . . . . . . . . . . . . . . . . . . . . . 363

xiii

Authenticating users in Application.cfm . . . . . . . . . . . . . . . . . . . . . . . . . . . . 363

Checking for authentication and authorization . . . . . . . . . . . . . . . . . . . . . 365

Chapter 20 Using cfobject to Invoke Component Objects .

367

Component Object Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 368

About COM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 368

About CORBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 368

About Java objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 368

Invoking Component Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 369

Using properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 369

Calling methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 369

Calling nested objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 370

Getting Started with COM/DCOM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 370

Requirements for COM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 370

Registering the object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 371

Finding the component ProgID and methods . . . . . . . . . . . . . . . . . . . . . . . 371

Creating and Using COM Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 374

Connecting to COM objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 374

Setting properties and invoking methods . . . . . . . . . . . . . . . . . . . . . . . . . . . 375

Getting Started with CORBA. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 376

Calling CORBA Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 376

Declaring structures and sequences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 376

Exception handling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 378

Calling Java Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 378

Getting Started with Java . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 378

Java and Cold Fusion Data Type Conversions . . . . . . . . . . . . . . . . . . . . . . . 381

Exception Handling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 382

The class loading mechanism . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 384

A more complex Java example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 385

Chapter 21 Building Custom CFXAPI Tags . . . . . . . . 389

What Are CFX Tags?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 390

Before You Begin Developing CFX Tags in Java. . . . . . . . . . . . . . . . . . . . . . . . . . . . 391

Sample Java CFXs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 391

Setting up your development environment to develop CFXs in Java . . . 391

Customizing and Configuring Java . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 392

Writing a Java CFX. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 393

Processing requests . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 393

Loading Java CFX classes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 395

Automatic class reloading . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 395

Life cycle of Java CFXs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 396

Calling the CFX from a ColdFusion page . . . . . . . . . . . . . . . . . . . . . . . . . . . . 396

Developing Web Applications with ColdFusionDRAFT 5/ 9/01 Path/Filen ame/Author

xiv Contents

ZipBrowser Example. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 397

Approaches to Debugging Java CFXs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 398

Outputting debug information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 398

Using the debugging classes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 399

Developing CFX Tags in C++. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 401

Sample C++ CFXs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 401

Setting up your C++ development environment . . . . . . . . . . . . . . . . . . . . . 401

Using the Tag Wizard to create CFXs in C++ . . . . . . . . . . . . . . . . . . . . . . . . 401

Compiling C++ CFXs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 402

Implementing C++ CFX tags . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 402

Debugging C++ CFXs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 402

Registering CFXs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 404

Distributing CFX Tags. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 405

Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 407

About This Book

Developing ColdFusion Applications describes the process of developing Web applications using ColdFusion. In the first eight chapters, you can follow the instructions presented to learn how to create basic ColdFusion applications. Then, chapters nine through 19 cover various topics of interest in enhancing your applications. Finally, chapters 20 through 23 explain how to extend ColdFusion’s capabilities.

Because of the power and flexibility of ColdFusion, you can create many different types of Web applications of varying complexity. As you become more familiar with the material presented in this manual, and begin to develop your own applications, you will want to refer to the CFML Reference for details about various tags and functions.

Contents

• Intended Audience...................................................................................................xvi

• New Features ............................................................................................................xvi

• Developer Resources...............................................................................................xvii

• About ColdFusio n Documentatio n....................................... ...... ....... ...... ............ xviii

• Getting Answers.........................................................................................................xx

• Contacting Macromedia............................................................................................xx

xvi About This Book

Intended Audience

Developing ColdFusion Applications is intended for Web application programmers who are learning ColdFusion orwish to extended their ColdFusion programming knowledge. It provides a solid grouding in the tools that ColdFusion provides to develop Web applications. The initial chapters provide e instructions for creating a basic ColdFusion application and are intended for those who are new to ColdFusion. Later chapters cover more specific features in greater detail and are intended for both new ColdFusion programmers and for those who are looking to extend existing skill.

New Features

The following table lists the new features in ColdFusion 5:

Benefit Feature Description

Breakthrough productivity

Powerful business intelligence capabilities

User-defined functions Create reusable functions to

accelera te development.

Query of queries

Easily integrate data from heterogeneous so ur ce s by merging and querying data in memory using standard SQL.

Server analysis and troublshooting

Quickly detect and diagnose server errors with built-in server reporting and the new Log File Analyzer.

Charting engine

Create professional-quality charts and graphs from queried data without leaving the ColdFusion environment.

Enhanced V erity K2 full-text search

Index and search up to 250,000 documents and enjoy greater performance.

Reporting interface for Crystal Reports 8.0

Create professional-quality tabular reports from queried data and applications.

Developer Resources xvii

Benefit Feature Description

Enhanced performance

Easy managment

Core engine tuning

Incremental page delivery

Wire protocol database drivers

Application deployment services

Enhanced application monitoring

SNMP support

Take advantage of dramatically improved server performance and reduced memory usage to deliver faster, more scalable applications.

Improve response time by delivering page output to users as it is built.

Deliver high-performance ODBC connectivity using new drivers.

Effortlessly and reliably deploy, archive, or restore entire applications using ColdFusion archive files.

Keep track of server performance and availability with customizable alerts and recovery.

Monitor ColdFusion appli catio ns from enterprise manageme nt systems.

Expanded integration

Expanded Linux support

Enhanced hardware load balancer integration

Enhanced COM support

Developer Resources

Macromedia, Inc. is committed to setting the standard for customer support in developer education, technical support, and professional services. The Web site is

Deploy on additional Linux distributions, including SuSE and Cobalt.

Apply optimized, agent-based support for hardware load balancers, including new support for the Cisco CSS 11000.

Experience easier integration with COM components.

xviii About This Book

designed to give you quick access to the entire range of online resources, as the following table describes.

Resource Description URL

Macromedia W eb site

Information on ColdFusion

Technical Support

ColdFusion Support Forum

Installation Support

Professional Education

Developer Community

General information about Macromed ia products and services

Detailed product information on ColdFusion and related topics

Professional supp ort programs that Macromedia offers.

Access to experienced ColdFusion developers through participation in the Online Forums, where you can post messages and read replies on many subjects relating to ColdFusion.

Support for installation-related issues for all Macromedia prod uc t s

Information about classes, on-site training, and online courses offered by Macromedia

All the resources that you need to stay on the cutting edge of ColdFusion development, including online discussion groups, Knowledge Base, technical papers and more

www.macromedia.com/

www.coldfusion.com/products/coldfusion/

www.coldfusion.com/support/

http://forums.coldfusion.com/spectraconf/

www.coldfusion.com/support/installation/

www.coldfusion.com/developer/training.cfm

www.coldfusion.com/developer/

ColdFusion Dev Center

Macromedia Alliance

Development tips, articles, documentation, and white papers

Connection with the growing network of solution providers , appli ca tio n developers, resellers, and hosting services creating solutions with ColdFusion

www.coldfusion.com/developer/ coldfusionreferencedesk/

www.coldfusion.com/partners/

About ColdFusion Documentation

ColdFusion documentation is designed to provide support for the complete spectrum of particip ant s . T he p rint and online versions are or ga niz ed to a llow you to quickly locate the information that you need. The C oldF usion online documentation is provided in HTML and Adobe Acrobat formats.

About ColdFusion Documentation xix

Printed and online documentation set

The ColdFusion documentation set consists of the following titles.

Book Description

Installing and Configuring ColdFusion Server

Advanced ColdFusion Server Administration

Developing ColdFusion Applications

CFML Reference The online-only ColdFusion Reference provides descriptions,

CFML Quick Reference

Using ColdFusion Studio

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

Describes how to connect your data sources to the ColdFusion Server, configure security for your applications, and how to use ClusterCATS to manage scalability, clustering, and load-balancing f or your site

Describes on how to develop your dynamic Web applications, including retrie ving an d updatin g your da ta, using st ructures , and forms.

syntax, usage, and code examples for all ColdFusion tags, functions, and variables.

A brief guide that shows the syntax of ColdFusion tags, functions, and variables

Describes how to use ColdFusion Studio to build, test, and deploy Web content, including using the built-in editor for a variety of scripting and markup languages

Viewing online documentation

All ColdFusion documentation is available online in HTML and Adobe Acrobat PDF formats. To view the HTML documentation, open the following URL on the Web server running ColdFusion: http://localhost/coldfusion/docs/dochome.htm.

To view and print ColdFusion documentation in Acrobat format, open the following URL on the Web server running ColdFusion: http://localhost/coldfusion/docs/ AcrobatDocs/index.htm.

Printing ColdFusion documentation

To read printed documentation, lo cate the Adob e Acroba t PDF files installe d with the product. The PDF files offer excelle nt print output. You can print an entire book or individual sections.

Locate the ColdFusion PDF files by opening the following URL on the host system: http://localhost/coldfusion/docs/AcrobatDocs/index.htm

xx About This Book

Getting Answers

One of the best ways to solve particular programming problems is to tap into the vast expertise of the ColdFusion developer communities on the ColdFusion Forums. Other deve loper s on the f orum ca n he lp y ou fig ur e o ut how to do just about anyt hing with ColdFusion. The search facility can also help you search messages from the previous 12 months, allowing you to learn how others have solved a problem that you might be facing. The F o rums is a gr e at r es our ce for learning C o ldFu sion, bu t it is also a great place to see the ColdFusion developer community in action.

Contacting Macromedia

Corporate headquarters

Technical support

Sales

Macromedia, Inc. 600 Townsend street San Francisco, CA 4103

Tel: 415.252.2000 Fax: 415.626.0554

Web: www.macromedia.com

Macromedia offers a range of telephone and Web-based support options. Go to http://www.coldfusion.com/ support/ for a complete description of technical support services.

You can mak e postings to the ColdFusion Support Forum (http://forums.coldfusion.com/DevConf/index.cfm) at any time.

Toll Free: 888.939.2545 Tel: 617.219.2100

Fax: 617.219.2101

E-mail: sales@macromedia.com Web: http://commerce.coldfusion.com/purchase/

index.cfm

Chapter 1

Introduction to ColdFusion

This chapter explains the difference between creating static Web pages with HTML and creating dynamic applications with ColdFusion. It also describes what ColdFusion is and how it works.

Contents

• A Quick Web Overview................................................................................................2

• Before You Begin .........................................................................................................3

• What is ColdFusion?................................................................................... ....... ...... ....4

• ColdFusion Features and Components.....................................................................5

• How ColdFusion Server Works...................................................................................8

2 Chapter 1 Introduction to ColdFusion

A Quick Web Overview

Over the last few years, the Web changed from being simply a collection of static HTML pages to an application development platform. Rather than offering a space where organizations can merely advertise goods and services, similar to traditional yellow pages directories, companies conduct business ranging from e-commerce to managing internal business processes. For example, a static HTML page allows a bookstore to publish its location, list services such as the ability to place special orders, and advertise upcoming events like book signings. A dynamic site for the same bookstore allows customers to order books online, write reviews of books they read, and even get suggestions for purchasing books based on their reading preferences.

ColdFusion is a rapid application development environment that lets you build dynamic sites. You can use the Web to handle business transactions and condu ct th e day -t o - da y bu siness of your organi z a tion.

Before You Begin 3

Before You Begin

Before you begin using ColdFusion to create your Web applications, you should be familiar with HTML, relational database design and management, and Structured Query Language (SQL).

HTML

You will find that ColdFusion tags (CFML) are similar in syntax to HTML tags, yet, unlike HTML, they enable you to cr e ate dy namic Web pages. You should understand how to create a basic HTML page, put information into tables, gather data in forms, and create links.

Relational database design and management

If you plan on creating applications that use data from existing data sources, you should understand how the data is organized. In most cases, this means understanding how tables are organized to prevent unnecessary duplication of data. For example, if you have data about employees, rather than repeating the department number and name in each employee’s record, you most likely have a separate table that lists each department number and name just once.

SQL

Familiarity w ith some SQL is helpful as you de velop yo ur ColdF usi on applications . I n particular, you should be able to use the SELECT, UPDATE, INSERT, and DELETE statements, as well as WHERE clauses and Boolean logic operators.

4 Chapter 1 Introduction to ColdFusion

What is ColdFusion?

ColdFusion lets you create page-based Web applications using ColdFusion Markup Language (CFML), the tag-based language you use to create server-side scripts that dynamically control data integration; application logic; and user interface generation. ColdFusion Web applications can contain XML, HTML, and other client technologies such as CSS and JavaScript.

ColdFusion application pages are different from static HTML pages in the following ways:

• They are saved and referen ced with a specific file ex tension.

• The default ColdFusion file extension is cfm.

• They contain ColdFusion Markup Language.

Editions of ColdFusion

There are two editions of ColdFusion: Enterprise and Professional. Using ColdFusion Enterprise or Professional Edition and ColdFusion Studio, you can build Web applications that leverage existing technologies and business systems such as RDBMS, messagin g servers, file rep ositories , dire ctory servers, an d distributed object middleware. ColdFusion Enterprise also offers advanced security features, load balancing, server failover, and visual cluster administration.

ColdFusion Features and Components 5

ColdFusion Features and Components

ColdFusion provides a comprehensive set of features and components for developing and managing your Web applications. Using the ColdFusion components, you can enhance the speed and ease of development, dynamically deploy your applications, integrate new and legacy technologies, and build secure applications.

About the features

The following table describes the C oldfF us ion featur es that let you mana ge your Web site:

Benefits Features

Rapid development

Scalable deployment

• A tag-based server scripting language that is powerful and intuitive

• Two-way visual programming and database tools

• Remote interactive debugging for quickly identifying and fixing

problems

• Web application wizards to automate common development tasks

• Source control integration to enable team development

• Secure file and database access using HTTP for remote

development

• A tag-based component architecture for flexible code reuse

• A multithreaded service architecture that scales across

processors

• Database connection pooling to optimize database performance

• Just-in-time page compilation and caching to accelerate page

request processing

• Dynamic load balancing for scalable performance in a cluster environment (Enterpris e Editi on only)

• Automatic server recovery and failover for high availability (Enterprise Edition only)

6 Chapter 1 Introduction to ColdFusion

Benefits Features

Open integration • Database c onn ec tiv ity us in g na tiv e d atabase drivers (Enterprise

Edition only), ODBC, or OLE DB

• Embedded support for full-text indexing and searching

• Sta ndards-based integration with directory, mail, HTTP, FTP, and

file servers

• Connectivity to distributed object technologies, including CORBA (Enterprise Edition only), COM (Windows Enterprise Edition only), Java objects and EJBs

• Open extensibility with C/ C++ and Jav a

Security • Integration with existing authenticati on systems, including

Windows NT domain and LDAP directory servers, and proprietary user and group databases

• Advanced access control so that server administrators can control developers’ access to files and data sources

• Support for existing database security

• Server sandbox secu rity for prot ecting mu ltiple appli catio ns on a

single server (Enterprise Edition only)

• Support for existing Web server authentication, security, and encryption

For detailed information about security, see Advanced ColdFusion Administration. For the latest publications from Macromedia on security, visit the Security Zone at

http://www.coldfusion.com/de veloper/securityzone/. For a complete feature list and more detailed information, see the ColdFusion

product pages at http://www.coldfusion.com/coldfusion.

About the components

ColdFusion applications rely on several core components:

• ColdFusion application pages

• ColdFusion Server

• ColdFusion Administrator

• ODBC data sources and other data sources

In addition to the core components, as you become more familiar with ColdFusion and build more complex applications, you can use ColdFusion Extensions to extend its capabilities.

ColdFusion application pages

ColdFusion application pages (often called templates) look somewhat like HTML pages, but are much more dynamic and powerful. They are the functional parts of a ColdFusion application, including the user interface pages and forms that handle

ColdFusion Features and Components 7

data input and format data output. They can contain ColdFusion (CFML) tags, HTML tags, CFScript, JavaScript, and anything else that you can normally embed in an ordinary HTML page. You can easily access data sources, such as relational databases, from your application pages. The default file extension used for ColdFusion application pages is cfm.

CFML

CFML is a tag-based server scripting language that encapsulates complex processes, such as connecting to databases and LDAP servers, and sending e-mail. The core of the ColdFusion developm ent platf orm language is mor e than 70 server -side tags and more than 200 functions.

ColdFusion Server

ColdFusion Server listens for requests from the Web server to process ColdFusion application pages. It runs as a service under Windows NT and as a process under UNIX.

For information on installing and configuring ColdFusion Server, see Installing and Configuring Col dFusion Server.

ColdFusion Administrator

You use the Administra tor to conf igur e vari ous Cold Fu sion Se rver option s, incl uding:

• ColdF usio n dat a sources

• Debugging output

• Server settings

• Application security

• Server clustering

• Scheduling pa g e ex ecu t ion

• Directory mapping

For details on using the Administrator, see Advanced ColdFusion Administration.

Data sources

ColdFusion applications can interact with any database that supports the ODBC standard. However, ColdFusion is not limited to ODBC data sources. You can also retrieve data using OLE-D B, nativ e database drivers, or dir ectory servers th at suppo rt the Lightweight Directory Access Protocol (LDAP). You can also retrieve data from mail servers that support the Post Office Protocol (POP), and index the data in Verity collections.

8 Chapter 1 Introduction to ColdFusion

How ColdFusion Server Works

Regardless of which ColdFusion Server you have installed, ColdFusion application pages are processed on the server at runtime, each time they are requested by a browser.

A page request happens when you click a Web site link to open a Web page in your browser. When you request a ColdFusion application page, ColdFusion Server processes the reques t, retrieves any data if nece ssary, and routes the data through the Web server, back to your browser.

The following steps describe in more detail what h appens when you open a ColdFusion page:

1 The client requests a page that contains CFML tags. 2 The Web server passes files to ColdFusion Server if a page request contains a

ColdFusion file extension. 3 ColdFusion Server scans the page and processes all CFML tags. 4 ColdFusion Server then returns only HTML and other client-side technologies to

the Web server. 5 The Web server passes the page back to the browser.

Chapter 2

Writing Your First ColdFusion Application

This chapter guides you through the ColdFusion development process as you create a ColdFusion application page, save it, and view it in a browser.

Contents

• The Development Process........................................................................................10

• Working with ColdFusion Application Pages..........................................................10

• Working with Variables.......................... ...... ....... ...... ....... ....... ..................................13

• Development Considerations..................................................................................16

10 Chapter 2 Writing Your First ColdFusion Application

The Development Process

Whether you are creating a static HTML page or a ColdFusion application page, you follow the same iterative process:

1Write some code. 2 Save the code to a document or page. 3 View the page in a browser. 4 Modify the page. 5 Save the page again. 6 View it in a browser.

Working with ColdFusion Application Pages

While you can code your application pages using NotePad or any HTML editor, this book uses ColdFusion Studio because it provides many features that make ColdFusion development easier. You should install ColdFusion Studio if you have not done so already.

About applicaton pages

From a coding perspective, the major difference between a static HTML page and a ColdFusion application page is that ColdFusion pages contain ColdFusion Markup Language (CFML). CFML is a markup language that is very similar in syntax to HTML, so Web developers find it intuitive. Unlike HTML, which defines how things are displayed and formatted on the client, CFML identifies specific operations that are performed by ColdFusion Server.

Creating application p ages

The following procedure creates a simple ColdFusion Application page, which you use for other examples in this chapter.

To create a ColdFusion application page:

1Open ColdFusion Studio. 2Select File > New and select the Default Template for your new page. 3 Edit the file so that it appears as follows:

<html>

<head>

<title>Call Department</title>

</head>

<body>

<strong>Call Department</strong><br>

Working with ColdF usion Application Pages 11

I’d like to talk to someone in #Department#.

</cfoutput>

</body>

</html>

Saving application pages

Instead of saving pages with an htm or html file extension, you save ColdFusion application pages with a cfm or cfml extension. By default, the Web server knows to pass a page that contains a cfm extension to the ColdFusion Server when it is requested by a browser.

Save ColdFusion application pages underneath the Web root or another Web server mapping so that the Web server can publish these pages to the In ternet. F or example , you can create a directory

myapps and save your practice pages there.

To save the page:

1Select File > Save. 2 Save your page as calldept.cfm in myapps under the Web root directory.

For example, the directory path on your machine might be:

(on Windows NT) c:\inetpub\wwwroot\myapps

(on UNIX) <mywebserverdocroot>/myapps

Viewing application pages

Y o u view the appli cation page on the W e b server to ensur e that the cod e is working as expected. Presently, your page is very simple. But, as you add more code, you will want to ensure that the page continues to work.

To view the page in a local browser:

1 Open a Web browser on your local machine and enter the following URL:

http://127.0.0.1/myapps/calldept.cfm

where 127.0.0.1 refers to the localhost and is only valid when you are viewing

pages locally. 2 Use the Web browser facility that allows you to view a page’s source code to

examine the code that the browser uses for rendering.

Note that only HTML and text is returned to the browser.

12 Chapter 2 Writing Your First ColdFusion Application

Compare the code that was returned to the browser with what you originally

created. Notice that the ColdFusion comments and CFML tags are processed, but

do not appear in the HTML file that is returned to the browser.

Original ColdFusion page HTML file returned by Web server

<html> <head> <title>Call Department</title> </head> <body> <strong>Call Department</strong><br>  <cfset department="Sales">  <cfoutput> I’d like to talk to someone in #Department#. </cfoutput> </body> </html>

<html> <head> <title>Call Department</title> </head> <body> <strong>Call Department</strong><br>

I’d like to talk to someone in Sales.

</body> </html>

Reviewing the code

The application p a ge th at you just created conta in s bo th HT ML an d C F ML. You used the CFML tag then used the CFML tag c following table describes the code and its function:

Code Description

<cfoutput> I’d like to tal k to som eone in #Department#. </cfoutput>

cfset to define a variable, Department, and set its value to “Sales.” You

foutput to display text and the value of the variable. The

CFML comment, which is not returned in the HTML page.

Creates a variable named Department and sets the value equal to Sales.

CFML comment, which is not returned in the HTML page.

Displays whatever appears between the opening and closing

cfoutput tags; in this example, the

text “I'd like to talk to someone in” is followed by the value of the variable Department, which is “Sales.”

Working with Variables 13

Working with Variables

A Web application page is different from a static Web page because it can publish data dynamically. This involves creating, manipulating, and outp utting variables.

A variable stores data that you can u se in applications. As with other programming languages, you se t variables in ColdF usi on to stor e data tha t you want to acc ess later. You reference a range of variables to per form different types of application processing.

About variables

ColdFusion variable names are case-insensitive. The variable names CITY and city refer to the same data.

The kind of information that variables contain varies. T wo characteristics distinguish the information in a variable:

• Data type

• Scope type

Data types

Scope types

A variable’s data type specifies the kind of value a variable can represent, such as a text string or number. ColdFusion does not require you to specify a variable’s data type. Whether a variable represents a string, a number, a Boolean value (Yes/No), a date and time, or a more complex object such as an array or structure, ColdFusion automatically uses the appropriate internal data representation when you assign its value. However, ColdFusion does provide methods to examine and change the type of data that a variable represents. For a complete list of data types see the CFML Reference.

For example, use the following syntax to create a string variable:

The following example uses scientific notation to create a floating-point numeric variable:

Variables differ in the source the data came from, the places in your code where they are meani ngful, a nd ho w long thei r values pe rsist. Th ese consi dera tions ar e gene rally referred to as a variable’s scope.

ColdFusion has many different scope ty pes, which are identified by prefixes to a variable name. For example, the variable Department in calldept.cfm is a local variable (a variable that has meaning on the current page). Loca l variables have the optional prefix Variables. Instead of writing:

I’d like to talk to someone in #Department#.

14 Chapter 2 Writing Your First ColdFusion Application

you can write:

I’d like to talk to someone in #Variables.Department#.

Some variable scopes, such as the local scope, do not require the scope identifier prefix, while others do. However, it is good programming practice to use prefixes for most or all scopes. This helps to better identify each variable’s use and can prevent multiple uses of the same name. This book uses the scope prefix for all variables except for local variables.

The following table lists some of the more common types of variable scopes and the prefixes that you use to identify the variabl es. Other chapters i n this book discuss additional scop e types. The CFML Reference has a complete list of scope types, their identifiers, and how they are used.

Scope type Prefix Description

Local (or Variables)

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

URL URL Variables passed to a page as URL query string

Using the pound sign (#)

You surround a ColdFusion variable or function with pound signs (#) to tell the ColdFusion Server that it is not plain text. You only need to use pound signs in limited circumstances, particularly in the cfoutput and cfquery tag blocks. You do not need to use pound signs when you create a variable, assign it a value, or use it in a ColdFusion expression or as a parameter in a ColdFusion function.

Note

Remember that ColdFusion cannot interpret anything, including variables, that is not inside a ColdFusion tag or tag block.

V ariab les V aria bles create d using cf set or cfp aram, w ith or withou t

specifying the s cope prefix . You must define the variable on the current page or a page you include using

cfinclude.

cfform tag block and proc essed on an action page.

parameters.

The follow ing table illust rates the ba sic u se of po und si gns . F or a detaile d descriptio n of the use of pound signs, see CFML Reference.

CFML code Results

cfset Department="Sales">

<cfoutput> I’d like to talk to someone in Department. </cfoutput>

The variable named Department is created and the value is set to Sales.

ColdFusion does not treat Department as a variable because it is not surrounded by pound signs. The HTML page displays:

I’d like to talk to someone in Department.

Working with Variables 15

CFML code Results

<cfoutput> I’d like to talk to someone in #Department#. </cfoutput>

ColdFusion replaces the variable Department with its value. The HTML page displays:

I’d like to talk to someone in Sales.

<cfoutput> The department name spelled backward is Reverse(Department). </cfoutput>

ColdFusion sees Reverse(Department) as text and displays it unchanged. The HTML page displays:

The department name spelled backward is Reverse(Department).

<cfoutput> The department name spelled backward is #Reverse(Department)#. </cfoutput>

ColdFusion uses the Reverse function to reverse the text in the Department variable and displays the re sult. The po und s igns tell

cfoutput to interpret Reverse as a

ColdFusion function. The

Reverse function

uses the Department variable name. The HTML page displays:

The department name spelled backward is selaS.

Adding more variables to the application

Applications can use many different variables. For example, the calldept.cfm application page can set and display values for department, city, and salary.

To modify the application:

1 Open the file calldept.cfm in ColdFusion Studio,. 2 Modify the code so that it appears as follows:

<html>

<head>

<title>Call Department</title><br>

</head>

<body>

<strong>Call Department</strong><br>

I’d like to talk to someone in #Department# in #City# who earns at

least #Salary#.

</cfoutput>

</body>

</html>

3 Save the file.

16 Chapter 2 Writing Your First ColdFusion Application

4 View the page in your Web browser by entering the following URL:

http://127.0.0.1/myapps/calldept.cfm.

Development Considerations

The same development rules that apply for any programming environment apply to ColdFusion. You should also follow the same programming conventions that you would with any other language:

• Comment your code as you go. HTML comments use this syntax:

CFML comments add an extra dash:  ColdFusion removes CFML comments from the HTML that it sends to the

brows er, so users do not see them if they vi ew the HT ML sour ce . C o ldF usion doe s send HTML comments to the browser.

• Filenames should be all one word, begin with a letter, and can contain only letters, numbers, and the underscore.

• Filenames should not contain special characters.

• Some oper atin g syste ms ar e case- sensitiv e , so yo u sh ould be con sistent wit h you r

use of capital letters in filenames.

Chapter 3

Querying a Database

This chapter describes how to retrieve data from a database, work with query data, and enable debugging in ColdFusion applications. You will learn how to use the ColdFusion Administrator to set up a data source and enable debugging, use the

cfquery tag to query a data source, and use the cfoutput tag to output the query

results to a Web page.

Contents

• Publishing Dynamic Data.........................................................................................18

• Understanding Database Basics..............................................................................19

• Understanding Data Sources ...................................................................................20

• Accessing Data Sou rces ............................................................................................21

• Retrieving Data..........................................................................................................25

• Writing SQL................................................................................................................26

• Building Queries........................................................................................................ 28

• Outputting Query Data.............................................................................................30

• Getting Information About Query Results..............................................................32

• Using Query Results in Queries................................................................................34

18 Chapter 3 Queryi ng a Database

Publishing Dynamic Data

A Web application page is different from a static Web page because it can publish data dynamically. This can involve querying databases, connecting to LDAP or mail servers, and leveraging COM, DCOM, CORBA, or Java objects to retrieve, update, insert, and delete data at runtime—as your users interact with pages in their browsers.

For ColdFusion developers, the term data source can refer to a number of different types of structured content accessible locally or across a network. You can query Web sites, LDAP servers, POP mail servers, and documents in a variety of formats.

Most commonly though, a database drives your applications, and for this discussion a data source means the entry point from ColdFusion to a database.

In this chapter, you build a query to retrieve data from the which accesses a Microosft Access database (company.mdb or a DBase database on UNIX systems. In subsequent chapters in this book, you will insert and update data in this database.

To query a database, you need to use:

• ColdF usio n dat a sources

• The

• SQL commands

cfquery tag

CompanyInfo data source,

), on Windows systems

Understanding Database Basics 19

Understanding Database Basics

You do not need a thorough knowledge of databases to develop a data-driven ColdFusion application, but you need to know some basic concepts and techniques.

A database is a structure for storing information. Databases are organized in tables, which are collections of related items. For example, a table might contain the names, street addresses, and phone numbers of individuals. Think of a table as a grid of columns and rows. In this case, one column contains names, a second column contains street addresses, and the third column contains phone numbers. Each row constitutes one data record. In this case, each row is unique because it applies to one individual. Rows are also referred to as records. Columns are also referred to as fields.

You can organize data in multiple tables. This type of data structure is known as a relational database and is the type used for all but the simplest data sets.

20 Chapter 3 Queryi ng a Database

Database design guidelines

From this basic description, a few database design rules emerge:

• Each record should contain a unique identifier, known as the primary key. This can be an employee ID, a part number, or a customer number . The primary key is typically the column used to maintain each record’s unique identity among the tables in a relational database.

• After you define a column to contain a specific type of informat ion, you must enter data in that column in a consistent way.

• To enter data consistently, you define a data type for the column, such as allowing only numeric values to be entered in the salary column.

• Assessing user needs and incorporating those needs in the database design is essential to a successful implementation. A well-designed database accommodates the changing data needs within an organization.

The best way to fa milia riz e y o urself w i th the c apa bil itie s o f y our data ba se product or database management system (DBMS) is to review the product documentation.

Understanding Data Sources

A database is a file or server that contains a collection of data. A data source defines the properties which ColdFusion uses to connect to a specific database. You add data sources to your ColdFusion Server so that you can connect to the databases from your ColdFusion applications.

About Open Database Connectivity

Open Database Connectivity (O DBC) is a standard interface for connecting to a database from an application. Applications that use ODBC must have an ODBC driver installed and configured for each data source.

On Windows, you can check your system’s installed drivers by opening the ODBC Data Source Manager in the Windows Control Panel.

On Windows, the installed set of ColdFusion ODBC drivers includes:

• Microsoft SQL Server

• Microsoft Access and FoxPro databases

Accessing Data Sources 21

• Borland dBase-compliant databases

• Microsoft Excel worksheet data ranges

• Borland Paradox Databases

• Informix databases

• Progress databases

• Oracle 8 databases

• Centura SQLBase databases

• Sybase ASE databases

• Delimited text files

You can also use any additional ODBC drivers that are installed on your system. On UNIX, look in the ODBC page of the ColdFusion Administrator for a list of

available ODBC drivers. A good source of information on ODBC is the ODBC Programmer’s Reference at http:/

/www.microsoft.com/data/odbc.

Accessing Data Sources

There are two ways to access data sources:

• Add data sources in the ColdFusion Administrator. You assign a data source name and set all the information required to establish

an ODBC connection. You then use the data source name in any CFML tags that establish database connections. This technique puts all the information about a ColdFusion Server’s database connections in a single, easy-to-manage location.

• Specify the database information directly in your CFML tag. This way you ac ces ses th e data source dynamically. It el im inat es the need for you

to add a data source for each database on your server. It also allows a ColdFusion application to run on mult iple s ervers with out havin g t o statically configure each server independently.

22 Chapter 3 Queryi ng a Database

Adding data sources

You use the ColdFusion Administrator to add data sources to the ColdFusion Administrator.

When you add a data source, you assign it a name so that you can reference it within tags such as cfquery on application pages to query databases. During a query, the data source tells ColdFusion which database to connect to and what parameters to use for the connection.

Use the following procedure to add the CompanyInfo data source that you use in many examples in this book.

Note

By default, the Co ld F u sion setu p insta lls the C omp anyInfo and cfsnippets databases used in examples in this book and adds them to the available ODBC data sources. Therefore, this procedure should not be necessary to work with examples in this book.

To add a data source:

1 Start the ColdF usio n Adm inist ra tor.

On Windows, select Start > Programs > ColdFusion Server > ColdFusion Administrator. On UNIX, enter the URL your browser.

The Administrator prompts you for a password if you assigned one to the ColdFusion Server during the installation.

hostname/CFIDE/administrator in

2 Enter a password to gain access to the Administrator. 3 Select ODBC under the Data Sources heading on the left menu.

Accessing Data Sources 23

4 Name the data source CompanyInfo. 5 On Windows Select Microsoft Acc ess Driver (*.mdb) from the drop-down box to

describe the ODBC driver. On UNIX, select the Merant Dbase/FoxPro driver.

6Click Add. 7 In the Database File field, enter the full path of the database. (You can also use

the Browse button to locate the file.). On Windows specify the path to the company.mdb file, typically

C:\CFusion\database\Company.mdb. On UNIX, specify the path to the CompanyInfo directory, typically /opt/coldfusion/database/CompanyInfo.

8 Click Create to create the

The data source is added to the data source list.

9Locate CompanyInfo in the data source list. 10 Select Verify to run the verification test on the data source.

If the data source was created, you should see this message:

The connection to the data source was verified successfully.

11 Click Go Back to return to the data sources list. For more information about managing data sources, see Advanced ColdFusion

Administration.

Specifying a connection string

You can dynamically override ODBC connection information that you set in the ColdFusion Administrator. You can also specify connection attributes that are not set in the Administrator. To do so, use the connectstring attribute in any CFML tag that connects to a database:

cfstoredproc.

For example, the following code creates a connection to a defined Microsoft SQLServer data source using a connect string to specify the Application and Work Station ID.

CompanyInfo data source.

cfquery, cfinsert , cfupdate, cfgridupdate, and

<cfupdate datasource = "mssql"

connectstring = "APP=ColdFusion;WSID=fenway" tablename = "department">

Note

Connect string properties are specific to the database you are connecting to. See the documentation for your database for a list of connect string properties.

24 Chapter 3 Queryi ng a Database

Adding data source notes and considerations

When adding data sources to ColdFusion Server, keep these guidelines in mind:

• Data source names should be all one word and begin with a letter.

• Data source names can contain only letter s, numbers, and the underscore.

• Data source names should not contain special characters.

• Although data source names are not case-sensitive, you should use a consistent

capitalization scheme.

• A data source must exist in the ColdFusion Administrator before you use it on an application page to retrieve data (unless you specify the data source dynamically).

Specifying data sources dynamically

To specify a data source dynamically, use the following attribute in the cfquery tag:

dbtype = "dynamic"

Specify all the required ODBC connection information, including the ODBC driver and the database location, in the use the following code for a query that dynamically specifies the pubs database on a local Microsoft SQLServer:

connectstring attribute. For example, you could

<cfquery name = "datelist"

dbtype = "dynamic" blockfactor = 100 connectstring = "DRIVER={SQLSERVER};SERVER=(local);UID=sa;PWD=;

DATABASE=pubs">

SELECT * FROM authors

</cfquery>

The following example uses a Microsoft Access database:

<cfquery name="titles"

dbtype = "dynamic" ConnectString="DRIVER=Microsoft Access Driver (*.mdb);

DBQ=C:\CFusion\Database\cfsnippets.mdb;DriverId=281; FIL=MS Access;MaxBufferSize=2048;PageTimeout=5">

SELECT * FROM Courses

</cfquery>

Retrieving Data 25

Retrieving Data

You can query databases to retrieve data at runtime. The retrieved data, called the result se t , is stored on that page as a query object. When retrieving data from a database, perform the following tasks:

• Use the

• Write SQL commands inside the

to retrieve from the database.

• Later on the page, reference the query object and use its data values in any tag that presents data, such as

The cfquery tag

The cfquery tag is one of the most frequently used CFML tags. You use it in conjunction with the returned from a query.

When ColdFusion encounters a cfquery tag on a page, it does the following:

• Connects to the specified data source.

• Performs SQL commands that are enclosed within the block.

• Returns result set values to the page in a special kind of var iable called a query

object. You specify the query object’s name in the cfquery tag’s name attribute. Often, we refer to the query object simply as “the query”.

cfquery tag on a page to tell ColdFusion how to connect to a database.

cfquery block to specify the data that you want

cfoutput, cfgrid, cftable, cfgraph, or cftree.

cfoutput tag so that you can retrieve and reference the data

The cfquery tag syntax

You’ll type SQL here

</cfquery>

In this example, the query code tells ColdFusion to:

• Use the CompanyInfo data source to connect to the company.mdb database.

• Store the retrieve d data in the query object EmpList.

Follow these rules when creating a

• The cfquery tag is a block tag, that is, it has an opening <cfquery> and ending

cfquery> tag.

• Use the name attribute to name the query object so that you can reference it later on the page.

• Use the used to connect to a specific database. Alternatively, use the

"dynamic"

• Always surround attribute values with double quotes (").

• Place SQL stat ements inside the

process during the query.

datasource attribute to name an existing data source that should be

and connectString attributes to dynamically specify a database.

cfquery tag:

dbtype =

cfquery block to tell the database what to

26 Chapter 3 Queryi ng a Database

• When referencing text literals in SQ L, use singl e quote s (’). For example, Select

* from mytable WHERE FirstName=’Russ’

which the first name is Russ.

selects every record from mytable in

Writing SQL

In between the begin and end cfquery tags, write the SQL that you want the database to execute.

For example, to retrieve data from a database:

• Write a SELECT statement that lists the fields or columns that you want to select for the query.

• Follow the SELECT statement with a FROM clause that specifies the database tables that contain the colu mns.

Tip

If you are using ColdFusion Studio, you can use the Query Builder to build SQL statements by graphically selecting the tables and records within those tables that you want to retrieve.

When the database processes the SQL, it creates a data set (a structure containing the requested data) that is returned to ColdFusion Server. ColdFusion places the data set in memory and assigns it the name that you defined for the query in the attribute of the

You can reference that data set by name using the

cfquery tag.

Basic SQL syntax elements

The following sections present brief descriptions of the main SQL command elements.

Statements

A SQL statement always begins with a SQL verb. The following keywords identify commonly used SQL verbs:

Keyword Description

SELECT Retrieves the specified records INSERT Adds a new row UPDATEw Changes values in the specified rows

name

cfoutput tag later on the page.

DELETE Removes the specified rows

Writing SQL 27

Statement clauses

Use the following keywords to refine SQL statements:

Keyword Description

FROM Names the data tables for the operation WHERE Sets one or more cond itions for the operation ORDER BY Sorts the result set in the specified order GROUP BY Groups the result set by the specified select list items

Operators

The following basic operators specify conditions and perform logical and numeric functions:

Operator Description

AND Both conditions must be met OR At least one condition must be met NOT Exclude the condition following LIKE Matches with a pattern IN Matches with a list of value s BETWEEN Matches with a range of values = Equal to <> Not equal to < Less than > Greater than <= Less than or equal to >= Greater than or equal to + Addition

- Subtraction / Division * Multiplication

SQL notes and considerations

When writing SQL in ColdFusion, keep the following guidelines in mind:

• There is a lot more to SQL than what is covered here. It is a good idea to purchase one or several SQL guides that you can refer to.

• The data source, columns, and tables that you reference must exist in order to perform a successful query.

28 Chapter 3 Queryi ng a Database

• Some DBMS vendors use nonstandard SQL syntax ( known as a dial ect) in their products. ColdFusion does not validate the SQL in a

cfquery, so you are free to

use any syntax that is supported by your data source. Check your DBMS documentation for nonstandard SQL usage.

Building Queries

As discussed earlier in this chapter, you build queries using the cfquery tag and SQL.

To que ry the table:

1 Create a new application page in ColdFusion Studio. 2 Edit the page so that it appears as follows:

<html> <head> <title>Employee List</title> </head> <body> <h1>Employee List</h1>

SELECT FirstName, LastName, Salary, Contract

FROM Employee </cfquery> </body>

</html>

3 Save the page as emplist.cfm in myapps under the Web root directory. For

example, the directory path on your machine might be: C:\INETPUB\WWWROOT\myapps on Windows NT

4 Return to your browser and enter the following URL to view EmpList.cfm:

http://127.0.0.1/myapps/emplist.cfm

5 View the source in the browser.

The ColdFusion Server creates the EmpList data set, but only HTML and text is sent back to the browser so you just see the heading “Employee List”. To displa y the data set on the page, you must code tags and variables to output the data.

Building Queries 29

Reviewing the code

The query you just created retr ieves data from the CompanyInfo da tabase. The following table describes the code and its function:

Code Description

SELECT FirstName, LastName, Salary, Contract FROM Employee

</cfquery>

Query notes and considerations

When creating queries to retrieve data, keep the following guidelines in mind:

• Enter the query name and datasource attributes in the begin cfquery tag.

• Surround attribute settings with double quotes(").

• Make sure that a data source exists in the ColdFusion Administrator before you

reference it n a

queryString attributes to dynamically specify a database.

• The SQL that you write is sent to the database and performs the actual data retrieval.

• Columns and tables that you refer to in your SQL statement must exist, otherwise the query will fail.

• Reference the query data by naming the query in one of the presentation tags, such as

cfoutput, cfgrid, cftable, cfgraph, or cftree later on the page.

cfquery tag. Alternatively, use the dbtype = "dynamic" and

Queries the database specified in the CompanyInfo data source

Gets informatio n fr om the FirstName, LastName, Salary, and Contract fields in the Employee tab le

Ends the cfquery block

30 Chapter 3 Queryi ng a Database

Outputting Query Data

After you define a query on a page, you can use the cfoutput tag with the query attribute to specify the query object that contains the data you want to output to a page. When you use the

• ColdFusion loops o ver all th e code contain ed within the each row returned from a database.

• You must reference spec ific column names within the the data to the page.

• You can place text, CFML tags, and HTML tags inside or surrounding the

cfoutput block to format the data on the page.

• You do not have to specify the query object name when you refer to a query column. For example, if you sp ecify the Emplist query in your can refer to the Firstname column in the Emplist query as either Emplist.Firstname or just Firstname.

The

cfoutput tag accepts a variety of optional attributes but, ordinarily, you use the

query attribute to define the name of an existing query.

query attribute:

cfoutput block, once for

cfoutput block to output

cfoutput tag, you

To output query data on your page:

1Return to empList.cfm in ColdFusion Studio. 2 Edit the file so that it appears as follows:

<html> <head> <title>Employee List</title> </head> <body> <h1>Employee List</h1> <cfquery name="EmpList" datasource="CompanyInfo">

SELECT FirstName, LastName, Salary, Contract FROM Employee

</cfquery>

<cfoutput query="EmpList"> #FirstName#, #LastName#, #Salary#, #Contract#<br> </cfoutput>

</body> </html>

3 Save the file as emplist.cfm. 4 View the page in a browser.

A list of employees appears in the browser, with each line displaying one row of data.

You created a ColdFusion application page that retrieves and displays data from a database. At present, the output is raw. You will learn how to format the data in the next chapter.

Reviewing the code

Outputting Query Data 31

You now display the results of the query on the page. The following table describes the code and its function:

Code Description

Display information retrieved in the EmpList query. Display information for each record in the query, until you run out of records.

#FirstName#, #LastName#, #Salary#, #Contract#

Display the value of the FirstName,

LastName, Salary, Contract

record, separated by commas and spaces.

<br>

Insert a line break (go to the next line) after each record.

</cfoutput>

End the cfoutput block.

Query output notes and considerations

When outputting query results, keep the following guidelines in mind:

• A

cfquery must precede the cfoutput that references its results. Both must be on

the same page (unless you use the

• It is a good idea to run all queries before all output blocks.

• To output data from all the records of a query, specify the query name by using

the query attribute in the cfoutput tag.

• Columns must exist and be retrieved to the application in order to output their values.

• Inside a

cfoutput block that uses a cfquery attribute you can optionally prefix

the query variables with the name of the query, for example

• As with other attributes, surround the query attribute value with double quotes (").

• As with any variables that you reference for output, surround column names with pound signs (#) to tell ColdFusion to output the column’s current values.

• Add a

<br> tag to the end of the variable references so that ColdFusion starts a

new line for each row that is returned from the query.

cfinclude tag).

fields of each

Emplist.FirstName.

32 Chapter 3 Queryi ng a Database

Getting Information About Query Results

Each time you query a database with the cfquery tag, you get n ot o n ly t he da ta its elf, but also query properties, as described in the following table:

Property Description

RecordCount The total number of records returned by the query. ColumnList A comma-delimited list of the query columns. CurrentRow The current row of the query being processed by

To output query data on your page:

1Return to emplist.cfm in ColdFusion Studio. 2 Edit the file so that it appears as follows:

<html> <head> <title>Employee List</title> </head> <body> <h1>Employee List</h1> <cfquery name="EmpList" datasource="CompanyInfo">

SELECT FirstName, LastName, Salary, Contract

FROM Employee </cfquery> <cfoutput query="EmpList">

#FirstName#, #LastName#, #Salary#, #Contract#<br> </cfoutput> <br>

The query returned #EmpList.RecordCount# records. </cfoutput>

</body> </html>

cfoutput.

3 Save the file as emplist.cfm. 4 View the page in a browser. The number of employees now appears below the list of employees.

Note

The variabl e

cfquery.executionTime contains the amount of time, in milliseconds,

it took for the query to complete. Do not prefix the variable name wi th the query name.

Reviewing the code

Getting Information About Query Results 33

You now display the number of records retrieved in the query. The following table describes the code and its function:

Code Description

<cfoutput> The query returned #EmpList.RecordCount#

Display what follows Display the text “The query returned” Display the numb er of records retri eved in the EmpLis t

query

records </cfoutput>

Display the text “records” End the cfoutput block.

Query properties notes and considerations

When using query properties, keep the following guidelines in mind:

• Reference the query property w ithin a cfoutput block so that ColdFusion outputs the query property value to the page.

• Surround the query property reference with pound signs (#) so that ColdFusion knows to replace the property name with its current value.

• Do not use the or ColumnList property. If you do, you will get one copy of the output for each row. Instead, prefix the property with the name of the query.

cfoutput tag query attribute when you output the RecordCount

34 Chapter 3 Queryi ng a Database

Using Query Results in Queries

ColdFusion allows you to use the results of a previous query in any cfquery tag that returns ro w data to ColdF us ion. You can query a database o nce and us e the r esult s in several dependent queries. Queries generated from other queries are often referred to as query of queries.

Query of query benefits

Performing queries on query results has many benefits:

• If you need to access the same tables multiple times, you greatly reduce access time for tables with up to 10,000 rows because the data is already in memory.

• You can join and perform unions on results from different data sources. For example , y ou can do a union o n queries fro m differ en t databases to eliminate

duplicates for a mailing list .

• You can efficiently manipulate cached query results in different ways. You can query a database once, and then use the results to generate several different summary tables.

For example, if you need to summarize the total salary by department, by skill, and job, you can make one query to the da tabase and use its results in three separate queries to generate the summaries.

• You can make drill-down, master-detail-like function ality whe r e you do not go to the database for the details.

For example, you can select information about departments and employees in a query and cache the results. You can then display the employee names. When users select an employee, the applicati on displays the employee details by selecting information from the cached query without accessing the database.

Creating queries of queries

You can create a query using a query object from any ColdFusion tag or function that generates query results, including

cfpop, cfindex, and the Query functions.

You can use a limited subset of the SQL SELECT syntax, which includes:

FROM WHERE GROUP BY UNION ORDER BY HAVING AS DISTINCT

cfldap, cfdirectory, chttp, cfstoredproc,

Using Query Re sults in Queries 35

Boolean predicates:

LIKE NOT LIKE IN NOT IN BETWEEN NOT BETWEEN AND OR

Aggregate functions:

Count([DISTINCT][*] expr) Sum([DISTINCT] expr) Avg([DISTINCT] expr) Max(expr) Min(expr)

You cannot nest aggregate functions.

Comparison operators:

<= >= = < > <>

You can also do the following tasks:

• Use the results of one or two queries in your SQL statement.

• Generate computed columns.

Performing a query on a query

To generate a query using an existing query:

• Specify the cfquery tag’s dbtype attribute as "query".

• Do not specify a datasource attribute.

• Specify the names of one or more existing queries as the table names in a SQL

SELECT statement.

• If the database content does not change rapidly, it is a good idea to use the

cachedwithin attribute to cache the query results of between page requests. This

way, ColdFusion accesses the database on the first page request, and does not query the database again until the specified time expires. Note that you must use

CreateTimeSpan functio n to specify the cachedwithi n attribute value.

the

Note

You cannot add a literal value as a column to the SELECT list in a query of queries.

Your query generates a new query results set, identified by the value of the name attribute. The following example illustrates the us e of a master query and a single detail query that extracts information from the master. A more extended example would use multiple detail queries to get different information from the same master query.

36 Chapter 3 Queryi ng a Database

To use the results of a query in a query:

1 Create a new application page in ColdFusion Studio. 2 Edit the page so that it appears as follows:

<html> <head> <title>Using Query Results in a Query</title> </head> <body> <h1>Employee List</h1>  <cfset LastNameSearch = "Jones">  <cfquery datasource = "CompanyInfo" name = "EmpList"

cachedwithin=#CreateTimeSpan(0,1,0,0)#> SELECT * FROM Employee

</cfquery>

<cfquery dbtype = "query" name = "QueryFromQuery" >

SELECT Emp_ID, FirstName, LastName FROM EmpList WHERE LastName = ’#LastNameSearch#’

</cfquery>

Output using a query of query<br> <cfoutput query = QueryFromQuery>

#Emp_ID#: #FirstName# #LastName#<br>

</cfoutput> <br>

Columns in the Emplist database query<br> <cfoutput>

#Emplist.columnlist#<br>

</cfoutput> <br>

Columns in the QueryFromQuery query<br> <cfoutput>

#QueryFromQuery.columnlist#<br>

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

3 Save the page as queryquery.cfm in myapps under the Web root director y. 4 Return to your browser and enter the following URL to view the results of the

query: http://127.0.0.1/myapps/queryquery.cfm

5 View the source in the browser.

Using Query Re sults in Queries 37

Reviewing the code

The page retrieves the entire Employee table from the CompanyInfo database. A second query selects only the three columns to display for employees with the specified last name. The following table describes the code and its function:

Code Description

cfset ListNameSearch = "Jones"

SELECT * FROM Employee

</cfquery>

SELECT Emp_ID, FirstName,

LastName FROM Emplist WHERE LastName=’#LastNameSearch#’

</cfquery>

#Emp_ID#: #FirstName# #LastName#<br>

</cfoutput> <br>

#EmpList.columnlist#<br>

</cfoutput> <cfoutput>

#QueryFromQuery.columnlist#<br>

</cfoutput>

Set the last name to use in the second query. In a complete application, this information comes from user interaction.

Query the database specified in the CompanyInfo da ta sourc e and select all data in the Employee table. Cache the query data between requests to this page, and do not query the database if the cached data is less than an hour old.

Use the EmpList q uery a s the source of the data in a new query. This query selects only entries that match the last name specified by the

LastNameSearch

variable. The query also selects only three columns of data: employee ID, first name, and last name.

Use the QueryFromQuery query to display the list of employee IDs, first names, and last names.

List all t he column s returned by the Emplist query.

List all t he column s returned by the QueryFromQuery query.

38 Chapter 3 Queryi ng a Database

Chapter 4

Retrieving and Formatting Data

This chapter explains ho w to select the data to display in a dynam ic Web page. It also describes how to populate an HTML table with query results and how to use ColdFusion functions to format and manipulate data.

Contents

• Using Forms to Specify the Data to Retrieve...........................................................40

• Working with Action Pages.......................................................................................45

• Working with Queries and Data...............................................................................49

• Returning Results to the User...................................................................................55

40 Chapter 4 Retrieving and Formatting Data

Using Forms to Specify the Data to Retrieve

In the examples in previous chapters, you have retrieved all of the records from a table. However, there are many instances when you want to retrieve data based on certain criteria. For example, you might want to see records for everyone in a particular department, everyone in a parti cular town whose last name is Smith, or books by a certain author.

You can use forms in ColdFusion applications to allow users to specify what data they want to retrieve in a query. When you submit a form, you pass the variables to an associated page, called an action page, where some type of processing takes place.

Note

Because forms are standard HTML, the syntax and examples th at follow provide you with just enough detail to begin using ColdFusion.

form tag syntax

Use the following syntax for the create a form tag:

...

</form>

Attribute Description

action

method

You can override the server request timeout (set on the ColdFusion Administrator Server Settings page) by adding a RequestTimeout parameter to the action page URL. The following example specifies a request timeout of two minutes:

Specifies an action page to which you pass form variables for processing.

Specifies how the variables are submitted from the browser to the action page on the server. All ColdFusion forms must be submitted with an attribute setting of method=“post”

Using Forms to Spec ify the Data to Retrieve 41

<form name="getReportCriteria"

action="runReport.cfm?RequestTimeout=120" method="p ost">

Form controls

Within the form, you describe the form controls needed to gather and submit user input. There are a variety of form controls types available. You choose form control input types based on the type of input the user should provide.

The following table illustrates the format of form control tags:

Control Code

Text control

Radio buttons

List box

Check box

<input type="Text" name="ControlName" size="Value"

maxlength="Value">

<input type="Radio" name="ControlName"

value="Value1">DisplayName1

<input type="Radio" name="ControlName"

value="Value2">DisplayName2

<input type="Radio" name="ControlName"

value="Value3">DisplayName3

<option value="Value1">DisplayName1 <option value="Value2">DisplayName2 <option value="Value3">DisplayName3

</select>

<input type="Checkbox" name="ControlName"

value="Yes|No">Yes

42 Chapter 4 Retrieving and Formatting Data

Control Code

Reset button Submit button

<input type ="Res et" name ="Con trolN ame" val ue=" Displ ayName" > <input type="Submit" name="ControlName"

value="DisplayName">

Use the following procedure to create a sample form.

To cre ate a form

1 Create a new application page, using ColdFusion Studio. 2 Edit the page so that it appears as follows:

<html> <head> <title>Input form</title> </head> <body> <!--- define the action page in the form tag. The form variables will

pass to this page when the form is submitted --->

<p> First Name: <input type="Text" name="FirstName" size="20"

maxlength="35"><br>

Last Name: <input type="Text" name="LastName" size="20"

maxlength="35"><br>

Salary: <input type="Text" name="Salary" size="10" maxlength="10">

</p>

<p> City <select name="City">

<option value="Arlington">Arlington <option value="Boston">Boston <option value="Cambridge">Cambridge <option value="Minneapolis">Minneapolis

<option value="Seattle">Seattle </select> </p>

<p> Department:<br>

<input type="radio" name="Department" value="Training">Training<br> <input type="radio" name="Department" value="Sales">Sales<br> <input type="radio" name="Department"

value="Marketing">Marketing<br>

</p>

Using Forms to Spec ify the Data to Retrieve 43

<p> Contractor? <input type="checkbox" name="Contractor" value="Yes"

checked>Yes

</p>

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

3 Save the page as formpage.cfm within the myapps directory under your Web root

directory.

4 View the form in a browser.

The form appears in th e browser. Remember that you need an action page in order to submit values; you will create

one later in this chapter.

Reviewing the code

The following table describes the highlighted code and its function:.

Code Description

<form action="actionpage.cfm"

method="post">

<input type="Text" name="FirstName"

size="20" maxlength="35">

<input type="Text" name="LastName"

size="20" maxlength="35">

<input type="Text" name="Salary"

size="10" maxlength="10">

Gather the information from this form using the Post method , and do something with it o n the page actionpage.cfm.

Create a text box cal led FirstName w here users can enter their first name. Make it 20 characters wide, but allow input of up to 35 characters.

Create a text box called LastNa me wher e users can enter their first name. Make it 20 characters wide, but allow input of up to 35 characters.

Create a text box called Salary where users can enter a sal ary to l ook fo r. Make it 10 characters wide, and allow input of up to 10 characters.

44 Chapter 4 Retrieving and Formatting Data

Code Description

Arlington <option value="Boston">Boston <option value="Cambridge">

Cambridge <option value="Minneapolis">

Minneapolis <option value="Seattle">Seattle

</select> <input type="checkbox" name=

"Contractor" value="Yes|No" checked>Yes

Create a drop-down list box named City and populate it with the values

“Arlington,” “Boston,” “Cambridge,” “Minneapolis,” and “Seattle.”

Create a check box that allows users to specify whether they want to list employees who are contractors. Make the box selected by default.

Create a reset button to allow users to clear the form. Put the tex t Clear Form o n the button.

Create a submit butt on to send the values that users enter to the action page for processing. Put the text Submit on the button.

Form notes and considerations

• To make the coding process easy to follow, name form controls the same as target database fields.

• For ease of use, limit radio buttons to between three and five mutually exclusive options. If you need more options, consider a d rop-down select list.

• Use list boxes to allo w the user to choose from many options or to chose multiple items from a list.

• All the data that you collect on a form is automatically passed as form variables to the associated action page.

• Check boxes and radio buttons do not pass to action pages unless they are selected on a form. If you try to reference these variables on the action page, you receive an error if they are not present.

• You can dynamically populate drop-down select lists using query data. See “Dynamically Populating List Boxes” on page 82 for details.

Working with Action Pages 45

Working with Action Pages

A ColdFusion action page is just like any other appli cation page except that you can use the form variables that are passed to it from an associated form. The following sections des cribe how to create effective action pages.

Processing form variables on action pages

The action page gets a form variable for every form control that contains a value when the form is subm itted.

Note

If multiple controls have the same name, one form variable is passed to the action page. It contains a comma-delimited list.

A form v a ria bl e’s name is the name that y ou as signed to the form con trol on the fo rm page. Refer to the form variable by name within tags, functions, and other expressions on an action page.

Because Form variables extend beyond the local page—their scope is the action page—prefix them with “Form.” to explicitly tell ColdFusion that you are referring to a form variable. For example the following code references the LastName form variable for output on an action page:

#Form.LastName#

</cfoutput>

Dynamically generating SQL statements

As you have already learned, you can retrieve a record for every employee in a database table by composing a query like this:

SELECT FirstName, LastName, Contract FROM Employee

</cfquery>

But when you want to return information about employees that match user search criteria, you use the S Q L WHERE clause with a SQL SELECT statement to compare a value against a character string field. When the WHERE clause is processed, it filters the query data based on the results of the comparison.

For example, to return employee data for only employees with the last name of Smith, you build a query that looks like this:

SELECT FirstName, LastName, Contract FROM Employee

WHERE LastName = "Smith"

</cfquery>

46 Chapter 4 Retrieving and Formatting Data

However, instead of putting the LastName directly in the SQL WHERE clause, you can use the text that the user entered in the form for comparison:

SELECT FirstName, LastName, Salary FROM Employee

WHERE LastName="#Form.LastName#"

</cfquery>

For more information on Dynamic SQL, see “Dynamic SQL” on page 96.

Creating action pages

Use the following procedure to create an action page for cormpage.cfm.

To create an action page for the form:

1 Create a new application page in ColdFusion Studio. 2 Enter the following code:

<html> <head> <title>Retrieving Employee Data Based on Criteria from Form</title> </head>

<body>

SELECT FirstName, LastName, Salary FROM Employee WHERE LastName=’#Form.LastName#’

</cfquery>

<h4>Employee Data Based on Criteria from Form</h4>

<cfoutput query="GetEmployees"> #FirstName# #LastName# #Salary#<br> </cfoutput>

<br>

<cfoutput>Contractor: #Form.Contractor#</cfoutput>

</body> </html>

3 Save the page as actionpage.cfm within the myapps directory. 4View

formpage.cfm in your browser.

5 Enter data, for example, Smith, in the Last Name box and submit the form.

The browser displays a line with the first and last name and salary for each entry in the database that match the name you typed, followed by a line with the text “Contractor: Yes”

6 Click Return in your browser to redisplay the form. 7 Remove the check mark from the check box and submit the form again.

Working with Action Pages 47

This time an error occurs because the check box does not pass a variable to the action page.

Reviewing the code

The following table describes the highlighted code and its function:

Code Description

SELECT First Name, LastNa me, S alary FROM Employee WHERE LastName=’#Form.LastName#’

<cfoutput query="GetEmployees"> #FirstName#

#LastName# #Salary#<BR>

</cfoutput> <br>

<cfoutput>Contractor: #Form.Contractor#</cfoutput>

Query the data source CompanyInfo and name the query GetEmployees.

Retrieve the FirstName, LastName, and Salary fields from the Employee table, but only if the value of the LastName field matches what the user entered in the LastName text box in the form on formpage.cfm.

Display results of the GetEmployees query. Display the value of the FirstName,

LastName, and Salary fields for a record, starting with the first record, then go to the next line. Keep displaying the records that match the criteria you specified in the SELECT statement, followed by a line break, until you run out of records.

Close the cfoutput block. Display a blank line followed by the text

Contractor: and the value of the form Contractor check box. A more complete example would test to ensure the existence of the variable and wou ld use th e var iable in the query.

Testing for a variable’s existence

Before relying on a v ariable’s exis tence in an application page , you can test to see if it exists using the input and operates on it. For example, the variable is defined. CFML provides a large number of functions, which are documented in the CFML Reference.

The following code prevents the error that you saw in the previous example by checking to see if the Contractor Form variable exists before using it:

<cfoutput>Contractor: #Form.Contractor#</cfoutput>

</cfif>

IsDefined function. A function is a named procedure that takes

IsDefined function determines whether a

48 Chapter 4 Retrieving and Formatting Data

The argument pass ed to the IsDefined function must always b e enclosed in double quotes. For more information on the IsDefined function, see the CFML Refer ence.

If you attempt to evaluate a variable that you did not define, ColdFusion cannot process the page and displays an error message. To help diagnos e such problems, use the interactive debugger in ColdFusion Studio or turn on debugging in the ColdFusion Administrator. The Administrator debugging information shows which variables are being passed to your application pages.

Form variable notes and considerations

When using form variables, keep the following guidelines in mind:

• A Form variable’s scope is the action page.

• Prefix form variables with "Form." when referencing them on the action page.

• Surround variable values with pound signs (#) for output.

• Check boxes and radio button variables only get passed to the action page if you

select an option. Text boxes pass an empty string if you do not enter text.

• An error occurs if the action page tries to use a variable that has not been passed.

Working with Queries and Data 49

Working with Queries and Data

The ability to generate and display query data is one of the most important and flexible features of ColdFusion. The following s ections further y our un ders tanding of using queries and displaying their results. Some of these tools are effective for presenting any data, not just query results.

Using HTML tables to display query results

You displayed each row of data from the Employee table, but the information was unformatted. You can use HTML tables to control the layout of information on the page. In addition, you can use CFML functions to format individual pieces of data, such as dates and numeric values.

You can use HTML tables to specify how the results of a query appear on a page. To do so, you put the tag to put column labels in a header row. To create a row in the table for each row in the query results, put the tr block inside the cfoutput tag.

cfoutput tag inside the table tags. You can also use the HTML th

To put the query results in a table:

1 Return to the file actionpage.cfm in ColdFusion Studio. 2 Modify the page so that it appears as follows:

<html> <head> <title>Retrieving Employee Data Based on Criteia from Form</title> </head>

SELECT FirstName, LastName, Salary FROM Employee

WHERE LastName=’#Form.LastName#’ </cfquery> <h4>Employee Data Based on Criteria from Form</h4>

<table> <tr> <th>First Name</th> <th>Last Name</th> <th>Salary</th> </tr> <cfoutput query="GetEmployees"> <tr> <td>#FirstName#</td> <td>#LastName#</td> <td>#Salary#</td> </tr> </cfoutput> </table>

<br> <cfoutput>Contractor: #Form.Contractor#</cfoutput>

50 Chapter 4 Retrieving and Formatting Data

</body> </html>

3 Save the page as actionpage.cfm within the myapps directory. 4 View formpage.cfm in your browser. 5 Enter Smith in the Last Name text box and submit the form. 6 The records that match the criteria specified in the form appear in a table.

Reviewing the code

The following table describes the highlighted code and its function:

Code Description

<th>First Name</th> <th>Last Name</th> <th>Salary</th>

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

<tr>

<td>#FirstName#</td> <td>#LastName#</td> <td>#Salary#</td>

</tr> </cfoutput>

</table>

Put data into a table. In the first row of the table, include three

columns, with the headings: First Name, Last Name, and Salary.

Get ready to display the results of the GetEmployees query.

Create a new row in the table, with three columns. For a record, put the value of the FirstName field, the value of the LastName field, and the value of the Salary field.

Keep getting records that matches the criteria, and display each row in a new t able row unti l you run out of records.

End of table.

Formatting individual data items

You might want to format individual data items . For example, you can format the Salary field as a monetary value.

To format the Salary using the dollar format, you use the CFML expression

DollarFormat(number).

To change the format of the Salary:

1 Open the file actionpage.cfm in ColdFusion Studio. 2 Change the line

<td>#Salary#</td>

<td>#DollarFormat(Salary)#</td>

Working with Queries and Data 51

Performing pattern matching

Use the SQL LIKE operator and SQL wildcard strings in a SQL WHERE clau se when you want to co mpa r e a va lue a gai ns t a cha r a cter s t ring fi eld so th at th e qu ery r e tu rns database information based on commonalities. This technique is known as pattern matching and is often used to query databases.

For example, to return data for employees whose last name starts with AL, you build a query that looks like this:

SELECT FirstName, LastName, StartDate, Salary, Contract FROM Employee WHERE LastName LIKE ’AL%’

</cfquery>

• The LIKE operator tells the database to use the string that follows for pattern matching.

• If you place a wildcard before and after AL, you retrieve any record in that column that contains AL.

• Surround strings in SQL statements with single quotes (

’).

To return information from the Departmt table on all departments except the sales department, you would build a query that looks like this:

SELECT * FROM Departmt WHERE Dept_Name NOT LIKE ’[Ss]ales’

</cfquery>

• The first character in the match can be either upper cas e S or lower case s.

Note

Whether SQL identifiers and data comparison operations are case sensitive depends on the database.

Filtering data based on multiple conditions

When you want to retrieve data based on the results of more than one comparison you can use AND and OR operators to combine conditions.

For example, to return data for contract employees who earn more than $50,000, would build a query that looks like this:

SELECT FirstName, LastName, StartDate, Salary, Contract FROM Employee WHERE Contract = ’Yes’ AND Salary > 50000

</cfquery>

52 Chapter 4 Retrieving and Formatting Data

Creating table joins

Many times, the data that you want to retrieve is maintained in multiple tables. For example, in the database that you are working with:

• Department information is maintained in the Depar tmt table. This includes department ID numbers.

• Employee information is maintained in the Employee table. This also includes department ID numbers.

To compare and retrieve data from more than one table during a query, use the WHERE clause to join two tables through common information.

For example, to return employee names, start dates, department names, and salaries for employees who work for the HR department, you build a query that looks like this:

SELECT Departmt.Dept_Name, Employee.FirstName, Employee.LastName, Employee.StartDate, Employee.Salary FROM Departmt, Employee WHERE Departmt.Dept_ID = Employee.Dept_ID AND Departmt.Dept_Name = ’HR’

</cfquery>

In this example, the following criteria joins the two tables:

Departmt.Dept_ID = Employee.Dept_ID

It ensures that each row of the query re su lts c ontains the department name from the Departmt table that corresponds to the Department ID in this employee’s row in the Employee table. Without this statement, the query returns a row for every employee in the Employee table, and all rows have the Dept_Name HR, even if the employee is not in the HR department.

When you do table joins, keep the following information in mind:

• Prefix each column in the SELECT statement to explic itly state which table the data should be retrieved from.

• In this example, the Dept_ID field is the primary key of the Departmt table and the foreign Key of the Employee table. A foreign key uniquely identifi es an o t h er record (in this example, a record in the Departmt table) but does not uniquely identify the current record (in the Employee table).

Building flexible search interfaces

If you want users to optionally enter multiple search criteria, you can wrap conditional logic around the SQL AND clause to build a flexible search interface. To test for multiple conditions, wrap additional cfif tags around additional AND clauses.

Working with Queries and Data 53

The following action page allows users to search for employees by department, last name, or both.

To build a more flexible search interface:

1 Open the page actionpage.cfm in ColdFusion Studio. 2 Modify the page so that it appears as follows:

<html> <head> <title>Retrieving Employee Data Based on Criteia from Form</title> </head> <body> <cfquery name="GetEmployees" datasource="CompanyInfo">

AND Departmt.Dept_Name = ’#Form.Department#’ </cfif> <cfif Form.LastName IS NOT "">

AND Employee.LastName = ’#Form.LastName#’

</cfif>

</cfquery>

<h4>Employee Data Based on Criteria from Form</h4> <table> <tr> <th>First Name</th> <th>Last Name</th> <th>Salary</th> </tr> <cfoutput query="GetEmployees"> <tr> <td>#FirstName#</td> <td>#LastName#</td> <td>#Salary#</td> </tr> </cfoutput> </table> </body> </html>

3 Save the file. 4 View formpage.cfm in your browser. 5 Select a department, optionally enter a last name, and submit the form.

54 Chapter 4 Retrieving and Formatting Data

Reviewing the code

The following table describes the highlighted code and its function:

Code Description

SELECT Departmt.Dept_Name,

Employee.FirstName, Employee.LastName, Employee.StartDate, Employee.Salary FROM Departmt, Employee WHERE Departmt.Dept_ID =

Employee.Dept_ID

<cfif IsDefined("FORM.Department")> AND Departmt.Dept_Name =

’#Form.Department#’

</cfif>

<cfif Form.LastName IS NOT ""> AND Employee.LastName =

’#Form.LastName#’

</cfif>

Retrieve the fields listed from the Departmt and Employee tables, joining the tables base d on the D ept_ ID field in each table.

If the user specified a department on the form, only retrieve records where the department name is the same as the one the user specified. Note that you need the pound signs in the SQL AND statement to identify Form.Department as a ColdFusion variable, but not in the IsDefined function.

If the user specified a last name in the form, only retrieve the records in which the last name is the same as the one the user entered in the form.

Returning Results to the User 55

Returning Results to the User

When you return your results to the user, you must make sure that your pages respond to the user’s needs and are appropriate for the type and amount of information. In particular you must consider the following situations:

• When there are no query results

• When you want to return partial results

If there are no query results

Your code must accommodate the cases where a query does not return any records. To determine whether a search has retrieved records, use the variable. You can use the variable in a conditional logic expression that determines how to display search results appropriately to users.

For example, to inform the user when no records were found by the GetEmployees query, insert the following code before displaying the data:

No records match your search criteria. <BR>

RecordCount query

You mst do the following:

• Prefix RecordCount with the query name.

• Add a procedure after the cfif tag that displays a message to the user.

• Add a procedure after the

cfelse tag to format the returned data.

• Follow the second procedure with a </cfif> tag end to indica te the end of the conditional code.

To return search results to users:

1 Open the page actionpage.cfm in ColdFusion Studio. 2 Change the page so that it appears as follows:

<html> <head> <title>Retrieving Employee Data Based on Criteia from Form</title> </head>

AND Departmt.Dept_Name = ’#Form.Department#’

</cfif>

56 Chapter 4 Retrieving and Formatting Data

AND Employee.LastName = ’#Form.LastName#’

</cfif>

</cfquery>

<cfif GetEmployees.recordcount is "0"> No records match your search criteria. <br> Please go back to the form and try again. <cfelse>

</cfif>

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

3 Save the file. 4 Return to the form, enter search criteria and submit the form. 5 If no records match the criteria you specified, the message displays.

Returning results incrementally

You can use the cfflush tag to incrementally output long-running requests to the browser before a ColdFusion page is fully processed. This allows you to give the user quick feedback when it takes a long time to complete processing a request. For example, you can use please wait.” when a request takes time to return. You can also use it to incrementally display a long list as it gets retrieved.

The first time you use the HTML headers and any other available HTML. Subsequent send only the output that ColdFusion generated since the previous flush.

You can specify an interval attribute to tell ColdFusion to flush the output each time that at least the specified number of bytes become available. (The count does not include HTML headers and any data that is already available when you make this call.) You can use the becomes available. This format is particularly useful when a query responds slowly with large amounts of data.

cfflush to display the message, “Processing your request --

cfflush tag on a page, it sends to the browser all of the

cfflush tag in a cfloop to incrementally flush data as it

cfflush tags on the page

Returning Results to the User 57

When you flush data, make sure that a sufficient amount of information is available, because some browsers might not respond if you flush only a very small amount. Similarly, if you use an

interval attribute, set it for a reasonable size, such as a few

hundred bytes or more but not many thousands of bytes.

Caution

After you use the cfflush tag on a page, any CFML function or tag on the page that modifies the HTML header causes an error. These include the

cfcookie,

error if you use the errors can be caught with a

cfform, cfheader, cfhtmlhead, and cflocation tags. Yo u also get an

cfset tag to set a Cookie scope variable. All errors except Cookie

cfcatch type="template" tag. Cookie errors can be

caught with cfcatch type="Any".

cfcontent,

The followi ng ex ample uses the

cfloop tag and the rand() random n umber

generating function to artificially delay the generation of data for display. It simulates a situation in which it takes time to retrieve the first data and additional information becomes available slowly.

<title>Your Magic numbers</title>

</head>

<body> <H1>Your Magic numbers</H1> <P>It will take us a little while to calculate your ten magic numbers. It takes a lot of work to find numbers that truly fit your personality. So relax for a minute or so while we do the hard work for you.</P> <H2>We are sure you will agree it was worth the short wait!</H2> <cfflush>

</cfloop>

<cfloop index="Myindex" from="1" to="10" step="1">

Magic number number #Myindex# is: #RandRange( 100000,

999999)#<br><br>

</cfoutput>

</cfloop> </body> </html>

58 Chapter 4 Retrieving and Formatting Data

Reviewing the code

The following table describes the code and its function:

Code Description

<H2>We are sure you will agree it was

worth the short wait!</H2>

<cfloop index="randomindex" from="1"

to="200000" step="1">

</cfloop>

<cfloop index="Myindex" from="1"

to="10" step="1">

<cfloop index="randomindex"

from="1" to="100000" step="1"> <cfset random=rand()> </cfloop> <cfoutput>

Magic number number #Myindex#

is: #RandRange

(100000,999999)#<br><br> </cfoutput>

</cfloop>

Send the HTML header and all HTML output to the

cfflush tag to the user.

This displays the explanatory paragraph and H2 tag contents.

Flush additional data to the user every time at least ten bytes are available.

Insert an artificial delay by using the

Rand function to calculate many random

numbers. Generate and display ten random

numbers. This c ode u ses two loop s. Th e outer loop is rep eated ten tim es, once f or each number to display. The inner loop uses the rand function to create another delay by generating more (unused) random numbers. It then calls the

RandRange function to generate a

six-digit random number for display.

Chapter 5

Graphing Data

This chapter explain s how to use the cfgraph tag to display graphs. I t illustr ates ways that you can graph data and gives you the tools you need to create effective graphs.

The cfgraph tag requires the JRun server and the Macromedia Generator server, which install with ColdFusion.

Contents

• Creating a Graph .......................................................................................................60

• Graphing Data...........................................................................................................62

• Controlling Graph Appearance................................................................................67

• Linking Dynamically from Graphs........................................................................... 77

60 Chapter 5 Graphing Data

Creating a Graph

The cfgraph tag provides five graph types. A large number of attributes let you customize the graph appearance to meet your needs.

Graph types

You can create the following types of graphs:

• Bar

• Horizontal bar

• Line

• Area (a line graph with the area below the line filled in)

• Pie

The following illustrations show one sample of each type of graph:

Creating a Graph 61

Creating a basic graph

You use the following cfgraph attributes to create a basic graph:

Attribute Description

type Must be one of the following values:

line. (An area graph is a kind of line graph.)

bar, horizontalbar, pie, or

query The query containing the data. valueColumn The query column containing the values to be graphed. itemColumn (Optional) The query column conta ining the descripti on for this data

point. The item normally appears on the horizontal axis of bar and line graphs, and in pie charts.

You must end your

cfgraph tag with a </cfgraph> end tag.

For example, if you have a query that contains average salary by department, the following tag displays a bar graph with the information:

<cfgraph type="bar"

query="DataTable" valueColumn="AvgByDept" itemColumn="Dept_Name">

</cfgraph>

The resulting graph looks like this:

Later sections in this chapter provide information on how you can specify the data differently and how you can change and enhance graph appearance.

62 Chapter 5 Graphing Data

Graphing Data

One of the most important considerations when you graph data is the way you supply the data to the

• Provide all the data in a single query.

• Specify individual data points using

• Combine data from a query with additional data points from

Note

cfgraph tag graphs numeric data only. As a result, you must convert any dates,

The times, or preformatted currency values, such as $3,000.53, to integers or real numbers.

Graphing a query

When you graph a que ry, you specify the query name in the cf graph tag query attribute. In this format the provide the might look like this:

<\cfgraph> end tag. For example, a simple cfgraph tag f o r a b a r ch a rt

cfgraph tag. You can supply data in several ways:

cfgraphdata tags.

cfgraph tag body is empty. However, you must still

<cfgraph type="bar" title="Salaries by Department"

query="DataTable" itemColumn="Dept_Name" valueColumn="AvgByDept">

</cfgraph>

This tag displays the values in the AvgByDept column of the DataTable query. It displays the Dept_Name column value as th e item label by each bar. The title “Salaries by Department” appears above the chart.

cfgraph tag can take the following information from a query:

The

Attribute Description

query The query containing the data. valueColumn The query column containing the values to be graphed. itemColumn (Optional) The query column conta ining the descripti on for this data

point. The item normally appears on the horizontal axis of bar and line graphs, on the vertical axis of horizontal bar graph s, a nd i n th e legend in pie charts.

Graphing Data 63

Attribute Description

URL (Optional) Works only with bar, horizontal bar, and pie charts in

Flash file format. A static prefix for all data point U RLs. Wh en the us er clic ks a ba r or

pie wedge, the page links to a URL created by appending the data from the data point’s

URLColumn value.

Use this attribute to specify a string that is part of all links on a chart, such as http://www.mycompany.com/myapp/salary_info/chart_details/.

URLColumn (Optional) Works only with bar, horizontal bar, and pie charts in

Flash file format. The query column containing the data point–specific part of a URL

to load when the user clicks the corresponding data point the graph. ColdFusion encodes the contents of the query field, in URL format (for example, replacing space characters with %20) and appends it to any static URL string in the

URL attribute to create a

full URL link. Use this attribute to do data drill-down from your charts. For more information on using URLs in grap hs , see “Linking

Dynamically from Graphs,” on page 77.

The ability to use queries of queries, as described in Chap ter 3, “Using Query Results in Queries” on page 34 provides significant power in generatin g the data for the chart. For example, you can use aggregating clauses operators such as SUM, AVG, and GROUP BY to create a query of queries with statistical data based on a raw database query.

You can also take advantage of the ability to reference and modify query data dynamically. For example, you can loop through the entries in a query column and reformat the data to show whole dollar values

Example: graphing a query of queries

The example in the following procedure analyzes the salary data in the CompanyInfo database and displays three graphs:

To graph a query of queries :

1 Create a new application page in ColdFusion Studio. 2 Edit the page so that it appears as follows:

<cfquery name="GetSalaries" datasource="CompanyInfo">

SELECT Departmt.Dept_Name,

Employee.Salary FROM Departmt, Employee WHERE Departmt.Dept_ID = Employee.Dept_ID

</cfquery>

64 Chapter 5 Graphing Data

<cfquery dbtype = "query" name = "DeptSalaries">

SELECT

Dept_Name,

AVG(Salary) AS AvgByDept FROM GetSalaries GROUP BY Dept_Name

</cfquery>

<cfloop index="i" from="1" to="#DeptSalaries.RecordCount#">

<cfset DeptSalaries.AvgByDept[i]=Round(DeptSalaries .AvgByDep t[i]/

1000)*1000>

</cfloop>

<title>Employee Salary Analysis</title>

</head>

<body> <h1>Employee Salary Analysis</h1>

<cfgraph type="bar"

query="DeptSalaries" valueColumn="AvgByDept" itemColumn="Dept_Name">

</cfgraph> <br>

</body> </html>

3 Save the page as graphdata.cfm in myapps under the Web root directory. For

example, the directory path on your machine might be: C:\Inetpub\wwwroot\myapps on Windows NT

4 Return to your browser and enter the following URL to view graphdata.cfm:

http://127.0.0.1/myapps/graphdata.cfm

Graphing Data 65

Reviewing the code

The following table describes the code and its function:

Code Description

SELECT Departmt.Dept_Name,

Employee.Salary FROM Departmt, Employee WHERE Departmt.Dept_ID =

Employee.Dept_ID </cfquery>

SELECT

Dept_Name,

AVG(Salary) AS AvgByDept FROM GetSalaries GROUP BY Dept_Name

</cfquery> <cfloop index="i" from="1"

to="#DeptSalaries.RecordCount#"> <cfset DeptSalaries.AvgByDept[i]=

Round(DeptSalaries.AvgByDept[i]

/1000)*1000>

</cfloop>

Query the Compan yIn fo dat abase to get the Dept_Name and Salary for each employee. Because the Dept_Name is in the Departmt table and the Salary is in the Employee table, you need a table join in the WHERE clause. The raw results of this query could be used elsewhere on the page.

Generate a new query from the GetSalaries query. Use the AVG aggregating function to get statistical data on the employees. Use the GROUP BY st atem en t t o e nsure tha t there is only one row for each department.

Loop through all the rows in DeptSalaries query and round the salary data to the nearest thousand. This loop uses the query variable RecordCount to get the number of rows and chan ges the cont ents of th e query object directly.

Graphing individual data points

When you graph individual data points you specify each data point by putting a

cfgraphdata tag in the cfgraph tag body. For example, the following code creates a

simple pie chart:

</cfgraph>

Create a bar graph using the data from the AvgByDept column of the DeptSalaries query. Label the bars with the D epartment names.

66 Chapter 5 Graphing Data

This pie chart displays the income values of four departments. Each cfgraph tag specifies a department’s income and the corresponding item description for the legend. The values are specified by individual ColdFusion variables. The title “Income by Department” appears above the chart.

cfgraphdata tag lets you specify the following information about a data point:

The

Attribute Description

value The data value to be graphed. item (Optional) The description for this data point. The item normally

appears on the horizontal axis of bar and line graphs, on the vertical axis of horizontal bar graphs, and in the legend in pie charts.

color (Optional) The co lor of the ba r or pie slic e. Ignored f or line and area

graphs.

URL (Optional) Works only with bar, horizontal bar, and pie charts in

Flash file format. A URL to load when the user clicks this data point. Use this

attribute to do data drill-down from your charts. For more information on using URLs in grap hs , see “Linking

Dynamically from Graphs,” on page 77.

Combining a query and data points

To graph data from both query and individual data value, you specify the query name and related attributes in the points and their appearance at tributes in

Data specified by a

cfgraphdata tag is graphed before (for example, to the left on a

bar chart) the data from a query. For example, if the database is missing data for one department, you can add th e

information manually:

<cfgraph type="bar" title="Salaries by Department" query="DataTable"

itemColumn ="Dept_Name" valueColumn="AvgByDept"

</cfgraph>

cfgraph tag and provide the additional data

cfgraphdata tags.

Controlling Graph Appearance 67

Controlling Graph Appearance

The cfgraph tag allows you to customize the appearance of your graph in many ways.

Common graph characteristics

You can optionally specify the following characteristics on all types of graphs:

Graph characteristic Attributes used Description

Title title

titleFont

File Type fileFormat Whether to send the graph to the user as a jpeg

Dimensions graphWidth

graphHeight

Background backgroundColor The backgroun d c olo r to us e f or the ent ire g r aph

The title to display on the graph and the font to use.

or Flash (.swf) file. Flash is the default format. On pie and bar charts, Flash supports rollover display of data values and data drill-dow n by clicking on the data point (using the attribute).

The width and height in pixels of the graph. This size defines the entire graph area, including the legend and background area around the graph.

You cannot use these attributes to change the ratio of the data area height to width. For example, you cannot set a large value to stretch just the horizontal dimension. To change the overall graph size, specify both the

graphHeight and graphWidth.

area, including legends and margins. You can specify any of the standard 256 W eb colors. Y o u can use any valid HTML color format. If you use the numeric format, you must use double pound signs, for example, ##CCFFFF.

URLColumn

graphWidth

Border borderWidth

3D Appearance

The border that surr ounds the graph . Y o u specify

borderColor

depth The depth of the shading that gives the graph

the width in pixels and the color using any valid HTML color format, as described for the

backgroundColor. A value of 0 means no

border.

three-dimensi onal appear ance, in pixels. A value of 0 (the default) means no 3D appearance.

68 Chapter 5 Graphing Data

Setting bar and horizontal bar chart characteristics

You can specify the following additional characteristics for bar and horizontal bar charts:

Graph characteristic Attributes used Description

Value labels showValueLabel

valueLabelFont valueLabelSize valueLocation

Value axis scaleFrom

scaleTo

Grid lines gridLin es The number of grid li nes between the top an d

Item labels showItemLabel

itemLabelFont itemLabelSize itemLabelOrientation

Labels that display the numeric value being graphed.

By default, value labels are on. You can turn them off or have them display when the user points to the bar (Flash file format only). You can specify the font type (Arial, Courier, or Times), point size, and location (OnBar or OverBar).

The minimum and maximum points on the data axis (vertical axis for bar charts, horizontal axis for horizontal bar charts.

By default the minimum is 0 and the maximum is the largest data value.

bottom of the graph. The value of each grid line app ears along th e

value axis. The horizontal grids only. A value of 0 (the default) means no grid lines.

Labels to show on the second axis of the chart.

Item labels are o n by defaul t if you specif y an

itemColumn (or for cfgraphdata tags, item)

attribute. You can specify the label font type (Arial, Courier, or Times), point size, and orientation (horizontal or vertical).

cfgraph tag displays

Controlling Graph Appearance 69

Graph characteristic Attributes used Description

Data point

colorList A comma-separated list of colors to use for

colors

Bar spacing barSpacing The space, in pixels, between bars.

Example: adding character to a bar graph

each bar. You can use any of the 256 standard Web

colors and any valid Web color name notation (for example, blue or ##FF33CC). You must use double pound signs with hexadecimal color notation. These colors replace the standard system-defined colors. If you specify fewer colors than data points, the colors repeat. If you specify more colors than data points, the extra colors are not used.

Any 3D shadow specified by the

depth

attribute app ears i n thi s sp ac e, so if y ou wa nt the background to appear between all bars, make the

depth value.

barSpacing value greater than the

The example in the following procedure adds a title to the bar graph and changes its appearance from the default, flat look, to a 3D look. It adds gridlines, sets the maximum Y-axis value to 100000, separates the bars, and uses a custom set of colors.

To enhance the bar graph:

1 Open graphdata.cfm in ColdFusion Studio. 2 Edit the

<cfgraph type="bar"

</cfgraph>

3Save the page.

cfgraph tag so that it appears as follows:

query="DeptSalaries" valueColumn="AvgByDept" itemColumn="Dept_Name"

title = "Average Salary by Department" depth = 10 scaleTo = 100000 itemLabelSize=16 itemLabelOrientation="horizontal" colorList = "red,orange,green,teal,purple" gridLines = 4 barSpacing = 15>

70 Chapter 5 Graphing Data

4 Return to your browser and enter the following URL to view graphdata.cfm:

http://127.0.0.1/myapps/graphdata.cfm

Reviewing the code

The following table describes the highlighted code and its function:

Code Description

title = "Average Salary by

Department"

depth = 10

scaleTo = 100000

itemLabelSize=16

itemLabelOrientation="horizontal"

colorList = "red,orange,

green,teal,purple"

gridLines = 4

barSpacing = 15

Put a title above the graph.

Give the graph 10 pixels of 3D "depth" shadow.

Set the maximum value of the vertical axis to 100000. The minimum value is the default, 0.

Make the labels on the horizontal axis 16 points.

Make the labels horizo nta l on the horiz ont al axis.

Get the bar colo rs f r om a c us tom l is t. In thi s example, the graph does not use purple because there are only four data points.

Display four grid lines between the top and bottom of the graph.

Separate the bars by 15 pixels of background.

Controlling Graph Appearance 71

Setting pie chart characteristics

You can specify the following additional characteristics for pie charts:

Graph characteristic Attributes used Description

Value labels showValueLabel

valueLabelFont valueLabelSize valueLocation

Legend showLegend

legendFont

Data point colors

colorList A comma separated list of colors to use for

Labels that display the numeric value being graphed.

Value labels are on by default. You can turn them off or have them display when the user points to the bar (Flash file format only). You can specify the font type (Arial, Courier, or Times), point size, and location (OnBar or OverBar).

A legend relating the pie slice colors to the data point Item descriptions from the

itemColumn attribute or cfgraphdata tag itemColumn attribute.

By default the legend appears to the left of the chart. You can als o spe cify above , below, right, and no ne. You can sp ecify the f ont type as Arial (the default), Courier, or Times.

each bar. You can use any of the 256 standard Web

colors and any valid Web color name notation (for example, blue or ##FF33CC). You must use double pound signs with hexadecimal color notation. These colors replace the standard system-defined colors.

If you specify fewer colors than data points, the colors repeat. If you specify more colors than data points, the extra colors are not used.

72 Chapter 5 Graphing Data

Example: adding a pie chart

The example in the following procedure adds a pi e chart to the page.

To cre ate a pie chart :

1 Open graphdata.cfm in ColdFusion Studio. 2 Edit the DeptSalaries query and the

<cfquery dbtype = "query" name = "DeptSalaries">

SELECT

Dept_Name,

SUM(Salary) AS SumByDept,

AVG(Salary) AS AvgByDept FROM GetSalaries GROUP BY Dept_Name

</cfquery>

<cfloop index="i" from="1" to="#DeptSalaries.RecordCount#">

<cfset DeptSalaries.SumByDept[i]=Round(DeptSalaries .SumByDep t[i]/

1000)*1000>

<cfset DeptSalaries.AvgByDept[i]=Round(DeptSalaries .AvgByDep t[i]/

1000)*1000>

</cfloop>

cfloop code so that they appear as follows:

3 Add the following cfgraph tag before the end of the body:

<cfgraph type="pie"

query="DeptSalaries" valueColumn="SumByDept" itemColumn="Dept_Name" title="Total Salaries by Department" titleFont="Times" showValueLabel="rollover" valueLabelFont="Times" borderWidth = 0 backgroundColor = "##CCFFFF" colorlist="##6666FF,##66FF66,##FF6666,##66CCCC" LegendFont="Times">

</cfgraph>

<br>

4Save the page. 5 Return to your browser and enter the following URL to view graphdata.cfm:

http://127.0.0.1/myapps/graphdata.cfm

Controlling Graph Appearance 73

Reviewing the code

The following table describes the highlighted code and its function:

Code Description

SUM(Salary) AS SumByDept,

<cfset DeptSalaries.SumByDept[i]=

Round(DeptSalaries.SumByDept[i]/

1000)*1000>

<cfgraph type="pie" query="DeptSalaries" valueColumn="SumByDept"

itemColumn="Dept_Name"

title="Total Salaries by

Department"

titleFont="Times" showvalue="rollover"

valueLabelFont="Times" borderWidth = 0

backgroundColor = "##CCFFFF" colorList = "##6666FF,##66FF66,

##FF6666,##66AAAA"

In the DeptSalaries query, add a SUM aggregation function to get the sum of all salaries per department.

In the cfloop, round the salary sums to the nearest thousand.

Create a pie graph using the SumByDept salary sum values from the DeptSalares query.

Use the contents of the Dept_Nam e column for the item la bels di splay ed in the chart legend.

Put a title above the graph. Format it in Times font.

Display the data value, in Times font, only when the user points to a pie slice.

Do not put a border around the chart Set the background for the entire chart

area to a light blue. Get the pie slice colors from a custom list,

which uses hexadecimal color numbers. The double pound signs prevent ColdFusion from trying to interpret the color data as variable names.

LegendFont="Times"

Use Times font for the legend.

74 Chapter 5 Graphing Data

Setting line and area graph characteristics

You can specif y the following additional characteristics for line-based graphs

Graph characteristic Attributes used Description

Value axis scaleFrom

scaleTo

Item labels showItemLabel

itemLabelFont itemLabelSize itemLabelOrientation

Line characteristics

lineColor lineWidth

The minimum and maximum points on the vertical axis.

By default the minimum is 0 and the maximum is the largest data value.

Labels to show on the horizontal axis of the chart.

By default, item labels are on if you sp ecify

itemColumn (or for cfgraphdata tags,

item) attribute. You can specify th e label font

type (Arial, Couri er , or T i mes), poi nt size, and orientation (horizontal or vertical).

These attributes specify the line format. For the line colo r, you can use any of the 256

standard W eb colors and any valid We b color name notation (for example, blue or ##FF33CC). You must use double pound signs with hexadecimal color notation. The default line color is blue.

You can also specify the line width in pixels. The default is 1 pixel.

Area fill fill Specifies whether to fill the area below the

Grid lines gridLin es The number of grid li nes between the top an d

Example: adding an area graph

The example in the following procedure adds an area graph showing the average salary by start date t o the sala ries analysis page. It shows the use of a se cond qu ery of queries to generate a new analysis of the raw data from the GetSalaries query; in this example, the average salary by start date. It also shows the u se of additional attributes.

To create an area graph:

1 Open graphdata.cfm in ColdFusion Studio.

line with the line color to form an area graph By default there is no fill.

bottom of the graph. The value of each grid line appears along the value axis. The

cfgraph tag displays horizontal grids only. A

value of 0 (the default) means no grid lines.

cfgraph

Controlling Graph Appearance 75

2 Edit the GetSalaries query so that it appears as follows:

<cfquery name="GetSalaries" datasource="CompanyInfo">

SELECT Departmt.Dept_Name,

Employee.StartDate,

Employee.Salary FROM Departmt, Employee WHERE Departmt.Dept_ID = Employee.Dept_ID

</cfquery>

3 Add the following code before the html tag:

<!--- You must explicitly convert the date to a number for the query

to work ---> <cfloop index="i" from="1" to="#GetSalaries.RecordCount#"> <cfset GetSalaries.StartDate[i]=NumberFormat(DatePa rt("yyyy" ,

GetSalaries.StartDate[i]) ,9999)> </cfloop>

<cfquery dbtype = "query" name = "HireSalaries">

SELECT

StartDate,

AVG(Salary) AS AvgByStart FROM GetSalaries GROUP BY StartDate

</cfquery>

<cfloop index="i" from="1" to="#HireSalaries.RecordCount#">

<cfset

HireSalaries.AvgByStart[i]=Round(HireSalaries.AvgByStart[i]/

1000)*1000>

</cfloop>

4 Add the following cfgraph tag before the end of the body tag block.

<cfgraph type="line"

query="HireSalaries" valueColumn="AvgByStart" itemColumn="StartDate" title="Average Salaries by Date of Hire" fileFormat="Flash" GraphWidth=400 BackgroundColor="##FFFF00" Depth=5 lineColor="teal" fill="yes" >

</cfgraph>

<br>

5 Save the page.

76 Chapter 5 Graphing Data

6 Return to your browser and enter the following URL to view graphdata.cfm:

http://127.0.0.1/myapps/graphdata.cfm

Reviewing the code

The following table describes the highlighted code and its function:

Code Description

Employee.StartDate,

<cfloop index="i" from="1"

to="#GetSalaries.RecordCount#"> <cfset GetSalaries.StartDate[i]= NumberFormat(DatePart("yyyy", GetSalaries.StartDate[i]) ,9999)>

</cfloop> <cfquery dbtype = "query" name =

"HireSalaries">

SELECT

StartDate,

AVG(Salary) AS AvgByStart FROM GetSalaries GROUP BY StartDate

</cfquery> <cfloop index="i" from="1"

to="#HireSalaries.RecordCount#"> <cfset HireSalaries.AvgByStart[i] =Round(HireSalaries.AvgByStart[i] /1000)*1000>

</cfloop> <cfgraph type="line"

query="HireSalaries" valueColumn="AvgByStart" itemColumn="StartDate"

title="Average Salaries by Date of Hire"

fileFormat="Flash"

Add the employee start date to the data in the GetSalaries query.

Use a cfloop to extract the year of hi re from each employee’s hire data and convert the result to a four-digit number.

Create a second query from the GetSalaries query. This query contains the average salary for each start year.

Round the salaries to the nearest thousand.

Create a line graph using the HireSalaries query. Graph the average salaries against the start date.

Title the graph.

Send the graph to the user as a Flash file.

GraphWidth=400

BackgroundColor="##FFFF00" Depth=5 lineColor="teal"

fill="yes"

Limit the graph width to 400 pixels. Generator automatically resizes the graph’s height to maintain the aspect ratio.

Display a 3D graph in teal against a yellow background.

Fill the region below the graph to create an area graph.

Linking Dynamically from Graphs 77

Linking Dynamically from Graphs

You can mak e Flash-format bar and pie charts in te ractive so that ColdFusion displays a new data point–specific Web page when the user clicks a bar or pie wedge. ColdFusion provides two methods for specifying the destination page:

• For data points from queries, ColdFusion takes the value of the attribute, appends the value of the query column specifie d by the attribute, and sends the resulting Web request.

• For data points from

URL attribute as the page to link to.

cfgraphdata tags, ColdFu sion uses th e value of the tag’s

Using ColdFusion you can combine a static URL component with a query column component. This lets you link dynamically based on query column data without having to format the column contents as a URL. For example, you can use the values of the Dept_Name field in the CompanyInfo database to determine the data to display. To do this, follow these guidelines:

• In the cfgraph tag, specify a single Web page in the URL attribute.

• In the URL attribute, include the name of a parameter, but not its value, in the

form ParameterName

• In the URLColumn attribute, specify a query column that contains the value of the parameter being passed.

• In the target page, determine the data to be displayed based on the parameter that gets passed.

cfgraph URL

URLColumn

The example code in the following procedure illustrates this technique.

Example: dynamically linking from a pie chart

In the following example, when you click a pie wedge, ColdFusion displays a table containing the detailed salary information for the departments represented by the wedge. The example is divided into two parts: creating the detail page and making the graph dynamic.

Part 1: Creating the detail page

1 Create a new application page in ColdFusion Studio.

This page displays the drill-down information on the selected department based on the department name passed as the URL parameter.

2 Edit the page so that it appears as follows:

SELECT Departmt.Dept_Name,

Employee.FirstName, Employee.LastName, Employee.StartDate, Employee.Salary,

Employee.Contract FROM Departmt, Employee WHERE Departmt.Dept_Name = ’#URL.Dept_Name#’ AND Departmt.Dept_ID = Employee.Dept_ID

78 Chapter 5 Graphing Data

ORDER BY Employee.LastName, Employee.Firstname

</cfquery>

<title>Employee Salary Details</title>

</head>

<body>

<h1><cfoutput>#GetSalaryDetails.Dept_Name[1]# Department

Salary Details</cfoutput></h1> <table border cellspacing=0 cellpadding=5> <tr>

<th>Employee Name</td> <th>StartDate</td> <th>Salary</td>

<th>Contract?</td> </tr> <cfoutput query="GetSalaryDetails" > <tr>

<td>#FirstName# #LastName#</td>

<td>#dateFormat(StartDate, "mm/dd/yyyy")#</td>

<td>#numberFormat(Salary, "$999,999")#</td>

<td>#Contract#</td> </tr> </cfoutput> </table> </body> </html>

3 Save the page as Salary_details.cfm in myapps under the Web root directory.

Linking Dynamically from Graphs 79

Reviewing the code

The following table describes the code and its function:

Code Description

<cfquery name="GetSalaryDetails"

datasource="CompanyInfo">

SELECT

Departmt.Dept_Name, Employee.FirstName, Employee.LastName, Employee.StartDate, Employee.Salary,

Employee.Contract FROM Departmt, Employee WHERE

Departmt.Dept_Name =

’#URL.Dept_Name#’

AND Departmt.Dept_ID =

Employee.Dept_ID ORDER BY Employee.LastName,

Employee.Firstname

</cfquery> <table border cellspacing=0 cellpadding=5>

<tr>

<th>Employee Name</td> <th>StartDate</td> <th>Salary</td> <th>Contract?</td>

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

<td>#FirstName# #LastName#</td> <td>#dateFormat(StartDate,

"mm/dd/yyyy")#</td> <td>#numberFormat(Salary, "$999,999")#</td> <td>#Contract#</td>

</tr> </cfoutput> </table>

Get the salary data for the department whose name was passed in the URL parameter string. Sort the data by the employee’s las t and first names.

Display the data retrieved by the query as a table. Format the start date into standard month/date/year format, and format the salary with a leading dolla r sign comma separator, and no decimal places.

Part 2: Making the graph dynamic

1 Open graphdata.cfm in ColdFusion Studio. 2 Edit the

<cfgraph type="pie"

cfgraph tag for the pie chart so it appears as follows:

query="DeptSalaries" valueColumn="SumByDept" itemColumn="Dept_Name"

URL="Salary_Details.cfm?Dept_Name=" URLColumn="Dept_Name"

80 Chapter 5 Graphing Data

title="Total Salaries by Department" titleFont="Times" showValueLabel="rollover" valueLabelFont="Times" backgroundColor = "##CCFFFF" borderWidth = 0 colorlist="##6666FF,##66FF66,##FF6666,##66CCCC" LegendFont="Times">

</cfgraph>

3 Save the page. 4 Return to your browser and enter the following URL to view graphdata.cfm:

http://127.0.0.1/myapps/graphdata.cfm. Click the slices of the pie chart.

Reviewing the code

The following table describes the highlighted code and its function:

Code Description

URL="Salary_Details.cfm?

Dept_Name="

URLColumn="Dept_Name"

When the user clicks a data point, call the Salary_Details.cfm page in the current directory, and pass it the parameter named Dept_Name. The parameter value must come from the

URLColumn attribute.

Complete the URL string with the value from the query Dept_Name field. So, if the Dept_Name is HR, ColdFusion calls the following URL: Salary_Details.cfm?Dept_Name=HR

MACROMEDIA COLDFUSION 5-DEVELOPING, ColdFusion 5 Develop Manual

Specifications and Main Features

Frequently Asked Questions

User Manual