Connecting to the Database

<cfquery> is used to prepare and submit an SQL statement to a data source. <cfquery> and its attributes also can be used to override the default settings on the data source connection to the database. Any SQL statement that can be interpreted by the database and its driver can be sent, including SELECT, UPDATE, and INSERT statements:

 <cfquery name="users"          datasource="dsn"> SELECT LastName, FirstName, EmpID FROM Users </cfquery> 

SQL is used to communicate with the database through the data source. Cold Fusion does not perform SQL validation, so you can use any syntax that is supported by your data source. Check your database documentation for details on the usage of nonstandard SQL code.

Interactions with the database are covered in detail throughout Part VII, "Databases." Chapter 44, "Basic SQL," offers an introduction to SQL.

The <cfquery> attributes cachedwithin and cachedafter are used to improve query performance and implement dynamic caching. These attributes are covered in detail in Chapter 48, "Improving Performance."

When <cfquery> performs a SELECT operation on the database and records are found, a record set is returned. This record set is converted into a ColdFusion query object. The query object is neither an array nor a structure but a special variable in its own right. Although it does exhibit array-like properties, the query object has its own set of ColdFusion functions, such as QueryAddColumn() and QueryAddRow().

Arrays are discussed in Chapter 14, "Arrays," and structures are covered in Chapter 15, "Structures."

A query object is referenced by the name attribute specified in the <cfquery> tag. This tag stores the complete record set returned by the SQL statement and several additional variables that might be of use, as shown in Table 7.1.

Table 7.1. <cfquery> Variables

VARIABLE NAME	DESCRIPTION
RecordCount	The total number of records in the query. If no records were returned, this is 0.
ColumnList	A comma-delimited list of all column names in the query (returned in no particular order)
CurrentRow	The current row of the query. This is relevant when the result set is being processed by a tag such as <cfoutput> that loops over the rows in the record set.
ExecutionTime	The time taken to execute the query on the database and return the results to ColdFusion, in milliseconds. (This variable is returned as cfquery.executionTime).

The variables listed in Table 7.1, and additional information, can also be accessed as a result structure. To do so simply specify the name of a structure to be created in the <cfquery> result attribute, as in result="cfqueryResults". ColdFusion will create and populate the named result structure.

TIP

Additional information about query contents may be obtained using the getMetaData() function.

You can generate dynamic queries within <cfquery> by using variables and conditional logic. <cfquery> processes any ColdFusion code or variables first, before submitting the resulting SQL code to the database:

 <cfquery name="users"          datasource="dsn"> SELECT LastName, FirstName, EmpID FROM Users <cfif FORM.EmpID IS NOT "">  WHERE EmpID = #FORM.EmpID# </cfif> </cfquery> 

Conditional processing is covered in Chapter 3, "Conditional Processing."

Care must be taken when building dynamic SQL because of the following:

• Unlike CFML, SQL is not typeless. Variables passed to SQL must be cast to the appropriate type (for example, strings must be surrounded with single quotes).

• SQL is very picky about the order of clauses; when statements are being built dynamically, all possible statements must be syntactically correct.