<cfquery>

edit

Passes SQL statements to a data source.

Not limited to queries.

This tag may have a body.

This tag is also supported within <cfscript>

<cfquery name=string columnKey=string result=string dataSource=any dbType=string dbServer=string dbName=string tags=any connectString=string username=string password=string maxRows=number blockFactor=number timeout=any cachedAfter=datetime cachedWithin=object provider=string providerDSN=string debug=boolean cacheName=string psq=boolean unique=boolean ormOptions=struct indexName=string returnType=string timezone=timezone lazy=boolean params=object sql=string listener=any async=boolean cacheId=string ><!--- body --->[</cfquery>]
Attribute Description
name
string, optional 		edit

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.

Alias: variable
columnKey
string, optional 		edit

Specifies the column to use as primary key when returnType is set to struct.

Alias: columnname, column, keycolumn
result
string, optional 		edit

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)
dataSource
any, optional 		edit

The name of the data source from which this query should retrieve data.
dbType
string, optional 		edit

The following values are supported

  • query: for doing a query on an existing query object (QoQ / Query of Queries)
  • hql: for doing a query on orm
tags
any, optional 		edit

tags stored with the cache.

Alias: tag
username
string, optional 		edit

If specified, username overrides the username value specified in the data source setup.
password
string, optional 		edit

If specified, password overrides the password value specified in the data source setup.
maxRows
number, optional 		edit

Specifies the maximum number of rows to return in the record set.
blockFactor
number, optional 		edit

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.
timeout
any, optional 		edit

The maximum number of seconds for the query to execute before returning an error indicating that the query has timed-out. This attribute is not supported by most ODBC drivers. timeout is supported by the SQL Server 6.x or above driver. The minimum and maximum allowable values vary, depending on the driver.
cachedAfter
datetime, optional 		edit

This is the age of which the query data can be
cachedWithin
object, optional 		edit

Supported 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, params, data source, query name, user name, and password.
debug
boolean, optional 		edit

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.
psq
boolean, optional 		edit

preserve single quote or not
unique
boolean, optional 		edit

Specifies if the object parameter is unique, used only for dbtype=orm or hql
ormOptions
struct, optional 		edit

Object parameter for the entity.
indexName
string, optional 		edit

Name of the column to index, see QueryRowByIndex(), QueryGetCellByIndex() and QueryRowDataByIndex()
returnType
string, optional 		edit

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: converts the query object into an array of structs
  • 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
timezone
timezone, optional 		edit

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.
params
object, optional 		edit

Alternative to using nested cfqueryparam tags. Supports two parameter styles:

  • Positional parameters (Array): Use question marks (?) as placeholders in SQL.

SQL example:

    "SELECT * FROM users WHERE status = ? AND id > ? LIMIT ?"
    Params: [
    { type: "string", value: "active" },
    { type: "integer", value: 42 },
    { type: "integer", value: 100 }
    ]
  • Named parameters (Struct): Use colon-prefixed names as placeholders in SQL.

SQL example:

    "SELECT * FROM users WHERE status = :status AND id > :id LIMIT :limit"
    Params: {
    "status": { type: "string", value: "active" },
    "id": { type: "integer", value: 42 },
    "limit": { type: "integer", value: 100 }
    }
  • List parameters (for IN clauses): Set list=true to expand arrays into comma-separated values.

SQL example:

    "SELECT * FROM users WHERE status IN (?)"
    Params: [
    { type: "string", value: ["active", "pending"], list: true }
    ]

(The same can be done with named parameters)
sql
string, optional 		edit

the SQL query to execute.
listener
any, optional 		edit

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(...){}}

async
boolean, optional 		edit

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.

Unimplemented Attribute(s)

Attribute Description
dbServer
string, optional 		edit

This attribute has been deprecated and is non-functional.

* deprecated *
dbName
string, optional 		edit

This attribute has been deprecated and is non-functional.

* deprecated *
connectString
string, optional 		edit

This attribute has been deprecated and is non-functional.

* deprecated *
provider
string, optional 		edit

This attribute has been deprecated and is non-functional.

* deprecated *
providerDSN
string, optional 		edit

This attribute has been deprecated and is non-functional.

* deprecated *
cacheName
string, optional 		edit

This attribute has been deprecated and is non-functional.

* deprecated *
lazy
boolean, optional 		edit

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

* deprecated *
cacheId
string, optional 		edit

Attribute not supported

* unimplemented *

Examples

edit

Tags

<cfset qry= queryNew("name,age,whatever", "varchar,date,int", [
[ "Susi", CreateDate( 1970, 1, 1 ), 5 ],
[ "Urs" , CreateDate( 1995, 1, 1 ), 7 ],
[ "Fred", CreateDate( 1960, 1, 1 ), 9 ],
[ "Jim" , CreateDate( 1988, 1, 1 ), 11 ]
])>
<!-- bad example, not using a bound parameter, unsafe when using input from users -->
<cfquery name="q" dbtype="query">
	select * from qry where name = 'jim'
