View comments | RSS feed

cfquery

Description

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, www.macromedia.com/devnet/security/security_zone/asb99-04.html, and Accessing and Retrieving Data in ColdFusion MX Developer's Guide.

Category

Database manipulation tags

Syntax

<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

result = "result_name">
</cfquery>

See also

cfinsert, cfprocparam, cfprocresult, cfqueryparam, cfstoredproc, cftransaction, cfupdate; Optimizing database use in Designing and Optimizing a ColdFusion Application and Chapters 19-22 in ColdFusion MX Developer's Guide

History

ColdFusion MX 7:

ColdFusion MX:

Attributes

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 unless dbtype=query.

 

Name of data source from which query gets data. You must specify either dbtype or dataSource.

dbtype

Optional

 

Use this value to specify the results of a query as input. You must specify either dbtype or dataSource.

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. Might not be supported by some database systems.

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

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

 
  • Yes, or if omitted: if debugging is enabled, but the Administrator Database Activity option is not enabled, displays SQL submitted to datasource and number of records returned by query.
  • No: if the Administrator Database Activity option is enabled, suppresses display.

result

Optional

 

Specifies a name for the structure in which cfquery returns the result variables. For more information, see the Usage section .

Usage

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

Variable name Description 
query_name.currentRow

Current row of query that cfoutput is processing.

 
query_name.columnList

Comma-delimited list of the query columns.

 
query_name.RecordCount

Number of records (rows) returned from the query.

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.

Variable name Description 
result_name.sql

The SQL statement that was executed.

 
result_name.recordcount

Number of records (rows) returned from the query.

 
result_name.cached

True if the query was cached; False otherwise.

 
result_name.sqlparameters

An ordered Array of cfqueryparam values.

 
result_name.columnList

Comma-delimited list of the query columns.

 
result_name.ExecutionTime

Cumulative time required to process the query.

You can cache query results and execute stored procedures. For information about this and about displaying cfquery output, see ColdFusion MX Developer's Guide.

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, <cfsetting requestTimeout="300">).

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 Escaping reserved keywords in Using Query of Queries in ColdFusion MX Developer's Guide.

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">
         &nbsp;
      </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>

ColdFusion MX 7 | ColdFusion MX 6.1 | ColdFusion MX | Forums | Developer Center | KnowledgeBase | Bug Reporting

Version 7

Comments


rvoosten said on Feb 9, 2005 at 12:17 AM :
docbug: the result = "result_name" should be before the greater than (>) symbol
cf.Objective said on Feb 12, 2005 at 9:06 AM :
In the chart outlining resultset extended variables, the text claims that result_name.recordcount is the current row of the query that's being processed by cfoutput. And, nobody is claiming to represent the total number of rows returned in the query object.

Just thought you'd like to know...

J
Gregd66 said on Mar 17, 2005 at 10:58 AM :
<!---
This code works in CF5 with the proper Data OK message.
It does not work on MX7 using an oracle jdbc or native driver.
If you look at the debug of the insert statement you will see it
is not escaped correctly. CF is suppossed to escape it automatically.
--->


<cfset dsn="testdsn">
<cfset mydata="'' '"> <!--- if you put a character between the first 2 single quotes it will work in MX7 ???? --->

<cfquery name="selectdata" datasource="#dsn#">
truncate table temp_table
</cfquery>

<cfquery name="inserttest" datasource="#dsn#">
insert into temp_table
(testdata)
values
('#mydata#')
</cfquery>

<cfquery name="selectdata" datasource="#dsn#">
select testdata
from temp_table
</cfquery>

<cfoutput>
<cfif selectdata.testdata neq mydata>
ERROR: DB data #selectdata.testdata# does not match original #mydata#
<cfelse>
Data OK.
</cfif>
</cfoutput>
wyntermoonwolf said on Aug 23, 2005 at 1:38 PM :
result_name.recordcount - Current row of query that cfoutput is processing.

