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:
<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
New in ColdFusion MX: ColdFusion can index query results and delete index keys.
New in ColdFusion MX: Query of Queries supports a subset of standard SQL. For more information, see Developing ColdFusion MX Applications with CFML.
New in ColdFusion MX: ColdFusion supports dot notation within a record set name. ColdFusion interprets such a name as a structure. For more information, see Developing ColdFusion MX Applications with CFML.
New in ColdFusion MX: The connectString
, dbName
, dbServer
, provider
, providerDSN
, and sql
attributes, and all values of the dbtype
attribute except query
, are deprecated. Do not use them. They do not work, and might cause an error, in releases later than ColdFusion 5.
Attribute | Req/Opt | Default | Description |
---|---|---|---|
name |
Required |
|
Name of query. Used in page to reference query record set. Must begin with a letter. Can include letters, numbers, and underscores. |
dataSource |
Required |
|
Name of data source from which query gets data. |
dbtype |
Optional |
query |
query. Use this value to specify the results of a query as input. |
username |
Optional |
|
Overrides username in data source setup. |
password |
Optional |
|
Overrides password in data source setup. |
maxRows |
Optional |
-1 (All) |
Maximum number of rows to return in record set. |
blockFactor |
Optional |
1 |
Maximum rows to get at a time from server. Range: 1 - 100. Applies to ORACLE native database drivers. |
timeout |
|
|
Maximum number of seconds that each action of a query is permitted to execute before returning an error. The cumulative time may exceed this value. For JDBC statements, ColdFusion sets this attribute. For other drivers, check driver documentation. |
cachedAfter |
Optional |
|
Date value (for example, April 16, 1999, 4-16-99). If date of original query is after this date, ColdFusion uses cached query data. Takes effect only if query caching is enabled in Administrator. To use cached data, current query must use same SQL statement, data source, query name, user name, password. A date/time object is in the range 100 AD-9999 AD. See "How ColdFusion processes two-digit year values". When specifying a date value as a string, you must enclose it in quotation marks. |
cachedWithin |
Optional |
|
Timespan, using the CreateTimeSpan function. If original query date falls within the time span, cached query data is used. CreateTimeSpan defines a period from the present, back. Takes effect only if query caching is enabled in the Administrator. To use cached data, the current query must use the same SQL statement, data source, query name, user name, and password. |
debug |
Optional; value and equals sign may be omitted |
|
|
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 with CFML.
You cannot use SQL reserved words as ColdFusion variable or query names.
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 http://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>