</cfquery>
<cfdump var="#q#" />

<!-- using a bound parameter with cfqueryparam -->
<cfquery name="q" dbtype="query">
	select * from qry where name = <cfqueryparam value='jim'>
</cfquery>
<cfdump var="#q#" />

<!-- using an array of simple params -->
<cfscript>
    p = [ 'jim' ];
</cfscript>
<cfquery name="q" dbtype="query" params=#p#>
	select * from qry where name = ?
</cfquery>
<cfdump var="#q#" />

<!-- using an array of struct params -->
<cfscript>
    p = [ { value='jim', sqltype='varchar' } ];
</cfscript>
<cfquery name="q" dbtype="query" params=#p#>
	select * from qry where name = ?
</cfquery>
<cfdump var="#q#" />

<!-- using an array of named struct params -->
<cfscript>
    p = [id= { value='jim', sqltype='varchar' } ];
</cfscript>
<cfquery name="q" dbtype="query" params=#p#>
	select * from qry where name = :id
</cfquery>
<cfdump var="#q#" />

Script

  qry= queryNew("name,age,whatever", "varchar,date,int", [
    [ "Susi", CreateDate( 1970, 1, 1 ), 5 ],
    [ "Urs" , CreateDate( 1995, 1, 1 ), 7 ],
    [ "Fred", CreateDate( 1960, 1, 1 ), 9 ],
    [ "Jim" , CreateDate( 1988, 1, 1 ), 11 ]
    ])

	query name="q" dbtype="query" {
	echo("select * from qry where name = 'jim'");
	}
	writedump(q);

	query name="q" dbtype="query" {
	echo("select * from qry where name = ")
	queryParam cfsqltype="cf_sql_varchar" value="jim";
	}
	writedump(q);

	p = [ 'jim' ];
	query name="q" dbtype="query" params=#p#{
	echo("select * from qry where name = ?");
	}
	writedump(q);

 	p = [id= { value='jim', sqltype='varchar' } ];
	query name="q" dbtype="query" params=#p#{
	echo("select * from qry where name = :id");
	}
	writedump(q);

Related System Properties / Environment Variables

  • LUCEE_CASCADE_TO_RESULTSET - When a variable has no scope defined (example: `#myVar#` instead of `#variables.myVar#`), Lucee will also search available resultsets (CFML Standard) or not
    Type: boolean, Default: true
  • LUCEE_DATASOURCE_MSSQL_MODERN - Boolean value to enable modern MSSQL datasource handling
    Type: boolean, Default: false
  • LUCEE_DATASOURCE_POOL_VALIDATE - If enabled, Lucee will validate existing datasource connections reused from the datasource pool before using them. This protects from exceptions caused by connections dropped by the DB server but creates additional communication between Lucee and the DB server. Removed in 6.2
    Type: boolean, Deprecated: 6.2
  • LUCEE_QOQ_HSQLDB_DEBUG - Boolean value to enable debug logging for HSQLDB Query of Queries operations
    Type: boolean, Default: false
  • LUCEE_QOQ_HSQLDB_DISABLE - Boolean value to disable HSQLDB for Query of Queries, forcing use of alternative QoQ engine
    Type: boolean, Default: false
  • LUCEE_QOQ_PARALLELISM - Controls the parallelism level for Query of Queries operations
    Type: numeric, Default: true
  • LUCEE_QUERY_ALLOWEMPTYASNULL - In Lucee 5, an empty string passed into a query parameter with a numeric type was interpreted as null. In Lucee 6, this is no longer accepted and throws an exception. You can simulate the old behavior by setting this environment variable or SysProp to `true`. By setting the log level of the datasource log to `warn`, you will receive information in the log when the old behavior is used. This allows you to modify your code for the new behavior without encountering runtime issues with the existing code
    Type: boolean, Default: false
  • LUCEE_QUERY_RESULT_THRESHOLD - Enables automatic logging of database queries that return large result sets to help proactively identify potential OutOfMemory (OOM) issues. When set to a positive integer, Lucee will log a warning message to the datasource log category whenever a query returns a number of rows greater than or equal to the specified threshold. Set to 0 or leave unset to disable this feature (default behavior). Logs include execution time, row count, column count, threshold value, SQL query, and tag context. Helps identify problematic queries before they cause memory issues in production environments
    Type: numeric, Default: false, Introduced: 6.2.3.15
  • LUCEE_TAG_POPULATE_LOCALSCOPE - Controls whether tags like cflock and cfquery populate their default result variables to local scope when inside a function. When `true`, variables go to local scope. When `false`, restores pre LDEV-5416 behavior where variables go to variables scope
    Type: boolean, Default: true, Introduced: 7.0.1.13

See also