User Guide Cancel

cfquery

Last updated on May 30, 2024 | Also applies to ColdFusion

ColdFusion

Open app

CFML Reference User Guide
ColdFusion functions
ColdFusion Tags
1. ColdFusion tag summary
2. ColdFusion tags by category
  1. Application framework tags
    1. cfapplication
    2. cfassociate
    3. cferror
    4. cfimport
    5. cfinterface
    6. cflock
    7. cfscript
    8. cfthread
  2. Communications tags
  3. Database manipulation tags
  4. Data output tags
    1. cfchart
    2. cfchartdata
    3. cfchartseries
    4. cfcol
    5. cfcontent
    6. cfdocument
    7. cfdocumentitem
    8. cfdocumentsection
    9. cfflush
    10. cfheader
    11. cflog
    12. cfoutput
    13. cfpresentation
    14. cfpresentationslide
    15. cfpresenter
    16. cfprocessingdirective
    17. cfprint
    18. cfreport
    19. cfreportparam
    20. cfsilent
    21. cftable
  5. Debugging tags
    1. cfdump
    2. cftimer
    3. cftrace
  6. Exception handling tags
    1. cfcatch
    2. cferror
    3. cffinally
    4. cfrethrow
    5. cfthrow
    6. cftry
  7. Extensibility tags
    1. cfchart
    2. cfchartdata
    3. cfchartseries
    4. cfcollection
    5. cfcomponent
    6. cfexecute
    7. cfftp
    8. function
    9. cfindex
    10. cfinterface
    11. cfinvoke
    12. cfinvokeargument
    13. cfobject
    14. cfproperty
    15. cfreport
    16. cfreportparam
    17. cfreturn
    18. cfsearch
    19. cfsharepoint
    20. cfspreadsheet
    21. cfwddx
    22. cfxml
  8. File management tags
    1. cfdirectory
    2. cffile
    3. cffileupload
    4. cfftp
    5. cfzip
    6. cfzipparam
  9. Flow-control tags
    1. cfabort
    2. cfbreak
    3. cfcase
    4. cfcontinue
    5. cfdefaultcase
    6. cfelse
    7. cfelseif
    8. cfexecute
    9. cfexit
    10. cfif
    11. cfinclude
    12. cflocation
    13. cfloop
    14. cfrethrow
    15. cfswitch
    16. cfthrow
    17. cftry
  10. Forms tags
    1. cfapplet
    2. cfcalendar
    3. cffileupload
    4. cfform
    5. cfformgroup
    6. cfformitem
    7. cfgrid
    8. cfgridcolumn
    9. cfgridrow
    10. cfgridupdate
    11. cfinput
    12. cfpdf
    13. cfpdfform
    14. cfpdfformparam
    15. cfpdfparam
    16. cfpdfsubform
    17. cfselect
    18. cfslider
    19. cftextarea
    20. cftree
    21. cftreeitem
  11. Internet Protocol tags
    1. cfajaximport
    2. cfajaxproxy
    3. cfftp
    4. cffeed
    5. cfimap
    6. cfhttp
    7. cfhttpparam
    8. cfldap
    9. cfmail
    10. cfmailparam
    11. cfmailpart
    12. cfpop
    13. cfsprydataset
  12. Page processing tags
    1. cfcache
    2. cfcontent
    3. cfflush
    4. cfheader
    5. cfhtmlhead
    6. cfinclude
    7. cfprocessingdirective
    8. cfsetting
    9. cfsilent
  13. Security tags
  14. Variable manipulation tags
    1. cfcookie
    2. cfdump
    3. cfparam
    4. cfregistry
    5. cfsavecontent
    6. cfschedule
    7. cfset
    8. cfsetting
  15. Other tags
    1. cfimage
    2. cflog
    3. cfregistry
3. Tags a-b
4. Tags c
  1. cfcache
  2. cfcalendar
  3. cfcase
  4. cfcatch
  5. cfchart
  6. cfchartdata
  7. cfchartseries
  8. cfclient
  9. cfclientsettings
  10. cfcol
  11. cfcollection
  12. cfcomponent
  13. cfcontent
  14. cfcontinue
  15. cfcookie
  16. Tags d-e
