How to refresh individual query cache in ColdFusion 2016

Question

I found a piece of code that works under the ColdFusion 10 engine, but not under the ColdFusion 2016 (CF12) engine.

I have a CFC that houses cached queries that are grabbed by function calls. Say I have a query I want to cache, but I make a change to the db table that this query uses. I do not see the data in the returned cached query, so I need to refresh the query cache, simple enough. This is how I have my code set up:

<cffunction name="getVariables" access="public" returntype="query">
    <cfargument name="time_span" required="true" default="#this.cacheSpan#" />
    <cfset var qryGetVariables="">

    <!--- IF REFRESH, NEW QRYTIMESPAN --->
    <cfif arguments.time_span eq 0 AND NOT this.bln_refresh>
        <!--- IF time_span 0 but not refresh, reset to original cache span --->
        <cfset arguments.time_span = this.cacheSpan />
    </cfif>
    <cfquery name="qryGetVariables" datasource="#this.dsn#" cachedwithin="#arguments.time_span#">
        select *
          from get_variables
         order by id, value
    </cfquery>
    <cfreturn qryGetVariables>
</cffunction>

I call a function in the same CFC that refreshes this query in the following manner:

this.bln_refresh = true;
<cfinvoke method="getVariables" returnvariable="qryReturn">
   <cfinvokeargument name="time_span" value="0" />
</cfinvoke>
this.bln_refresh = false;

Again, this worked before on ColdFusion 10 but now it does not work on ColdFusion 2016. What do I need to do differently to refresh the cache of this specific query?

Answer 1

Yes, this has changed after CF10. It's considered as bug, but it still not fixed. Once a query result is cached using cachedWithin, it cannot be invalidated using createTimeSpan(0, 0, 0, 0) (equal to 0) or any negative value.

Demo

<!--- cache data for 10 minutes --->
<cfquery cachedWithin="#createTimeSpan(0, 0, 10, 0)#">
    SELECT `foo` FROM `example`;
</cfquery>

| foo
|-----
| ABC

Let's change the data.

UPDATE `example` SET `foo` = 'DEF';

And as expected...

<!--- invalidate cache and fetch new data --->
<cfquery cachedWithin="#createTimeSpan(0, 0, 0, 0)#">
    SELECT `foo` FROM `example`;
</cfquery>

| foo
|-----
| DEF

And now let's cache the most recent data.

<!--- cache new data for 10 minutes --->
<cfquery cachedWithin="#createTimeSpan(0, 0, 10, 0)#">
    SELECT `foo` FROM `example`;
</cfquery>

Result in CF10

| foo
|-----
| DEF

cachedWithin <= 0 invalidated the cached query. Thus the next cachedWithin > 0 stored the new data in cache.

Result in CF2016

| foo
|-----
| ABC

cachedWithin <= 0 simply skipped the cache. The next cachedWithin > 0 fetches from cache. And the cache didn't change.

---

UPDATE

The current workaround for this bug is using the cacheID attribute of <cfquery> and then invalidate using cacheRemove(theCacheID).

Old Answer

You can invalidate the query cache using <cfobjectcache action="CLEAR">, but it invalidates ALL cached queries, which is pretty bad. The other option is caching the query by yourself using cachePut and fetching it using cacheGet.

Here is a hint how to do it. You might have to incorporate your this.cacheSpan logic in here, I'm not really sure what the goal of it is:

<cffunction name="getVariables" access="public" returntype="query">

    <cfargument name="time_span" required="true" default="#this.cacheSpan#">

    <cfset var qryGetVariables = "">

    <cfset var cacheKey = "qryGetVariables"> <!--- make sure the key is unique and only used in this function --->
    <cfset var useCache = ((arguments.time_span lte 0) or this.bln_refresh)>

    <cfif useCache>
        <cfset qryGetVariables = cacheGet(cacheKey)>
    </cfif>

    <cfif not isQuery(qryGetVariables)>

        <cfquery name="qryGetVariables" datasource="#this.dsn#">
            select *
              from get_variables
             order by id, value
        </cfquery>

        <cfset cachePut(cacheKey, qryGetVariables, arguments.time_span)>

    </cfif>

    <cfreturn qryGetVariables>
</cffunction>

Answer 2

Much thanks to @Alex, I came across the following solution that appears to be working on our servers:

<cffunction name="getVariables" access="public" returntype="query">
    <cfargument name="time_span" required="true" default="#this.cacheSpan#" />
    <cfset var qryGetVariables="" />
    <cfset var flt_qryTimeSpan=0>

    <cfif NOT cacheIdExists("qryGetVariablesCache") OR this.bln_refresh>
        <cfquery name="qryGetVariables" datasource="#this.dsn#" cachedwithin="#flt_qryTimeSpan#">
            select *
              from get_variables
             order by id, value
        </cfquery>
        <cfset cacheRemove("qryGetVariablesCache") />
        <cfset cachePut("qryGetVariablesCache",qryGetVariables,this.cacheSpan) />
    <cfelse>
        <cfset qryGetVariables = cacheGet("qryGetVariablesCache") />
    </cfif>
    <cfreturn qryGetVariables>

</cffunction>