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

Lucee

BL 1.0.0+

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

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 numeric

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: `false`

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.

Values:

true
false

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 separated. (string) * ExecutionTime: Execution time for the SQL request. (numeric) * GENERATEDKEY: CF 9+ If the query was an INSERT with an identity or auto-increment 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 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 dependent 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".

Compatibility

BoxLang:

Version 1.0.0+ <bx:query> in BoxLang

Links more information about cfquery

Adobe Query of Queries User Guide - From the Adobe documentation, useful to see what SQL statements are supported.

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. Deprecated in ColdFusion 2018. Removed in ColdFusion 2025. It is superseded 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" } }

Insert a Record and Retrieve the Generated Key

Inserts a new record into a table called myTable. The "ID" column is an auto-incremented primary key column.

<!--- insert the new record --->
<cfquery datasource="myDatasource" result="result">
INSERT INTO myTable ( name, email ) VALUES ( 'Joe', 'joe@domain.com' )
</cfquery>

<!--- output the new primary key --->
<cfdump var="#result.generatedKey#" />

© 2012–present Foundeo, Inc. and the CFDocs contributors.
Licensed under the MIT License.
ColdFusion is a trademark of Adobe Systems Incorporated.
https://cfdocs.org/cfquery