CFCoding

In one of my assignments, I cam across a scenario where I needed to determine the time a query was cached to give the user an accurate time in which the updated data will be available. I knew the query was caches for 12 hours as the previous developer created a timespan: createTimeSpan(0, 12, 0, 0). That was helpful in knowing how long it would be cached for, but I needed to know when it would re-cache.

Through some thinking, I decided to inject the date into the query itself, and I would be able to determine the re-cache date from that like so:

With this method, I could always reference qryMaterials.CachedOn to determine when the query was cached, and use dateAdd() to figure out that date 12 hours from now. While I believe that was the most effective way to handle this in my situation, I did have to search into it further because it was bothering me that I couldn't get the date from CF. I decided to look around at the CF factories to see if anything there would be helpful... and what do you know! The Java class coldfusion.server.ServiceFactory contained a reference to the coldfusion.server.DataSourceService. From within this service, I was able to access all of the cached queries and get more than just the cache date.

Rather than explain explain the rest with pretty screenshots, I'll let you do the work and see exactly what's available for you. Here is a quick snippet I wrote that displays all of the queries, their names, the cached date, and a link to re-run then query manually. You can also get the ResultSet, change the ResultSet, and a few more methods that I won't discuss here. Hopefully this will be as beneficial to you as it has me.