CODEWITHSUNDEEP
sqlloopscoldfusioncfquery
user_1856538_
user_1856538_

Reputation: 149

Debugging Coldfusion Query with cfloop inside

I'm trying to debug a cf query and cannot do this because of his complex structure.The code is following:

<cfquery name="qQuery" datasource="#variables.datasource#">
    <cfloop index="i" from="1" to="#ArrayLen(aSQL)#" step="1">
        <cfif IsSimpleValue(aSQL[i])>
            <cfset temp = aSQL[i]>#Trim(DMPreserveSingleQuotes(temp))#
        <cfelseif IsStruct(aSQL[i])>
            <cfset aSQL[i] = queryparam(argumentCollection=aSQL[i])>
            <cfswitch expression="#aSQL[i].cfsqltype#">
                <cfcase value="CF_SQL_BIT">
                    #getBooleanSqlValue(aSQL[i].value)#
                </cfcase>
                <cfcase value="CF_SQL_DATE,CF_SQL_DATETIME">
                    #CreateODBCDateTime(aSQL[i].value)#
                </cfcase>
                <cfdefaultcase>
                    <!--- <cfif ListFindNoCase(variables.dectypes,aSQL[i].cfsqltype)>#Val(aSQL[i].value)#<cfelse> --->
                    <cfqueryparam value="#aSQL[i].value#" cfsqltype="#aSQL[i].cfsqltype#" maxlength="#aSQL[i].maxlength#" scale="#aSQL[i].scale#" null="#aSQL[i].null#" list="#aSQL[i].list#" separator="#aSQL[i].separator#">
                    <!--- </cfif> --->
                </cfdefaultcase>
            </cfswitch>
        </cfif>                     
    </cfloop>               
</cfquery>

If I run <cfdump var="#qQuery#"> it's not working nor cfoutput, I get undefined qQuery error. How can I find what query is executing behind ? I don't want to use MS SQL profiler.

Thanks,

Upvotes: 1

Views: 554

Answers (5)

Tim Jasko
Tim Jasko

Reputation: 1542

Take everything inside the query and wrap it in a cfsavecontent instead. Output that result.

If you place the cfsavecontent inside the cfquery tags, you don't even need to worry about the cfqueryparam tags barfing, although you do need to re-output that saved content inside the query. See http://coldflint.blogspot.com/2016/01/debugging-queries-dirty-way.html

Basically, you should have this:

<cfquery name="qQuery" datasource="#variables.datasource#">
    <cfsavecontent variable="sqlContent">
        <cfloop index="i" from="1" to="#ArrayLen(aSQL)#" step="1">
            <cfif IsSimpleValue(aSQL[i])>
                <cfset temp = aSQL[i]>#Trim(DMPreserveSingleQuotes(temp))#
            <cfelseif IsStruct(aSQL[i])>
                <cfset aSQL[i] = queryparam(argumentCollection=aSQL[i])>
                <cfswitch expression="#aSQL[i].cfsqltype#">
                    <cfcase value="CF_SQL_BIT">
                        #getBooleanSqlValue(aSQL[i].value)#
                    </cfcase>
                    <cfcase value="CF_SQL_DATE,CF_SQL_DATETIME">
                        #CreateODBCDateTime(aSQL[i].value)#
                    </cfcase>
                    <cfdefaultcase>
                        <!--- <cfif ListFindNoCase(variables.dectypes,aSQL[i].cfsqltype)>#Val(aSQL[i].value)#<cfelse> --->
                        <cfqueryparam value="#aSQL[i].value#" cfsqltype="#aSQL[i].cfsqltype#" maxlength="#aSQL[i].maxlength#" scale="#aSQL[i].scale#" null="#aSQL[i].null#" list="#aSQL[i].list#" separator="#aSQL[i].separator#">
                        <!--- </cfif> --->
                     </cfdefaultcase>
                 </cfswitch>
             </cfif>                     
         </cfloop>               
    </cfsavecontent>
    #sqlContent#
</cfquery>

<pre>#sqlContent#</pre>

Do make sure to put everything back to normal once you're done debugging.

Upvotes: 2

user_1856538_
user_1856538_

Reputation: 149

The solution was to put <cftry> outside <cfloop> but not outside <cfquery>.I found that I forgot to send one parametter.

So the code is following:

        <cfquery name="qQuery" datasource="#variables.datasource#">
                    <cftry>             
                        <cfloop index="i" from="1" to="#ArrayLen(aSQL)#" step="1">
                            <cfif IsSimpleValue(aSQL[i])>
                                <cfset temp = aSQL[i]>#Trim(DMPreserveSingleQuotes(temp))#                          
                            <cfelseif IsStruct(aSQL[i])>
                                <cfset aSQL[i] = queryparam(argumentCollection=aSQL[i])>
                                <cfswitch expression="#aSQL[i].cfsqltype#">
                                    <cfcase value="CF_SQL_BIT">
                                        #getBooleanSqlValue(aSQL[i].value)#
                                    </cfcase>
                                    <cfcase value="CF_SQL_DATE,CF_SQL_DATETIME">
                                        #CreateODBCDateTime(aSQL[i].value)#
                                    </cfcase>
                                    <cfdefaultcase>                                     
                                        <cfqueryparam value="#aSQL[i].value#" cfsqltype="#aSQL[i].cfsqltype#" maxlength="#aSQL[i].maxlength#" scale="#aSQL[i].scale#" null="#aSQL[i].null#" list="#aSQL[i].list#" separator="#aSQL[i].separator#">                                      
                                    </cfdefaultcase>
                                </cfswitch>
                            </cfif>                 
                        </cfloop>
                        <cfcatch>
                            <cfdump var="#cfcatch#" >
                        </cfcatch>
                    </cftry>                            
                </cfquery

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20804

Your error has nothing to do with the sql being generated by the code in the cfquery block. It's an undefined variable. If there was a problem with the code you displayed, the error message would be different.

Troublehoot as follows. First comment out all the code inside that query and replace it with:

select 1 record

This is valid for MS SQL. Leave everything else unchanged.

Running the page will produce the same error. You then have to determine why the query is not running. Likely, you have something like this going on:

<cfif some Condition is met>
run the query
</cfif>
dump the query

You'll have to determine why your condition wasn't met and make sure your page runs properly when it isn't.

Upvotes: 0

James A Mohler
James A Mohler

Reputation: 11120

You have to work from the inside out this.

As I look at this query, I note that it is split on IsSimpleValue() and IsStruct(). So run this

 <cfquery name="qQuery" datasource="#variables.datasource#">
    <cfloop index="i" from="1" to="#ArrayLen(aSQL)#" step="1">
       <cfif IsSimpleValue(aSQL[i])>
            <cfset temp = aSQL[i]>#Trim(DMPreserveSingleQuotes(temp))#
       </cfif>                     
    </cfloop>               
 </cfquery>

Note that temp is never used.

The rest of the code creates <cfqueryparam>s

Conclusion

This code cannot work. It cannot create valid SQL.

Upvotes: 0

TRose
TRose

Reputation: 1738

If this question is more about HOW to debug or get some output you can work with, cftry and cfcatch are your friends.

<cftry>

---code logic---

<cfcatch>
<cfdump var="#cfcatch#">
</cfcatch>
</cfctry>

This should provide a complete dump of whatever errors ColdFusion encounters as well as SQL statements that were attempted, if there is indeed a syntax error generated by the loopy logic.

Upvotes: 1

Related Questions