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.