Home » Adobe Manuals » Computer Software » Adobe 38043740 » Manual Viewer

Adobe 38043740 Developing Applications - Page 448

Using aggregate functions, Using group by and having expressions

UPC - 883919135168

Add to My Manuals
Save this manual to your list of manuals

Page 448 highlights

DEVELOPING COLDFUSION 9 APPLICATIONS 443 Accessing and Using Data Using aggregate functions Aggregate functions operate on a set of data and return a single value. Use these functions for retrieving summary information from a table, as opposed to retrieving an entire table and then operating on the recordset of the entire table. Consider using aggregate functions to perform the following operations: • To display the average of a column • To count the number of rows for a column • To find the earliest date in a column Since not every relational database management system (RDBMS) supports all aggregate functions, refer to the documentation of your database. The following table lists the aggregate functions that Query of Queries supports: Function AVG() COUNT() MAX() MIN() SUM() Description Returns the average (mean) for a column. Returns the number of rows in a column. Returns the largest value of a column. Returns the lowest value of a column. Returns the sum of values of a column. Syntax aggregate_func ::= (* | column_name) | AVG | SUM | MIN | MAX) ([ALL | DISTINCT] numeric_exp) Example The following example uses the AVG() function to retrieve the average IQ of all terriers: SELECT dog_name, AVG(dog_IQ) AS avg_IQ FROM Dogs WHERE breed LIKE '%Terrier'; Arbitrary expressions in aggregate functions Query of Queries supports aggregate functions of any arbitrary expression, as follows: SELECT lorange, count(lorange+hirange) FROM roysched GROUP BY lorange; Aggregate functions in arbitrary expressions Query of Queries supports mathematical expressions that include aggregate functions, as follows: SELECT MIN(lorange) + MAX(hirange) FROM roysched GROUP BY lorange; Using group by and having expressions Query of Queries supports the use of any arbitrary arithmetic expression, as long as it is referenced by an alias. Last updated 1/20/2012

