List of Topics

SfC Home > Web Design > SQL > ColdFusion >

 

Query Database with ColdFusion

by Ron Kurtus (22 July 2016)

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:

This lesson will answer those questions.



CFQUERY tag

You use the CFQUERY tag to query a database on your website server:

<cfquery datasource="Employees">
select 'First_name'
from Address_list
</cfquery>

where

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.

<cfquery 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

Ron Kurtus' Credentials

Websites

ColdFusion Database Queries (cfquery) - quackit.com

ColdFusion Resources

Books

Top-rated books on ColdFusion


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

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:
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.

Copyright © Restrictions


Where are you now?

School for Champions

ColdFusion topics

Query Database with ColdFusion




ColdFusion topics

Basics

Development methods

Specific applications

Also see



Let's make the world a better place

Be the best that you can be.

Use your knowledge and skills to help others succeed.

Don't be wasteful; protect our environment.

You CAN influence the world.





Live Your Life as a Champion:

Take care of your health

Seek knowledge and gain skills

Do excellent work

Be valuable to others

Have utmost character

Be a Champion!



The School for Champions helps you become the type of person who can be called a Champion.