ColdFusion Query

Is there a way that I can assign the query I just ran in a <CFQUERY> tag to a variable so I can store the actual SQL text? Does the Query Object have a property that contains the query?
jcorbinAsked:
Who is Participating?
 
kc5sigConnect With a Mentor Commented:
Unfortunately I do not think that there is a way to report what specific SQL statements are used in a query without first setting them to a variable name or names as I mentioned in my previous comment.

Hatton
0
 
dlewis9Commented:
You can store it in a variable first, and then reference it later like this:

<CFSET sqltext = "SELECT * FROM TABLE">

<CFQUERY NAME="qry" DATASOURCE="datasource">
#sqltext#
</CFQUERY>

Then, you can use sqltext after the query has been run..
0
 
Nathan Stanford SrSenior ProgrammerCommented:
No, the Query Object does not store the sql.  Of couse tyou could do it like dlewis9 has said but that is not what you asked is it.  So the answer to your question is NO the query object does not have a object that contains the sql text in it.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
smuniasamyCommented:
I agree with nathans. Query object does not store the sql value.

I assume that u want to store the sql value somewhere for future use.

U can construct the query using the variable and execute it like what dlewis9 said.

<CFSET sqltext = "SELECT * FROM TABLE">

<CFQUERY NAME="qry" DATASOURCE="datasource">
#sqltext#
</CFQUERY>

Then u can store the value in the form. That query(sql) can be executed in the consecutive pages. It can also be used while reloading the page.

I believe that this is what u asked. Good luck
0
 
kc5sigCommented:
If I'm reading your question right, you want to create a page with a CFQuery and then output both the query's results and the actual query.  As mentioned by both smuniasamy and dlewis9 you can use the CFSet tag to define the actual query.  Check the exact length of the cfset tag though, since it may limit string lengths to 254 characters or something silly like that.  I haven't tested this, but if I'm reading your question right, this should work:

<cfset #sql1# = "Select * From Table">
<cfset #sql2# = "Where <criteria>">
<cfset #sql3# = "Order By <whatever>">

<cfquery name="Testit" datasource="source" dbtype="ODBC">
#Sql1#
#Sql2#
#Sql3#
</cfquery>

<html>
<head>
</head>
<body>
<cfoutput>
Results for Query:<br>
#Sql1#<br>
#Sql2#<br>
#Sql3#<br>
</cfoutput>
<cfoutput query="Testit">
....(Insert your output here)
</cfoutput>
</body>
</html>

That looks like it will work, but that's only if I'm reading what you're wanting to have done right.

Hatton Humphrey
0
 
Nathan Stanford SrSenior ProgrammerCommented:
Please Grade this one

You have several answers

Grade this for someone.
0
 
jcorbinAuthor Commented:
None of the answers do what I need
0
 
jcorbinAuthor Commented:
Comment accepted as answer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.