Lucee Tag Reference

<cfquery>

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

Body

This tag may have a body.

Example

<cfquery
[async=boolean]
[blockfactor=number]
[cachedafter=datetime]
[cachedwithin=object]
[cacheid=string]
[cachename=string]
[columnkey=string]
[connectstring=string]
[datasource=any]
[dbname=string]
[dbserver=string]
[dbtype=string]
[debug=boolean]
[indexname=string]
[lazy=boolean]
[listener=any]
[maxrows=number]
[name=string]
[ormoptions=struct]
[params=object]
[password=string]
[provider=string]
[providerdsn=string]
[psq=boolean]
[result=string]
[returntype=string]
[sql=string]
[tags=any]
[timeout=any]
[timezone=timezone]
[unique=boolean]
[username=string]>
[</cfquery>]
This tag is also supported within cfscript
<cfscript>
query
[async=boolean]
[blockfactor=number]
[cachedafter=datetime]
[cachedwithin=object]
[cacheid=string]
[cachename=string]
[columnkey=string]
[connectstring=string]
[datasource=any]
[dbname=string]
[dbserver=string]
[dbtype=string]
[debug=boolean]
[indexname=string]
[lazy=boolean]
[listener=any]
[maxrows=number]
[name=string]
[ormoptions=struct]
[params=object]
[password=string]
[provider=string]
[providerdsn=string]
[psq=boolean]
[result=string]
[returntype=string]
[sql=string]
[tags=any]
[timeout=any]
[timezone=timezone]
[unique=boolean]
[username=string] {
[...]
}
</cfscript>

Attributes

The attributes for this tag are fixed. Except for the following attributes no other attributes are allowed.
Name Type Required Description
async boolean No if set to true, the query is executed asynchronously by the Lucee Task manager,
if set to false (default) the query is executed in the same thread that executes the request. 
blockfactor number No 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. 
cachedafter datetime No Sets a date/time from when the tag will start to cache the query. In difference to other CFML Engines this attribute only works in combination with the tag "cachedWithin", so you still have to set with "cachedWithin" how long the query remains in the cache. To match other CFML engines simply set "cachedWithin" to "36500", that will hold it in cache for 100 years! 
cachedwithin any No possible values are:
String "request": If original query was created within the current request, cached query data is used.
a timeSpan (created with function CreateTimeSpan): If original query date falls within the time span, cached query data is used.
To use cached data, the current query must use the same SQL statement, data source, query name, maxrows, user name, and password. 
cacheid string No Attribute not supported 
cachename string No This Attribute is deprecated 
columnkey string No When return type is set to "struct", the value of this column can be used as key in the struct for every record.
If not set then the query must not return more than one record, and the keys of the returned struct are the column names
and the values are the column values for the single record that is returned. 
connectstring string No This Attribute is deprecated 
datasource any No The name of the data source from which this query should retrieve data. 
dbname string No This Attribute is deprecated 
dbserver string No This Attribute is deprecated 
dbtype string No support the following values:
  • query: for doing a query on an existing query object
  • hql: for doing a query on orm 
    		•
    debug boolean No 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. 
    indexname string No  
    lazy boolean No This Attribute is deprecated 
    listener any No listener for the query. the listener can have 3 (optional) functions, "before" "after" and "error" that get triggered before and after executing the query and in case of an exception.
    The functions get all data about the query.
    This attributes overwrites any query listener defined in the application.cfc/cfapplication.
    All the functions can also modify all data, by returning a struct containing the keys to overwrite following the same structure as the input coming in the argument scope.
    the listener can be a component looking like this:
    component {
    function before(cachedAfter, cachedWithin, columnName, datasource, dbType, debug, maxRows, name, ormOptions, username, password, result, returnType, timeout, timezone, unique, sql, args, params, caller){}
    function after(result,meta,cachedAfter, cachedWithin, columnName, datasource, dbType, debug, maxRows, name, ormOptions, username, password, result, returnType, timeout, timezone, unique, sql, args, params, caller){}
    function error(exception,lastExecution,nextExecution,created,id,type,detail,tries,remainingTries,closed,caller,advanced,passed,exception){}}
    or a struct looking like this:
    component {before:function(...){}, after:function(...){}, error:function(...){}} 
    maxrows number No Specifies the maximum number of rows to return in the record set. 
    name string No 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. 
    ormoptions struct No Object parameter for the entity. 
    params any No  
    password string No If specified, password overrides the password value specified in the data source setup. 
    provider string No This Attribute is deprecated 
    providerdsn string No This Attribute is deprecated 
    psq boolean No preserve single quote or not 
    result string No 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. (numeric)
  • ExecutionTime: Execution time for the SQL request. (numeric) 
    		•
    returntype string No one of the following values:
  • query: default for all dbType expect "hql", returns a query object
  • array_of_entity: works only with dbType "hql" and is also the default value for dbType "hql"
  • array: returns an array, where each element is a struct with a query record
  • struct: returns either a struct of structs where the key is specified by the keyColumn attribute
    and each value is a struct with a query record, or a single record if keyColumn is not set, where
    each key is a column name and each value has its corresponding value. 
    		•
    sql string No the SQL query to execute. 
    tags any No tags stored with the cache. 
    timeout any No The maximum number of seconds for the query to execute before returning an error
    indicating that the query has timed-out. Value can be a number (seconds) or a TimeSpan Object. The minimum and maximum allowable values
    vary, depending on the driver. 
    timezone timezone No 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. 
    unique boolean No Specifies if the object parameter is unique, used only for dbType=orm or hql 
    username string No If specified, username overrides the username value specified in the data source setup.