<!--- Retrieve employee records from the EmpoloyeeDirectory. Employ a
subquery to obtain the average salary of all employees --->
<cfquery name="GetSalaries" datasource="ProgrammingCF">
Example 11-15. Displaying departments that have two or more employees (continued)
This is the Title of the Book, eMatter Edition
Copyright © 2003 O’Reilly & Associates, Inc. All rights reserved.
Advanced SQL
|
347
SELECT Name, Title, Department,
Salary, (SELECT AVG(Salary)
FROM EmployeeDirectory) AS AverageSalary
FROM EmployeeDirectory
ORDER BY Name
</cfquery>
<html>
<head>
<title>Using a Subquery Along with an Aggregate Function</title>
<style type="text/css">
th {
background-color : #888888;
font-weight : bold;
text-align : center;
}
td {
background-color : #C0C0C0;
}
td.Total {
background-color : #D3D3D3;
font-weight : bold;
}
</style>
</head>
<body>
<h3>Average Salary Report</h3>
<table cellpadding="3" cellspacing="1">
<tr>
<th>Name</th>
<th>Title</th>
<th>Department</th>
<th>Salary</th>
</tr>
<!--- Output the employee records --->
<cfoutput query="GetSalaries">
<tr>
<td>#Name#</td>
<td>#Title#</td>
<td>#Department#</td>
<td style="text-align: right;">#DollarFormat(Salary)#</td>
</tr>
</cfoutput>
<!--- Output the average employee salary. Note that the query attribute of
the cfoutput tag was not used. The query name is prepended to the
AverageSalary salary variable --->
<cfoutput>
<tr>
<td class="Total">Average Salary</td>
Example 11-16.Using a subquery along with an aggregate function (continued)
This is the Title of the Book, eMatter Edition
Copyright © 2003 O’Reilly & Associates, Inc. All rights reserved.
348
|
Chapter 11:Advanced Database Techniques
In Example 11-16,a query is run to retrieve the
Name
,
Title
,
Department
,and
Salary
of each employee in the
EmployeeDirectory
table.A subquery is used within the
SELECT
statement to obtain the average of all salaries using the
AVG
aggregate func-
tion.Subqueries returning more than one record can be used only in the
WHERE
clause.The results are written to the browser in an HTML table that contains a list-
ing of all the employees in the table along with their salaries.The average salary is
given at the bottom of the table.
You can also include subqueries in the
WHERE
clause of a query.One way to do this is
with the
EXISTS
keyword.
EXISTS
is used only with subqueries and tests for a non-
empty record set (you can test for an empty record set by using
NOT EXISTS
).To see
how this is useful,consider Example 11-17,in which we retrieve a list of all employ-
ees from the
EmployeeDirectory
table who have received individual incentive awards
of less than $5,000 each in 2003.
<td class="Total" style="text-align: right;"
colspan="3">#DollarFormat(GetSalaries.AverageSalary)#</td>
</tr>
</cfoutput>
</table>
</body>
</html>
Example 11-17.Using EXISTS with a subquery
<!--- Retrieve a list of employees who received individual incentive awards
of less than $5000 each in 2003 --->
<cfquery name="GetSalaries" datasource="ProgrammingCF">
SELECT ID, Name, Title, Department
FROM EmployeeDirectory
WHERE EXISTS
(SELECT ID
FROM IncentiveAwards
WHERE IncentiveAwards.ID = EmployeeDirectory.ID
and Amount < 5000
and {fn YEAR(DateAwarded)} = 2003)
ORDER BY Name
</cfquery>
<html>
<head>
<title>Using EXISTS with a Subquery</title>
<style type="text/css">
th {
background-color : #888888;
font-weight : bold;
text-align : center;
}
td {
background-color : #C0C0C0;
}
Example 11-16.Using a subquery along with an aggregate function (continued)
This is the Title of the Book, eMatter Edition
Copyright © 2003 O’Reilly & Associates, Inc. All rights reserved.
Advanced SQL
|
349
Example 11-17 uses a few of the advanced techniques we covered so far.In plain
English,the query works by saying “select the employees from the
Employee
-
Directory
table where a matching record exists in the
IncentiveAwards
table that
meets the criteria set in the subquery.”
Subqueries can also be used in the
WHERE
clause of a
SELECT
query by using the equal
sign (
=
) or the
[NOT] IN
operator.Use the equal sign when only one record will be
returned by the subquery.If more than one record can be returned,use
IN
.Here are
some example
WHERE
clauses:
WHERE MyField = (SELECT SomeField
FROM SomeTable
WHERE OtherField = Value)
WHERE MyField IN (SELECT SomeField
FROM SomeTable
WHERE OtherField = Value)
WHERE MyField NOT IN (SELECT SomeField
FROM SomeTable
WHERE OtherField = Value)
Unions
The
UNION
clause is used with a
SELECT
statement to merge result sets from two or
more queries into a single result set.In order to use the
UNION
clause,each result set
</style>
</head>
<body>
<h3>Incentive Awards under $5000 Granted in 2003</h3>
<table cellpadding="3" cellspacing="1">
<tr>
<th>Name</th>
<th>Title</th>
<th>Department</th>
</tr>
<!--- Output the employee records --->
<cfoutput query="GetSalaries">
<tr>
<td>#Name#</td>
<td>#Title#</td>
<td>#Department#</td>
</tr>
</cfoutput>
</table>
</body>
</html>
Example 11-17.Using EXISTS with a subquery (continued)
This is the Title of the Book, eMatter Edition
Copyright © 2003 O’Reilly & Associates, Inc. All rights reserved.
350
|
Chapter 11:Advanced Database Techniques
must contain the same number of columns,with each matching column being of the
same datatype.Additionally,each column must have been
SELECT
’ed in the same
order during the formation of the original result sets.Example 11-18 demonstrates
how this works.
Example 11-18.Using the UNION clause to merge two result sets
<!--- Retrieve records from both the ITDirectory and HRDirectory tables
and merge them using the UNION clause. This example is somewhat
impractical as the user's department is not stored in the
database --->
<cfquery name="GetEmployees" datasource="ProgrammingCF">
SELECT Name, Title, Email, PhoneExt
FROM ITDirectory
UNION
SELECT Name, Title, Email, PhoneExt
FROM HRDirectory
</cfquery>
<html>
<head>
<title>Using the UNION Clause to Merge Two Result Sets</title>
<style type="text/css">
th {
background-color : #888888;
font-weight : bold;
text-align : center;
}
td {
background-color : #C0C0C0;
}
</style>
</head>
<body>
<h2>Combined IT Directory and HR Directory:</h2>
<table cellpadding="3" cellspacing="1">
<tr>
<th>Name</th>
<th>Title</th>
<th>E-mail</th>
<th>Phone Ext.</th>
</tr>
<!--- Dynamically generate a table containing all of the records returned
by the query --->
<cfoutput query="GetEmployees">
<tr>
<td>#Name#</td>
<td>#Title#</td>
<td>#Email#</td>
<td>#PhoneExt#</td>
This is the Title of the Book, eMatter Edition
Copyright © 2003 O’Reilly & Associates, Inc. All rights reserved.
Advanced SQL
|
351
In order to get Example 11-18 to work,you need to go back to the code in
Example 11-12 and modify it to create a new table called
HRDirectory
.Simply substi-
tute
HRDirectory
for
ITDirectory
in the
FROM
clause and
'HR'
for
'IT'
in the
WHERE
clause and execute the template.You should now have an
ITDirectory
and an
HRDirectory
table containing IT employees and HR employees respectively.
Once you have these two tables in your database,go ahead and execute
Example 11-18.The template retrieves a list of all employees from both tables and
merges the result sets using the
UNION
clause.An HTML table containing the records
from the merged result set is dynamically generated.
Joins
Relational database design allows you to create database tables that maintain rela-
tionships.These relationships are usually defined in terms of primary and foreign key
values.For example,in our
EmployeeDirectory
table,the
ID
value for each employee
is the primary key value.The
IncentiveAwards
table contains a field named
ID
as
well.The
ID
field in the
IncentiveAwards
table is known as a foreign key.Records in
the
IncentiveAwards
table are related to records in the
EmployeeDirectory
table by
their
ID
values.Each record in the
IncentiveAwards
table should have a correspond-
ing record in the
EmployeeDirectory
table.
A join operation lets you select records fromtwo or more tables where a relationship
between primary key and foreign key values exists.Most joins fall into one of two
categories: inner or outer joins.
Inner joins
Inner joins are the most common type of join and are used to retrieve records from
two or more tables where values in the joined columns match.There are many ways
to implement inner joins in SQL,and support for these methods varies from data-
base to database.Consult your database documentation to find out which methods
are supported by your database.
One common method involves using the equal sign (
=
) in the
WHERE
statement to join
the tables by a related column, as shown in Example 11-19.
</tr>
</cfoutput>
</table>
</body>
</html>
Example 11-19.Inner join performed in the WHERE statement
<!--- Query the EmployeeDirectory and IncentiveAwards tables and only return
records where the ID from the EmployeeDirectory table matches the ID in
Example 11-18.Using the UNION clause to merge two result sets (continued)
This is the Title of the Book, eMatter Edition
Copyright © 2003 O’Reilly & Associates, Inc. All rights reserved.
352
|
Chapter 11:Advanced Database Techniques
Example 11-19 queries the
EmployeeDirectory
and
IncentiveAwards
tables and
returns only records in which the
ID
value fromthe
EmployeeDirectory
table matches
the
ID
value in the
IncentiveAwards
table.The inner join is performed using the equal
the IncentiveAwards table, The inner join is performed by the equal
sign. --->
<cfquery name="GetEmployeeInfo" datasource="ProgrammingCF">
SELECT EmployeeDirectory.ID, EmployeeDirectory.Name,
IncentiveAwards.ID, IncentiveAwards.Category,
IncentiveAwards.DateAwarded, IncentiveAwards.Amount
FROM EmployeeDirectory, IncentiveAwards
WHERE EmployeeDirectory.ID = IncentiveAwards.ID
</cfquery>
<html>
<head>
<title>Inner Join Performed in the WHERE Statement</title>
<style type="text/css">
th {
background-color : #888888;
font-weight : bold;
text-align : center;
}
td {
background-color : #C0C0C0;
}
</style>
</head>
<body>
<h3>Incentive Awards</h3>
<table cellpadding="3" cellspacing="1">
<tr>
<th>Name</th>
<th>Award Type</th>
<th>Date Awarded</th>
<th>Amount</th>
</tr>
<!--- Dynamically generate a table containing all of the records returned
by the query --->
<cfoutput query="GetEmployeeInfo">
<tr>
<td>#Name#</td>
<td>#Category#</td>
<td>#DateFormat(DateAwarded, 'mm/dd/yyyy')#</td>
<td>#DollarFormat(Amount)#</td>
</tr>
</cfoutput>
</table>
Example 11-19.Inner join performed in the WHERE statement (continued)
This is the Title of the Book, eMatter Edition
Copyright © 2003 O’Reilly & Associates, Inc. All rights reserved.
Advanced SQL
|
353
sign in the
WHERE
clause.Executing this template results in the output shown in
Figure 11-7.
You can also perform an inner join using
INNER JOIN
in the
FROM
clause of the
SELECT
statement,as shown in Example 11-20.The results of this query will mimic the
results of Example 11-19 (shown in Figure 11-7).
Figure 11-7.Inner join performed using the equal sign in the WHERE clause
Example 11-20.Inner join performed in the FROM statement
<!--- Query the EmployeeDirectory and IncentiveAwards tables and only return
records where the ID from the EmployeeDirectory table matches the ID in
the IncentiveAwards table. The inner join is performed by the INNER
JOIN operator. --->
<cfquery name="GetEmployeeInfo" datasource="ProgrammingCF">
SELECT employee.ID, employee.Name, incentive.ID,
incentive.Category, incentive.DateAwarded, incentive.Amount
FROM EmployeeDirectory employee
INNER JOIN IncentiveAwards incentive
ON employee.ID = incentive.ID
</cfquery>
<html>
<head>
<title>Inner Join Performed in the FROM Statement</title>
<style type="text/css">
th {
background-color : #888888;
font-weight : bold;
text-align : center;
}
This is the Title of the Book, eMatter Edition
Copyright © 2003 O’Reilly & Associates, Inc. All rights reserved.
354
|
Chapter 11:Advanced Database Techniques
Example 11-20 introduces another technique known as table aliasing.If you look at
the first query,you’ll notice that we refer to the
EmployeeDirectory
table as
employee
and the
IncentiveAwards
table as
incentive
:
SELECT employee.ID, employee.Name, incentive.ID,
incentive.Category, incentive.DateAwarded, incentive.Amount
FROM EmployeeDirectory employee
INNER JOIN IncentiveAwards incentive
ON employee.ID = incentive.ID
SQL lets us alias table names when working with multiple tables.To create an alias
for a table name,simply specify the alias immediately following the table name in the
FROM
clause.
Outer joins
Outer joins differ frominner joins in that they let you query all the records fromone
table even if corresponding records don’t exist in the other table.There are two types
of outer joins: left and right.
td {
background-color : #C0C0C0;
}
</style>
</head>
<body>
<h3>Incentive Awards</h3>
<table cellpadding="3" cellspacing="1">
<tr>
<th>Name</th>
<th>Award Type</th>
<th>Date Awarded</th>
<th>Amount</th>
</tr>
<!--- Dynamically generate a table containing all of the records returned
by the query --->
<cfoutput query="GetEmployeeInfo">
<tr>
<td>#Name#</td>
<td>#Category#</td>
<td>#DateFormat(DateAwarded, 'mm/dd/yyyy')#</td>
<td>#DollarFormat(Amount)#</td>
</tr>
</cfoutput>
</table>
</body>
</html>
Example 11-20.Inner join performed in the FROM statement (continued)
This is the Title of the Book, eMatter Edition
Copyright © 2003 O’Reilly & Associates, Inc. All rights reserved.
Advanced SQL
|
355
As its name implies,a left outer join returns all the records from the left table (as
specified in the
WHERE
or
FROM
clause) and only the records from the right table where
the values in the joined fields match.All empty rows fromthe right table are assigned
NULL
values.
Example 11-21 uses a left outer join in the
FROM
clause to query the
EmployeeDirectory
and
IncentiveAwards
tables and return all records from the left
table (
EmployeeDirectory
) and only those from the right table (
IncentiveAwards
) that
match
ID
values.
Example 11-21.Using LEFT OUTER JOIN in the FROM clause
<!--- Query the EmployeeDirectory and IncentiveAwards tables and return all
records from the left table (EmployeeDirectory) and only those from
the right table (IncentiveAwards) where the ID values are equal. The
left outer join is performed by the LEFT OUTER JOIN operator. --->
<cfquery name="GetEmployeeInfo" datasource="ProgrammingCF">
SELECT employee.ID, employee.Name, incentive.ID,
incentive.Category, incentive.DateAwarded, incentive.Amount
FROM EmployeeDirectory employee
LEFT OUTER JOIN IncentiveAwards incentive
ON employee.ID = incentive.ID
</cfquery>
<html>
<head>
<title>Using LEFT OUTER JOIN in the FROM Clause</title>
<style type="text/css">
th {
background-color : #888888;
font-weight : bold;
text-align : center;
}
td {
background-color : #C0C0C0;
}
</style>
</head>
<body>
<h3>Incentive Awards</h3>
<table cellpadding="3" cellspacing="1">
<tr>
<th>Name</th>
<th>Award Type</th>
<th>Date Awarded</th>
<th>Amount</th>
</tr>
<!--- Dynamically generate a table containing all of the records returned
by the query. Substitute NULL for any blanks (NULLs) returned by
the Left Outer Join --->
This is the Title of the Book, eMatter Edition
Copyright © 2003 O’Reilly & Associates, Inc. All rights reserved.
356
|
Chapter 11:Advanced Database Techniques
Executing the template in Example 11-21 results in the output shown in Figure 11-8.
A right outer join is the exact opposite of a left outer join.Right outer joins return all
records from the right table and only the records from the left table where the values
in the joined fields match. All empty rows from the left table are assigned
NULL
values.
Example 11-22 uses a right outer join to query the
EmployeeDirectory
and
IncentiveAwards
tables.A result set containing all records from the right table
(
IncentiveAwards
) and only those from the left table (
EmployeeDirectory
) where the
ID
values are equal is returned.
<cfoutput query="GetEmployeeInfo">
<tr>
<td>#Name#</td>
<td><cfif Category is "">NULL<cfelse>#Category#</cfif></td>
<td><cfif DateAwarded is "">NULL<cfelse>
#DateFormat(DateAwarded, 'mm/dd/yyyy')#</cfif></td>
<td><cfif Amount is "">NULL<cfelse>#DollarFormat(Amount)#</cfif></td>
</tr>
</cfoutput>
</table>
</body>
</html>
Figure 11-8.Results of using a left outer join in the FROM clause
Example 11-22.Using RIGHT OUTER JOIN in the FROM clause
<!--- Query the EmployeeDirectory and IncentiveAwards tables and return all
records from the Right table (IncentiveAwards) and only those from
the left table (EmployeeDirectory) where the ID values are equal. The
right outer join is performed by the RIGHT OUTER JOIN operator. --->
<cfquery name="GetEmployeeInfo" datasource="ProgrammingCF">
SELECT employee.ID, employee.Name, incentive.ID,
incentive.Category, incentive.DateAwarded, incentive.Amount
FROM EmployeeDirectory employee
Example 11-21.Using LEFT OUTER JOIN in the FROM clause (continued)
This is the Title of the Book, eMatter Edition
Copyright © 2003 O’Reilly & Associates, Inc. All rights reserved.
Advanced SQL
|
357
Executing the template in Example 11-22 results in the output shown in Figure 11-9.
Note that there are various shorthand techniques you can use to specify left outer
joins and right outer joins in the
WHERE
clause.The syntax varies from database to
database.Refer to your specific database’s documentation for guidelines on specific
syntax for outer joins.
RIGHT OUTER JOIN IncentiveAwards incentive
ON employee.ID = incentive.ID
</cfquery>
<html>
<head>
<title>Using RIGHT OUTER JOIN in the FROM Clause</title>
<style type="text/css">
th {
background-color : #888888;
font-weight : bold;
text-align : center;
}
td {
background-color : #C0C0C0;
}
</style>
</head>
<body>
<h3>Incentive Awards</h3>
<table cellpadding="3" cellspacing="1">
<tr>
<th>Name</th>
<th>Award Type</th>
<th>Date Awarded</th>
<th>Amount</th>
</tr>
<!--- Dynamically generate a table containing all of the records returned
by the query. Substitute NULL for any blanks (NULLs) returned by
the Right Outer Join --->
<cfoutput query="GetEmployeeInfo">
<tr>
<td><cfif Name is "">NULL<cfelse>#Name#</cfif></td>
<td><cfif Category is "">NULL<cfelse>#Category#</cfif></td>
<td><cfif DateAwarded is "">NULL<cfelse>#DateFormat(DateAwarded,
'mm/dd/yyyy')#</cfif></td>
<td><cfif Amount is "">NULL<cfelse>#DollarFormat(Amount)#</cfif></td>
</tr>
</cfoutput>
</table>
Example 11-22.Using RIGHT OUTER JOIN in the FROM clause (continued)
This is the Title of the Book, eMatter Edition
Copyright © 2003 O’Reilly & Associates, Inc. All rights reserved.
358
|
Chapter 11:Advanced Database Techniques
There are several other types of joins that can be performed in SQL.Most of these
are variations on the joins we already covered.Many are highly specialized and rarely
employed.Some are exclusive to specific database platforms.For more information
on joins,see your database documentation or pick up a good SQL reference book,
such as SQL in a Nutshell by Kevin Kline and David Kline (O’Reilly).
Data Binding and Preventing Malicious Query Code
You can use the
cfqueryparam
tag to check the datatype of a query parameter and
optionally validate it against a specific SQL type.This allows you to prevent arbi-
trary code from being passed to your queries and can also speed up performance.
You can also use the
cfqueryparam
tag to update long text fields.The
cfqueryparam
tag must be nested within a
cfquery
tag and appears on the right side of the equal
sign in the
WHERE
clause:
<cfquery name="MyQUERY" datasource="MyDatasource">
SELECT *
FROM MyTable
WHERE MyValue =
<cfqueryparam value="parameter_value"
cfsqltype="parameter_data_type"
maxlength="number"
scale="number_of_decimal_places"
separator="seperator_character"
list="Yes|No"
null="Yes|No">
</cfquery>
The
value
attribute is required and specifies the value that ColdFusion should pass
to the right of the comparison operator in the
WHERE
clause.
cfsqltype
is also required
and specifies the SQL type the parameter should be bound to.Possible entries are
listed in Table 11-1. The default value for
cfsqltype
is
CF_SQL_CHAR
.
Figure 11-9.Using a right outer join in the FROM clause
This is the Title of the Book, eMatter Edition
Copyright © 2003 O’Reilly & Associates, Inc. All rights reserved.
Advanced SQL
|
359
The
maxlength
attribute specifies the maximumlength of the parameter being passed
and is an optional attribute.
scale
is also optional and,if you use a numeric datatype,
specifies the number of decimal places for the parameter.The
separator
attribute is
optional and specifies the character used to delimit the list of values when the
list
attribute is set to
Yes
.The default is the comma (
,
).The
list
attribute is optional
and accepts a
Yes
/
No
value indicating whether the
value
attribute of the
cfqueryparam
tag should be treated as a list of values separated by the character specified in the
separator
attribute.If set to
Yes
,a SQL parameter is generated for each value in the
list.Each list item is validated separately.If a value is specified for the
maxlength
attribute,the
maxlength
applies to each item in the list as opposed to the list as a
whole. If the value passed is
NULL
, it is treated as a single
NULL
value. The default is
No
.
The final attribute is
null
.
null
is optional and specifies a
Yes/No
value indicating
whether the value passed is a
NULL
.If
Yes
,ColdFusion ignores the
value
attribute.
The default value for
null
is
No
.
If the database being used doesn’t support the binding of parameters,validation is
still performed with the validated parameter being written back to the string.If for
any reason validation fails,ColdFusion throws an exception.The following rules
determine the validation performed:

