|
ColdFusion 9.0 Resources |
cfqueryDescriptionPasses queries or SQL statements to a data source. Adobe 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 Security Zone, www.adobe.com/go/sn_asb99-04, and Accessing and Retrieving Data in the Developing ColdFusion Applications. Syntax<cfquery
name = "query name"
blockFactor = "block size"
cachedAfter = "date"
cachedWithin = "timespan"
dataSource = "data source name"
dbtype = "query"
debug = "yes|no"
maxRows = "number"
password = "password"
result = "result name"
timeout = "seconds"
username = "user name">
</cfquery>
Note: You can specify
this tag’s attributes in an attributeCollection attribute
whose value is a structure. Specify the structure name in the attributeCollection attribute
and use the tag’s attribute names as structure keys.
See alsocfdbinfo, cfinsert, cfprocparam, cfprocresult, cfqueryparam, cfstoredproc, cftransaction, cfupdate; Optimizing database use in the Developing ColdFusion Applications HistoryColdFusion 9: Datasource attribute is optional now. ColdFusion 8: Added the result variable that specifies the ID of a row. ColdFusion MX 7:
ColdFusion MX:
Attributes
UsageUse this tag to execute a SQL statement against a ColdFusion data source. Although you can use the cfquery tag to execute any SQL Data Definition Language (DDL) or Data Manipulation Language (DML) statement, you typically use it to execute a SQL SELECT statement. Note: To call a stored procedure, use the cfstoredproc tag.
This tag creates a query object, providing this information in query variables:
The cfquery tag also returns the following result variables in a structure. You can access these variables with a prefix of the name you specified in the result attribute. For example, if you assign the name myResult to the result attribute, you would retrieve the name of the SQL statement that was executed by accessing #myResult.sql#. The result attribute provides a way for functions or CFCs that are called from multiple pages, possibly at the same time, to avoid overwriting results of one call with another. The result variable of INSERT queries contains a key-value pair that is the automatically generated ID of the inserted row; this is available only for databases that support this feature. If more than one record was inserted, the value can be a list of IDs. The key name is database-specific.
You can cache query results and execute stored procedures. For information about this and about displaying cfquery output, see the Developing ColdFusion Applications. Because the timeout attribute only affects the maximum time for each suboperation 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 or use the RequestTimeout attribute of the cfsetting tag (for example, <cfsettingrequestTimeout="300">). The Caching page of the ColdFusion 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, see Escaping reserved keywords in the Developing ColdFusion Applications. Example<!--- This example shows the use of CreateTimeSpan with CFQUERY ------>
<!--- to cache a record set. 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="cfdocexamples"
cachedwithin="#CreateTimeSpan(0, 6, 0, 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 bgcolor="f0f0f0">
</td>
<td 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>
<cfoutput><a href="#CGI.SCRIPT_NAME#?startrow=#Evaluate(StartRow + MaxRows)#">
See next #MaxRows# rows</a></cfoutput>
</cfif>
</td>
</tr>
</table>
|
