A web application page is different from a static web page because it can publish data dynamically. This can involve querying databases, connecting to LDAP or mail servers, and leveraging COM, DCOM, CORBA, or Java objects to retrieve, update, insert, and delete data at run time--as your users interact with pages in their browsers.

For ColdFusion developers, the term data source can refer to a number of different types of structured content accessible locally or across a network. You can query websites, LDAP servers, POP mail servers, and documents in a variety of formats. Most commonly though, a database drives your applications, and for this discussion a data source means the entry point from ColdFusion to a database.

In this topic, you build a query to retrieve data from the cfdocexamples data source.

To query a database, you must use:

• ColdFusion data sources
• The cfquery tag
• SQL commands

Retrieving data

You can query databases to retrieve data at run time. 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, ColumnList, SQL, Cached, and SQLParameter 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 = "cfdocexamples">
    SELECT * FROM Employee
    ORDER BY LastName
</cfquery>

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 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 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 your database's documentation for a list of reserved words.
• If you use COMPUTE AVG() in your SQL, ColdFusion 8 returns avg() as the column name. (Previous versions (ColdFusion 5 and ColdFusion MX 7) returned ave() as the column name.)

Building queries

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

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 putting queries in ColdFusion components; however, for simplicity, the examples in this topic 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, you must 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: