11 Replies Latest reply on May 4, 2010 5:27 AM by Dan Bracuk

    Rescue Query name

    SteelDust

      Dear all,

       

      I need to rescue the names of every Query in my page dynamically.

      I'd like to use it in every my page to check the Query ExecutionTime.

      How can I rescue the query name?

       

      thanks in advance

       

      Best regards

        • 1. Re: Rescue Query name
          Adam Cameron. Level 5

          Switch debugging on?

           

          --

          Adam

          • 2. Re: Rescue Query name
            SteelDust Level 1

            Yes, but... I'd like to:


            1) retrive the name's query;
            2) loop the queries;
            3) check the ExecutionTime for each query;
            4) if ExecutionTime > 3000 send an e-mail to developers

             

            like this:

             

            <cfset qNames = I don't know...>

            <cfset qList = "">

             

            <cfloop index="i" list="#qNames#" delimiters=",">

                

                 <cfif  evaluate(i & ".getMetaData().getExtendedMetaData().executionTime") GT 3000>

                      <cfset ListAppend(qList, i)>

                 </cfif>

             

            </cfloop>

            <cfif ListLen(qList)>

                 ... send an email...

            </cfif>

             

            could you help me, please?

             

            thanks in advance

             

            best regards

            • 3. Re: Rescue Query name
              Adam Cameron. Level 5

              Have a look at the debugging template.  It's in theWEB-INF/debug dir.

               

              --

              Adam

              • 4. Re: Rescue Query name
                Owain North Level 4

                You could always append some code to the bottom of the page to loop the Variables scope, then use isQuery() to establish which of those variables are query resultsets.

                 

                However you obviously won't catch anything that's out of scope, so as Adam says I'd look into debugging as that's exactly what you're trying to do here.

                 

                O.

                • 5. Re: Rescue Query name
                  SteelDust Level 1

                  Thank you very much!!

                   

                  I've found it!!!!

                   

                  this:

                  <cfobject action="CREATE" type="JAVA" class="coldfusion.server.ServiceFactory" name="factory">
                  <cfset cfdebugger = factory.getDebuggingService()>

                   

                  <!--- Load the debugging service's event table --->
                  <cfset qEvents = cfdebugger.getDebugger().getData()>

                   

                  :-)

                  • 6. Re: Rescue Query name
                    Dan Bracuk Level 5

                    Interesting.

                     

                    Are you planning to put this into a onRequestEnd function?

                    • 7. Re: Rescue Query name
                      SteelDust Level 1

                      Yes, but now I have another problem...

                       

                      If Application.cfm is the first page that CF run... now I need to create a "footer" page that CF run every time when it's finish to load any page...

                       

                      have you some idea?? :-)

                       

                       

                      thank you very much... this is my template:

                       

                      ----------------------------------------------

                       

                      <cftry>
                          <cfobject action="CREATE" type="JAVA" class="coldfusion.server.ServiceFactory" name="factory">
                          <cfset cfdebugger = factory.getDebuggingService()>
                          <cfcatch type="Any"></cfcatch>
                      </cftry>
                      <!--- Load the debugging service's event table --->
                      <cfset qEvents = cfdebugger.getDebugger().getData()>
                         
                         
                      <!--- EVENT: SQL Queries --->
                      <cftry>
                      <cfquery dbType="query" name="cfdebug_queries" debug="false">
                        SELECT *, (endTime - startTime) AS executionTime
                        FROM qEvents
                        WHERE type = 'SqlQuery'
                      </cfquery>
                      <cfscript>
                        if( cfdebug_queries.recordCount eq 1 and not len(trim(cfdebug_queries.executionTime)) )
                        {
                         querySetCell(cfdebug_queries, "executionTime", "0", 1);
                        }
                      </cfscript>
                      <cfcatch type="Any">
                        <cfscript>
                         cfdebug_queries = queryNew('ATTRIBUTES, BODY, CACHEDQUERY, CATEGORY, DATASOURCE, ENDTIME, EXECUTIONTIME, LINE, MESSAGE, NAME, PARENT, PRIORITY, RESULT, ROWCOUNT, STACKTRACE, STARTTIME, TEMPLATE, TIMESTAMP, TYPE, URL, et');
                        </cfscript> 
                      </cfcatch>
                      </cftry>

                       

                      <!--- loop tra le query. --->
                      <cfset qQueries = QueryNew("Name, Time, Records, Template, OrarioEsecuzione")>
                      <cfloop query="cfdebug_queries">
                      <!--- se più grande di 3ms, inserisco nella lista e mando e-mail. --->
                      <cfif Max(cfdebug_queries.executionTime, 0) GT 3000>
                           <cfset QueryAddRow(qQueries)>
                              <cfset QuerySetCell(qQueries, "Name", cfdebug_queries.name)>
                              <cfset QuerySetCell(qQueries, "Time", Max(cfdebug_queries.executionTime, 0))>
                              <cfif IsDefined("cfdebug_queries.rowcount") AND IsNumeric(cfdebug_queries.rowcount)>
                               <cfset QuerySetCell(qQueries, "Records", Max(cfdebug_queries.rowcount, 0))>
                              <cfelseif IsDefined("cfdebug_queries.result.recordCount")>
                               <cfset QuerySetCell(qQueries, "Records", cfdebug_queries.result.recordCount)>
                              </cfif>
                              <cfset QuerySetCell(qQueries, "Template", cfdebug_queries.template)>
                              <cfset QuerySetCell(qQueries, "OrarioEsecuzione", TimeFormat(cfdebug_queries.timestamp, "HH:mm:ss.SSS"))>
                      </cfif>    
                      </cfloop>

                       

                      <cfif qQueries.recordcount GT 0>

                      <!--- invia mail. --->
                          <cfif config EQ "PRODUZIONE">
                        <cfset alert_to = mail_sviluppo>
                          <cfelse>
                              <cfset alert_to = mailtest>
                          </cfif>
                                 
                          <cfmail to="#alert_to#" from="#frommail#" password="#pwd_server#" server="#mailserver#" username="#login_server#" port="#porta_SMTP#" subject="Problemi nell'esecuzione di query" type="text" charset="iso-8859-1">
                      Sono stati riscontrati problemi sulle seguenti query:
                      <cfloop query="qQueries">
                      - Query: #qQueries.name# (Tempo: #qQueries.time#ms):
                        n.Records: #qQueries.Records# -- Template: #qQueries.template#

                      </cfloop>
                          </cfmail>
                             
                             
                      </cfif>

                      • 8. Re: Rescue Query name
                        Adam Cameron. Level 5

                        You should do a search in the docs for OnRequestEnd.cfm, or - better yet -Application.cfc.

                         

                        --

                        Adam

                        • 9. Re: Rescue Query name
                          SteelDust Level 1

                          Thank you very much :-)

                           

                          at the moment I try the onRequestEnd.cfm... in that page I include my template...

                           

                          Thank you for your precious help!!

                           

                          regards

                          Stefano

                          • 10. Re: Rescue Query name
                            SteelDust Level 1

                            ops... now I have another problem...

                             

                            If the users can't see the debug option in CF administrator... CF give me an error:

                             

                            It can't inizialize the qEvent...

                            ... and now??? mmmhhhh....

                             

                            Have you some idea??

                             

                            thanks in advance

                             

                            regards

                            • 11. Re: Rescue Query name
                              Dan Bracuk Level 5

                              I don't see what users have to do with it.  I don't have access to my Admin panel but I could run your code and get results.

                               

                              About those results.  Once you have your ColdFusion query object, doing a select * is not going to help the recipients much.  Sometimes the stacktrace field is huge which will make the mail hard to read.  Also, the results field could be huge.

                               

                              I suggest something like this.

                               

                              select template, body, datasource, rowcount, starttime, endtime - starttime runtime
                              from qevents
                              where type = 'SqlQuery'

                              I found it odd that if you didn't include either the starttim or endtime, selecting the difference between the two would give you null.