Get SQL Source Code from Debug on CFMX
Here's a great CFMX function using the coldfusion.server.ServiceFactory class to get at the debugging information and extract just the generated SQL for a specific query.
This can be useful for logging or debugging. It renders the SQL so it can be copied into query analyzer (for example). The only thing to watch out for is the use of <cfqueryparam> in your SQL. When this tag is used (and it should be used a lot) it causes the rendered SQL to have placeholders (question marks) in the place of actual values - with the values listed below. This means it has to be edited when it's ported to query analyzer.
<CFFUNCTION NAME="QueryGetSQL" RETURNTYPE="string">
<!--- Query name is required --->
<CFARGUMENT NAME="queryname"
TYPE="string"
REQUIRED="yes">
<!--- Initialize result string --->
<CFSET result="">
<!--- Requires debug mode --->
<CFIF IsDebugMode()>
<!--- Use debugging service --->
<CFOBJECT ACTION="CREATE"
TYPE="JAVA"
CLASS="coldfusion.server.ServiceFactory"
NAME="factory">
<CFSET cfdebugger=factory.getDebuggingService()>
<!--- Load the debugging service's event table --->
<CFSET events = cfdebugger.getDebugger().getData()>
<!--- Get SQL statement (body) for specified query --->
<CFQUERY DBTYPE="query" NAME="getquery" DEBUG="false">
SELECT body
FROM events
WHERE type='SqlQuery' AND name='#queryname#'
</CFQUERY>
<!--- Save result --->
<CFSET result=getquery.body>
</CFIF>
<!--- Return string --->
<CFRETURN result>
</CFFUNCTION>
One limitation that I can't seem to overcome is that you can't use the function on 2 separate queries in the same page. So I can't do: <cfoutput> #queryGetSQL('query1')# ---- #queryGetSQL('Query2')# </cfoutput>. The second call to the function doesn't throw an error or anything - it just doesn't return anything.
2 Comments
-
You can turn on debugging if it's not already enabled with:
cfdebugger.setEnabled(1)
The only problem here is that it won't work for the current request. Once the page is refreshed, debugging will be enabled. Also, if you want don't want the cf debug output, there is a method to disable it's display. I think it's cfdebugger.showDebug(0) or showDebugging(0), you can find out by cfdumping your cfdebugger var. -
Sorry, but I forgot to comment on your last paragraph. You actually CAN get each of multiple queries from a page:
<CFQUERY DBTYPE="query" NAME="getquery" DEBUG="false">
SELECT body
FROM events
WHERE type='SqlQuery'
ORRDER BY timestamp asc
</CFQUERY>
<cfset queries = ArrayNew(1)>
<cfloop query="getquery">
<cfset queries[currentrow] = body>
</cfloop>
I use code such as this to capture and store all insert, update and delete queries from my admin app.
Sites to Watch
Aggregators
RSS
