ColdFusion Database Queries (cfquery)
In the previous lesson we created a datasource so that it could be used by ColdFusion to connect to a database.
Now that we have the datasource, we can perform a query against the database that the datasource is linking to.
To perform a database query in ColdFusion, you use the cfquery tag.
Basic CFQUERY Example
This code is all you need to do in order to query a database in ColdFusion. The cfquery tag connects to and opens the database for you, all you need to do is supply it with the name of the datasource.
<cfquery datasource="Entertainment"> select * from Movies </cfquery>
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="Entertainment" username="webuser" password="letmein"> select * from Movies </cfquery>
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="Entertainment" cachedAfter="November 20, 2006"> select * from Movies </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="Entertainment" cachedwithin="#CreateTimeSpan(0,1,0,0)#"> select * from Movies </cfquery>
Limiting the Number of Records Returned
You can limit the number of rows to be returned by using the maxrows attribute.
<cfquery datasource="Entertainment" maxrows="50"> select * from Movies </cfquery>
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="Entertainment" timeout="30"> select * from Movies </cfquery>