How can I get the SQL from a query object in ColdFusion?

Question

How can I get the SQL used to generate a cfquery object? If I cfdump the object, it shows it having an "SQL" property, which contains the actual query. Turning on debugging won't help me because I am making an API call, so output is not HTML and debug info would break it. I'm just trying to debug exactly what query is being executed.

<cfquery name="tableElements" datasource="TestSQLServer">
SELECT * FROM tableElements
</cfquery>

<cfdump var="#tableElements#" /> <!--- Shows object having "SQL" property --->
<cfoutput>SQL: #tableElements.SQL#</cfoutput> <!--- Error: Element SQL is undefined in TABLEELEMENTS. --->

Answer 1

Add a 'result' attribute to your cfquery. The SQL is in the result struct, not the query variable.

Answer 2

<cfquery name="tableElements" datasource="TestSQLServer" result="r">
SELECT * FROM tableElements
</cfquery>

<cfdump var="#tableElements#" /> <!--- Shows object having "SQL" property --->
<cfoutput>SQL: #r.SQL#</cfoutput>

Answer 3

Personally I like to have some SQL that has all the parameters inserted into it (rather than the ? question marks). This way I can just copy and paste the SQL to run a query on the database. To do this, I get a result (as mentioned in other comments), then use this function...

<cffunction name="getRealSQL" returntype="string">
    <cfargument name="qryResult" type="any">
    <cfset realSQL = arguments.qryResult.sql>
    <cfloop array="#arguments.qryResult.sqlParameters#" index="a">
        <cfscript>
            if (NOT isNumeric(a)) a = "'#a#'";
            realSQL = Replace(realSQL, "?", a);
        </cfscript>
    </cfloop>
    <cfreturn realSQL>
</cffunction>

Answer 4

Use the result attribute of cfquery. Specify a variable name and that will have a key called sql with your sql.