Debugging Coldfusion Query with cfloop inside

Question

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,

Out of context, that is a scary-looking way to build a query! Can you provide a sample of aSQL? That would make it much easier to parse, mentally. — beercodebeer
– beercodebeer, Commented Jan 15, 2016 at 21:59
Agree with @beercodebeer - but I think his problem is he can't actually produce the regular SQL the query is supposed to execute because his CFML is messing up the syntax. He's trying to get started on debugging, which is why I suggested cftry below. — TRose
– TRose, Commented Jan 15, 2016 at 22:00
What precisely are you trying to debug? Is the query crashing when you run it? Is it just not acting as you expect? If you can't dump the query, that indicates something pretty deep must be going wrong. — Tim Jasko
– Tim Jasko, Commented Jan 15, 2016 at 22:08
Run your page with debugging turned on. Among other things, you will see if the query actually runs. Based on your error message, it probably didn't. — Dan Bracuk
– Dan Bracuk, Commented Jan 16, 2016 at 1:17
Try/catch is a very fundamental troubleshooting tool and debugging 101. If you are new to CF/programming, start by reviewing the error handling section in the documentation. Also, PreserveSingleQuotes makes your database vulnerable to SQL injection. Read up on cfqueryparam. — Leigh
– Leigh, Commented Jan 17, 2016 at 3:46

rrk · Accepted Answer · 2016-01-25 21:01:53Z

2

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.

edited Jan 25, 2016 at 21:01

rrk

15.9k4 gold badges32 silver badges49 bronze badges

answered Jan 15, 2016 at 22:11

Tim Jasko

1,5421 gold badge8 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Leigh Over a year ago

(Edit) Good idea, but unfortunately CF will balk at trying to use cfqueryparam outside a cfquery tag. You may have to resort to tricks like replacing the < in <cfqueryparam with the html equivalent &lt for it to work. Very klunky, but I have had to resort to that when debugging monster legacy apps ...

Tim Jasko Over a year ago

Yes, very clunky, and it will require a little manual editing, but it's probably the fastest move at this point.

Tim Jasko Over a year ago

I did a little investigation based on @Leigh 's comment, and found a better workaround (admittedly, this is all workaround), and updated my comment accordingly.

Leigh Over a year ago

Unfortunately, that will not quite work either. CF will see any single quotes embedded in the string (such as with createODBCDateTime) and helpfully escape them, thus breaking the SQL. Honestly, I would just copy and paste the code inside the cfquery, escape the query params, and dump it out on screen. Then go from there. It is duplication, but is probably as good as it gets without cfscript. Having said all that, the original question is very ambiguous. Until the asker provides more specific information on the error and what is being debugged all we can do is guess... Voting to close.

Leigh Over a year ago

Just my 2¢, but this is exactly why dynamic sql always seems like a great idea, but in reality, not so much ;-) It is difficult to do well, and troubleshooting problems is always more time consuming than you expect.

|

TRose · Accepted Answer · 2016-01-15 21:47:22Z

1

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.

answered Jan 15, 2016 at 21:47

TRose

1,7382 gold badges17 silver badges36 bronze badges

3 Comments

Dan Bracuk Over a year ago

He can achieve the same result with normal debugging. Besides, in this case, the error is an undefined variable, which changes the troubleshooting approach.

user_1856538_ Over a year ago

You're the best man! Uou helped me alot. Actually <cftry> should be putted between <cfquery> and <cfloop>, not outside <cfquery>.Thanks alot man!

TRose Over a year ago

Ha, I was beginning to think with all the other answers I was way off base. Glad I could help.

James A Mohler · Accepted Answer · 2016-01-15 23:39:00Z

0

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.

answered Jan 15, 2016 at 23:39

James A Mohler

11.1k15 gold badges51 silver badges76 bronze badges

1 Comment

user_1856538_ Over a year ago

it's not working :( I also putted the debugger and I don't understand why I have for sql something like : [INSERT INTO [Properties_ClientCompanies] (, [property_uid], ,, [client_company_uid], ,, [company_role_uid], ,, [relationship_start], ,, [is_active], ,, [created_by], ,, [created_date], ,, [modified_by], ,, [modified_date], ), VALUES (, {NULL=no, SEPARATOR=,, PrimaryKey=false, CFSQLTYPE=CF_SQL_IDSTAMP, AllowNulls=NO, VALUE=F812D675-AE0C-898A-3851-753EDC7853D2, MAXLENGTH=<<Recursive Reference - Cannot display value>>...

Dan Bracuk · Accepted Answer · 2016-01-16 13:13:18Z

0

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.

answered Jan 16, 2016 at 13:13

Dan Bracuk

20.8k5 gold badges30 silver badges44 bronze badges

Comments

user_1856538_ · Accepted Answer · 2016-01-16 20:55:36Z

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

Collectives™ on Stack Overflow

Debugging Coldfusion Query with cfloop inside

5 Answers 5

6 Comments

3 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

6 Comments

3 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related