Query Not Working Within CFFUNCTION

[ErikMadsen]

I've been a CF developer for 15 years and I've never run into anything this strange or frustrating.  I've pulled my hair out for hours, googled, abstracted, simplified, prayed and done it all in reverse.  Can you help me?

A cffunction takes one string argument and from that string I build an array of "phrases" to run a query with, attempting to match a location name in my database.  For example, the string "the republic of boulder" would produce the array: ["the","republic","of","boulder","the republic","the republic of","the republic of boulder","republic of","republic of boulder","of boulder"].

Another cffunction uses the aforementioned cffunction and runs a cfquery.  A query based on the previously given example would be...

select locationid, locationname, locationaliasname from vwLocationsWithAlias where LocationName in ('the','the republic','the republic of','republic','republic of','republic of boulder','of','of boulder','boulder') or LocationAliasName in ('the','the republic','the republic of','republic','republic of','republic of boulder','of','of boulder','boulder')

This returns 2 records...

locationid - locationname - locationalias

99 - 'Boulder' - 'the republic'

68 - 'Boulder' - NULL

This is good.  Works fine and dandy.  HOWEVER... if the string is changed to "the republic", resulting in the phrases array ["the","republic","the republic"] which is then used to produce the query...

select locationid, locationname, locationaliasname from vwLocationsWithAlias where LocationName in ('the','the republic','republic') or LocationAliasName in ('the','the republic','republic')

This returns 0 records.  Say what?!  OK, just to make sure I'm not involuntarily HIGH I run that very same query in my SQL console against the same database in the cf datasource.  1 RECORD!

locationid - locationname - locationalias

99 - 'Boulder' - 'the republic'

I can even hard-code that sql within the same cffunction and get that one result, but never from the dynamically generated SQL.  I can get my location phrases from another cffunction of a different name that returns hard-coded array values and those work, but never if the array is dynamically built.  I've tried removing cfqueryparams, triple-checking my datatypes, datasource setups, etc., etc., etc.  NO DICE

WTF!?  Is this an obscure bug?  Am I losing my mind?  I've tried everything I can think of and others (including Ray Camden) can think of.

ColdFusion 8 (with all the latest hotfixes)

SQL Server 2005 (with all the greatest service packs)

Windows 2003 Server (with all the latest updates, service packs and nightly MS voodoo)

[-__cfSearching__-]

> I can even hard-code that sql within the same cffunction and get that  one result ...

>  but never from the dynamically generated SQL

I hesitate to suggest something so simple, as you seem to have checked all the likely causes already. But did you verify it is not a white space issue? Those catch me off guard every once in a while. I only suggest it because everything you have described suggests something about the dynamic sql string is different ...

Also, did you try profiling the query? Just to see what sql is actually executing in the database and verify  nothing unusual is happening in between the CF and database layer.  It is unlikely, but as you have tried everything else it may be worth a shot.

[ErikMadsen]

Yeah, I'm using trim on each item within the getLocationPhrases function.  I've even tried using lists to the same effect.

<!--- build array of location phrases --->

<cfset locationPhrases = UtilService.getLocationPhraseList(arguments.message) />

<!--- match locations ---> 

<cfquery datasource="#variables.dsn#" name="qLocations">

select locationid, locationname, locationaliasname

from vwLocationsWithAlias

where LocationName in (#listQualify(locationPhrases, "'")#)

or LocationAliasName in (#listQualify(locationPhrases, "'")#)

</cfquery>

I can copy the sql from the cfdump of the query and run it in SQL console and get expected results.

What's most troubling is that the longer list of phrases does produce results in CF, the shorter one does not and yet the same queries directly run in SQL console always give the expected results.

[Adam Cameron.]

Have you used SQL Server Profiler to check what SQL the DB drivers are passing to the DB?

You say you've done some simplification & refactoring.  Do you have a simple repro case you can post, with a table schema and some basic test data so we can try to eyeball this happening?

--

Adam

[ErikMadsen]

No, I haven't yet.  I've never used SQL Profiler before.  It can help me see what CFQUERY is sending to the server?

Incidentally, I just found something interesting. This code...

<cfset myQuery = "select locationid, locationname, locationaliasname from vwLocationsWithAlias where LocationName in ('the','the republic','republic') or LocationAliasName in ('the','the republic','republic')" />

<cfdump var="#myQuery#" />

<cfdump var="#request.qLocationsResult.sql#" />

Compare: <cfdump var="#compare(myQuery, request.qLocationsResult.sql)#" />

myQuery Len:<cfdump var="#len(preserveSingleQuotes(myQuery))#" />

SQL Len:<cfdump var="#len(request.qLocationsResult.sql)#" />

Produced this output:

select locationid, locationname, locationaliasname from vwLocationsWithAlias where LocationName in ('the','the republic','republic') or LocationAliasName in ('the','the republic','republic')

select locationid, locationname, locationaliasname from vwLocationsWithAlias where LocationName in ('the','the republic','republic') or LocationAliasName in ('the','the republic','republic')

Compare: 1

myQuery Len: 190

SQL Len: 211

The strings LOOK identical, but there's obviously something different between them.  How in the heck can I find out what those differences are?

[Adam Cameron. · Accepted solution]

Loop over them char by char outputting the char in each string, as well as its ASCII code.  Side by side.

--

Adam

[-__cfSearching__-]

I see it was a white space issue after all. Glad I am not the only one that gets caught by it every now and then

[ErikMadsen]

Yep. Embarrassing as all hell.

[ErikMadsen]

Wow.  I feel like such an idiot.  One stupid frigging space too many in the worst possible place.  That's the last time I'm copying from cfdump... browsers don't output two consecutive spaces.... only one.  Thanks for the help getting back to basics.

[Adam Cameron.]

Everyone has been caught out by that.

Whilst it's topical, it's perhaps still worth looking at the SQL Server Profiler, as it's quite a handy tool.

--

Adam

[Dan_Bracuk]

Speaking of getting back to basics, why are you using listqualify instead of cfqueryparam list="yes"?  Plus, since you're doing it that way, do you have anything in place to handle apostrophes?

[ErikMadsen]

Since i found the problem I'm back to using an array and yes I am

using the list option.

Sent from my iPhone