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

[Adam Cameron.]

Switch debugging on?

--

Adam

[SteelDust]

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

[Adam Cameron. · Accepted solution]

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

--

Adam

[Owainnorth]

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.

[SteelDust]

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()>

🙂

[Dan_Bracuk]

Interesting.

Are you planning to put this into a onRequestEnd function?

[SteelDust]

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>

[Adam Cameron.]

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

--

Adam

[SteelDust]

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

[SteelDust]

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

[Dan_Bracuk]

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.