Displaying Query Contents In Debug Output

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 responses to “Displaying Query Contents In Debug Output”

  1. stv. Avatar

    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. Carlos Avatar

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

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

  3. Carlos Avatar

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

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

  4. Raymond Camden Avatar
    Raymond Camden

    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. Ben Forta Avatar
    Ben Forta

    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. Jason Nokes Avatar
    Jason Nokes

    Is there a way to display record counts of stored procedure result sets?

  7. stv. Avatar

    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.

  8. Ajas Mohammed Avatar
    Ajas Mohammed

    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

  9. Ben Forta Avatar
    Ben Forta

    Ajas, sure, do the same thing, just use the name(s) of the querie(s) returned from the SP.
    — Ben

  10. Ajas Mohammed Avatar
    Ajas Mohammed

    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>
    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 index="x" from=1 to="#arrayLen(cfdebug_storedproc.result)#">
    Thanks in advance….

  11. Ajas Mohammed Avatar
    Ajas Mohammed

    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.

  12. Ajas Mohammed Avatar
    Ajas Mohammed

    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(">. 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.

  13. Ajas Mohammed Avatar
    Ajas Mohammed

    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??

  14. Brad Melendy Avatar
    Brad Melendy

    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.

  15. html templates Avatar
    html templates

    dreamweaver is great and it helped make 2008 a wonderful year for web design and technology in general.But I hope to see some new excellent designs this coming new year. Check out for some eye popping designs in web page template you should check out an outstanding site to help design the future have a great new year!!!!

Leave a Reply