http://coldfusion.com logo
#cfml-general
Title
# cfml-general
s

Simone

02/25/2022, 10:15 PM
I am using cfquery result attribute to get the sql, but how can i get the full sql along with the parameters, i tried this function but it seems to be creating a problem, it dooes not return anything but instead says, complex queries cannot return data
Copy code
<cffunction name="getRealSQL" returntype="string">
    <cfargument name="qryResult" type="any">
    <cfset realSQL = QueryExecute("#arguments.qryResult#",{},{datasource="#Application.DSN#"})>
    <cfloop array="#arguments.qryResult.sqlParameters#" index="a">
        <cfscript>
            if (NOT isNumeric(a)) a = "'#a#'";
            realSQL = Replace(realSQL, "?", a);
        </cfscript>
    </cfloop>
    <cfreturn realSQL>
  </cffunction>
if the function is right, what should i pass an argument, a query object or query results or what?
m

Myka Forrest

02/25/2022, 10:18 PM
What exactly are you passing in as your
qryResult
argument?
I would look very closely at what a query
result
returns and what the
QueryExecute
function takes in as its
SQL
argument.
But the real question is probably: if you are running the query in the first place to return a result, why do you need to get the "real" SQL from its result? Wouldn't you already have the SQL and its parameters from the original query?
s

Simone

02/25/2022, 10:30 PM
i need full query along with parameters, not ? marks
m

Myka Forrest

02/25/2022, 10:31 PM
How did you determine the original query that you used to get the results?
s

Simone

02/25/2022, 10:33 PM
i am passing the cfquery result attribute of sql to it, its failing, then i am passing a whole cfquery name to it its failing
what else it need
m

Myka Forrest

02/25/2022, 10:34 PM
1.) what data type is the result? 2.) what does queryExecute take for its first argument?
But the query parameters should be in an array in the result structure
s

Simone

02/25/2022, 10:37 PM
as per functions, it expects query
sqlparameters are array
when it says queryexecute, it fails there
if i pass a query to it, it fails at questions so i do not know what it needs
m

Myka Forrest

02/25/2022, 10:40 PM
What's the documentation show?
s

Simone

02/25/2022, 10:41 PM
documentation is there to fetch a query but not along with parameters
m

Myka Forrest

02/25/2022, 10:53 PM
"The SQL is in the result struct"
d

danmurphy

02/25/2022, 10:54 PM
I guess to back up a step (as Myka asked), what do you need the query to give you the sql and parameters for? What are you going to do with it?
☝️ 1
m

Michael Schmidt

02/25/2022, 11:03 PM
Are you trying to QueryExecute the results of a query and not a string? Here is a sample of what comes back with results you will see that there are ? in place for where the parameterized SQL is placed... and there is an array of sqlparameters in the result that corresponds with the ? marks... https://trycf.com/gist/90a195c01fe34598ef6cc46efa5ddf28/lucee5?theme=monokai
s

Simone

02/26/2022, 12:07 AM
ok, take this WriteDump(res); how you get the realsql from it
or even from the WriteDump(q2);
i need sql, i do not need ?
or say what needs to be passed to the function getrealsql to return it as full query
m

Myka Forrest

02/26/2022, 12:09 AM
What is preventing you from using the sql from the original query?
s

Simone

02/26/2022, 12:36 AM
preventing ?
forget it
m

Myka Forrest

02/26/2022, 12:42 AM
We're trying to get to the root of the problem. You seem to have picked a methodology that is more difficult than it needs to be.
s

Simone

02/26/2022, 12:50 AM
we are overcomplicating it, i posted a stackoverflow where the guy has same problem and i am trying to get the same value
now i do not understand how the function mentioned got so many votes and its not even working with real qury
m

Myka Forrest

02/26/2022, 12:52 AM
The function you posted is not the same as the one given in an answer on SO.
☝️ 1
s

Simone

02/26/2022, 1:35 AM
its same, i copied from there
m

Myka Forrest

02/26/2022, 1:43 AM
Unless you meant a different function? Black is what you posted; white is on SO.
☝️ 1
t

tonyjunkes

02/27/2022, 5:24 AM
As has already been mentioned, what is in the
arguments.qryResult
that's different from what you're trying to extract after the fact? But… Looking away from that, what you're doing to fill in the parameters to the sql string returned from queryExecute doesn't appear to be correct. Calling replace on
realSql
, if it is in fact a query object, won't give you the desired result as you need to access the
sql
key in the object:
realSql.sql
. Here’s an SO question I answered a while back with code demonstrating what could be done. It’s very similar to the SO post already shared. https://stackoverflow.com/q/64767251/985709
2 Views