It should read: result_name.CurrentRow
ASandstrom said on Aug 24, 2005 at 9:47 AM :
In response to wyntermoonwolf:
Actually, it should read:
result_name.recordcount - Number of records (rows) returned from the query.
funkdancer said on Nov 2, 2005 at 4:47 AM :
When performing date comparisons in QoQs, CFMX7 lets you do e.g.
where myDate <= #createodbcdatetime(now())#

In CFMX6 you will need to use bound params, e.g.
where myDate <= <cfqueryparam cfsqltype="cf_sql_date" value="#now()#">
105 said on Dec 27, 2005 at 10:47 PM :
It should be stated that cfquery only creates a query object for select statements. For insert, update and delete statements, no query object is created and a variable named with the query name is undefined.
MikerRoo said on Jul 11, 2006 at 1:55 PM :
It should be noted here that cachedWithin behavior has changed.
Using cachedWithin on a non-select query makes no sense but it did not throw an exception until version 7.02. (This has come up on the forums a few times now.)
Sankalan said on Oct 11, 2006 at 12:38 PM :
How to get ColumnList from a query resultset ?

<cfquery name="myQuery" datasource="yourDataSourceName" result="myResult">
select * from tableName
</cfquery>

<cfoutput>
#myQuery.ColumnList#<br>
#myResult.ColumnList#
</cfoutput>
This will give the column names in alphabetical order.

But to get the column names in the same order as in database tables, use this:
<cfoutput>
#ArrayToList(myQuery.getColumnList())#
</cfoutput>
carehart said on Mar 13, 2007 at 10:57 PM :
Docs above say "Setting this value to 0 disables query caching", but isn't it true instead that setting to 0 means "unlimited query caching"? That's what's been documented here (http://www.adobe.com/cfusion/knowledgebase/index.cfm?id=tn_19406) for CF 6, 6.1. My testing shows it still so in 7.02. If so, besides changing the above, It would seem helpful for that document to be udpated to reflect that it's still true.

It also seems that this should be corrected, really, to offer "disable" and "unlimited" buttons instead, to make intended behavor clear. Then 0 should be interpreted instead as disabled.

The potential negative impact of this current behavior is substantial, for those who think that setting to 0 is preventing any caching, where in fact code on the server uses the caching attributes, and therefore could be using large amounts of memory (with no easy means to detect that).
No screen name said on Jul 1, 2007 at 2:38 PM :
The comment in Dec 2005 about cfquery only creates a query object for select statements is not completely true.

If the statment you run returns a recordset, it will create a query object. The database does not return a recordset for most insert/updates, but you could for example: add an on insert trigger that runs "select @@identity as ID" which returns the primary key value in an identity column. You could now name your cfquery that runs the insert statement, it will have one row, one field, ID with the new record ID just inserted.
Joe1982 said on Oct 18, 2007 at 11:08 AM :
It may also be useful to note that when setting up a query dynamically, using the code "ArrayToList(userQuery.getColumnList())" can be a little tricky.

I have the following code that I am using to build a dynamic query. If I use 2 tables that have a field with the same name I will only have the data in the field from the first table repeated 2 times. Here is the code:

Here I declare the columns:
<cfset colList = ArrayToList(userQuery.getColumnList())>

Here I loop through the columns to print the data:
<cfloop list="#colList#" index="i">
<cfgridcolumn name="#i#" header="#i#" headeralign="left" dataalign="left" bold="yes" italic="no" select="yes" display="yes" headerbold="no" headeritalic="yes">
</cfloop>

If I write the sql "select a.name, b.name from schema1.table1 a, schema1.table2 b" I will only get the data for a.name twice.

Has anyone found a solution for this?

 

RSS feed | Send me an e-mail when comments are added to this page | Comment Report

Current page: http://livedocs.adobe.com/coldfusion/7/htmldocs/00000316.htm