CF_SQL_SMALLINT
,
CF_SQL_INTEGER
,
CF_SQL_REAL
,
CF_SQL_FLOAT
,
CF_SQL_DOUBLE
,
CF_
SQL_TINYINT
,
CF_SQL_MONEY
,
CF_SQL_MONEY4
,
CF_SQL_DECIMAL
,
CF_SQL_NUMERIC
,and
CF_SQL_BIGINT
can be converted to numbers.

CF_SQL_DATE
,
CF_SQL_TIME
,and
CF_SQL_TIMESTAMP
can be converted to a valid date
format.
• If the
maxlength
attribute is used,the length of the value for the specified param-
eter can’t exceed the specified length.
The actual SQL that is generated by the
cfqueryparam
tag is dependent on the data-
base used.Example 11-23 uses the
cfqueryparam
tag to validate the value of a vari-
able called
ID
.Changing the value of
ID
from numeric to a text character causes
ColdFusion to throw an error.
Table 11-1.Values for cfsqltype
CF_SQL_BIGINT CF_SQL_FLOAT CF_SQL_REFCURSOR
CF_SQL_BIT CF_SQL_IDSTAMP CF_SQL_SMALLINT
CF_SQL_BLOB CF_SQL_INTEGER CF_SQL_TIME
CF_SQL_CHAR CF_SQL_LONGVARCHAR CF_SQL_TIMESTAMP
CF_SQL_CLOB CF_SQL_MONEY CF_SQL_TINYINT
CF_SQL_DATE CF_SQL_MONEY4 CF_SQL_VARCHAR
CF_SQL_DECIMAL CF_SQL_NUMERIC
CF_SQL_DOUBLE CF_SQL_REAL
This is the Title of the Book, eMatter Edition
Copyright © 2003 O’Reilly & Associates, Inc. All rights reserved.
360
|
Chapter 11:Advanced Database Techniques
Example 11-23.Using cfqueryparam for data binding and validation
<!--- Set URL.ID=1. Normally, this value would come as a URL parameter or
form post (then it would be form.ID) --->
<cfset URL.ID = 1>
<!--- Retrieve the full record of the employee whose ID is specified.
Use the cfqueryparam tag to bind URL.ID to a numeric value. --->
<cfquery name="GetEmployeeRecord" datasource="ProgrammingCF">
SELECT Name, Title, Department, Email, PhoneExt, Salary
FROM EmployeeDirectory
WHERE ID = <cfqueryparam value="#URL.ID#"
cfsqltype="CF_SQL_INTEGER">
</cfquery>
<html>
<head>
<title>Using cfqueryparam for Data Binding and Validation</title>
<style type="text/css">
th {
background-color : #888888;
font-weight : bold;
text-align : center;
}
td {
background-color : #C0C0C0;
}
</style>
</head>
<body>
<h2>Employee Profile</h2>
<table cellpadding="3" cellspacing="1">
<tr>
<th>Name</th>
<th>Title</th>
<th>Department</th>
<th>E-mail</th>
<th>Phone Extension</th>
<th>Salary</th>
</tr>
<!--- Generate an HTML table containing the employee record from the
GetEmployeeRecord query. --->
<cfoutput>
<tr>
<td>#GetEmployeeRecord.Name#</td>
<td>#GetEmployeeRecord.Title#</td>
<td>#GetEmployeeRecord.Department#</td>
<td><a
href="Mailto:#GetEmployeeRecord.Email#">#GetEmployeeRecord.Email#</a></td>
<td>#GetEmployeeRecord.PhoneExt#</td>
<td>#GetEmployeeRecord.Salary#</td>
</tr>
This is the Title of the Book, eMatter Edition
Copyright © 2003 O’Reilly & Associates, Inc. All rights reserved.
Query of Queries
|
361
Query of Queries
Now that you have an understanding of advanced SQL,it’s time to introduce a new
concept known as “query of queries” or CFSQL (ColdFusion SQL).Introduced in
ColdFusion 5.0 and greatly enhanced in MX,query of queries allows you to use the
cfquery
tag to query an already existing query object using a subset of ANSI SQL 92.
This feature makes it easy to program functionality previously difficult or impossible
to implement in ColdFusion. Some potential uses for query of queries include:
• Manipulate query objects (sort,summarize,group,etc.) returned by other Cold-
Fusion tags such as
cfhttp
,
cfftp
,
cfldap
,
cfpop
,
cfsearch
,or
cfstoredproc
as
well as the various query functions
• Perform joins and unions between tables from different data sources
• Resorting a query result set without having to go back to the data source
• Moving entire tables into memory (Macromedia recommends no more than
50,000 rows in ColdFusion MX or 10,000 rows in ColdFusion 5),effectively
speeding up query times because ColdFusion no longer has to make a round trip
to the database for each query performed
Regardless of what you decide to use this feature for,you need to know what SQL
constructs are available.For starters,query of queries can only be used to performan
SQL
SELECT
.This means that query of queries can be used only to select records,not
INSERT
,
UPDATE
,or
DELETE
them.This makes sense,as the purpose of query of queries
is to allow you to perform a query against an already existing query object.When
you perform your
SELECT
,a number of SQL keywords are available to help you con-
struct your query.These keywords are
FROM
,
WHERE
,
ORDER BY
,
GROUP BY
,
HAVING
,and
UNION
.Query of queries does not support the
INNER JOIN
or
OUTER JOIN
statement.
However,you can still perform an inner join by using the equal sign in the
WHERE
clause as was discussed in the section on joins.
A number of comparison and Boolean operators are also available:
=
,
<>
,
<
,
>
,
<=
,
>=
,
AND
,
OR
,
NOT
,
IN
,
BETWEEN
,
LIKE
,and
EXISTS
.In addition,query of queries supports sev-
eral aggregate functions including
COUNT
,
SUM
,
AVG
,
MAX
,and
MIN
.You should note that
query of queries doesn’t support nested aggregate functions nor does it support
ODBC formatted dates in comparison operations.
To perform a query of a query,you set the
dbtype
attribute of the
cfquery
tag to
Query
and reference the name of one or more existing queries in the
FROM
clause of
your SQL statement.The
datasource
attribute isn’t used when performing a query of
</cfoutput>
</table>
</body>
</html>
Example 11-23.Using cfqueryparam for data binding and validation (continued)
This is the Title of the Book, eMatter Edition
Copyright © 2003 O’Reilly & Associates, Inc. All rights reserved.
362
|
Chapter 11:Advanced Database Techniques
a query.Example 11-24 shows a useful way to use query of a query to build an “A to
Z” list of employees in the
EmployeeDirectory
table.
In this example,the first
cfquery
retrieves the name,first initial,and email address of
each user in the
EmployeeDirectory
table into a query called
GetAllUsers
.The results
are cached for 15 minutes.The next query uses a query of a query to retrieve all of
the distinct first initials from the
GetAllUsers
query.This information is also cached.
The third query is also uses a query of a query.It retrieves all of the users from the
GetAllUsers
query whose first initial matches the value stored in
Letter
(the default
is “
A
”).Notice that
Letter
is not scoped.This is to allow the value to be passed in by
form post or URL.Once all of the queries have been run,the list of initials is output
Example 11-24.Perform a query of a query
<cfparam name="Letter" default="A">
<cfquery name="GetAllUsers" datasource="ProgrammingCF"
cachedwithin="#CreateTimeSpan(0,0,15,0)#">
SELECT Name, {fn LEFT(Name, 1)} AS Initial, Email
FROM EmployeeDirectory
ORDER BY Name
</cfquery>
<cfquery name="GetAllInitials" dbtype="query"
cachedwithin="#CreateTimeSpan(0,0,15,0)#">
SELECT DISTINCT Initial
FROM GetAllUsers
ORDER BY Initial
</cfquery>
<cfquery name="GetSpecificLetter" dbtype="query">
SELECT Name, Email
FROM GetAllUsers
WHERE Name LIKE '#Letter#%'
ORDER BY Name
</cfquery>
<cfoutput>
<h3>#GetAllUsers.RecordCount# users in the database</h3>
<p>
<cfloop list="#ValueList(GetAllInitials.Initial)#" index="i">
<cfif i is Letter>
#i#
<cfelse>
<a href="#CGI.ScriptName#?letter=#i#">#i#</a>
</cfif>
</cfloop>
</cfoutput>
<p>
<cfoutput query="GetSpecificLetter">
#Name# (<a href="mailto:#Email#">#Email#</a>)<br>
</cfoutput>
This is the Title of the Book, eMatter Edition
Copyright © 2003 O’Reilly & Associates, Inc. All rights reserved.
Calling Stored Procedures
|
363
at the top of the page.Each letter is displayed as a hyperlink.Thanks to our second
query,only those letters matching the first initial of an employee are displayed in the
list.Each employee’s name and email address is then listed below the A to Z list by
using
cfoutput
to loop over the
GetSpecificLetter
query.Clicking on one of the let-
ters causes the template to be rerun,pulling the employee records for the letter you
clicked on.Because the first two queries are cached,the execution time for the entire
page is very fast.
Calling Stored Procedures
Most enterprise-level databases (MS SQL Server,DB2,Oracle,Informix,Sybase)
support creating special programs within the database called stored procedures.
Stored procedures allow you to encapsulate SQL and other database-specific func-
tions in a wrapper that can be called from external applications.There are several
reasons to use stored procedures whenever possible in your applications:
• Stored procedures generally execute faster than identical code passed using the
cfquery
tag because they are precompiled on the database server.
• Stored procedures support code reuse.A single procedure needs to be created
only once and can be accessed by any number of templates,even different appli-
cations and those written in other languages.
• Stored procedures allow you to encapsulate complex database manipulation
routines, often utilizing database-specific functions.
• Security is enhanced by keeping all database operations encapsulated within the
stored procedure.Because ColdFusion passes parameters only to the stored pro-
cedure, there is no way to execute arbitrary SQL commands.
There are two ways to call stored procedures in ColdFusion.You can use the
cfquery
tag or the
cfstoredproc
tag (generally the preferred method).Unfortunately,mate-
rial on writing stored procedures is beyond the scope of this book.For more infor-
mation on creating stored procedures,consult the documentation for your specific
database.
Using cfstoredproc
The preferred method for calling stored procedures in ColdFusion is via the
cfstoredproc
tag.This tag takes several attributes that allow you to specify informa-
tion about the data source on which you want to execute the stored procedure,as
well as the stored procedure itself. The three most commonly used attributes are:
<cfstoredproc procedure="procedure_name"
datasource="datasource_name"
returncode="Yes|No">
</cfstoredproc>
This is the Title of the Book, eMatter Edition
Copyright © 2003 O’Reilly & Associates, Inc. All rights reserved.
364
|
Chapter 11:Advanced Database Techniques
procedure
is a required attribute and specifies the name of the stored procedure on
the database server that you want to execute.The
datasource
attribute is also
required and specifies the data source that contains the stored procedure.The final
attribute,
returncode
,is optional and accepts a
Yes
/
No
value.If set to
Yes
,
cfstoreproc
populates
cfstoredproc.StatusCode
with the status code returned by the stored pro-
cedure.The default value for
ReturnCode
is
No
.There are a number of additional
attributes that can be used with the
cfstoredproc
tag.For a complete list,see
Appendix A.
The
cfstoredproc
tag calls only the stored procedure you want to execute.You still
need a way to pass values in and receive data back from the stored procedure.These
functions are handled by two child tags of the
cfstoredproc
tag,the
cfprocparam
and
cfprocresult
tags respectively.
When a stored procedure is executed using the
cfstoredproc
tag,two return values
are automatically created by ColdFusion. These variables are:
cfstoredproc.StatusCode
Returned when
returncode
is set to
Yes
;contains the status code returned by the
stored procedure
cfstoredproc.ExecutionCode
The number of milliseconds it took for the stored procedure to execute
Passing parameters using cfprocparam
The
cfprocparam
tag specifies parameter information to send to the stored procedure
named in the
cfstoredproc
tag.You may specify multiple
cfprocparam
tags within a
single
cfstoredproc
tag.
cfprocparam
tags must be nested within the
cfstoredproc
tag
and use the following syntax:
<cfprocparam type="In|Out|InOut"
variable="variable_name"
value="parameter_value"
cfsqltype="parameter_data_type"
maxlength="length"
scale="decimal_places"
null="Yes|No">
The
type
attribute is optional and specifies whether the variable being passed is an
input (
In
),output (
Out
),or input/output (
InOut
) variable.The default
type
is input
(
In
).
variable
is required when
type
is
Out
or
InOut
and specifies the name of the
ColdFusion variable used to reference the value returned by the output parameter
after the stored procedure is called.The
value
attribute is required when
type
is
In
or
InOut
. It specifies the value to pass to the stored procedure.
The next attribute,
cfsqltype
,is required and specifies the SQL type of the parame-
ter being passed to the stored procedure.Possible values are listed back in
This is the Title of the Book, eMatter Edition
Copyright © 2003 O’Reilly & Associates, Inc. All rights reserved.
Calling Stored Procedures
|
365
Table 11-1.The default value for
cfsqltype
is
CF_SQL_CHAR
.The
maxlength
attribute
specifies the maximum length of the parameter being passed and is an optional
attribute.
scale
is also optional and specifies the number of decimal places for the
parameter should it be a numeric datatype.The final attribute is
null
.
null
is
optional and specifies a
Yes/No
value indicating whether the value passed is a
NULL
.If
Yes
, ColdFusion ignores the
value
attribute. The default value for
null
is
No
.
ColdFusion MX passes values to stored procedures using positional notation.This
means that
cfprocparam
tags must be specified in the same order as the parameters
are expected by the stored procedure.Previous versions of ColdFusion allowed
parameters to be passed using named notation via an attribute of the
cfprocparam
tag
called
dbvarname
.Named notation is not supported in JDBC,and subsequently,the
dbvarname
attribute is no longer supported.If you specify a value for
dbvarname
,Cold-
Fusion MX ignores it.
Specifying result sets using cfprocresult
The
cfprocresult
tag specifies the name for a given result set returned by the
cfstoredproc
tag.This allows other ColdFusion tags to reference the result set
returned by the stored procedure.Because stored procedures can return more than
one result set,the
cfprocresult
tag allows you to specify which result set to use.
Because of this feature,it is possible to nest multiple
cfprocresult
tags within a
cfstoredproc
tag, provided you assign a different
name
for each
cfprocresult
set:
<cfprocresult name="query_name"
resultset="1-n"
maxrows="number">
The
name
attribute is required and specifies a name for the query result set returned
by the stored procedure.
resultset
is an optional attribute and specifies the result set
to use if the stored procedure returns more than one result set.The default value for
resultset
is
1
.The final attribute is
maxrows
.
maxrows
is also optional and specifies the
maximum number of rows to return with the result set.By default,all rows are
returned.
Example 11-25 uses the
cfstoredproc
tag,several
cfprocparam
tags,and the
cfprocresult
tag to execute a stored procedure called
sp_AddEmployee
that adds a
new employee record to a database.
Example 11-25.Executing a stored procedure using cfstoredproc
<!--- Assign blank default values for any fields not passed in --->
<cfparam name="form.Name" default="">
<cfparam name="form.Title" default="">
<cfparam name="form.Department" default="">
<cfparam name="form.Email" default="">
<cfparam name="form.PhoneExt" default="">
<cfparam name="form.Salary" default="">
This is the Title of the Book, eMatter Edition
Copyright © 2003 O’Reilly & Associates, Inc. All rights reserved.
366
|
Chapter 11:Advanced Database Techniques
In Example 11-25,if the stored procedure returns a
StatusCode
of 1,the record was
inserted successfully,and you can output the ID value assigned to the newly inserted
record.Returning this value is part of the stored procedure.If it returns any other
StatusCode
,an error occurred.The value of
StatusCode
is set inside of the stored pro-
cedure, allowing you to assign any status codes you intend.
Using cfquery
Prior to the introduction of the
cfstoredproc
tag in ColdFusion 4.0,the only way to
call a stored procedure from ColdFusion was with the
cfquery
tag.The syntax for
calling a stored procedure using
cfquery
is:
<cfquery name="MyQuery" datasource="MyDataSource">
CALL MyDB.dbo.sp_mysp (#var1#, '#Var2#')
</cfquery>
<!--- Call the sp_AddEmployee stored procedure --->
<cfstoredproc procedure="sp_AddEmployee"
datasource="ProgrammingCF"
returncode="Yes">
<!--- Pass each parameter. If the field being passed contains a blank value,
make it NULL. Thanks to Dan Switzer for the YesNoFormat( ) tip. --->
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" value="#form.Name#"
maxlength="255" null="#YesNoFormat(form.Name is "")#">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" value="#form.Title#"
maxlength="255" null="#YesNoFormat(form.Title is "")#">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" value="#form.Department#"
maxlength="255" null="#YesNoFormat(form.Department is "")#">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" value="#form.Email#"
maxlength="255" null="#YesNoFormat(form.Email is "")#">
<cfprocparam type="In" cfsqltype="CF_SQL_DECIMAL" value="#form.PhoneExt#"
null="#YesNoFormat(form.PhoneExt is "")#">
<cfprocparam type="In" cfsqltype="CF_SQL_DECIMAL" value="#form.Salary#"
null="#YesNoFormat(form.Salary is "")#">
<!--- Assign a query object named InsertRecord to the first result set
returned --->
<cfprocresult name="InsertRecord" resultset="1">
</cfstoredproc>
<!--- Output status information --->
<cfif cfstoredproc.StatusCode is 1>
<cfoutput>
Record inserted successfully. The employee ID assigned is: #InsertRecord.ID#
<br>
The stored procedure executed in #cfstoredproc.ExecutionTime# milliseconds.
</cfoutput>
<cfelse>
There was an error inserting the record!
</cfif>
Example 11-25.Executing a stored procedure using cfstoredproc (continued)
This is the Title of the Book, eMatter Edition
Copyright © 2003 O’Reilly & Associates, Inc. All rights reserved.
Transaction Processing
|
367
There are several drawbacks to using the
Call
statement with
cfquery
to call stored
procedures.For example,parameters must be passed in the order in which they
appear in the stored procedure,and you can’t specify input parameters by name or
bind parameters to datatypes.There is no way to explicitly pass a
NULL
,and you can’t
specify the length for a given parameter.There is also no way to access return codes
or output parameters created by the stored procedure,nor can you return multiple
result sets.
Depending on your database,you may be able to use a native function to call a
stored procedure with the
cfquery
tag.Several databases allow you to call stored pro-
cedures using the
EXECUTE
or
EXEC
keyword instead of
CALL
, as in:
<cfquery name="MyQuery" datasource="MyDataSource">
EXECUTE MyDB.dbo.sp_mysp
@Var = #var#,
@Var2 = '#Var2#'
</cfquery>
Using a native database function for calling a stored procedure allows you to over-
come some of the problems associated with using the
CALL
method,such as referring
to input parameters by name,passing
NULL
s,and data binding.For example,you can
use the
cfqueryparam
tag with a stored procedure called using
cfquery
with the
EXECUTE
statement:
<cfquery name="GetCustomer" datasource="dclive">
EXECUTE MyDB.dbo.sp_mysp
@cunu=<cfqueryparam value="1" cfsqltype="CF_SQL_INTEGER" maxlength="4">
</cfquery>
However,this in and of itself isn’t justification for using the
cfquery
tag to call stored
procedures.Unless you are running a version of ColdFusion prior to Version 4.0,
you should consider calling your stored procedures using the
cfstoredproc
tag.
There is one benefit to using the
cfquery
method over the
cfstoredproc
tag and that’s
query caching.The
cfstoredproc
tag doesn’t allow you to cache queries like
cfquery
does.Depending on your database platform,however,the stored procedure may
actually cache the query on the database,making subsequent calls to the procedure
execute faster.This is something you might have to weigh when deciding on which
method to use.If query caching is extremely important to your application,that may
be a compelling enough reason to use
cfquery
to execute your stored procedures.
Transaction Processing
ColdFusion provides support for database transaction processing using the
cftransaction
tag.The
cftransaction
tag lets you treat all query operations with the
<cftransaction>
and
</cftransaction>
tags as a single transaction.Changes to the
database aren’t committed until all queries in the transaction have executed suc-
cessfully.In the event a query within the transaction fails,all previous queries are
This is the Title of the Book, eMatter Edition
Copyright © 2003 O’Reilly & Associates, Inc. All rights reserved.
368
|
Chapter 11:Advanced Database Techniques
automatically rolled back.The exception to this occurs when the database itself is
changed, as in the case when a table or column is created or deleted.
The
cftransaction
tag accepts two optional attributes for controlling how transac-
tions are processed,
action
and
isolation
:
action
Specifies the transaction action to take. Valid options include:
Begin
(default)
Specifies the beginning of the block of code to execute
Commit
Commits a pending transaction
Rollback
Rolls back a pending transaction
isolation
Specifies the ODBC lock type to use for the transaction.The following ODBC
lock types are supported:
Read_Uncommitted
,
Read_Committed
,
Repeatable_Read
,
and
Serializable
.
Note that not all databases and/or database drivers support isolation levels.Many
support only a subset of those listed.Refer to your particular database/driver’s docu-
mentation for more information on the isolation levels supported.
Example 11-26 shows how to use the
cftransaction
tag with two queries that delete
records from different tables within the same data source.You need to use the
cftransaction
tag to ensure that both queries are treated as a single transaction.If
either query fails, any changes made are automatically rolled back.
Example 11-26.Simple cftransaction usage
<!--- Use the cftransaction tag to ensure that both queries are treated
as a single transaction. If either query fails, any changes made
are automatically rolled back --->
<cftransaction>
<!--- Delete an employee from the EmployeeDirectory table where the
employee's ID is equal to the ID value passed in as a form var --->
<cfquery name="DeleteEmployee" datasource="ProgrammingCF">
DELETE FROM EmployeeDirectory
WHERE ID = #form.ID#
</cfquery>
<!--- Delete any entries for the employee from the IncentiveAwards table.
This table is described later in the chapter. --->
<cfquery name="DeleteBonusRecords" datasource="ProgrammingCF">
DELETE FROM IncentiveAwards
WHERE ID = #form.ID#
</cfquery>
</cftransaction>
This is the Title of the Book, eMatter Edition
Copyright © 2003 O’Reilly & Associates, Inc. All rights reserved.
Transaction Processing
|
369
In Example 11-25,the
cftransaction
tag ensures that both
DELETE
queries are treated
as a single transaction.If either query fails,any changes made are automatically
rolled back.Note that both queries are made to the same data source,but not to the
same table.
A particularly useful feature of the
cftransaction
tag is that it can be nested to allow
portions of a transaction to be committed or rolled back within the main
cftransaction
block as the code executes.The syntax for a nested transaction differs
slightly fromthe syntax used to call most other tags.To commit a transaction within
a nested
cftransaction
tag, use the following syntax:
<cftransaction action="Commit" />
Note the trailing forward slash at the end of the tag.This lets ColdFusion know you
have nested the
cftransaction
tag and no end tag is necessary.Rolling back a trans-
action uses similar syntax:
<cftransaction action="Rollback" />
Using nested
cftransaction
tags and exception handling with
cftry
/
cfcatch
gives
you full control over how queries are committed and rolled back within
cftransaction
blocks.This technique also lets you write to more than one database
within a single
cftransaction
block,if each transaction is committed or rolled back
prior to writing a query to the next database.Example 11-27 demonstrates how to
use nested
cftransaction
tags to create a new table populated with data from an
existing table (using
SELECT INTO
) and then add another record to the table.
Example 11-27.Using nested cftransaction tags with multiple queries
<!--- Initialize a variable called Continue to control the transaction --->
<cfset Continue = true>
<!--- Begin transaction --->
<cftransaction action="begin">
<!--- Wrap the INSERT in a cftry block --->
<cftry>
<!--- Select the name, title, email, and phone ext for each employee in
the EmployeeDirectory table that belongs to the Sales department
and use it to populate a new table called SalesDirectory --->
<cfquery name="MakeSalesDirectory" datasource="ProgrammingCF">
SELECT Name, Title, Email, PhoneExt
INTO SalesDirectory
FROM EmployeeDirectory
WHERE Department = 'Sales'
</cfquery>
<!--- If a database error occurs, rollback the transaction and set the
Continue variable to false. --->
<cfcatch type="Database">
<cftransaction action="Rollback" />
<cfset ProblemQuery = "MakeSalesDirectory">
<cfset Continue = false>
This is the Title of the Book, eMatter Edition
Copyright © 2003 O’Reilly & Associates, Inc. All rights reserved.
370
|
Chapter 11:Advanced Database Techniques
</cfcatch>
</cftry>
<!--- If the INSERT was successful, commit the transaction and execute another
query to insert a new record into the table we created with the last
query. --->
<cfif Continue>
<cftry>
<cftransaction action="Commit" />
<cfquery name="InsertRecord" datasource="ProgrammingCF">
INSERT INTO SalesDirectory(Name, Title, Email, PhoneExt)
VALUES('Lynda Newton', 'Account Manager', 'lynda@example.com', 1261)
</cfquery>
<!--- If a database error occurs, rollback the transaction and set the
Continue variable to false. --->
<cfcatch type="Database">
<cftransaction action="Rollback" />
<cfset ProblemQuery = "InsertRecord">
<cfset Continue = false>
</cfcatch>
</cftry>
</cfif>
<!--- If the record was successfully added, commit the transaction --->
<cfif Continue>
<cftransaction action="Commit" />
</cfif>
</cftransaction>
<html>
<head>
<title>Using Nested cftransaction Tags with Multiple Queries</title>
<style type="text/css">
th {
background-color : #888888;
font-weight : bold;
text-align : center;
}
td {
background-color : #C0C0C0;
}
</style>
</head>
<body>
<!--- If both transactions were successful, generate a table containing all
of the records from the new table. If not display a message letting
the user know there was a problem. --->
<cfif Continue>
<!--- Retrieve all of the records from the Sales Directory table we just
created --->
Example 11-27.Using nested cftransaction tags with multiple queries (continued)
This is the Title of the Book, eMatter Edition
Copyright © 2003 O’Reilly & Associates, Inc. All rights reserved.
Transaction Processing
|
371
In Example 11-27,the
Continue
variable controls the transaction.At the start of the
template,this variable is initialized to
true
.As long as queries within the
cftransaction
block execute successfully,
Continue
keeps the value of
true
.Each
subsequent query checks the status of this variable before executing.If at any point
Continue
is
false
,the transaction is rolled back,and no other queries with the
cftransaction
block are executed.
Executing the template in Example 11-27 results in the output shown in
Figure 11-10.If you execute the template a second time,however,you should see the
error message generated by the template.
<cfquery name="GetEmployees" datasource="ProgrammingCF">
SELECT Name, Title, Email, PhoneExt
FROM SalesDirectory
ORDER BY Name
</cfquery>
<h2>All queries in the transaction executed successfully</h2>
<h3>Below is the data from the new table:</h3>
<table cellpadding="3" cellspacing="0">
<tr>
<th>Name</th>
<th>Title</th>
<th>E-mail</th>
<th>Phone Ext.</th>
</tr>
<!--- Dynamically generate a table containing all of the records returned
by the query --->
<cfoutput query="GetEmployees">
<tr>
<td>#Name#</td>
<td>#Title#</td>
<td>#Email#</td>
<td>#PhoneExt#</td>
</tr>
</cfoutput>
</table>
<cfelse>
<cfoutput>
<h2>An Error has occurred. All queries have been rolled back</h2>
<b>The query that caused the error is: <I>#ProblemQuery#</I>.</b>
</cfoutput>
</cfif>
</body>
</html>
Example 11-27.Using nested cftransaction tags with multiple queries (continued)
This is the Title of the Book, eMatter Edition
Copyright © 2003 O’Reilly & Associates, Inc. All rights reserved.
372
|
Chapter 11:Advanced Database Techniques
Figure 11-10.Successful completion of queries within a nested cftransaction block