Query Results Not Expected

Question

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)

Answer 1

It seems to me that your function generates a slightly different query than what you are expecting, try to put a breakpoint in your function after generating the query, copy the generated query and run it in sql server. I expect no results will be found also.

May be some spaces or something resulting in no records found. Notes: You are searching for exact strings. To search for near strings use like in your queries.

Answer 2

Are you using CF 8 or CF 8.01? Upgrade to CF 8.01 if you're not on it already.

You can also try loading a different JDBC driver for SQL Server 2005 than the built-in one. See http://kb2.adobe.com/cps/421/ded4216b.html I suggest this because the connection between CF/JRUN and the database seems to be the layer where your problem is occurring. That article is a bit old so it may not point to the most up to date JDBC driver for SQL Server 05