Section	Page
Contents	3
Chapter 1: What’s New	6
What’s new in ColdFusion 9.0 Update 1	6
What’s new in ColdFusion 9	10
ORM support	10
Flex and AIR integration	10
Adobe AIR applications	10
Enhanced Flash Remoting	10
Language enhancements	10
CFScript	10
onServerStart	11
Other language enhancements	11
Integration with other products and technologies	11
New Ajax controls	11
Integration with SharePoint	12
Office file interoperability	12
Integration with Apache Solr server	12
Performance enhancements	12
Granular control over caching	12
In-memory files	13
Improved Clustering	13
Other performance enhancements	13
Database enhancements	13
DataDirect	13
datasource is optional attribute	13
Code Analyzer	13
Service features	14
ColdFusion as a service	14
Other enhancements	14
Server Manager	14
Built-in support for portlet standards	14
PDF functionality	15
IMAP support	15
JRE specifications	15
Chapter 2: Introduction	16
Using the Developing ColdFusion Applications guide	16
About Adobe ColdFusion 9 documentation	16
Documentation set	16
Viewing online documentation	16
Chapter 3: Introducing ColdFusion	17
About Internet applications and web application servers	17
About web pages and Internet applications	17
About web application servers	17
About ColdFusion	18
Saving ColdFusion pages	19
Testing ColdFusion pages	19
Elements of ColdFusion	19
Verity Search Server	21
ColdFusion Administrator	21
About J2EE and the ColdFusion architecture	21
About ColdFusion and the J2EE platform	21
Chapter 4: The CFML Programming Language	22
Elements of CFML	22
CFML Basics	22
Comments	22
Tags	23
Tag syntax	23
Built-in tags	24
Custom tags	25
Tags as functions and operators	25
cflock	26
cftransaction	27
cfthread	27
Functions	34
Built-in functions	34
Implicit Get and Set Functions	34
User-defined functions	36
ColdFusion components	36
Constants	37
Variables	37
Expressions	37
Data types	37
Flow control	38
cfif, cfelseif, and cfelse	39
cfswitch, cfcase, and cfdefaultcase	39
cfloop and cfbreak	40
cfabort and cfexit	41
Character case	41
Special characters	42
Reserved words	42
CFScript	43
Using ColdFusion Variables	43
Creating variables	44
Variable naming rules	44
Variable characteristics	45
Data types	45
Numbers	46
Strings	47
Lists	48
Boolean values	48
Date-Time values	48
Binary data type and binary encoding	50
Complex data types	50
Multiple references to an object	53
Assigning objects to scopes	53
Copying and duplicating objects	53
Using a query column	53
Using periods in variable references	54
Understanding variables and periods	54
Creating variables with periods	56
Data type conversion	56
Operation-driven evaluation	56
Conversion between types	57
Evaluation and type conversion issues	58
Examples of type conversion in expression evaluation	60
About scopes	61
Scope types	61
Creating and using variables in scopes	63
Using scopes	65
Ensuring variable existence	66
Testing for a variable’s existence	66
Using the cfparam tag	67
Validating data	68
Passing variables to custom tags and UDFs	68
Passing variables to CFML tags and UDFs	68
Passing variables to CFX tags	69
Using Expressions and Number Signs	69
Expressions	69
Operator types	70
Operator precedence and evaluation ordering	73
Using functions as operators	73
Using multiple assignments in one expression	74
Using number signs	75
Using number signs in ColdFusion tag attribute values	75
Using number signs in tag bodies	76
Using number signs in strings	76
Nested number signs	77
Using number signs in expressions	77
Dynamic expressions and dynamic variables	77
About dynamic variables	78
About dynamic expressions and dynamic evaluation	78
Dynamic variable naming without dynamic evaluation	79
Using dynamic evaluation	80
Using the IIF function	83
Example: a dynamic shopping cart	84
Using Arrays and Structures	87
About arrays	88
Basic array concepts	88
About ColdFusion arrays	88
Basic array techniques	89
Referencing array elements	89
Creating arrays	89
Using implicitly created arrays	92
Adding elements to an array	92
Deleting elements from an array	93
Copying arrays	94
Populating arrays with data	94
Populating an array with the ArraySet function	95
Populating an array with the cfloop tag	95
Populating an array from a query	96
Array functions	97
About structures	98
Structure notation	98
Referencing complex structures	99
Creating and using structures	101
Creating structures	101
Using implicitly created structures in functions and tags	102
Adding and updating structure elements	102
Getting information about structures and keys	103
Copying structures	104
Deleting structure elements and structures	106
Looping through structures	106
Structure examples	107
Example file newemployee.cfm	107
Example file addemployee.cfm	109
Structure functions	110
Extending ColdFusion Pages with CFML Scripting	111
About CFScript	111
Comparing tags and CFScript	112
Language Enhancements in ColdFusion 9	113
New tag equivalents in CFScript	113
Script Functions added in ColdFusion 9	114
Reserved words introduced in ColdFusion 9	114
What is supported in CFScript	114
Tag equivalents in CFScript	114
Reserved words	116
Script functions	116
The CFScript language	116
Identifying CFScript	116
Variables	117
Expressions and operators	117
Statements	117
Statement blocks	117
Comments	118
Differences from JavaScript	118
CFScript limitation	119
Using CFScript statements	119
Using assignment statements and functions	119
Using conditional processing statements	119
Using looping statements	121
Using continue	125
Using break	125
for-in construct (for arrays)	125
var declaration within for loop	126
Defining components and functions in CFScript	126
Basic Syntax	126
Setting attributes	128
Specifying page encoding	128
Accessing component metadata	129
Support for creating custom metadata	129
Explicit function-local scope	130
Using system level functions	130
Import and new operations using cfimport	131
Handling exceptions	132
Exception handling syntax and rules	132
Exception handling example	133
CFScript example	133
Using Regular Expressions in Functions	136
About regular expressions	136
Using ColdFusion regular expression functions	137
Basic regular expression syntax	137
Regular expression syntax	138
Using character sets	138
Finding repeating characters	139
Case sensitivity in regular expressions	139
Using subexpressions	140
Using special characters	140
Using escape sequences	142
Using character classes	143
Using backreferences	144
Using backreferences in replacement strings	145
Omitting subexpressions from backreferences	146
Returning matched subexpressions	146
Specifying minimal matching	148
Regular expression examples	148
Regular expressions in CFML	149
Types of regular expression technologies	149
Chapter 5: Building Blocks of ColdFusion Applications	151
Creating ColdFusion Elements	151
About CFML elements that you create	151
Including pages with the cfinclude tag	151
Using the cfinclude tag	152
Recommended uses	153
About user-defined functions	153
Recommended uses	153
For more information	154
Using ColdFusion components	154
Recommended uses	154
For more information	154
Using custom CFML tags	155
Calling custom CFML tags	155
Recommended uses	156
For more information	156
Using CFX tags	156
Calling CFX tags	156
Recommended uses	157
For more information	157
Selecting among ColdFusion code reuse methods	157
Writing and Calling User-Defined Functions	158
About user-defined functions	158
Creating user-defined functions	158
Determining where to create a user-defined function	159
About creating functions using CFScript	159
Defining functions in CFScript	159
About creating functions by using tags	161
Defining functions by using the cffunction tag	161
Rules for function definitions	163
Calling user-defined functions	163
Working with arguments and variables in functions	164
Good argument naming practice	164
Passing arguments	164
Passing complex data	165
About the Arguments scope	166
Function-only variables	169
Referencing caller variables	170
Using arguments	170
Handling errors in UDFs	171
Displaying error messages	171
Providing status information	172
Using exceptions	174
A user-defined function example	176
Defining the function using CFScript	176
Defining the function using the cffunction tag	177
Using UDFs effectively	177
Using functions in ColdFusion component	177
Using Application.cfm and function include files	178
Specifying the scope of a function	178
Using function names as function arguments	179
Handling query results using UDFs	180
Identifying and checking for UDFs	181
Using the Evaluate function	181
Using recursion	181
Building and Using ColdFusion Components	182
About ColdFusion components	182
CFCs and object-oriented programming	182
When to use CFCs	183
Creating ColdFusion components	184
Tags for creating CFCs	184
Elements of a CFC	184
Building ColdFusion components	185
Defining component methods	185
Defining CFCs with related methods	186
Placing executable code in a separate file	186
Initializing instance data	187
Defining and using method parameters	188
Providing results	191
Using ColdFusion components	194
Tags for using CFCs	195
CFC invocation techniques	195
Instantiating CFCs	195
Invoking CFC methods with the cfinvoke tag	196
Using components directly in CFScript and CFML	198
Invoking CFC methods with forms and URLs	199
Accessing CFCs from outside ColdFusion and basic HTML	200
Specifying the CFC location	201
Passing parameters to methods	201
Passing parameters to methods by using the cfinvoke tag	201
Passing parameters in direct method invocations	202
Passing parameters in a URL	203
CFC variables and scope	203
The This scope	203
The Variables scope	203
The Arguments scope	205
Other variable scopes	205
Function local variables	205
Using CFCs effectively	206
Structuring and reusing code	206
Invoke a packaged component method with the cfinvoke tag	208
Building secure ColdFusion components	209
Using introspection to get information about components	210
Display metadata for a CFC	212
ColdFusion component example	212
Creating and Using Custom CFML Tags	213
Creating custom tags	213
Creating and calling custom tags	214
Securing custom tags	216
Accessing existing custom tags	216
Passing data to custom tags	216
Passing values to and from custom tags	216
Using tag attributes summary	218
Custom tag example with attributes	218
Passing custom tag attributes by using CFML structures	220
Managing custom tags	221
Executing custom tags	221
Accessing tag instance data	221
Handling end tags	222
Processing body text	223
Terminating tag execution	224
Nesting custom tags	224
Passing data between nested custom tags	225
Variable scopes and special variables	226
High-level data exchange	226
Example: ancestor data access	227
Building Custom CFXAPI Tags	229
What are CFX tags?	229
Before you begin developing CFX tags in Java	230
Sample Java CFX tags	230
Setting up your development environment to develop CFX tags in Java	230
Customizing and configuring Java	231
Writing a Java CFX tag	231
Calling the CFX tag from a ColdFusion page	231
Processing requests	232
Life cycle of Java CFX tags	233
ZipBrowser example	234
Approaches to debugging Java CFX tags	235
Outputting debugging information	235
Debugging in a Java IDE	236
Using the debugging classes	236
Developing CFX tags in C++	237
Sample C++ CFX tags	237
Setting up your C++ development environment	238
Compiling C++ CFX tags	238
Locating your C++ library files in UNIX	238
Implementing C++ CFX tags	238
Debugging C++ CFX tags	238
Registering CFX tags	239
Chapter 6: Developing CFML Applications	240
Designing and Optimizing a ColdFusion Application	240
About applications	240
Elements of a ColdFusion application	241
The application framework	241
Reusable application elements	241
Shared variables	241
Application events and the Application.cfc file	242
Other application-level settings and functions	242
Specifying settings per application	243
Application security and user identification	244
Structuring an application	244
How ColdFusion finds and process application definition pages	244
Defining the directory structure	245
Defining the application and its event handlers in Application.cfc	246
Defining application-level settings and variables	246
Using application event handlers	248
Managing the application with Application.cfc	249
Managing sessions in Application.cfc	250
Managing requests in Application.cfc	251
Handling errors in Application.cfc	252
Example: a complete Application.cfc	254
Migrating from Application.cfm to Application.cfc	257
Using an Application.cfm page	257
Naming the application	258
Setting the client, application, and session variables options	258
Defining page processing settings	258
Setting application default variables and constants	258
Processing logins	258
Handling errors	259
Example: an Application.cfm page	259
Optimizing ColdFusion applications	261
Caching ColdFusion pages that change infrequently	261
Caching parts of ColdFusion pages	262
Using Amazon S3 storage	263
Accessing Amazon S3	263
Bucket operations	264
Object operations	265
New attributes in cfdirectory action=\	266
ACLObject	267
Access control functions	267
storeSetACL	267
storeAddACL	268
storeGetACL	269
StoreGetMetadata	270
StoreSetMetadata	270
Standard keys	271
Working with in-memory files	272
Example	273
Example	273
Using in-memory files	273
Supported functions	274
File operations	275
Document and image actions	275
Custom tags	275
Using in-memory files in tags	275
Adding permissions	276
Accessing VFS information	276
Deleteing in-memory files	276
Limitations	277
Optimizing database use	277
Providing visual feedback to the user	280
Handling Errors	280
About error handling in ColdFusion	280
Understanding errors	281
About error causes and recovery	281
ColdFusion error types	281
About ColdFusion exceptions	282
How ColdFusion handles errors	284
Error messages and the standard error format	285
Determining error-handling strategies	286
Handling missing template errors	286
Handling form field validation errors	287
Handling compiler exceptions	287
Handling runtime exceptions	287
Specifying custom error messages with the cferror tag	288
Specifying a custom error page	288
Creating an error application page	289
Logging errors with the cflog tag	290
Handling runtime exceptions with ColdFusion tags	292
Exception-handling tags	292
Using cftry and cfcatch tags	293
Using the cftry tag: an example	296
Using the cfthrow tag	300
Using the cfrethrow tag	301
Example: using nested tags, cfthrow, and cfrethrow	302
Using Persistent Data and Locking	306
About persistent scope variables	306
ColdFusion persistent variables and ColdFusion structures	307
ColdFusion persistent variable issues	308
Managing the client state	308
About client and session variables	309
Maintaining client identity	309
Ensuring CFToken uniqueness and security	311
Providing Session security	311
Configuring and using client variables	312
Enabling client variables	312
Using client variables	314
Configuring and using session variables	316
What is a session?	316
Configuring and enabling session variables	317
Storing session data in session variables	318
Standard session variables	318
Getting a list of session variables	319
Creating and deleting session variables	319
Accessing and changing session variables	319
Ending a session	320
Configuring and using application variables	320
Configuring and enabling application variables	321
Storing application data in application variables	321
Using application variables	321
Using server variables	322
Locking code with cflock	323
Sample locking scenarios	323
Using the cflock tag with write-once variables	325
Using the cflock tag	325
Considering lock granularity	327
Nesting locks and avoiding deadlocks	327
Examples of cflock	329
Example with application, server, and session variables	329
Example of synchronizing access to a file system	332
Example of protecting ColdFusion extensions	332
Using ColdFusion Threads	333
About ColdFusion threads	333
Creating and managing ColdFusion threads	333
Starting a thread	334
Suspending a thread	334
Ending a thread	335
Joining threads	336
Using thread data	337
Thread scopes	337
Locking thread data and resource access	339
Metadata variables	339
Working with threads	340