CFML Reference

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," at http://www.allaire.com/handlers/index.cfm?ID=8728&Method=Full
• Developing ColdFusion MX Applications with CFML

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

  SQL statement(s) >
</cfquery>

See also

cfinsert, cfprocparam, cfprocresult, cfqueryparam, cfstoredproc, cftransaction, cfupdate

History

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.

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

Usage

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:

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

Example

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

ColdFusion 9 | ColdFusion 8 | ColdFusion MX 7 | ColdFusion MX 6.1 | ColdFusion MX | Forums | Developer Center | Bug Reporting

Version 6

Comments are no longer accepted for ColdFusion MX. ColdFusion 8 is the current version.

Comments

Add Comment

carehart@systemanage said on Dec 30, 2002 at 8:26 AM :

Regarding the statement above:

New in ColdFusion MX: ColdFusion can index query results and delete index keys

This should be clarified in two ways. First, it's not obvious what's meant by the verb "index", so it should be amended to refer to CFINDEX since it's about creating verity indexes. Second, what's new is the ability to index the results from a CFSEARCH (a search of a verity index, whose results appear as a query). That failed in CF5 but seems to work in CFMX.

You've always been able to index other query results. It seems the sentence should read:

New in ColdFusion MX: ColdFusion can index (using CFINDEX) query results from a CFSEARCH and delete index keys

sd1985 said on Apr 17, 2003 at 11:15 AM :

above it lists that the blockfactor attribute works with oracle native databases, but Tech note 17462 states that it works with both Oracle Native and ODBC databases. Which is it?

mcumar said on Feb 22, 2003 at 5:49 AM :

What about the connection string option? Why was it abandoned so quickly?

bzhou@previsionmarketing. said on Oct 27, 2003 at 5:59 AM :

we have extensively used 'connectstring' attribute with the combination of 'dbtype' set to DYNAMIC to query dynamicaly created fixed length files. To do that, we have also dynamicaly created a QUTXT.INI file at the same time. On your document, please provide alternative ways to do the same job without 'connectstring' attribute.

jrunrandy said on Oct 31, 2003 at 10:40 AM :

Unfortunately, there is no way, in CFMX or CFMX 6.1 to do this.

smb1@cornell.edu said on Nov 5, 2003 at 11:21 AM :

There should be a way to determine the number of rows updated or deleted for UPDATE and DELETE commands. Without this, time based rows but first be selected in order to determine if that can/should be updated or inserted. This is terribl inefficient.

jrunrandy said on Nov 6, 2003 at 8:47 AM :

Good one. I have entered this as enhancement request 53695.

nectaar said on Nov 18, 2003 at 4:04 AM :

The example says:
"Query database for information if cached database information has not been updated in the last six hours; otherwise, use cached data."

This is referring to the following line of code: cachedwithin="#CreateTimeSpan(0, 0, 6, 0)#">

The CreateTimeSpan doc says that is of the format CreateTimeSpan(days, hours, minutes, seconds).

Therefore the <cfquery> example should say "...last six minutes..." or change the code to:
cachedwithin = "#CreateTimeSpan(0, 6, 0, 0)#"

GrandNagel said on Oct 8, 2004 at 4:04 PM :

Is there a maximum length for the Name parameter of the CFQuery or CFLDap tags?

chilbe said on Oct 11, 2004 at 6:35 AM :

Is there a way to get the access column names that have spaces without aliasing them first? Example: "select `First Name` from ...". Only way I have seen to get it out is to do "select `First Name` as FirstName from ...". I would prefer to maintain the original column name when extracting the data.

No screen name said on Jan 17, 2005 at 2:30 PM :

You can get the column names by using brackets:

SELECT [First Name] FROM table

You should then be able to access them using structure syntax:

queryname["First Name"].RowNumber

Huggie_ON said on Jan 26, 2005 at 6:20 AM :

Question for the Variable name "query_name.columnList "
Is there a way that I can get the items in queryname.Columnlist without being sorted by columns name?
In other word I whish the items in columnlist could be the same order in my select sentence. please help me, thanks

tahseen said on Mar 17, 2005 at 9:06 AM :

Hi,
Is there any way to add a query to another query?
I have a situation where i need to do this (q1+q2), both q1 and q1 will have the same number/type of attributes..its the exact same thing in terms of structure, except the data.
Any alternative way to do this?
any help is appreciated.
thanks

jrunrandy said on Mar 22, 2005 at 11:09 AM :

Sure. Query of queries:
CFMX: http://livedocs.macromedia.com/coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/using_recordsets.htm

CFMX 6.1: http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/using_re.htm#wp1181595

CFMX 7: http://livedocs.macromedia.com/coldfusion/7/htmldocs/00001263.htm

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

Current page: http://livedocs.adobe.com/coldfusion/6/CFML_Reference/Tags-pt218.htm

Add Comment