cfquery

Passes queries or SQL statements to a data source.
It is recommended that you use the cfqueryparam tag within
every cfquery tag, to help secure your databases from
unauthorized users

 <cfquery>SQL</cfquery>

 queryExecute(sql, params, options);

Attribute Reference for the cfquery tag

name

Required: No
Name of query. Used in page to reference query record set.
Must begin with a letter. Can include letters, numbers,
and underscores.

datasource

Required: No
Name of data source from which query gets data. As of CF 9+ you can specify a default datasource in Application.cfc using the variable this.datasource

timezone

Required: No
Lucee 4+ the timezone used to convert a date object to a timestamp (string), this value is needed when your database runs in another timezone and you are not using cfqueryparam to to insert dates.

dbtype

Required: No
Type of source query against which the SQL will be executed. Specify either dbtype or dataSource, not both. Supports the following values: query: for querying an existing query object (i.e. Query of Queries); hql: for querying an ORM. NOTE: Supported SQL syntax varies depending on this value. See Query of Queries user guide for Query of Query documentation. Values:
  • query
  • hql

username

Required: No
Overrides username in data source setup.

password

Required: No
Overrides password in data source setup.

maxrows

Required: No
Default: -1
Maximum number of rows to return in record set.
-1 returns all records.

blockfactor

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

timeout

Required: No
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, CFML sets this attribute. For
other drivers, check driver documentation.

cachedafter

Required: No
Date value (for example, April 16, 1999, 4-16-99). If date
of original query is after this date, CFML 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

Required: No
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

Required: No
Default: NO
Yes: 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

Required: No
CF 8+ Specifies a name for the structure in which cfquery returns
the result variables.
* SQL: The SQL statement that was executed. (string)
* Cached: If the query was cached. (boolean)
* SqlParameters: An ordered Array of cfqueryparam values. (array)
* RecordCount: Total number of records in the query. (numeric)
* ColumnList: Column list, comma seperated. (numeric)
* ExecutionTime: Execution time for the SQL request. (numeric)
* GENERATEDKEY: CF 9+ If the query was an INSERT with an identity or autoincrement value the value of that ID is placed in this variable.

ormoptions

Required: No
CF 9+ A structure of ORM Options when used for HQL queries (9.0.1+).

cacheID

Required: No
CF 10+ A value to serve as cache identifier when cachedWithin or cachedAfter are specified.

cacheRegion

Required: No
CF 10+ The name of the region cachedWithin or cachedAfter are specified.

clientInfo

Required: No
CF 10+ A structure containing properties to be set on the database connection.

fetchClientInfo

Required: No
Default: false
CF 10+ When true returns a struct with the clientInfo argument value passed by the last query

lazy

Required: No
Default: false
Lucee 4+ If "lazy" is set to true (default "false") Lucee does not initially load all the data from the datasource.

When "true" the data is only loaded when requested,this means the data is dependant on the datasource connection. If the datasource connection has been lost for some reason and the data has not yet been requested,Lucee throws an error if you try to access the data.

The "lazy" attribute only works if the following attributes are not used:cachewithin,cacheafter and result.

psq

Required: No
Default: false
Lucee 4+ When true preserve single quotes within the sql statement

Links more information about cfquery

Examples sample code using the cfquery tag


Example CFQuery with CFQueryParam

Shows how to use a cfqueryparam tag within cfquery.

<cfquery name="news">
    SELECT id,title,story
    FROM news
    WHERE id = <cfqueryparam value="#url.id#" cfsqltype="cf_sql_integer">
</cfquery>

Script Syntax using QueryExecute

Also see the Tags Implemented as Components section for another method of using in script.

myQuery = queryExecute(
 "SELECT myCol1, myCol2 FROM myTable 
  WHERE myCol1 = :myid 
  ORDER BY myCol1 ASC ", 
  {myid: 5}, 
  {datasource = "myDSN"} 
 );

Simple Query of Query Example

A dummy query is first created from scratch using queryNew, then sorted. A query of query is performed by specifying dbtype="query" and then using a query object variable name as in the FROM statement.

<!--- create a dummy query using queryNew --->
<cfset news = queryNew("id,title", "integer,varchar")>
<cfset queryAddRow(news)>
<cfset querySetCell(news, "id", "1")>
<cfset querySetCell(news, "title", "Dewey defeats Truman")>
<cfset queryAddRow(news)>
<cfset querySetCell(news, "id", "2")>
<cfset querySetCell(news, "title", "Men walk on Moon")>
<cfset writeDump(news)>

<!--- run QofQ (query of query) --->
<cfquery name="sortedNews" dbtype="query">
    SELECT id, title FROM news
    ORDER BY title DESC
</cfquery>
<cfset writeDump(sortedNews)>

Script Syntax using new Query()

This syntax was implemented by script-based components in CF 9 & 10. It is superceeded by queryExecute() in CF11.

queryObj = new Query(
 name="qryDemo",
 datasource="mydatasourcename",
 sql = "SELECT col1, col2
 FROM myTable
 WHERE id=:id"
); 
queryObj.addParam(name="id",value=arguments.id, cfsqltype="cf_sql_integer");
resultset=queryObj.execute().getResult();

Fork me on GitHub