How can I automate cached queries to update at an exact time each day?
While ColdFusion gives you the ability to choose how long query data is cached, and even couple of options to clear a cached query by hand, you may still find a situation that requires more precise control over your cached query updates.
For example, say you have a query that generates a list of the newest recipes submitted to your recipe site. Because this query is used so often, you choose to cache it. However, you would like to update the cached query at exactly noon each day to reflect a daily cutoff for new recipe entries. How could you do this?
The solution lies in using the scheduling engine of your choice (ColdFusion server has one built in) to run a ColdFusion template that will refresh your cached query.
If you were using the following cached query in your pages:
<cfquery
name="qWithinTest"
datasource="myDs"
cachedWithin="#createTimeSpan(1, 0, 0, 0)#">
select name
from recipes
</cfquery>
You could create a template that flushes the query cache using the following code:
<cfquery
name="qWithinTest"
datasource="myDs"
cachedwithin="#createTimeSpan(0, 0, 0, -1)#">
select name
from recipes
</cfquery>
It is then a simple matter of using your scheduling engine to run this query flush template at noon each day.
This question was written by Jeremy Petersen
It was last updated on March 15, 2006.