Retrieving Data

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

Use the cfquery tag on a page to tell ColdFusion how to connect to a database.
Write SQL commands inside the cfquery block to specify the data that you want 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 cfoutput, cfgrid, cftable, cfgraph, or cftree.

The cfquery tag

The cfquery tag is one of the most frequently used CFML tags. You use it in conjunction with the cfoutput tag so that you can 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 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".

The cfquery tag syntax

<cfquery name="EmpList" datasource="CompanyInfo">

  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 cfquery tag:

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 datasource attribute to name an existing data source that should be used to connect to a specific database. Alternatively, use the dbtype = "dynamic" and connectString attributes to dynamically specify a database.
Always surround attribute values with double quotes (").
Place SQL statements inside the cfquery block to tell the database what to process during the query.
When referencing text literals in SQL, use single quotes ('). For example, Select * from mytable WHERE FirstName='Russ' selects every record from mytable in which the first name is Russ.

Developing ColdFusion Applications
Querying a Database