5. Tags f
6. Tags g-h
  1. cfgraph
  2. cfgraphdata
  3. cfgrid
  4. cfgridcolumn
  5. cfgridrow
  6. cfgridupdate
  7. cfheader
  8. cfhtmlhead
  9. cfhtmltopdf
  10. cfhtmltopdfitem
  11. cfhttp
  12. cfhttpparam
7. Tags i
  1. cfif
  2. cfimage
  3. cfimap
  4. cfimapfilter
  5. cfimpersonate
  6. cfimport
  7. cfinclude
  8. cfindex
  9. cfinput
  10. cfinsert
  11. cfinterface
  12. cfinvoke
  13. cfinvokeargument
8. Tags j-l
9. Tags m-o
10. Tags p-q
  1. cfparam
  2. cfpdf
  3. cfpdfform
  4. cfpdfformparam
  5. cfpdfparam
  6. cfpdfsubform
  7. cfpod
  8. cfpop
  9. cfpresentation
  10. cfpresentationslide
  11. cfpresenter
  12. cfprint
  13. cfprocessingdirective
  14. cfprocparam
  15. cfprocresult
  16. cfprogressbar
  17. cfproperty
  18. cfquery
  19. cfqueryparam
11. Tags r-s
  1. cfregistry
  2. cfreport
  3. cfreportparam
  4. cfrethrow
  5. cfreturn
  6. cfsavecontent
  7. cfschedule
  8. cfscript
  9. cfsearch
  10. cfselect
  11. cfservlet
  12. cfservletparam
  13. cfset
  14. cfsetting
  15. cfsharepoint
  16. cfsilent
  17. cfslider
  18. cfspreadsheet
  19. cfsprydataset
  20. cfstoredproc
  21. cfswitch
12. Tags t
  1. cftable
  2. cftextarea
  3. cftextinput
  4. cfthread
  5. cfthrow
  6. cftimer
  7. cftooltip
  8. cftrace
  9. cftransaction
  10. cftree
  11. cftreeitem
  12. cftry
13. Tags u-z
  1. cfupdate
  2. cfwddx
  3. cfwebsocket
  4. cfwindow
  5. cfxml
  6. cfzip
  7. cfzipparam
CFML Reference
1. Reserved words and variables
2. Ajax JavaScript functions
3. ColdFusion ActionScript functions
4. ColdFusion mobile functions
5. Application.cfc reference
6. Script functions implemented as CFCs
7. ColdFusion Flash Form style reference
8. ColdFusion event gateway reference
9. ColdFusion C++ CFX Reference
10. ColdFusion Java CFX reference
11. WDDX JavaScript Objects
Cloud services

Note:

This tag is unsupported in CFFiddle.

Description

Passes queries or SQL statements to a data source.Adobe recommends that you use the cfqueryparam tag within every cfquery tag, to help secure your databases from unauthorized users.

Syntax

<cfquery  
name = "query name"  
blockFactor = "block size"  
cachedAfter = "date"  
cacheID = "ID"  
cacheRegion = "region"  
cachedWithin = "timespan" 
disableAutoGenKeys="true|false" 
dataSource = "data source name"  
dbtype = "query"  
debug = "yes|no"  
fetchClientInfo = "yes|no"  
maxRows = "number"  
ormoptions = #orm options structure#  
password = "password"  
result = "result name"  
timeout = "seconds"  
username = "user name" 
returnType="array | json/array | query | struct | json/struct">  
</cfquery>

Note:

You can specify this tag's attributes in an attributeCollection attribute whose value is a structure. Specify the structure name in the attributeCollection attribute and use the tag's attribute names as structure keys

History

ColdFusion (2021 release): Added the attribute returnType.

ColdFusion (2018 release): Added the parameter disableAutoGenKeys.

ColdFusion 11: Removed the following attributes:

connectString
dbName
dbServer
provider
providerDSN
sql

In addition, the following dbType attribute values have been removed:

dynamic
ODBC
Oracle73
Oracle80
Sybase11
OLEDB
DB2

ColdFusion 10: Added the following attributes: fetchClientInfo, cacheID, cacheRegion, clientInfo}}

