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" />