Get SQL From Query Object

[twkatadin]

I was wondering if you can get the SQL string out of a query object, or any way
of seeing the SQL once the query has been run. I do not have access to the SQL
where it is entered in the cfquery tag, just the resulting query object. Thanks

Tim

[Dan Bracuk]

Turn on debugging and then it will show up.

[twkatadin]

Yes, I know that it shows up in the debugging information, but is there any
other way to get the SQL in code? I can't turn debugging on in my production
enviornment. I want to log the SQL that was run, but the code that creates and
runs the query is invisible to my code. I was hoping the query object would
have a sql property, but it doesn't look like it does.

Thanks for the help
Tim

[MikerRoo]

Originally posted by: twkatadin
... ... but the code that creates and runs the query is invisible to my
code.... ... I hope you're not trying to bypass some security.


Anyway, there is no reliable way to get this information when debugging is off.

You can use the admin API to turn debugging on and off programmatically and
then use undocumented service factory functions to get the SQL.

Or you can have the source page build the query using Java and setStatement().
Then you could retrieve the SQL with getStatement().

Finally, I think that if you revert to ODBC drivers (NOT recommended), you can
use getStatement() without having to do any of the above. BUT I haven't (and
won't) tested this.

Finally, just about anything you do along these lines is pretty much going to
be unsupported and subject to breakage with every change to CF.

[BKBK]

Assuming that your query is myQuery ,



<!---
<cfquery name="myQuery" datasource="myDSN">
select myCol
from myTable
</cfquery>
--->

<cfscript>
adminDbugObj = createObject("component","cfide.adminapi.debugging ");
// getDebugRecordset() returns a query object.
// "name", "datasource" and "body" are three of its columns.
q = #adminDbugObj.getDebugRecordset()#;
</cfscript>
<!--- <cfdump var="#q#"> --->
<cfloop query="q">
<cfif name EQ "myQuery" and datasource EQ "myDSN">
<cfoutput>#body#</cfoutput>
<cfabort>
</cfif>
</cfloop>

[MikerRoo]

Originally posted by: BKBK
Assuming that your query is myQuery ,
... ...

Well that's better than using the service factory but it still doesn't work if
debugging is turned off.

getDebugRecordset() will return null.

[twkatadin]

Thanks for all the help

Tim

[BKBK]

Well that's better than using the service factory but it still doesn't work if
debugging is turned off.
We should not presume that, because Twkatadin cannot see any debugging, it has
been turned off. Admins usually have debugging on, but just for several Admin
IPs. I'll leave it to someone else to check whether the code works in that
case.

[MikerRoo]

Let me make this clear.

That code does not work unless BOTH of the following are true:
1) Debugging is ON.
2) The client PC's IP is on the debug info list.

Furthermore the admin API approach has one disadvantage that the service
factory does not:

If an admin password is required, then every time a session is started, that
uses the API, then the admin or RDS password must be provided.

This either precludes use in production or requires the coder to expose key
passwords in his CFML.