ColdFusion 9.0.1: Introduced support for HQL queries; added the attribute {{ormoptions.

ColdFusion 9: Datasource attribute is optional now.

ColdFusion 8: Added the result variable that specifies the ID of a row.

ColdFusion MX 7:

Added the result attribute for specifying an alternate name for the structure that holds the result variables.
Added result variables for the SQL statement executed ( sql ), the number of records returned ( recordcount ), whether the query was cached (cached), an array of cfqueryparam values ( sqlparameters ), and the list of columns in the returned query ( columnlist ).
ColdFusion MX:
Changed Query of Queries behavior: it now supports a larger subset of standard SQL.
Changed dot notation support: ColdFusion now supports dot notation within a record set name. ColdFusion interprets such a name as a structure.
Deprecated the connectString, dbName, dbServer, provider, providerDSN , and sql attributes, and all values of the dbtype attribute except query. They do not work, and might cause an error, in releases later than ColdFusion 5.
New query object variable: cfquery.ExecutionTime.
No longer supports native drivers. It now uses JDBC (and ODBC-JDBC bridge) for database connectivity.

Attributes

Attribute	Req/Opt	Default	Description
name	Required		Name of query. Used in page to reference query record set. Must begin with a letter. Can include letters, numbers, and underscores.
blockFactor	Optional	1	Maximum rows to get at a time from server. Range: 1 - 100. Might not be supported by some database systems.
cachedAfter	Optional		Date value (for example, April 16, 1999, 4-16-99). If date of original query is after this date, ColdFusion 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, enclose it in quotation marks.
cacheID	Optional		ID to be used to store query result in cache. This ID can be used to either retrieve or remove query from cache
cacheRegion	Optional		Cache region to be used to cache query result. If not specified, by default query is cached in the QUERY region.
cachedWithin	Optional		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.
clientInfo	Optional		Structure containing properties of the client to be set on the database connection.
dataSource	Optional		The Datasource attribute is now optional. If omitted, the query uses the datasourcespecified in the application. If it is not specified in either places, then the error will be thrown.
dbtype	Optional		Results of a query as input. Specify either dbtype or dataSource.ColdFusion supports HQL in cfquery. Therefore, you can specify dbtype="hql" as shown in the following example:<cfquery dbtype="hql" name="artists" ormoptions=#{cachename=""}#>from Artists where firstname=<cfqueryparam value="Aiden"></cfquery>
debug	Optional; value and equals sign may be omitted		· yes, or if omitted: if debugging is enabled, but the Administrator Database Activity option is not enabled, displays SQL submitted to the data source and number of records returned by query. · no: if the Administrator Database Activity option is enabled, suppresses display.
disableAutoGenKeys	Optional	false	Specify true to disable retrieval of autogenerated keys. In previous versions of ColdFusion, you could set this attribute at the datasource lavel. In the 2018 release of ColdFusion, you can set it at the query level.
fetchClientInfo	Optional	no	If set yes, returns a struct with the key-value pair passed by the last query.
maxRows	Optional	-1 (All)	Maximum number of rows to return in record set.
ormoptions	Optional		A struct that takes orm options for executing HQL. Applies only if dbtype is set to hql.
password	Optional		Overrides the password in the data source setup.
result	Optional		Name for the structure in which cfquery returns the result variables. For more information, see Usage.
timeout			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, ColdFusion sets this attribute. For other drivers, see the driver documentation.
username	Optional		Overrides user name in the data source setup.
returnType	Optional		Values are: Array JSON/Array Query (default)

Usage

Use this tag to execute a SQL statement against a ColdFusion data source. Although you can use the cfquery tag to execute any SQL Data Definition Language (DDL) or Data Manipulation Language (DML) statement, you typically use it to execute a SQL SELECT statement.

Note:

To call a stored procedure, use the cfstoredproc tag.

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

Variable name	Description
query_name.currentRow	Current row of query that cfoutput is processing.
query_name.columnList	Comma-separated list of the query columns.
query_name.RecordCount	Number of records (rows) returned from the query.

Variable name

Description

query_name.currentRow

Current row of query that cfoutput is processing.

query_name.columnList

Comma-separated list of the query columns.

query_name.RecordCount

Number of records (rows) returned from the query.

The cfquery tag also returns the following result variables in a structure. You can access these variables with a prefix of the name you specified in the result attribute. For example, if you assign the name myResult to the result attribute, you would retrieve the name of the SQL statement that was executed by accessing #myResult.sql#. The result attribute provides a way for functions or CFCs that are called from multiple pages, possibly at the same time, to avoid overwriting results of one call with another. The result variable of INSERT queries contains a key-value pair that is the automatically generated ID of the inserted row; this is available only for databases that support this feature. If more than one record was inserted, the value can be a list of IDs. The key name is database-specific.

Variable name	Description
result_name.sql	The SQL statement that was executed.
result_name.recordcount	Number of records (rows) returned from the query.
result_name.cached	True if the query was cached; False otherwise.
result_name.sqlparameters	An ordered Array of cfqueryparam values.
result_name.columnList	Comma-separated list of the query columns.
result_name.ExecutionTime	Cumulative time required to process the query.
result_name.IDENTITYCOL	SQL Server only. The ID of an inserted row.
result_name.ROWID	Oracle only. The ID of an inserted row. This is not the primary key of the row, although you can retrieve rows based on this ID.
result_name.SYB_IDENTITY	Sybase only. The ID of an inserted row.
result_name.SERIAL_COL	Informix only. The ID of an inserted row.
result_name.GENERATED_KEY	MySQL only. The ID of an inserted row. MySQL 3 does not support this feature.
result_name.GENERATEDKEY	Supports all databases. The ID of an inserted row.

You can cache query results and execute stored procedures. For information about this and about displaying cfquery output, see the Developing ColdFusion Applications. Because the timeout attribute only affects the maximum time for each suboperation of a query, the cumulative time may exceed its value. To set a timeout for a page that might get a very large result set, set the Administrator > Server Settings > Timeout Requests option to an appropriate value or use the RequestTimeout attribute of the cfsetting tag (for example, <cfsetting requestTimeout="300">).
The Caching page of the ColdFusion Administrator specifies the maximum number of cached queries. Setting this value to 0 disables query caching.
You cannot use ColdFusion reserved words as query names. You cannot use SQL reserved words as variable or column names in a Query of Queries, unless they are escaped. The escape character is the bracket []; for example:

SELECT [count] FROM MYTABLE.

For a list of reserved keywords in ColdFusion, see Escaping reserved keywords in Query of Queries user guide in the Developing ColdFusion Applications.

Example 1: Getting started

<!--- Get employee names from database --->
<cfquery name="getEmployees" datasource="cfdocexamples">
       SELECT FIRSTNAME,LASTNAME
       FROM EMPLOYEES
</cfquery>

<!--- Create HTML page --->
<HTML>
       <head>
             <title>
                    List of Employees
             </title>
       </head>
       <body>
             <h1>
                    Employee List
             </h1>
             <!--- List out employees--->
             <cfoutput query="getEmployees">
                    #FIRSTNAME# #LASTNAME# <br/>
             </cfoutput>
       </body>
</HTML>

Example 2: Using tables

<!--- Get employee names from database --->
<cfquery name="getEmployees" datasource="cfdocexamples">
 SELECT FIRSTNAME,LASTNAME,EMAIL,PHONE
 FROM EMPLOYEES
</cfquery>

<!--- Create HTML page --->
<html>
 <head>
  <title>
   Table of Employees
  </title>
 </head>
 <body>
  <table border="1">
   <!--- Loop through the database --->
   <cfoutput query="getEmployees">
    <tr>
     <td>
      #FIRSTNAME# #LASTNAME#
     </td>
     <td>
      #EMAIL#
     </td>
     <td>
      #PHONE#
     </td>
    </tr>
   </cfoutput>
  </table>
 </body>
</html>

Example 3: Using the result attribute

<!--- Get employee names from database --->
<cfquery name="getEmployees" datasource="cfdocexamples" result="result">
 SELECT FIRSTNAME,LASTNAME,EMAIL,PHONE
 FROM EMPLOYEES
</cfquery>
<cfdump var="#result#" >
<cfoutput>
 <!--- Display count of records --->
 #result.RecordCount#
</cfoutput>

Output

Example 4: Using the password attribute

Many database configurations require authentication (in the form of a username and password) before you can query the database. You can add authentication using the username and password attributes.

Note that You can also configure the user name and password in the datasource in the ColdFusion Administrator. Specifying the authentication details in your query overrides the username and password in the ColdFusion Administrator.

<cfquery datasource="cfdocexamples" username="myusername" password="mypassword">
  select *
  from Employees
</cfquery>

Example 5: Limiting the number of records

You can limit the number of rows to be returned by using the maxrows attribute.

<cfquery name="getEmployees" datasource="cfdocexamples" result="result" maxrows="10">
 SELECT FIRSTNAME,LASTNAME,EMAIL,PHONE
 FROM EMPLOYEES
</cfquery>
<cfdump var="#result#" >
<cfoutput>
 <!--- Display count of records --->
 #result.RecordCount#
</cfoutput>

Example 6: Using the timeout attribute

You can set a timeout limit using the timeout attribute. A timeout is useful in preventing requests that are running for more than the limit set.

The timeout attribute sets the maximum number of seconds that each action of a query is allowed to execute before returning an error.

<cfquery name="getEmployees" datasource="cfdocexamples" timeout="20" >
 SELECT FIRSTNAME,LASTNAME,EMAIL,PHONE
 FROM EMPLOYEES
</cfquery>
<cfdump var="#getEmployees#" >

Example 7: Cached queries

The volume of traffic to your web site or applications impacts the performance of your site or application. To enhance the performance, you can cache your database queries.

A cached query is a query that has its results stored in the server's memory. The results are stored when the query is first run. Whenever you run the query thereafter, ColdFusion retrieves the results from memory.

You can cache a query using the cachedAfter attribute.

<cfquery name="getEmployees" datasource="cfdocexamples" cachedafter="10-15-2018" >
 SELECT FIRSTNAME,LASTNAME,EMAIL,PHONE
 FROM EMPLOYEES
</cfquery>
<cfdump var="#getEmployees#" >

You can also cache a query using the cachedWithin attribute with the CreateTimeSpan function.

In the example below, if the query's cached data is older than 60 seconds, the query is re-run. Otherwise, the cached data is used.

<cfquery name="getEmployees" datasource="cfdocexamples" cachedwithin="#CreateTimespan(0,0,60,0)#" >
 SELECT FIRSTNAME,LASTNAME,EMAIL,PHONE
 FROM EMPLOYEES
</cfquery>
<cfdump var="#getEmployees#" >

Example 8- Return type is array

<cfquery name="getEmployees" datasource="cfArtGallery" result="myquery" RETURNTYPE = "array"> 
        select ARTID,ARTISTID,ARTNAME  from art where ARTID < 2 
</cfquery> 
<cfoutput>#getEmployees[1].ARTID# #getEmployees[1].ARTISTID# #getEmployees[1].ARTNAME#</cfoutput>

Example 9- Return type is json/array

<cfquery name="getEmployees" datasource="cfArtGallery" result="myquery" RETURNTYPE = "json/array"> 
        select ARTID,ARTISTID,ARTNAME from art where ARTID < 2 
</cfquery> 
<cfset record=deserializeJSON(getEmployees)> 
<cfoutput>#record[1].ARTID# #record[1].ARTISTID# #record[1].ARTNAME# #getEmployees#</cfoutput>

Example 10- Return type is default query

<cfquery name="getEmployees" datasource="cfArtGallery" result="myquery"> 
        select ARTID,ARTISTID,ARTNAME from art where ARTID < 2 
</cfquery> 
<cfset record=QueryGetRow(getEmployees,1)> 
<cfoutput>#record.ARTID# #record.ARTISTID# #record.ARTNAME#</cfoutput>

Adobe

Get help faster and easier

New user?

cfquery

Description

Category

Syntax

See also

History

Attributes

Usage

Example 1: Getting started

Example 2: Using tables

Example 3: Using the result attribute

Example 4: Using the password attribute

Example 5: Limiting the number of records

Example 6: Using the timeout attribute

Example 7: Cached queries

Example 8- Return type is array

Example 9- Return type is json/array

Example 10- Return type is default query

Get help faster and easier

Ask the Community

Contact Us