How can I clear the cache of a recordset created with <cfquery>?
Problem
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.
Solution
In order to have the results show immediately, you can simply run another SQL statement immediately after your update SQL has completed.
Detailed explanation
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 post was originally contributed by Brian Moss
+
This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License. Permissions beyond the scope of this license, pertaining to the examples of code included within this work are available at Adobe.
