Notice: With the launch of Adobe Cookbooks, this site will no longer be accepting new entries or posting new content. Thanks to everyone who submitted content!

How can I clear the cache of a recordset created with <cfquery>?

Following the post on "How can ColdFusion cache a database query?", it is often necessary to display the results of an updated record instantly after the update transaction completes. If the cachedwithin attribute of <cfquery> is used in the SQL, the updates will not show until after the time span stipulated has expired.

In order to have the results show immediately, you can simply run another sql statement immediately after your update sql has completed.

Take your original query used to obtain your recordset. It contains the cachedwithin attribute with a time set to cache the query for 3 hours:

<cfquery name="myQry" datasource="myDatasource" cachedwithin="#createTimeSpan(0,3,0,0)#">
select foo
from tblfoobar
order by bar desc
</cfquery>

Run your update SQL (the 'name' attribute is optional, but a good practice to include it anyway):

<cfquery name="myInsertQry" datasource="myDatasource">
insert into foobar (foo)
values (thisfoo)
where id = <cfqueryparam value="1" cfsqltype="cf_sql_integer">
</cfquery>

To clear the cache of the 'myQry' query that was generated previously, run another SQL statement immediately after the update and set the cachedwithin attribute to a past time:

<cfquery name="myQry" datasource="myDatasource" cachedwithin="#CreateTimeSpan(0,0,0,-1)#">
select foo
from tblfoobar
order by bar desc
</cfquery>

Here's the full update code that can be used:

<cftransaction>
<cfquery name="myInsertQry" datasource="myDatasource">
insert into foobar (foo)
values (thisfoo)
where id = <cfqueryparam value="1" cfsqltype="cf_sql_integer">
</cfquery>
<cfquery name="myQry" datasource="myDatasource" cachedwithin="#createTimeSpan(0,0,0,-1)#">
select foo
from tblfoobar
order by bar desc
</cfquery>
</cftransaction>

Remember, the 'clear' query must be the exact same syntax as the original query or it will not work. It's also a good practice to wrap your SQL transactions in the <cftransaction> tag. In case some sort of error occurs during the db write, the chance of chance of data corruption is reduced.


This question was written by Brian Moss.
It was last updated on March 9, 2006 at 1:45:34 PM EST.

CFML Referenced

<cfquery>
<cftransaction>

Categories

Caching

Comments

Comment made by Richard Davies on May 3, 2006 at 6:30 PM
I tried putting the second cache clearing query with the past time into an separate file and <cfinclude> it directly after the first query, but this doesn't work. (The cache isn't cleared.)

Can anyone explain to me why?

Comment made by Richard Davies on May 3, 2006 at 7:36 PM
Ok, I finally figured out why my <cfinclude> wasn't clearing my cached query...

They weren't kidding when they said "Remember, the 'clear' query must be the exact same syntax as the original query or it will not work."

It turns out that 'exact same syntax' also includes whitespace! My two queries where identical, but I'd indented the 'clear' query more than the original so CF wasn't matching them up.

So not only the query syntax and attributes must be identical, but any whitespace formatting in the query must also be identical.

Comment made by snekse on September 19, 2007 at 5:10 PM
Another good way to do this is put the query block into an include file and use a variable for the cachedWithin value. This will ensure your code block doesn't change.

e.g. <cfif cacheThisQry> <cfset cacheTime = createTimeSpan(0,3,0,0)> <cfelse> <cfset cacheTime = createTimeSpan(0,0,0,-1)> </cfif> <cfquery name="myQry" datasource="myDatasource" cachedwithin="#cacheTime#"> select foo from tblfoobar order by bar desc </cfquery>