SfC Home > Web Design > SQL > ColdFusion >
Query a Database with ColdFusion
by Ron Kurtus (updated 27 May 2023)
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.
CFQUERY tag
You use the CFQUERY tag to query a database on your website server:
<cfquery name="People" datasource="Employees">
select 'First_name'
from Address_list
</cfquery>
where
- 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)
Using Password
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'
from Address_list
</cfquery>
Added functions
Added functions to cfquery include cached queries, maxrows, and timeout.
Cached queries
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"
select 'First_name'
from Address_list
</cfquery>
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)#"
select 'First_name'
from Address_list
</cfquery>
MaxRows
You can limit the number of rows to be returned by using the maxrows attribute.
<cfquery datasource="Employees" maxrows="50">
select 'First_name'
from Address_list
</cfquery>
Timeout
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">
select 'First_name'
from Address_list
</cfquery>
Summary
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
Websites
ColdFusion Database Queries (cfquery) - quackit.com
CFQUERY - Adobe Help
Books
(Notice: The School for Champions may earn commissions from book purchases)
Students and researchers
The Web address of this page is:
www.school-for-champions.com/coldfusion/
query_database.htm
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