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

score 35 · Accepted Answer · answered Nov 17 '10 at 19:51

35

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

answered Nov 17 '10 at 19:51

Todd Sharp

3,207
2
19
27

Dang it Todd! You beat me to it while I was double checking my code! – Adam Tuttle Nov 17 '10 at 19:59
15

You could also technically dip down into Java and get it: – Todd Sharp Nov 17 '10 at 20:06
Darn it. Beat me too. I was going mention extended metadata. But with you pointing out the simpler result attribute, I figured why bother (We are going to have to start tinkering with your keyboard or something ..;) – Leigh Nov 17 '10 at 21:27
If you have parameters, this SQL will have a bunch of question marks for each param. Is it possible to get the final SQL, with the params inserted into the SQL? – Luke Jul 03 '13 at 18:28

score 23 · Answer 2 · answered Nov 17 '10 at 19:59

23

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

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

answered Nov 17 '10 at 19:59

Adam Tuttle

19,505
17
80
113

You could put

 tags around #r.sql# so the query displays formatted.

– usr-bin-drinking Feb 10 '22 at 16:54

score 10 · Answer 3 · answered Apr 03 '14 at 21:47

10

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>

answered Apr 03 '14 at 21:47

Luke

18,811
16
99
115

I should not that this may not work properly with dates. – Luke Aug 11 '14 at 18:50
Also doesn't work correctly if there may be literal ? chars the sql, i.e., ones inside single quotes. – enigment May 08 '17 at 11:32
Using [this](https://stackoverflow.com/questions/4208210/how-can-i-get-the-sql-from-a-query-object-in-coldfusion/22849629#comment4551585_4208519) comment we can skip adding Result attribute to CFQuery making life easier :) – Binod Kalathil Mar 05 '21 at 16:38

score 4 · Answer 4 · answered Nov 17 '10 at 19:53

4

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

answered Nov 17 '10 at 19:53

Sam Farmer

4,108
17
20

gordon · Answer 5 · 2018-05-11T17:23:03.440

3

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 query enter 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

edited May 11 '18 at 17:23

answered Apr 02 '14 at 20:03

gordon

1,152
1
12
18

score 2 · Answer 6 · answered Aug 20 '16 at 07:41

2

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.

answered Aug 20 '16 at 07:41

Sander

390
1
4
13

score 0 · Answer 7 · answered Nov 17 '10 at 19:47

0

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.

answered Nov 17 '10 at 19:47

Steve K.

725
1
6
8

1

"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." – Kip Nov 17 '10 at 19:56

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

7 Answers7