Macromedia COLDFUSION 5-DEVELOPING APPLICATIONS User 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 should not be constru ed as a commitm ent by Macrom edia, Incorpor ated. 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 Access, 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, and product names are trademarks of their respective holder(s).

Part number: ZCF50MDEV

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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/Filename/Au thor

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

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/Filename/Au thor

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

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

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 envi ronment . . . . . . . . . . . . . . . . . . . 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/Filename/Au thor

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 Mess ages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 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 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 07

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/Filename/Au thor

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 Security Features. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 356

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

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

Using Adva nced 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/Filename/Au thor

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 Audi ence...................................................................................................xvi

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

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

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

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

• Contacting Macromedia............................................................................................x x

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

accelerate development.

Query of queries

Easily integrate data from heterogeneous sour 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 fu ll-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

Core engine tuning

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

Incremental page delivery

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

Easy managment

Wire protocol database drivers

Application deployment services

Deliver high-performance ODBC connectivity using new drivers.

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

Enhanced application monitoring

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

SNMP support

Monitor ColdFusion applicatio ns from enterprise management systems.

Expanded integration

Expanded Linux support

Deploy on additional Linux distributions, including SuSE and Cobalt.

Enhanced hardware load balancer integration

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

Enhanced COM support

Experience easier integration with COM components.

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

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

ColdFusion Dev Center

Macromedia Alliance

General inf ormation about Macrom edia products and services

Detailed product information on ColdFusion and related topics

Professional support 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 produc t s

Information about classes, on-s ite 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

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

www.coldfusion.com/developer/ coldfusionreferencedesk/

www.coldfusion.com/partners/

About ColdFusion Documentation

ColdFusion documentation is designed to provide support for the comp lete spectrum of participant s . T he p rint a nd onl ine v e rsions are organized to allow y ou 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-bal ancing for your site

Describes on how to develop your dynamic Web applications, including retrievi ng and u pdating y our dat a, usin g structu res, an d 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 We b server running ColdFusion: http://localhost/coldfusio n/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

T o r ead printe d documentation, locate the Ado be Acroba t PDF files installe d with the product. The PDF files offer excellent 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 devel opers on th e forum can help y ou f igur e out ho w to do jus t abo ut a nythin g 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 Fo rums is a gr e at r es our ce for learning C oldF u 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 make 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 ColdFu sion

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 W e b to h andle bu sin ess tr ans actions and con duct the day-t o - da y bu siness of your organiza t i on.

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 crea te 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 with some SQL is helpful as you develop y our C oldFu sion application s . In 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 referenced with a specifi c file extension.

• 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 busines s sy stems such as RDBMS, messaging se rvers, file r epo sitories, d irect ory servers, and d istributed ob ject middleware. ColdFusion Enterprise also offers advanced security features, load balancing, server failover, and visual cluster administration.

ColdFusion Feature s and Comp one nts 5

ColdFusion Features and Components

ColdFusion provides a comprehensive set of features and components for developing and managing your Web applications. Usin g th e 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 des cribes the C oldfF usion featur es that let you manage y our 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 (Enterprise 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 connectiv 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

• Standards-based in tegration 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 Java

Security • Integration with existing authentication 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 security for protecting multi ple ap plicat ions 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/developer/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 source s

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 Feature s and Comp one nts 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 deve lopment platf orm language is mor e tha n 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 ColdFusion Server.

ColdFusion Administrator

Data sources

Y o u use the Adm inistr ato r to configure various ColdFusion Server options , inclu ding:

• ColdFusion data sources

• Debugging output

• Server settings

• Application security

• Server clustering

• Scheduling page ex ecu t ion

• Directory mapping

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

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 us ing OLE-DB, native da tabase drivers , or dir ectory servers that su pport 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 request, retrieves any data if necessary, and routes the data through the Web server, back to your browser.

The following steps describe in more detail what happens 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 proce sses all CFML tags. 4 ColdFusion Server then returns only HTML and other client-side technologies to

the W e b server. 5 The Web server passes the page back to the browser.

Chapter 2

Writing Your First Co ldFusion 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 Pa ges.......................................................... 10

• Wor king 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 pages

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 ColdFusion 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 W eb serv er can publish the se pages to the In ternet. F or example , you can create a directory

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

myapps and save your practice pages there.

Viewing application pages

Y ou vie w the applicat ion page on the Web server to ensure that the code is wo rking 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.

T o 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 contains bo th HT ML an d C F ML. You used the CFML tag then used the CFML tag c following table describes th e code and its function:

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

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

Code Description

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.

<cfoutput> I’d like to talk t o someon e in #Department#. </cfoutput>

Displays whatever appears between the opening and closing

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

cfoutput tags; in this example, the

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 out putting variables.

A variable stores data that you can use in applicatio ns. As with oth er programming languages, you set vari ables in Co ldF usion to stor e da ta that you want to access lat er. You reference a range of variables to perform 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

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:

Scope types

Variables differ in the source the data came from, the places in your code where they are meaning ful, and how lo ng their values per sist. Thes e consid eratio ns ar e gener ally referred to as a variable’s scope.

ColdFusion has many dif ferent scope types, 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). Local 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 scop es, such as the local scope, do not require the scope identifier prefix, while others do. However, it is good programming practice to us e 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 variables. Other chapt ers in this book discuss additional scope 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)

