CODEWITHSUNDEEP
coldfusion
Kip
Kip

Reputation: 109473

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

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. --->

Upvotes: 20

Views: 31331

Answers (7)

gordon
gordon

Reputation: 1182

If you are cfdump-ing a structure holding queries (not what Kip needs but I needed the SQL), use the metainfo="yes". W/o yields only the records of the queryenter image description here...

<cfset stx={}>
<cfquery name="stx.q" datasource="myDataSource">
    select emp_id from employees where 1=2
</cfquery>
<cfdump var="#stx.q#">
<cfdump var="#stx.q#" metainfo="yes">

examples of cfdump of a query in a struct without and with metainfo attribute

Upvotes: 3

Sander
Sander

Reputation: 390

If dumping debug output within the regular output would break things, i always use <cfdump var="#myvar#" output="c:\filename.html"> That way, the dump ends up in a separate HTML file.

Upvotes: 2

Luke
Luke

Reputation: 20070

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>

Upvotes: 10

Adam Tuttle
Adam Tuttle

Reputation: 19834

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

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

Upvotes: 23

Sam Farmer
Sam Farmer

Reputation: 4118

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

Upvotes: 4

Todd Sharp
Todd Sharp

Reputation: 3355

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

Upvotes: 35

Steve K.
Steve K.

Reputation: 725

Do you have access to turn debugging on via the CF administrator? That will give you a rolling list of every query (including the SQL statements) that are being called in a given page.

Upvotes: 0

Related Questions