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

cfquery Attribute Reference

name string

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

datasource string

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 string

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 string

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 string

Overrides username in data source setup.

password string

Overrides password in data source setup.

maxrows numeric
Default: -1

Maximum number of rows to return in record set.
-1 returns all records.

blockfactor numeric

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

timeout numeric

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 date

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 Timespan

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

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. (string)
* 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 struct

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

cacheID string

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

cacheRegion string

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

clientInfo struct

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

fetchClientInfo boolean
Default: false

CF 10+ When true returns a struct with the clientInfo argument value passed by the last query

lazy boolean
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 boolean
Default: false

Lucee 4+ When true preserve single quotes within the sql statement

returntype string
Default: query

Lucee 5+ The return type of the query result. One of the following values is accepted: "query" - returns a query object, "array_of_entity" - returns an array of ORM entities (requires dbtype to be "hql"), "array" - returns an array of structs, "struct" - returns a struct of structs (requires columnkey to be defined).

columnkey string

Lucee 5+ The struct key used for each result when returntype is "struct".

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

CF 11+ 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"} 
);
writeDump(myQuery);

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();

Return Query as an Array of Structs

Lucee 5+ Return a query object converted into an array of structs.

<!--- create a dummy query using queryNew --->
<cfset users = queryNew("firstname", "varchar", [{"firstname":"Han"}])>
<cfset writeDump(users)>

<!--- run QofQ (query of query) --->
<cfquery name="subUsers" dbtype="query" returntype="array">
    SELECT * FROM users
</cfquery>
<cfset writeDump(subUsers)>

Expected Result: [ { firstname: "Han" } ]


Return Query as a Struct of Structs

Lucee 5+ Return a query object converted into a struct of structs. (Struct key is based on the "columnkey" parameter)

<!--- create a dummy query using queryNew --->
<cfset users = queryNew("id, firstname", "integer, varchar", [{"id":1, "firstname":"Han"}])>
<cfset writeDump(users)>

<!--- run QofQ (query of query) --->
<cfquery name="subUsers" dbtype="query" returntype="struct" columnkey="id">
    SELECT * FROM users
</cfquery>
<cfset writeDump(subUsers)>

Expected Result: { 1: { id: 1, firstname: "Han" } }


Fork me on GitHub