V ariables Va riable s created usin g cfset or cfp aram, wi th or without

specifying the sco pe prefix. You must defin e the variab le on the current page or a page you include using

cfinclude.

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

cfform tag block and processed on an action page.

URL URL Variables passed to a page a s URL query string

parameters.

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 poun d 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 us e 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.

The followin g table ill ustrate s the bas ic use of pou nd signs . F o r a de tailed de scription 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>

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

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

I’d like to talk to someone in Sales. ColdFusion sees Reverse(Department) as

text and displays it unchanged. The HTML page displays:

The department name spell ed 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 th e resu lt. The pound sign s tel l

cfoutput to interpret Reverse as a

ColdFusion function. The

Reverse function

uses the Department variable name. The HTML page displays:

The department name spell ed 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

</cfoutput>

</body>

</html>

3 Save the file.

least #Salary#.

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

browser, so users do not see them if th ey view the HT ML sour ce . C oldF us ion does 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 operatin g syste ms are case-sensitive, so yo u sh ould be con sisten t 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 Sources ............................................................................................ 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 Querying 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:

• ColdFusion data 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 Querying 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 information, 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 wit hi n an organization.

The best way to familia riz e y o urself w ith the c apa bil itie s o f y our data base 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 (ODBC) 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 database s

• 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 li st 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 conne ctions. 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 da ta s ource dynamically. It eliminates the ne ed fo r yo u

to add a data source for each database on your server. It also allows a ColdFusion application to run on multiple servers w ith out havin g t o statically configu re each server independently.

22 Chapter 3 Querying 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 Cold 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.

T o add a data source:

1 Start the ColdFusio n Admin ist ra tor.

On Windows, selec t 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.

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

hostname/CFIDE/administrator in

Accessing Data Sources 23

4 Name the data source CompanyInfo. 5 On Windows Select Microsoft Access 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 p ath to the CompanyInfo directory, typically /opt/coldfusion/d atabase/CompanyInfo.

8 Click Create to create the

CompanyInfo data source.

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

<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 data base for a list of connect string properties.

cfquery, cfinsert, cfupdate, cfgridupdate, and

24 Chapter 3 Querying a Database

Adding data source notes and considerations

When adding data sources to ColdFusio n Server, keep these guideli n es in mind:

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

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

• Data source names should not contain special c haracters.

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

<cfquery name = "datelist"

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

DATABASE=pubs">

SELECT * FROM authors

</cfquery>

connectstring attribute. For example, you could

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 resul t set, 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 cf query 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 variable 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 retrieved 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 statements 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 Querying a Database

• When referencing text literals in SQL, use single quotes (’). For example, Select

* from mytable WHERE FirstName=’Russ’

which the first name is Russ.

selects every rec o r d 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 columns.

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.

cfoutput tag later on the page.

name

Basic SQL syntax elements

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

Statements

A SQL statement al ways 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 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 conditions 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 values 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 Querying a Database

• Some DBMS vendors use n o nstandard SQL syntax (known as a dialect ) 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 query 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 bro wse r.

The ColdFusion Server creates the EmpList data set, but only HTML and text is sent back to the browser so you ju st see the head ing “Employee List”. To display 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 retrieves data from the CompanyInf o database. The following table describes th e code and its function:

Code Description

SELECT FirstName, LastName, Salary, Contract FROM Employee

</cfquery>

Queries the database specified in the CompanyInfo data source

Gets information from the FirstName, LastName, Salary, and Contract fields in the Employee table

