cfquery

Description

Passes SQL statements to a data source. Not limited to queries.

Syntax


<cfquery name = "query_name"

  dataSource = "ds_name"

  dbType = "type"

  dbServer = "dbms"

  dbName = "database name"

  connectString = "connection string"

  username = "username"

  password = "password"

  maxRows = "number"

  blockFactor = "blocksize"

  timeout = "milliseconds"

  cachedAfter = "date" 

  cachedWithin = "timespan" 

  provider = "COMProvider" 

  providerDSN = "datasource" 

  debug>

 

SQL statements

 

</cfquery>

Attributes

Attribute

Description

name
Required. The name query. Must begin with a letter and may consist of letters, numbers, and the underscore character (spaces are not allowed). The query name is used later in the page to reference the query's record set.

dataSource
Required for all dbType operations except dbType = "query" and dbType = "dynamic". The name of the data source from which this query should retrieve data.

dbType
Optional. The database driver type:

dynamic Connect to an ODBC data source that is not defined in the ColdFusion Administrator. When you use this attribute value, you must specify all the ODBC connection information using the connectstring attribute.

query    Use the result set from an existing query as the data source. Do not specify a datasource attribute when using dbType = "query". Instead, use existing query names as table names.

ODBC    (default) ODBC driver.

Oracle73    Oracle 7.3 native database driver. Using this option, the ColdFusion Server computer must have Oracle 7.3.4.0.0 (or greater) client software installed.

Oracle80    Oracle 8.0 native database driver. Using this option, the ColdFusion Server computer must have Oracle 8.0 (or greater) client software installed.

Sybase11    Sybase System 11 native database driver. Using this option, the ColdFusion Server computer must have Sybase 11.1.1 (or greater) client software installed. Sybase patch ebf 7729 is recommended.

OLEDB    OLE DB provider. If specified, this database provider overrides the driver type specified in the ColdFusion Administrator.

DB2    DB2 5.2 native database driver.

Informix73    Informix73 native database driver.

dbServer
Optional. For native database drivers and the SQLOLEDB provider, specifies the name of the database server computer. If specified, dbServer overrides the server specified in the data source.

dbName
Optional. The database name (Sybase System 11 driver and SQLOLEDB provider only). If specified, dbName overrides the default database specified in the data source.

connectString
Required for dbType = "dynamic"; optional for all others. The contents of a connection string to send to the ODBC server. When connecting to a data source defined in the ColdFusion Administrator, you can use this attribute to specify additional connection details or to override connection information specified in the Administrator. If you are dynamically connecting to a datasource by specifying dbType = "dynamic", the connection string must specify all required ODBC connection attributes.

username
Optional. If specified, username overrides the username value specified in the data source setup.

password
Optional. If specified, password overrides the password value specified in the data source setup.

maxRows
Optional. Specifies the maximum number of rows to return in the record set.
The maxRows attribute accepts an argument of -1, which causes cfquery to return all rows in the query object.

blockFactor
Optional. Specifies the maximum number of rows to fetch at a time from the server. The range is 1 (default) to 100. This parameter applies to ORACLE native database drivers and to ODBC drivers. Certain ODBC drivers may dynamically reduce the block factor at runtime.

timeout
Optional. The maximum number of milliseconds for the query to execute before returning an error indicating that the query has timed-out. This attribute is not supported by most ODBC drivers. timeout is supported by the SQL Server 6.x or above driver. The minimum and maximum allowable values vary, depending on the driver.

cachedAfter
Optional. A date value (for example, 4/16/98, April 16, 1999, 4-16-99). ColdFusion uses cached query data if the date of the original query is after the date specified. Effective only if query caching is enabled in the ColdFusion Administrator. To use cached data, the current query must use the same SQL statement, data source, query name, user name, password, and dbType. For native drivers, it must have the same dbServer and dbName (Sybase only).
Years from 0 to 29 are interpreted as 21st century values. Years 30 to 99 are interpreted as 20th century values.
When specifying a date value as a string, you must enclose it in quotes.

cachedWithin
Optional. A timespan using the ColdFusion CreateTimeSpan function. Cached query data is used if the original query date falls within the time span you define. The CreateTimeSpan function is used to define a period of time from the present backwards. Effective only if query caching is enabled in the ColdFusion Administrator.
To use cached data, the current query must use the same SQL statement, data source, query name, user name, password, and dbType. For native drivers it must have the same dbServer and dbName (Sybase only).

provider
Optional. COM provider (OLE-DB only).

providerDSN
Optional. Data source name for the COM provider (OLE-DB only).

debug
Optional. Used for debugging queries. Specifying this attribute causes the SQL statement submitted to the data source and the number of records returned from the query to be returned.

