Query a Database with ColdFusion
by Ron Kurtus
When you query a database, you are preforming a search, questioning, or trying to find information in the database. ColdFusion uses a variation of SQL (structured query language) to make the query.
(See SQL for more information on that topic.)
You can protect your database from unauthorized queries by requiring a password, you can speed up queries by creating a cache for the query, and you can limit the number of records returned and set timeouts in your query definition.
Questions you may have include:
- How do you use the cfquery tag?
- How do you protect your query?
- What are some added features available?
This lesson will answer those questions.
You use the CFQUERY tag to query a database on your website server:
<cfquery name="People" datasource="Employees">
- name is the name of your query, in case you have more than one query
- datasource is the name of the database (use "...")
- select refers to the column within the database (use '...')
- from refers to the table within the database (no quotation marks)
Many database configurations require authentication (in the form of a username and password) before you can query the database. You can supply these using the username and password attributes.
Note that the username and password can also be configured against the datasource in the ColdFusion Administrator. Supplying these details in your query overrides the username and password in the ColdFusion Administrator.
Adobe recommends that you use the cfqueryparam tag within every cfquery tag, to help secure your databases from unauthorized users.
<cfquery name="People" datasource="Employees" username="joe" password="XWA">
select 'First_name', 'Last_name'
Added functions to cfquery include cached queries, maxrows, and timeout.
If you have a lot of traffic, you may find that performance of your website/application is affected. If so, you will need to look at ways of improving performance. One effective technique you can use is to cache some of 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. From then on, whenever that query is requested again, ColdFusion will retrieve the results from memory.
For ColdFusion, it's much faster retrieving the results from memory than to perform another query from the database. When you query a database, ColdFusion has to wait whilst the database connection (often on another server) is established, the database is opened, the query is run, and the results are returned to ColdFusion. All this takes time, and it can impact on other ColdFusion requests occurring at the same time.
You can cache a query using the cachedAfter attribute. If the query was last run after the supplied date, cached data is used. Otherwise the query is re-run.
<cfquery datasource="Employees" cachedAfter="November 20, 2006"
You can also cache a query using the cachedWithin attribute in conjunction with the CreateTimeSpan function.
In the following example, if the query's cached data is older than 1 hour, the query is re-run. Otherwise, cached data is used.
<cfquery datasource="Employees" cachedwithin="#CreateTimeSpan(0,1,0,0)#"
You can limit the number of rows to be returned by using the maxrows attribute.
<cfquery datasource="Employees" maxrows="50">
You can set a timeout limit using the timeout attribute. This can be useful in preventing requests running far longer than they should and impacting on the whole application as a result.
The timeout attribute sets the maximum number of seconds that each action of a query is allowed to execute before returning an error.
<cfquery datasource="Employees" timeout="30">
You can use ColdFusion to query a database. It also allows you to protect your database from unauthorized queries by requiring a password. You can speed up queries by creating a cache for the query, set the maximum rows, and set a timeout for the query.
Always do your best
Resources and references
ColdFusion Database Queries (cfquery) - quackit.com
CFQUERY - Adobe Help
(Notice: The School for Champions may earn commissions from book purchases)
Questions and comments
Do you have any questions, comments, or opinions on this subject? If so, send an email with your feedback. I will try to get back to you as soon as possible.
Share this page
Click on a button to bookmark or share this page through Twitter, Facebook, email, or other services:
Students and researchers
The Web address of this page is:
Please include it as a link on your website or as a reference in your report, document, or thesis.
Where are you now?
Query Database with ColdFusion