Query results are not expected
I have been a CF developer for 15 years and have never come across anything weird or frustrating. I pulled my hair out for hours, searched for text, selected, simplified, prayed, and did the opposite. Can you help me?
Cffunction takes one string argument, and from that string, I build an array of "phrases" to run a query trying to match a location name in my database. For example, the string "boulder republic" will create an array: "republic", "boulder", "republic", "republic", "boulder republic", "republic", "boulder republic", "boulder"].
Another function uses the aforementioned cffunction and executes cfquery. A query based on the previous example would be ...
select locationid, locationname, locationaliasname from vwLocationsWithAlias where LocationName is in ('the', 'republic', 'republic', 'republic', 'republic', 'boulder republic', 'of', 'boulder', 'boulder' ) or "LocationAliasName" ("the", "republic", "republic", republic "," republic "," boulder "," boulder ", 'boulder')
This returns 2 records ...
locationid - locationname - locationalias
99 - Boulder - Republic
68 - 'Boulder' - NULL
It's good. Works great and dandy. HOWEVER ... if the string is changed to "republic", the result is array ["the", "republic", "republic"], which is then used to create a query ...
select locationid, locationname, locationaliasname from vwLocationsWithAlias where LocationName is in ('the', 'republic', 'republic') or LocationAliasName is in ('the', 'republic', 'republic')
This returns 0 records. What?! Okay, just to make sure I'm unwittingly HIGH. I am running the same query in my SQL console against the same database in the cf data source. 1 RECORD!
locationid - locationname - locationalias
99 - Boulder - Republic
I can even hard code this sql within the same element and get the same result, but never from dynamically generated SQL. I can get my location phrases from another cffunction of another name that returns the values of a hardcoded array, and they work, but never if the array is dynamically constructed. I tried to remove cfqueryparams, triple validation of my datatypes, datasource settings, etc. Etc. Etc. NO DICE
WTF !? Is this an incomprehensible error? Am I losing my mind? I've tried everything I can think of and others (including Ray Camden) might think.
ColdFusion 8 (with all the latest patches) SQL Server 2005 (with the largest service packs) Windows 2003 Server (with all the latest updates, service packs and MS voodoo nightly)
a source to share
It seems to me that your function is generating a slightly different query than you expect, try to put a breakpoint in your function after generating the query, copy the generated query and run it on the sql server. I expect no results to be found.
There may be multiple spaces or something that does not result in the detection of records. Notes. You are looking for exact strings . To search for strings nearby, use as in your queries.
a source to share
Are you using CF 8 or CF 8.01? Upgrade to CF 8.01 if you're not already on it.
You can also try loading a different SQL Server 2005 JDBC driver than the built-in one. See http://kb2.adobe.com/cps/421/ded4216b.html I suggest this because the connection between the CF / JRUN and the database seems to be the layer where your problem occurs. This article is a little out of date, so it cannot point to the most up-to-date JDBC driver for SQL Server 05
a source to share