CFQUERY

Ability to execute SQL statements against databases, query's or Amazon

Usage

<cfquery> ... </cfquery>

Attributes

Attribute default required summary
DATASOURCE The datasource to which the database is tied to. If not available, defaults to the 'this.datasource' from the Application.cfc
DBTYPE= The type of database; default is the SQL, so the DATASOURCE points to a SQL server
DBTYPE=QUERY Querying one or more result sets; the DATASOURCE should be blank
DBTYPE=AMAZON Querying Amazon SimpleDB; the DATASOURCE is a previously setup Amazon datasource
USERNAME The database USERNAME if using an SQL database
PASSWORD The database PASSWORD if using an SQL database
BACKGROUND If set, will throw this query to the background spooling agent for running later
NAME The name of the variable that will receive the query result
PRESERVESINGLEQUOTES false A flag to preserve the quotes within the CFQUERY body
MAXROWS The maximum number of rows to return; default is to return them all
RESULT The variable that will hold the RESULT variable
DEBUG false Flag to control whether this query is included in the debugging output
CACHEDWITHIN The time span to which this query result will be cached for before it is reexecuted. Use CreateTimeSpan() to get a unit of a day to manage
CACHEDAFTER The time when the cache will be expired
CACHEID The name of the cache you have given this query. If omitted, then the id will be calculated from the SQL statement plus any arguments passed in
REGION CFQUERY The cache region to use

Extra

Query Object

This tag creates a query object, providing this information in query variables:

Object variables Description
currentRow Current row of query.
columnList Comma-separated list of the query columns.
recordcount Number of records (rows) returned from the query.

Query of Query

A Query of Query (QoQ) can be done with adding "query" to the dbtype:

<cfquery name="qcf" dbtype="query">
SELECT *
FROM {previousqueryresult}
</cfquery>

Note: There are some limitations to a QoQ as no subselects are allowed.

Using cachename and cachedomain to improve performance

You can improve your queries manifold by using the internal cache system or memcache. Here is an example how to cache your queries with the internal cache.

<cfquery name="qcf" datasource="mysql" cachename="myquery">
SELECT id, columm
FROM mytable
WHERE id = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#id#">
</cfquery>

Now all queries that will use this table will not hit the database anymore, but will use the cache. You can see that the cache is being used by dumping the query result.

If you want to group your cache you can do so by adding cachedomain to the query above. This is useful for flushing the cache in one go. Say you have different caches for one table with the cachedomain you can flush all the caches for this one table.

<cfquery name="qcf" datasource="mysql" cachename="myquery" cachedomain="mytable">
SELECT id, columm
FROM mytable
WHERE id = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#id#">
</cfquery>

In order to flush the cache you simply issue a cfquery tag again as in:

<cfquery datasource="mysql" action="flushall" cachedomain="mytable" />

or for flushing all caches

<cfquery datasource="mysql" action="flushall" />