So, I ran into a problem with one of my current applications for a client. Every quarter they like to lock their records from being edited once they have been submitted. In the previous version I had to run that event myself with a basic MySQL query – I didn’t like being part of their business practice/model, so on the current version I wrote a nice little script where all they have to do is to push a button and voila their records are locked. They have a complex report that provides them with information they need plus gives them a filter of locked and un-locked records. My problem that come about after they ran the lock they would click straight over to the report that show them all the “locked” records. Sounds pretty straight forward till you realize that the query used for this report has been cached to save some time and resources – about a 15min cache – to their surprise nothing showed up as locked. Waiting 15min is not a option so I had to find a way to clear all the cached queries that this application was using.
Below is a simple example of what I am talking about:
<cfquery name="getRecords" datasource="#variables.dsn#" cachedwithin="#createTimeSpan(0,0,15,0)#"> SELECT recordid, title, contacts FROM reports WHERE datelocked = '2012-12-21' AND datesubmitted IS NOT NULL </cfquery>
As you can see the query will be cached for 15 min. But if you change the details of any record within the 15 minute span the query will not display those changes. The updates will show up if you restart your ColdFusion server or you force the existing query cache to refresh by placing the same query right after your update or insert command
<cfquery datasource="#variables.dsn#"> UPDATE reports SET datelocked = NOW() WHERE datesubmitted IS NOT NULL </cfquery> <cfquery name="getRecords" datasource="#variables.dsn#" cachedwithin="#createTimeSpan(0,0,0,-1)#"> SELECT recordid, title, contacts FROM reports WHERE datelocked = '2012-12-21' AND datesubmitted IS NOT NULL </cfquery>
Please don’t use this method – The issue with this method is that your SQL statements must be exactly the same and used after every update or insert command you have for the table(s) in question. The examples I’ve used above are not real world examples. You know what your queries can look like and how extensive they can be, especially with a few INNER JOINs and LEFT JOINs.
There is a CFObjectCache tag which will clear every cached query in the Application scope (Please provide your applications with unique names).
<cfobjectcache action="clear" />