CODEWITHSUNDEEP
coldfusion
Nebu
Nebu

Reputation: 1793

Dump cfquery sql with cfqueryparam values

I want to dump the sql that is executed. I know several ways to dump the sql however all the methods I know show the cfqueryparam as an ? in the sql. For example when i dump this query :

<cfset testid = 1>
<cfquery name="myVariable" result="myResults" datasource="#myDatabase#">
select id from myTable where id = <cfqueryparam value="#testId#">
<cfquery>

The output is:

select id from myTable where id = ?

I want to the output to be:

select id from myTable where id = 1

I tried the following:

<Cfdump var="#myVariable.getMetadata().getExtendedMetaData().sql#">
<Cfdump var="#myResults.SQL#">

Both didn't work.

Upvotes: 1

Views: 525

Answers (2)

Tony Junkes
Tony Junkes

Reputation: 735

I'm not sure of a native solution to obtain the executed SQL string with parameters included. Perhaps there is an underlying Java class that I am not aware of (I think there is one if you have debugging enabled).

You could otherwise take the sql and sqlParameters keys from the result struct and build it yourself. Not ideal in my opinion, but an option.

<cfset testid = 1>
<cfquery name="myVariable" result="myResults" datasource="#myDatabase#">
select id from myTable where id = <cfqueryparam value="#testId#">
<cfquery>

<cfset completeQuery = myResults.sql>
<cfloop index="element" array="#myResults.sqlParameters#">
    <!--- Test for numeric value to help format in the SQL string --->
    <cfset completeQuery = replace(completeQuery, "?", (isNumeric(element)) ? element : "'#element#'", "one")>
</cfloop>

<cfdump var="#completeQuery#">

Upvotes: 1

Nichoru
Nichoru

Reputation: 41

try this in your dump var = "#myResults.SQLParameters#"

https://helpx.adobe.com/coldfusion/developing-applications/accessing-and-using-data/accessing-and-retrieving-data/getting-information-about-query-results.html

Upvotes: 1

Related Questions