Ends the cfquery block

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

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

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

30 Chapter 3 Querying 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 the code contain ed wit hin the each row returned from a database.

• You must reference specific 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 specify 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.

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

query attribute:

cfoutput block, once for

cfoutput block to output

cfoutput tag, you

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

fields of each

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 retur ned from the quer y.

cfinclude tag).

Emplist.FirstName.

32 Chapter 3 Querying 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 data 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>

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

cfoutput.

Note

The variable

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

it took for the query to complete. D o not prefix the variable n ame with 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 number of records retriev ed in the EmpList

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 quer y property within 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. Inste ad, prefix the property with the name of the query.

cfoutput tag query attribute when you o utput the RecordCount

34 Chapter 3 Querying 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 C oldF usion. You can query a database once and use the r esults 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 unio n on queries from dif fer ent databas es to eli minate

duplicat es for a mailing l is t.

• 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 database and use its results in three separate queries to generate the summaries.

• You can make drill-down, master-detail-like functionality where 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 application displays the employee details by selecting information from the cached query without accessing the database.

Creating queries of queries

Y ou 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 Results in Queries 35

Boolean predicates:

LIKE NOT LIKE IN NOT IN BETWEEN NOT BETWEEN AND OR

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 sp ec ifi ed time expi r e s. Note that you must use

CreateTimeSpan function to specify the cachedwithin attribute va l ue.

the

Aggregate functions:

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

You cannot nest aggregate functions.

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 use of a mast er query and a single detail query that extracts information from the master. A more extended example would use multiple de tail queries to get different information from the same master query.

36 Chapter 3 Querying a Database

T o 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 directory. 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 bro wse r.

Using Query Results 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>

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 source 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 query as the sourc e 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.

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

</cfoutput> <br>

#EmpList.columnlist#<br>

</cfoutput> <cfoutput>

#QueryFromQuery.columnlist#<br>

</cfoutput>

Use the QueryFromQuery query to display the list of employee IDs, first names, and last names.

List all th e columns retu rned by the Emplist query.

List all th e columns retu rned by the QueryFromQuery query.

38 Chapter 3 Querying a Database

Chapter 4

Retrieving and Formatting Data

This chapter explains ho w to sel ect the data to displa y in a dynamic 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 particular 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 re trieve 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 that 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 act ion page to whic h 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 Specify the Data to Retrieve 41

<form name="getReportCriteria"

action="runReport.cfm?RequestTimeout=120" method="post">

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="R eset " name=" Contr olNam e" value ="Dis playN ame"> <input type="Submit" name="ControlName"

value="DisplayName">

Use the following procedure to create a sample form.

To cre at e 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"

Last Name: <input type="Text" name="LastName" size="20"

Salary: <input type="Text" name="Salary" size="10" maxlength="10">

</p>

maxlength="35"><br>

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

</p>

value="Marketing">Marketing<br>

Using Forms to Specify the Data to Retrieve 43

<p> Contractor? <input type="checkbox" name="Contractor" value="Yes"

</p>

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

checked>Yes

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

Gather the information from this form using the Post method, and do som ething with it on t he page actionpage.cfm.

<input type="Text" name="FirstName"

size="20" maxlength="35">

Create a text box calle d FirstName wh ere users can enter their first name. Make it 20 characters wide, but allow input of up to 35 characters.

<input type="Text" name="LastName"

size="20" maxlength="35">

Create a text box called La stName where users can enter their first name. Make it 20 characters wide, but allow input of up to 35 characters.

<input type="Text" name="Salary"

size="10" maxlength="10">

Create a text box called Salary where users can enter a salary to look for. 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 li st 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 text Cle ar Form on the button.

Create a submit button to se nd 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 t he 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 drop-down select list.

• Use list boxes to allow 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 application page except that you can use the form variables that are passed to it from an associated form. The following sections describe 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 submitted.

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 ari ab le ’s name is th e nam e that you a ssigned to th e form c ontrol o n the form page. Refer to the form variable by name within tags, functions, and other expressions on an action page.

Because Form variables extend beyond the loca l 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 SQL WHE RE 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 co mparison:

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 5 Enter data, for example, Smith, in the Last Name box and submit the form.

6 Click Return in your browser to redisplay the form. 7 Remove the check mark from the check box and submit the form again.

formpage.cfm in your browser.