Attribute	Description
name	Required. The name query. Must begin with a letter and may consist of letters, numbers, and the underscore character (spaces are not allowed). The query name is used later in the page to reference the query's record set.
dataSource	Required for all `dbType` operations except `dbType = "query"` and `dbType = "dynamic"`. The name of the data source from which this query should retrieve data.
dbType	Optional. The database driver type: `dynamic` Connect to an ODBC data source that is not defined in the ColdFusion Administrator. When you use this attribute value, you must specify all the ODBC connection information using the `connectstring` attribute. `query` Use the result set from an existing query as the data source. Do not specify a `datasource` attribute when using `dbType = "query"`. Instead, use existing query names as table names. `ODBC` (default) ODBC driver. `Oracle73` Oracle 7.3 native database driver. Using this option, the ColdFusion Server computer must have Oracle 7.3.4.0.0 (or greater) client software installed. `Oracle80` Oracle 8.0 native database driver. Using this option, the ColdFusion Server computer must have Oracle 8.0 (or greater) client software installed. `Sybase11` Sybase System 11 native database driver. Using this option, the ColdFusion Server computer must have Sybase 11.1.1 (or greater) client software installed. Sybase patch ebf 7729 is recommended. `OLEDB` OLE DB provider. If specified, this database provider overrides the driver type specified in the ColdFusion Administrator. `DB2` DB2 5.2 native database driver. `Informix73` Informix73 native database driver.
dbServer	Optional. For native database drivers and the SQLOLEDB provider, specifies the name of the database server computer. If specified, `dbServer` overrides the server specified in the data source.
dbName	Optional. The database name (Sybase System 11 driver and SQLOLEDB provider only). If specified, `dbName` overrides the default database specified in the data source.
connectString	Required for `dbType = "dynamic"`; optional for all others. The contents of a connection string to send to the ODBC server. When connecting to a data source defined in the ColdFusion Administrator, you can use this attribute to specify additional connection details or to override connection information specified in the Administrator. If you are dynamically connecting to a datasource by specifying `dbType = "dynamic"`, the connection string must specify all required ODBC connection attributes.
username	Optional. If specified, `username` overrides the username value specified in the data source setup.
password	Optional. If specified, `password` overrides the password value specified in the data source setup.
maxRows	Optional. Specifies the maximum number of rows to return in the record set. The `maxRows` attribute accepts an argument of `-1`, which causes `cfquery` to return all rows in the query object.
blockFactor	Optional. Specifies the maximum number of rows to fetch at a time from the server. The range is 1 (default) to 100. This parameter applies to ORACLE native database drivers and to ODBC drivers. Certain ODBC drivers may dynamically reduce the block factor at runtime.
timeout	Optional. The maximum number of milliseconds for the query to execute before returning an error indicating that the query has timed-out. This attribute is not supported by most ODBC drivers. `timeout` is supported by the SQL Server 6.x or above driver. The minimum and maximum allowable values vary, depending on the driver.
cachedAfter	Optional. A date value (for example, 4/16/98, April 16, 1999, 4-16-99). ColdFusion uses cached query data if the date of the original query is after the date specified. Effective only if query caching is enabled in the ColdFusion Administrator. To use cached data, the current query must use the same SQL statement, data source, query name, user name, password, and `dbType`. For native drivers, it must have the same `dbServer` and `dbName` (Sybase only). Years from 0 to 29 are interpreted as 21st century values. Years 30 to 99 are interpreted as 20th century values. When specifying a date value as a string, you must enclose it in quotes.
cachedWithin	Optional. A timespan using the ColdFusion CreateTimeSpan function. Cached query data is used if the original query date falls within the time span you define. The CreateTimeSpan function is used to define a period of time from the present backwards. Effective only if query caching is enabled in the ColdFusion Administrator. To use cached data, the current query must use the same SQL statement, data source, query name, user name, password, and `dbType`. For native drivers it must have the same `dbServer` and `dbName` (Sybase only).
provider	Optional. COM provider (OLE-DB only).
providerDSN	Optional. Data source name for the COM provider (OLE-DB only).
debug	Optional. Used for debugging queries. Specifying this attribute causes the SQL statement submitted to the data source and the number of records returned from the query to be returned.

Usage

In addition to returning data from a ColdFusion data source, the cfquery tag also returns information about the query. cfquery.ExecutionTime returns the time it took the query to execute in milliseconds.

The cfquery tag creates a query object, providing information in query variables as described in the following table.

Variable Name

Description

query_name.recordCount
The number of records returned by the query.

query_name.currentRow
The current row of the query being processed by cfoutput.

query_name.columnList
A comma-delimited list of the query columns.

Variable Name	Description
`query_name.recordCount`	The number of records returned by the query.
`query_name.currentRow`	The current row of the query being processed by `cfoutput`.
`query_name.columnList`	A comma-delimited list of the query columns.

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

Example

<!--- This example shows the use of cfquery --->



<html>

<head>

  <title>cfquery Example</title>

</head>



<body>

<H3>cfquery Example</H3>

<!--- define startrow and maxrows to facilitate

  'next N' style browsing --->

<cfparam name = "MaxRows" default = "10">

<cfparam name = "StartRow" default = "1">



<!--- query database for information --->

<cfquery name = "GetParks" dataSource = "cfsnippets">

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 = "cfquery.cfm?startrow = <cfoutput>#Evaluate(StartRow + 

    MaxRows)#</cfoutput>">See next <cfoutput>#MaxRows#</cfoutput> 

    rows</A>

  </cfif>

  

  </TD>

</TR>

</table>

</body>

</html>