cfquery

Passes queries or SQL statements to a data source.

Macromedia recommends that you use the cfqueryparam tag within every cfquery tag, to help secure your databases from unauthorized users. For more information, see:

• Security Bulletin ASB99-04, "Multiple SQL Statements in Dynamic Queries," in the Macromedia Security Zone, http://www.macromedia.com/devnet/security/security_zone/asb99-04.html.
• Accessing and Retrieving Data in Developing ColdFusion MX Applications

Database manipulation tags

<cfquery 
   name = "query_name"
   dataSource = "ds_name"
   dbtype = "query"
   username = "username"
   password = "password"
   maxRows = "number"
   blockFactor = "blocksize"
   timeout = "seconds"
   cachedAfter = "date" 
   cachedWithin = "timespan" 

   Either of the following:
      debug = "Yes" or "No"
   or:
      debug
>
   SQL statement(s)
</cfquery>

cfinsert, cfprocparam, cfprocresult, cfqueryparam, cfstoredproc, cftransaction, cfupdate, chapters 19-22 of Developing ColdFusion MX Applications

ColdFusion MX:

• Changed Query of Queries behavior: it now supports a larger subset of standard SQL. For more information, see Using Query of Queries in Developing ColdFusion MX Applications.
• Changed dot notation support: ColdFusion now supports dot notation within a record set name. ColdFusion interprets such a name as a structure. For more information, see Using Query of Queries in Developing ColdFusion MX Applications.
• Deprecated the connectString, dbName, dbServer, provider, providerDSN, and sql attributes, and all values of the dbtype attribute except query. They do not work, and might cause an error, in releases later than ColdFusion 5.

Because the timeout parameter only the maximum time for each sub-operation of a query, the cumulative time may exceed its value. To set a timeout for a page that might get a very large result set, set the Administrator > Server Settings > Timeout Requests option to an appropriate value.

This tag returns data and query information from a ColdFusion data source. The cumulative query execution time, in seconds, is returned in the variable cfquery.ExecutionTime.

This tag creates a query object, providing this information in query variables:

You can cache query results and execute stored procedures. For information about this and about displaying cfquery output, see Developing ColdFusion MX Applications.

The Caching page of the ColdFusion MX Administrator specifies the maximum number of cached queries. Setting this value to 0 disables query caching.

You cannot use ColdFusion reserved words as query names.

You cannot use SQL reserved words as variable or column names in a Query of Queries, unless they are escaped. The escape character is the bracket []; for example:

SELECT [count] FROM MYTABLE. 

For a list of reserved keywords in ColdFusion MX, see Using Query of Queries in Developing ColdFusion MX Applications.

Database query results for date and time values can vary in sequence and formatting, unless you use functions to format the results. To ensure that customers using your ColdFusion application are not confused by the display, Macromedia recommends that you use the DateFormat and TimeFormat functions to format values from queries. For more information and examples, see TechNote 22183, "ColdFusion Server (5 and 4.5.x) with Oracle: Formatting Date and Time Query Results," at www.coldfusion.com/Support/KnowledgeBase/SearchForm.cfm.

<!--- This example shows the use of CreateTimeSpan with CFQUERY ------>
<!--- define startrow and maxrows to facilitate 'next N' style browsing ---->
<cfparam name="MaxRows" default="10">
<cfparam name="StartRow" default="1">
<!--------------------------------------------------------------------
Query database for information if cached database information has
not been updated in the last six hours; otherwise, use cached data.
--------------------------------------------------------------------->
<cfquery 
   name="GetParks" datasource="cfsnippets" 
   cachedwithin="#CreateTimeSpan(0, 0, 6, 0)#">
   SELECT PARKNAME, REGION, STATE
   FROM Parks
   ORDER BY ParkName, State
</cfquery>
<!---- build HTML table to display query ------------------------->
<table cellpadding="1" cellspacing="1">
   <tr>
      <td colspan="2" bgcolor="f0f0f0">
         <b><i>Park Name</i></b>
      </td>
      <td bgcolor="f0f0f0">
         <b><i>Region</i></b>
      </td>
      <td bgcolor="f0f0f0">
         <b><i>State</i></b>
      </td>
   </tr>
   <!---- Output the query and define the startrow and maxrows parameters. 
      Use the query variable CurrentCount to keep track of the row you 
      are displaying. ------>
   <cfoutput 
      query="GetParks" startrow="#StartRow#" maxrows="#MaxRows#">
   <tr>
      <td valign="top" bgcolor="ffffed">
         <b>#GetParks.CurrentRow#</b>
      </td>
      <td valign="top">
         <font size="-1">#ParkName#</font>
      </td>
      <td valign="top">
         <font size="-1">#Region#</font>
      </td>
      <td valign="top">
         <font size="-1">#State#</font>
      </td>
   </tr>
   </cfoutput>
   <!----- If the total number of records is less than or equal
   to the total number of rows, then offer a link to
   the same page, with the startrow value incremented by
   maxrows (in the case of this example, incremented by 10) --------->
   <tr>
      <td colspan="4">
      <cfif (StartRow + MaxRows) LTE GetParks.RecordCount>
         <a href="index.cfm?startrow=
            <cfoutput>               #Evaluate(StartRow + MaxRows)#            </cfoutput>
            ">See next <cfoutput>#MaxRows#</cfoutput> rows</a> 
      </cfif>
      </td>
   </tr>
</table>

View comments on LiveDocs