CFQuery and Full Text Searching 1

[nicfoster]

I am using full text searches in SQL Server7 DB. All seems to work except for queries with the FORMSOF verb. I get the following error:
OLEDB Error Code = 170

Line 1: Incorrect syntax near 'FORMSOF'

I cut and paste the query into SQL Server7's Query Analyzer tool and it runs wtihout any errors. Here is the query:

SELECT DISTINCT "Name"= Name , "Price"=min_price.minprice, "Supplier"=Products.Supplier, "Sku"= SKU ,
"Image Name"= ImageName , "ProductId"=Products.Product_Id
FROM Products
INNER JOIN FullProductDescr ON FullProductDescr.Prod_Id=Products.Product_Id
INNER JOIN min_price ON min_price.productid=Products.Product_Id
INNER JOIN ProductKeyWords ON ProductKeyWords.Prod_Id=Products.Product_Id
INNER JOIN WordList ON ProductKeyWords.KeywordNbr=WordList.WordValue
INNER JOIN EASearch ON EASearch.GroupWordValue=WordList.GroupWordValue
WHERE (Contains( Name ,'FORMSOF(INFLECTIONAL,glass)')
or Contains(FullProductDescr.Descr,'FORMSOF(INFLECTIONAL,glass)'))
and min_price.minprice<40 and EASearch.CategoryID in (100, 101, 102)
ORDER BY Products.Supplier, Products.Product_Id

Thank you for your help>

Nic

 

[ram123]

Does it SQL2 syntax? I think CF doesn't support sql2 syntax.

 

[GunJack]

I'm not real familiar with what you're doing but if it works in the query analyzer but doesn't work through CF, it may be that your ODBC drivers don't support the syntax. I would check to see if you have the latest ODBC drivers.

Just a thought,
GJ

 

[nicfoster]

ram123, if you mean SQL92 standard, I would think so. I use cfquery with all of my SQL Server7 queries and stored procedures.

GunJack, I am not using ODBC connections. I use OLEDB connections and we are running the MDAC 2.51. I think it has to do with the single quote marks not being parsed by CF correctly. I have tried escaping them and taking them out to no avail. Thanks
Nic

 

[GunJack]

Is this a dynamic query like <cfquery...>#sqlSyntax#</cfquery>? If so, CF will escape single quotes. You can get around it with #preservesinglequotes(sqlSyntax)# but you need to make sure all other single quotes are properly escaped.

GJ

 

[nicfoster]

The solution turns out to be that the CF implementation of SQLOLEDB provider does not support some SQL functions - FORMSOF being one of them. They suggested using a DSN and ODBC connection. I did and it worked.

Thanks for the help.

Nic