CFM Tutorial     

Cold Fusion Tag: <CFQUERY></CFQUERY>

Description

<CFQUERY> is the tag you'll use to submit SQL statements to an ODBC driver. SQL statements are not limited to SELECT statements, but can also be INSERT, UPDATE, and DELETE statements, as well as class to stored procedures. <CFQUERY> returns results in a named set if you specify a query name in the NAME attribute. The <CFQUERY> attributes setup the query, and any text between the <CFQUERY> and </CFQUERY> tags become the SQL statement that is sent to the ODBC driver. Cold Fusion conditional code may be used between the <CFQUERY> and </CFQUERY> tags, allowing you to create dynamic SQL statements.

Syntax

<CFQUERY NAME="Parameter Name" 
   DATASOURCE="ODBC Data Source"
   USERNAME="User Name" 
   PASSWORD="Password">
   SQL Statement
 </CFQUERY>

<CFQUERY> attributes are described in the following table:

Attribute Description Notes
DATASOURCE ODBC data source This optional attribute is used to override the ODBC data source specified when the report was created.
NAME Query name This optional query name is used to refer to the query results in <CFTABLE> tags.
PASSWORD ODBC data source password This optional attribute is used to override the ODBC login password specified in the Cold Fusion Adminstrator.
USERNAME ODBC data source login name This optional attribute is used to override the ODBC login name specified in the Cold Fusion Administrator.

The following example is a simple retrieval query. 

<CFQUERY 
	DATASOURCE="a2Z"
	NAME="employees"
>
SELECT FirstName, LastName, PhoneExtension
FROM Employees
ORDER BY LastName, FirstName
</CFQUERY>
The next example demonstrates how dynamic SQL statements can be constructed using the Cold Fusion conditional tags. 
<CFQUERY 
	DATASOURCE="a2Z"
	NAME="employees"
>
SELECT FirstName, LastName, PhoneExtension
FROM Employees
WHERE employeeID = EmployeeID
<CFIF #FirstName# is NOT "">
  AND FirstName LIKE '#FirstName#%'"
</CFIF>
<CFIF #LastName# is NOT "">
  AND LastName LIKE '#PhoneExtension#'
</CFIF>
ORDER BY LastName, FirstName;
</CFQUERY>
<CFQUERY> can be used to execute any SQL statements, and the following example demonstrates how <CFQUERY> can be used to delete rows from a table. 
<CFQUERY
 DATASOURCE="A2Z"  
>
DELETE FROM Employees WHERE EmployeeID = "#EmployeeID#"
</CFQUERY>

    References

    (1) the Cold Fusion Web Application Construction Kit, Second Edition.
KHMERCyber.com ©2008