Skip to main content
E
ErikMadsen
Known Participant
April 23, 2010
Answered

Query Not Working Within CFFUNCTION

  • April 23, 2010
  • 2 replies
  • 1754 views

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)

This topic has been closed for replies.
Correct answer Adam Cameron.

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

--

Adam

2 replies

A
Adam Cameron.
Inspiring
April 23, 2010

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

E
ErikMadsenAuthor
Known Participant
April 23, 2010

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?

A
Adam Cameron.Correct answer
Inspiring
April 23, 2010

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

--

Adam

-
-__cfSearching__-
Inspiring
April 23, 2010

> 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.

E
ErikMadsenAuthor
Known Participant
April 23, 2010

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.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices