Someone just e-mailed me to ask if there was a way to see a dump of all query contents when viewing ColdFusion debug output. And, actually, there is. Here’s the scoop.
The ColdFusion debug output that we are all so familiar with is actually generated by a .cfm page. If you look in coldfusionroot/wwwroot/WEB-INF/debug you’ll see several .cfm files. classic.cfm is the debug output that you usually see, dockable.cfm is the docked view debug output, and dreamweaver.cfm is used to generate debug output that Dreamweaver displays. And these files may be modified (well, don’t mess with the Dreamweaver one), and you can create your own ones, too.
Actually, if you are going to tinker with these, then please make a copy and edit it. Then save it in the same folder, and you’ll then be able to select it as the active debug template in CF Admin.
If you look at these files (and yes you can, they are not encoded) you will see that a big query named qEvents is retrieved from ColdFusion, this is all of the debugging data retrieved from the debugging service’s event table. The rest of the templates simply process this data to display the debug output.
To add the query contents dump, find the section that begins with the comment (it’ll be at about line 550 or so in CFMX7). That block of code starts with a statement that checks to see if any queries were processed. If yes, it loops through each query displaying the SQL, number of rows returned, and more. There are two nested loops in this section, the outer loop loops through queries, and the inner loop loops query attributes for each query. Locate the for the outer loop, and add this code before it (after the and before the :


Then save your new debug template, and activate it in CF Admin. Now the complete result set for any executed query will be included in the debug output.

15 thoughts

  1. Hey – not so much a comment as a question: Is there any way to "replicate" the CFMX 7 (or recreate some of it) debugging info if debugging has been turned off?
    I’m trying to track down a problem that’s only occuring on a live server in a shared hosting environment (it’s not present in our internal production/staging servers), and having the debugging info – but they won’t turn on debugging for me.
    The piece that I need are the how long a template is taking, and if I’m right, to see how long the queries are taking.

  2. Maybe you can use the new CFMX7 features of CFQUERY, with CFLOG.
    cfquery name="qryMyQuery" result="resultMyQuery"

    /cfquery
    cfif Application.debug
    cfsavecontent var="debugInfo"
    qryMyQuery: (Records=#qryMyQuery.RecordCount#, Time=#qryMyQuery.ExecutionTime#)
    SQL = #resultMyQuery.sql#
    /cfsavecontent
    cflog file="cfquery.log" text="#debugInfo#" /
    /cfif

  3. Take 2…
    <cfquery name="qryMyQuery" result="resultMyQuery">

    </cfquery>
    <cfif Application.debug>
    <cfsavecontent var="debugInfo">
    qryMyQuery: (Records=#qryMyQuery.RecordCount#, Time=#qryMyQuery.ExecutionTime#)
    SQL = #resultMyQuery.sql#
    </cfsavecontent>
    <cflog file="cfquery.log" text="#debugInfo#" />
    </cfif>

  4. I’ve played in here myself, and am working on a project that makes use of it. (Based on something I saw at cfunited.) One thing that bugs me though – the template makes use of "internal" functions, so you have to ‘cheat’ to make your own template. Not the end ofthe world, but…

  5. Stv, no if debugging is not turned on then that data is not collected.
    Carlos, yep, result can indeed be used to track some of the extra info.

  6. Hey – so I used the query.executiontime in a dump of all querues and that helped me track down the problem – so thanks for pointing me in the right direction.

  7. Hi Mr. Forta, What if I want to display the results of the stored procedure by using dump… Can we do that and if yes that will be great since running stored proc in query analyzer by copying the values sent from page is cumbersome

  8. Sorry to say, but its not working for me. Can you correct me Please….Heres what I did, I found the code where stored proce details are shown on debugging screen by classic.cfm . The line number should be like 580 & code would be like this
    <!— Stored Procs —>
    <cfif bFoundStoredProc>
    <cftry>
    Just after </table> & before the closing </cfloop> of outer loop <cfloop query="cfdebug_storedproc">, I added
    <cfdump var="#cfdebug_storedproc.result#"> . Now the debugging screen is showing resultset box twice. Ofcourse the second result box is diff color. What I need is that it should show the result set i.e all columns & there values returned by stored procedure like how we run SP in Query Analyzer by exec spname 1,2,3 and it gives all the results returned…
    By the way there are two cfloops inside the outer loop i.e.
    <cfloop index="x" from=1 to="#arrayLen(cfdebug_storedproc.attributes)#">
    </cfloop>
    <cfloop index="x" from=1 to="#arrayLen(cfdebug_storedproc.result)#">
    </cfloop>
    Thanks in advance….
    Ajas.

  9. has anyone tried doing the same stuff for Stored procs? I didnt get it to work and I am not sure what I am doing wrong… Can anyone please clarify?? I want the results of Stored Proc to be displayed. Right now default way is to show the result set only.
    Thanks

  10. Hello Mr. Ben Forta, After 2 weeks of testing various options/variables/parameters, I finally found success. Yes thats right I found a way to show the RESULTS OF A STORED PROCEDURE… WHOO HOO…. All that needs to be done is <cfdump var = "#Evaluate(ThisParam.name)#">. I worked so hard for this one… I asked so many ppl , big guns but nobody had an answer…. but I couldnt live without this u know… i wanted it so badly and atlast my patience paid off… Very good lesson for me … Where theres a Will, Theres a Way… What do you think about find??? Do you like it?? I will email you later a personal message.
    Ajas Mohammed.

  11. Next thing would be, to see all the templates in a page… for example a.cfm includes b.cfm c.cfm d.cfm… then Debugging screen should show what all pages were included or called during the life of a.cfm… I dont think the current debugging does that….
    any suggestions on this one??

  12. I’ve recently been trying to capture the data in classic.cfm into an error email I generate from a <cferror> tag. The only problem I have run into is that if I include the following line:
    <cfset qEvents = cfdebugger.getDebugger().getData()>
    … which is needed to populate qEvents with all the really good debug data, you must have debugging turned on and additionally, any IP addresses specified to get debug data on the screen will trip <cferror> but any IP address NOT on the list of Debug IPs will NOT trip <cferror> and they will get the CF Error Dump. It’s all very strange and I’m wondering if anyone has run into this and worked around it?
    If I remove all Debug IPs from CFAdmin, I get an nice HTML formatted email with all the classic.cfm debug info.

Leave a Reply to Raymond Camden Cancel reply