Retrieving data

You can query databases to retrieve data at runtime. The retrieved data, called the record set, is stored on that page as a query object. A query object is a special entity that contains the record set values, plus RecordCount, CurrentRow, and ColumnList query variables. You specify the query object's name in the name attribute of the cfquery tag. The query object is often called simply the query.

The following is a simple cfquery tag:

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

  SELECT * FROM  Employee
  ORDER BY LastName
</cfquery>

Note: The terms "record set" and "query object" are often used synonymously when discussing record sets for queries. For more information, see Chapter 22, "Using Query of Queries".

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.
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 with the cfoutput tag 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="CompanyInfo">

  SQL code...
</cfquery>

In this example, the query code tells ColdFusion to do the following:

Connect to the CompanyInfo data source (the company.mdb database).
Execute SQL code that you specify.
Store the retrieved data in the query object EmpList.

When creating queries to retrieve data, keep the following guidelines in mind:

You must 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 MX 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 will fail.
Reference the query data by naming the query in one of the presentation tags, such as cfoutput, cfgrid, cftable, cfgraph, or cftree later on the page.
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 Chapter 22, "Using Query of Queries".
You cannot use SQL reserved words, such as MIN, MAX, COUNT, in a SQL statement. Because reserved words are database-dependent, see your database's documentation for a list of reserved words.

Building queries

As discussed earlier in this chapter, you build queries using the cfquery tag and SQL.

Note: This and many subsequent procedures use the CompanyInfo data source that connects to the company.mdb database. This data source is installed by default. For information on adding or configuring a data source, see Administering ColdFusion MX.

To query the table:

Create a ColdFusion page with the following content:

<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>
</body>
</html>

Save the page as emplist.cfm in the myapps directory under your web_root directory. For example, the default path on a Windows computer would be:
C:\CFusionMX\wwwroot\myapps\
Enter the following URL in your web browser:
http://127.0.0.1/myapps/emplist.cfm
Only the header appears, as the following figure shows:
View the source in the browser:

ColdFusion creates the EmpList data set, but only HTML and text return to the browser. When you view the page's source, you see only HTML tags and the heading "Employee List." To display the data set on the page, you must code tags and variables to output the data.

Reviewing the code

The query you just created retrieves data from the CompanyInfo database. The following table describes the highlighted code and its function:

Code

Description

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

Queries the database specified in the CompanyInfo data source.

SELECT FirstName, LastName, Salary, Contract FROM Employee

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

</cfquery>

Ends the cfquery block.

Developing ColdFusion MX Applications with CFML
Accessing and Retrieving Data