Retrieving data



You can query databases to retrieve data at run time. The retrieved data, called the recordset, is stored on that page as a query object. A query object is a special entity that contains the recordset values, plus RecordCount, CurrentRow, ColumnList, SQL, Cached, and SQLParameter query variables. You specify the name of the query object 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 = "cfdocexamples"> 
    SELECT * FROM Employee 
    ORDER BY LastName 
</cfquery>
Note: The terms “recordset” and “query object” are often used synonymously when discussing recordsets for queries. For more information, see 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.

  • 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.

  • 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.

  • If you use COMPUTE AVG() in your SQL, ColdFusion 9 returns avg() as the column name. (Previous versions (ColdFusion 5 and ColdFusion MX 7) returned ave() as the column name.)

  • To retrieve results returned by database triggers, add the following connection parameter in the connection string:

    AlwaysReportTriggerResults=true

    This parameter determines how the driver reports results generated by database triggers (procedures that are stored in the database and executed, or fired, when a table is modified). For Microsoft SQL Server 2005, this includes triggers fired by Data Definition Language (DDL) events. If set to true, the driver returns all results, including results generated by triggers. Multiple trigger results are returned one at a time. Use the method Statement.getMoreResults to retrieve individual trigger results. Warnings and errors are reported in the results as they are encountered.

Building queries

As discussed earlier, you build queries by using the cfquery tag and SQL.

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

Query the table

  1. Create a ColdFusion page with the following content:

    <html> 
<head> 
<title>Employee List</title> 
</head> 
<body> 
<h1>Employee List</h1> 
<cfquery name="EmpList" datasource="cfdocexamples"> 
    SELECT FirstName, LastName, Salary, Contract 
    FROM Employee 
</cfquery> 
</body> 
</html>
    Note: Adobe recommends that you create structured, reusable code by placing queries in ColdFusion components; however, for simplicity, the examples here include the query in the body of the ColdFusion page. For more information about using ColdFusion components, see Building and Using ColdFusion Components.

  2. 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:\CFusion\wwwroot\myapps\

  3. Enter the following URL in your web browser:

    http://localhost/myapps/emplist.cfm

    Only the header appears.

  4. 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, use code tags and variables to output the data.

Reviewing the code

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

Code

Description

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

Queries the database specified in the cfdocexamples 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.