Copy link to clipboard
Copied
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
Have a look at the debugging template. It's in theWEB-INF/debug dir.
--
Adam
Copy link to clipboard
Copied
Switch debugging on?
--
Adam
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
Have a look at the debugging template. It's in theWEB-INF/debug dir.
--
Adam
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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()>
🙂
Copy link to clipboard
Copied
Interesting.
Are you planning to put this into a onRequestEnd function?
Copy link to clipboard
Copied
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>
Copy link to clipboard
Copied
You should do a search in the docs for OnRequestEnd.cfm, or - better yet -Application.cfc.
--
Adam
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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.