How can one get a list of all queries that have run on a page in ColdFusion 9

Question

I would like to add some code to my Application.cfc onRequestEnd function that, if a certain application variable flag is on, will log query sql and execution time to a database table. That part is relatively easy, since ColdFusion returns the sql and execution time as part of the query struct.

However, this site has probably close to 1000 pages, and modifying all of them just isn't realistic. So I'd like to do this completely programmatically in the onRequestEnd function. In order to do that I need to somehow get a list of all queries that have executed on the page and that's where I'm stumped.

How can I get a list of the names of all queries that have executed on the current page? These queries appear in the template's variables scope, but there are a myriad of other variables in there too and I'm not sure how to easily loop through that and determine which is a query.

Any help would be appreciated.

Declare

Answer 1

Since that information is available via the debugging templates, you might take a look at those files for some pointers.

Another thing to consider is encapsulating your queries in a CFC or custom tag and having that deal with the logging (but I suspect that your queries are spread all over the site so that might be a lot of pages to modify - although that speaks to why encapsulating data access is a good idea: it's easier to maintain and enhance for exactly this sort of situation).

The relevant code from the debug templates (modernized a bit), is:

<cfset tempFactory = createObject("java", "coldfusion.server.ServiceFactory") />
<cfset tempCfdebugger = tempFactory.getDebuggingService() />
<cfset qEvents = tempCfdebugger.getDebugger().getData() />

<cfquery dbType="query" name="qdeb">
    SELECT *, (endTime - startTime) AS executionTime
    FROM qEvents WHERE type = 'SqlQuery'
</cfquery>

Passifloraceous

Thanks for the response. I was not aware that the debugging templates were veiwable. Could you tell me where to find them? – Declare

I did manage to look at the debug templates and found the answer there. Unfortunately, like most professional jobs, this one came with a ready-made website for me to work on. So I had to work with what I got. For those with a similar problem, I'm including the solution in the next comment. – Declare

<cftry> <cfobject action="CREATE" type="JAVA" class="coldfusion.server.ServiceFactory" name="tempFactory"> <cfset tempCfdebugger = tempFactory.getDebuggingService()> <cfcatch type="Any"></cfcatch> </cftry> <cfset qEvents = tempCfdebugger.getDebugger().getData()> <cfquery dbType="query" name="qdeb"> SELECT *, (endTime - startTime) AS executionTime FROM qEvents WHERE type = 'SqlQuery' </cfquery> – Declare

"tempCfdebugger.getDebugger()" gives me "undefined". I did this: Debugging & Logging > Debug Output Settings , check the "Enable Request Debugging Output". Still it is undefined. Any other settings that needs to update? – Latty

Adding <cfsetting showDebugOutput ="true"> made it works :) – Latty