Your Rating: Results: PatheticBadOKGoodOutstanding! 2 rates

Description

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

Category

Database manipulation tags

Syntax

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 also

cfdbinfo, cfinsert, cfprocparam, cfprocresult, cfqueryparam, cfstoredproc, cftransaction, cfupdate; Optimizing database use in Optimizing ColdFusion applications in the Developing ColdFusion Applications

History

ColdFusion 10: Added the following attributes: fetchClientInfo, cacheID, cacheRegion, clientInfo}}

ColdFusion 9.0.1: Introduced support for HQL queries; added the attribute {{ormoptions.

ColdFusion 9: Datasource attribute is optional now.

ColdFusion 8: Added the result variable that specifies the ID of a row.

ColdFusion MX 7:

  • Added the result attribute for specifying an alternate name for the structure that holds the result variables.
  • Added result variables for the SQL statement executed (sql), the number of records returned (recordcount), whether the query was cached (cached), an array of cfqueryparam values (sqlparameters), and the list of columns in the returned query (columnlist).
    ColdFusion MX:
  • Changed Query of Queries behavior: it now supports a larger subset of standard SQL.
  • Changed dot notation support: ColdFusion now supports dot notation within a record set name. ColdFusion interprets such a name as a structure.
  • Deprecated the connectString, dbName, dbServer, provider, providerDSN, and sql attributes, and all values of the dbtype attribute except query. They do not work, and might cause an error, in releases later than ColdFusion 5.
  • New query object variable: cfquery.ExecutionTime.
  • No longer supports native drivers. It now uses JDBC (and ODBC-JDBC bridge) for database connectivity.

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.

blockFactor

Optional

1

Maximum rows to get at a time from server. Range: 1 - 100. Might not be supported by some database systems.

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, enclose it in quotation marks.

cacheID

Optional

 

ID to be used to store query result in cache. This ID can be used to either retrieve or remove query from cache

cacheR{{egion}}

Optional

 

Cache region to be used to cache query result. If not specified, by default query is cached in the QUERY region.

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.

clientInfo

Optional

 

Structure containing properties of the client to be set on the database connection.

dataSource

Optional

 

The Datasource attribute is now optional. If omitted, the query uses the datasource specified in the application. If it is not specified in either places, then the error will be thrown.

dbtype

Optional

 

Results of a query as input. Specify either dbtype or dataSource.ColdFusion supports HQL in cfquery. Therefore, you can specify dbtype="hql" as shown in the following example:<cfquery dbtype="hql" name="artists" ormoptions=#{cachename=""}#>from Artists where firstname=<cfqueryparam value="Aiden"></cfquery>

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 the data source and number of records returned by query.
  • no: if the Administrator Database Activity option is enabled, suppresses display.

fetchClientInfo

Optional

no

If set yes, returns a struct with the key-value pair passed by the last query.

maxRows

Optional

-1 (All)

Maximum number of rows to return in record set.

ormoptions

Optional

 

A struct that takes orm options for executing HQL. Applies only if dbtype is set to hql.

password

Optional

 

Overrides the password in the data source setup.

result

Optional

 

Name for the structure in which cfquery returns the result variables. For more information, see Usage.

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, see the driver documentation.

username

Optional

 

Overrides user name in the data source setup.

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

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-separated list of the query columns.

result_name.ExecutionTime

Cumulative time required to process the query.

result_name.IDENTITYCOL

SQL Server only. The ID of an inserted row.

result_name.ROWID

Oracle only. The ID of an inserted row. This is not the primary key of the row, although you can retrieve rows based on this ID.

result_name.SYB_IDENTITY

Sybase only. The ID of an inserted row.

result_name.SERIAL_COL

Informix only. The ID of an inserted row.

result_name.GENERATED_KEYMySQL only. The ID of an inserted row. MySQL 3 does not support this feature.
result_name.GENERATEDKEYSupports all databases. The ID of an inserted row.

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, <cfsetting requestTimeout="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:

For a list of reserved keywords in ColdFusion, see Escaping reserved keywords in Query of Queries user guide in the Developing ColdFusion Applications.

Example

Labels
  • None
  1. Nov 09, 2012

    CFSCRIPT EQUIVALENT

    component {
     
      /**
       *  This example defines a function that takes a course number
       *  parameter and returns the course description.
       *  @access public
       *  @returntype string
       *  @output false
       */
      function getDescription (required numeric Course_Number) {
        // Use the argument to get a course description from the database.
        var getDescription = new Query(datasource="cfdocexamples");
        var sql = "SELECT Descript
          FROM Courses
          WHERE Number = :Course_Number";
        
        // The new cfqueryparam
        LOCAL.getDescription.addParam(name="Course_Number", value=ARGUMENTS.Course_Number, cfsqltype="cf_sql_integer");
        
        var Description = LOCAL.getDescription.execute(sql=LOCAL.sql).getResult();
        
        // Specify the variable that the function returns.
        return LOCAL.Description.Descript;
      }
     
    }

Searching ColdFusion English Documentation