The browser displays a line with the first and last name and salary for each entry in the database that ma tch the name you typed, followed by a line with the text “Contractor: Yes”

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 FirstNam e, La stName, Sal ary 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 would us e the var iab le in the query.

Testing for a variable’s existence

Before relying on a v ariable’s exis tence in an app lication 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 passed to the IsDefined function must always be enclosed in double quotes. For more information on the IsDefined function, see the CF ML Reference.

If you attempt to evaluate a variable that you did not define, ColdFusion cannot process the page and displays an error message. To help di agnose 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 consideration s

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 sections further y our u nders tandi ng 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.

T o put the query results in a table:

1 Return to the file a ctionpage.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>

cfoutput tag inside the table tags. You can also use the HTML th

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 tab le row until 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 th e CFML expression

DollarFormat(number).

T o 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 str ings in a SQL WHERE cl ause when you want to co mpa r e a valu e a gai ns t a character string field so that the query re 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 case 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 Departmt table. This i ncludes 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 ensure s th at ea ch row of the query results contains the dep artmen t na me fr o m 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 j oins, keep the following information in mind:

• Prefix each column in the SELECT statement to explicitly 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 identifies another 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 buil d 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.

T o 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">

</cfif> <cfif Form.LastName IS NOT "">

</cfif>

</cfquery>

AND Departmt.Dept_Name = ’#Form.Department#’

AND Employee.LastName = ’#Form.LastName#’

<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 b ased on the De pt_ID fi eld i n 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 quer y 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 determi ne whether a search has retrieved records, use the variable. You can use t he 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>

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 indicate the end of the conditional code.

RecordCount query

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 ta g 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 li st as it gets retrieved.

The first ti me 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. You 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 following ex ample uses the

cfloop tag and the rand() random number

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 cod e use s two l oop s. The outer loop is repeat ed ten times , once for 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 explains h o w to us e the cfgraph tag to disp lay grap hs . 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 ser ver, 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 you r needs.

Graph types

You can create the fo llowing 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.)

query The query containing the data. valueColumn The query column containing the values to be graphed. itemColumn (Optional) The query column conta ining the descri ption for this dat a

point. The item normally appears on the horizontal axis of bar and line graphs, and in pie charts.

bar, horizontalbar, pie, or

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 gra ph with the information:

<cfgraph type="bar"

</cfgraph>

query="DataTable" valueColumn="AvgByDept" itemColumn="Dept_Name">

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 query, you specify the query name in the cfgraph tag query attribute. In this format the provide the might look like this:

<cfgraph type="bar" title="Salaries by Department"

</cfgraph>

This tag displays the values in the AvgByDept column of the DataTable query. It displays the Dept_Name column value as the 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

<\cfgraph> end tag. For example, a simple cfgraph tag fo r a bar chart

query="DataTable" itemColumn="Dept_Name" valueColumn="AvgByDept">

cfgraph tag. You can supply data in several ways:

cfgraphdata tags.

cfgraph tag body is empty. However, you must still

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 descri ption for this dat a

point. The item normally appears on the horizontal axis of bar and line graphs, on the vertical axis of horizontal bar graphs, and in 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 dat a poi nt URLs . When th e user cl icks a bar 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 graphs, see “Linking

Dynamically from Graphs,” on page 77.

The ability to use queries of queries, as described in Chapter 3, “Using Query Results in Queries” on page34 provides sign ificant power in genera ting 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 U RL 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 CompanyIn fo dat a base to get the Dept_Name and Salary for each employee. Because the Dept_Name is in the Departmt table and the Sala ry 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 statemen t to ensure that 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 changes the content s of the query object directly.

Create a bar graph using the data from the AvgByDept column of the DeptSalaries query. Label the bars with the Department names.

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>

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 color o f the bar or pi e slice. I gnored for li ne 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 graphs, 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 attri butes 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 the

information manually:

<cfgraph type="bar" title="Salaries by Department" query="DataTable"

</cfgraph>

itemColumn ="Dept_Name" valueColumn="AvgByDept"

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 yo ur 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 background color to use for the ent ire g raph

Border borderWidth

borderColor

3D Appearance

depth The depth of the shading that gives the graph

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-down by clicking on the data point (using the attribute).

The width and height in pixels of the graph. This size defines the en tire 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 st andard 256 Web colors. You can use any valid HTML color format. If you use the numeric format, you must use double pound signs, for example, ##CCFFFF.

