411
DEVELOPING
COLDFUSION 9 APPLICATIONS
Accessing and Using Data
Las
t
upda
te
d 8/5/2010
Note:
The terms “recordset” and “query object” are often used synonymously when discussing recordsets for queries. For
more information, see “
Using Query of Queries
” on page
428.
When retrieving data from a database, perform the following tasks:
•
To tell ColdFusion how to connect to a database, use the
cfquery
tag on a page.
•
To specify the data that you want to retrieve from the database, write SQL commands inside the
cfquery
block.
•
Reference the query object and use its data values in any tag that presents data, such as
cfoutput
,
cfgrid
,
cftable
,
cfgraph
, or
cftree
.
The cfquery tag
The
cfquery
tag is one of the most frequently used CFML tags. You use it to retrieve and reference the data returned
from a query. When ColdFusion encounters a
cfquery
tag on a page, it does the following:
•
Connects to the specified data source.
•
Performs SQL commands that are enclosed within the block.
•
Returns result set values to the page in a query object.
The cfquery tag syntax
The following code shows the syntax for the
cfquery
tag:
<cfquery name="EmpList" datasource="cfdocexamples">
SQL code...
</cfquery>
In this example, the query code tells ColdFusion to do the following:
•
Connect to the cfdocexamples data source (the cfdocexamples.mdb database).
•
Execute SQL code that you specify.
•
Store the retrieved data in the query object EmpList.
When creating queries to retrieve data, keep in mind the following guidelines:
•
Use opening
<cfquery>
and ending
</cfquery>
tags, because the
cfquery
tag is a block tag.
•
Enter the query
name
and
datasource
attributes within the opening
cfquery
tag.
•
To tell the database what to process during the query, place SQL statements inside the
cfquery
block.
•
When referencing text literals in SQL, use single-quotation marks ('). For example,
SELECT * FROM mytable
WHERE FirstName='Jacob'
selects every record from mytable in which the first name is Jacob.
•
Surround attribute values with double quotation marks (“attrib_value”).
•
Make sure that a data source exists in the ColdFusion Administrator before you reference it in a
cfquery
tag.
•
Columns and tables that you refer to in your SQL statement must exist, otherwise the query fails.
•
Reference the query data by naming the query in one of the presentation tags, such as
cfoutput
,
cfgrid
,
cftable
,
cfgraph
, or
cftree
.
•
When ColdFusion returns database columns, it removes table and owner prefixes. For example, if you query
Employee.Emp_ID in the query, the Employee, is removed and returns as Emp_ID. You can use an alias to handle
duplicate column names; for more information, see “
Using Query of Queries
” on page 428.
•
You cannot use SQL reserved words, such as MIN, MAX, COUNT, in a SQL statement. Because reserved words are
database-dependent, see the documentation of your database for a list of reserved words.