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