The border that surroun ds the graph. You specify 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-dimension al appearance, in pi xels. A value of 0 (the default) means no 3D appearance.

URLColumn

graphWidth

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 gridLines The number of grid lines between the top and

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 appears along the

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 ar e on b y de fault i f you spec ify 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 colors

colorList A comma-separated list of colors to use for

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.

Bar spacing barSpacing The space, in pixels, between bars.

Any 3D shadow specified by the

depth

attribute appears in this sp ace, so i f you want the background to appear between all bars, make the

depth value.

barSpacing value greater than the

Example: adding character to a bar graph

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 U RL 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 horizont al on the ho rizont a l axis.

Get the bar colors f rom a c us tom l is t. In thi s example, the graph does not use purp le 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 also speci fy abo ve, be low, right, and none. You can specify the f ont ty pe 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 fo llowing procedure adds a pie chart to the page.

To cre at e a pie chart:

1 Open graphdata.cfm in ColdFusion Studio. 2 Edit the DeptSa laries 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 U RL 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"

LegendFont="Times"

In the DeptSalaries query, add a SUM aggregation function to get the sum of all salaries pe r 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_Name column for the item label s disp layed 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 slic e 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.

Use Times font for the legend.

74 Chapter 5 Graphing Data

Setting line and area graph characteristics

You can spe cify 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

Area fill fill Specifies whether to fill the area below the

Grid lines gridLines The number of grid lines between the top and

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 specify

itemColumn (or for cfgraphdata tags,

item) attribute. You can specify the label fon t

type (Arial, Courier, or Times), point s ize, and orientation (horizontal or vertical).

These attributes specify the line format. For the line color, you can use any of the 256

standard We b colors and any v alid Web 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 spec ify the line width in pixels. The default is 1 pixel.

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.

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 sho ws th e u se of a se con d 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 use of additional attributes.

T o create an area graph:

1 Open graphdata.cfm in ColdFusion Studio.

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

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 U RL 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"

GraphWidth=400

BackgroundColor="##FFFF00" Depth=5 lineColor="teal"

fill="yes"

Add the employee start date to the data in the GetSalaries query.

Use a cfloop to extract the year of hire 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.

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 make Flash-format bar and pie charts interactive 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 f rom queries, ColdFusion takes t he value of the attribute, appends the va lue of the query column specified by the attribute, and sends the resulting Web request.

• For data points from

URL attribute as the page to link to.

cfgraphdata tags, ColdFusion uses the 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.

The example code in the following procedure illustrates this technique.

cfgraph URL

URLColumn

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 Salar y_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 last 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 dollar 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 U RL 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 APPLICATIONS User Manual

Specifications and Main Features

Frequently Asked Questions

User Manual

Contents

About This Book

Intended Audience

New Features

Developer Resources

About ColdFusion Documentation

Printed and online documentation set

Viewing online documentation

Printing ColdFusion documentation

Getting Answers

Contacting Macromedia

Introduction to ColdFu sion

A Quick Web Overview

Before You Begin

HTML

Relational database design and management

What is ColdFusion?

Editions of ColdFusion

ColdFusion Features and Components

About the features

About the components

How ColdFusion Server Works

Writing Your First Co ldFusion Application

The Development Process

Working with ColdFusion Application Pages

About applicaton pages

Creating application pages

Saving application pages

Viewing application pages

Working with Variables

About variables

Adding more variables to the application

Development Considerations

Querying a Database

Publishing Dynamic Data

Understanding Database Basics

Understanding Data Sources

About Open Database Connectivity

Accessing Data Sources

Adding data sources

Specifying data sources dynamically

Retrieving Data

The cfquery tag

The cfquery tag syntax

Writing SQL

Basic SQL syntax elements

SQL notes and considerations

Building Queries

Query notes and considerations

Outputting Query Data

Query output notes and considerations

Getting Information About Query Results

Query properties notes and considerations

Using Query Results in Queries

Query of query benefits

Creating queries of queries

Performing a query on a query

Retrieving and Formatting Data

Using Forms to Specify the Data to Retrieve

form tag syntax

Form controls

Form notes and considerations

Working with Action Pages

Processing form variables on action pages

Dynamically generating SQL statements

Creating action pages

Form variable notes and consideration s

Working with Queries and Data

Using HTML tables to display query results

Formatting individual data items

Performing pattern matching

Filtering data based on multiple conditions

Creating table joins

Building flexible search interfaces

Returning Results to the User

If there are no query results