Clicky

Hi,

I'm converting an old ASP site to Coldfusion and running into a problem with a query written by the previous developer. I've never dealt with freetextsearch or containstable in a query before.

The query throws the following error on Coldfusion but when I copy and paste the query into MSSQL Server Manager it functions properly. Do I need to format the CONTAINSTABLE parts differently in Coldfusion? I've tried wrapping the search term in ' and " with the same error resulting.

ERROR in CF: [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near 'SEARCHTERM'.
 SELECT prod.ballID, prod.title, company.companyID, company.companyName, prod.line, COUNT(review.reviewID) AS numReviews, AVG(review.pins) AS avgRating, prod.price, 1 as isCurrent, K.rank FROM review RIGHT OUTER JOIN prod INNER JOIN company ON prod.companyID = company.companyID ON review.ballID = prod.ballID INNER JOIN CONTAINSTABLE(prod,*,'SEARCHTERM') as K ON prod.ballid = K.[KEY] WHERE (prod.isCurrent = 1) GROUP BY prod.ballID, prod.title, company.companyID, company.companyName, prod.line, prod.price, k.rank UNION SELECT prod.ballID, prod.title, company.companyID, company.companyName, prod.line, COUNT(review.reviewID) AS numReviews, AVG(review.pins) AS avgRating, prod.price, 0 as isCurrent, K.rank FROM review RIGHT OUTER JOIN prod INNER JOIN company ON prod.companyID = company.companyID ON review.ballID = prod.ballID INNER JOIN CONTAINSTABLE(prod,*,'SEARCHTERM') as K ON prod.ballid = K.[KEY] WHERE (prod.isCurrent = 0) GROUP BY prod.ballID, prod.title, company.companyID, company.companyName, prod.line, prod.price, k.rank ORDER BY isCurrent DESC, K.rank DESC                             
1: 

Select allOpen in new window

asked 12/02/2011 12:01

Addicted2HD's gravatar image

Addicted2HD ♦♦


18 Answers:
(small comment. not for points... )

> Do I need to format the CONTAINSTABLE parts differently in Coldfusion?

Well CF doesn't care much about sql other than sending it to your db. But the jdbc driver used to connect to ms sql might.  

1) Have you tried enclosing the * in single quotes?  ie CONTAINSTABLE(prod,'*','SEARCHTERM')
2) Are you sure the CONTAINSTABLE statement is the problem? Error messages aren't always 100% accurate.  
link

answered

_agx_'s gravatar image

_agx_

> Well CF doesn't care much about sql other than sending it to your db. But the jdbc driver used to connect to ms sql might.

Which is why I'm surprised and stumped by the fact that the SQL runs without an error in MSSQL Server Manager.

I can't say I'm 100% certain the problem is with containstable but I'm 99% certain. Wrapping * only caused another error since it's referencing all the columns within the prod index.

The reason I'm fairly certain is the query works when there's no search term supplied by the user. The query is identical minus the containstable statement when no term is provided by the user.

I could always punt and check relevant columns in the prod table with LIKE instead of running a full text search against an index.
link

answered 2011-12-02 at 09:24:49

Addicted2HD's gravatar image

Addicted2HD

> Which is why I'm surprised and stumped by the fact that the SQL runs without an error in MSSQL Server Manager.

Well the manager tool uses a different mechanism to communicate with the db. So some differences are to be expected.  Even using different jdbc/odbc drivers can cause differences. But usually they're very minor things like reserved words and such.

> The query is identical minus the containstable statement when no term is provided by the user.

Hm... so in theory if that's the problem a simple query like this would generate the error

         SELECT * FROM prod INNER JOIN CONTAINSTABLE(prod,*,'SEARCHTERM') as K
link

answered 2011-12-02 at 09:35:46

_agx_'s gravatar image

_agx_

Actually, that returns this error:

[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near 'K'.
link

answered 2011-12-02 at 09:55:38

Addicted2HD's gravatar image

Addicted2HD

Well that may not be the exact syntax. I'm not near an ms sql instance w/full text installed.
link

answered 2011-12-02 at 10:05:51

_agx_'s gravatar image

_agx_

This works:

Select *
From prod INNER JOIN CONTAINSTABLE(prod,*,'creature') as K ON prod.prodid = K.[KEY]

Scratching my head now on what could be wrong with the other query which works in MSSQL Manager.
link

answered 2011-12-02 at 10:55:07

Addicted2HD's gravatar image

Addicted2HD

It may not be the issue, but the joins looked a little odd to me.  I'm used to seeing the ON clause directly after the JOIN.

ie
FROM review
      RIGHT OUTER JOIN prod ON review.ballID = prod.ballID
      INNER JOIN company ON prod.companyID = company.companyID

I'd say try it in steps.  Temporarily leaving out the containstable entirely. a) Try the 1st query. b) It it works add back the UNION. c) If that works, try adding back containstable in the 1st query, etc...

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
-- 1st query
SELECT 
prod.ballID
, prod.title
, company.companyID
, company.companyName
, prod.line
, COUNT(review.reviewID) AS numReviews
, AVG(review.pins) AS avgRating
, prod.price, 1 as isCurrent
, K.rank 
FROM review 
	RIGHT OUTER JOIN prod ON review.ballID = prod.ballID 
	INNER JOIN company ON prod.companyID = company.companyID 
	INNER JOIN CONTAINSTABLE(prod,*,'SEARCHTERM') as K ON prod.ballid = K.[KEY] 
WHERE (prod.isCurrent = 1) 
GROUP BY prod.ballID


link

answered 2011-12-02 at 11:38:54

_agx_'s gravatar image

_agx_

Gah... copy paste correction:

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
-- 1st query
SELECT 
prod.ballID
, prod.title
, company.companyID
, company.companyName
, prod.line
, COUNT(review.reviewID) AS numReviews
, AVG(review.pins) AS avgRating
, prod.price, 1 as isCurrent
FROM review 
	RIGHT OUTER JOIN prod ON review.ballID = prod.ballID 
	INNER JOIN company ON prod.companyID = company.companyID 
WHERE (prod.isCurrent = 1) 
GROUP BY prod.ballID
link

answered 2011-12-02 at 11:51:02

_agx_'s gravatar image

_agx_

The SQL is pieced together by <cfif> statements then just put #sql# between the cfquery tags. Mainly because this is how the logic was originally put together and I'm not comfortable changing it at this point so I just changed the ASP syntax to equiv CF syntax.

So I output the pieced together SQL before the CFQUERY call. Then I copy and paste that into a test document in a CFQUERY and dump the results. Wouldn't you know the query I pasted in my original question works fine.

Really scratching my head now! Any thoughts?
link

answered 2011-12-02 at 11:52:22

Addicted2HD's gravatar image

Addicted2HD

Generating sql dynamically is always tricky and it can be a pain to debug.  

> So I output the pieced together SQL before the CFQUERY call

I know you said you copied and pasted, but it's extremely likely something is different  Because the same sql shouldn't generate different results. How are you "piecing it together"?  What
I usually do is copy the entire cfquery into a cfsavecontent so I don't miss anything.  Then change the opening cfquery and cfqueryparam tags to the html equivalent ie  Change < to &lt;  - so the compiler doesn't complain. Then dump the string

ie  <cfsavecontent variable="myQuery">
             &lt;cfquery ....>
                    SELECT * FROM Table ..... WHERE col = &lt;cfqueryparam ....>
             &lt;/cfquery ....>
    </cfsavecontent>

   
link

answered 2011-12-02 at 12:03:49

_agx_'s gravatar image

_agx_

> I usually do is copy the entire cfquery into a cfsavecontent so I don't miss anything.

I don't quite follow that but the code is below on how I'm compiling the SQL dynamically. Do you see anything that would be causing a problem?
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
<cfset sql = "SELECT prod.ballID, prod.title, company.companyID, company.companyName, prod.line, COUNT(review.reviewID) AS numReviews, AVG(review.pins) AS avgRating, prod.price, #isCurrent# as isCurrent">

	<cfif searchstr neq "">
		<cfset sql = "#sql#, K.rank ">
	</cfif>	
		
	<cfset sql = "#sql# FROM review RIGHT OUTER JOIN prod INNER JOIN company ON prod.companyID = company.companyID ON review.ballID = prod.ballID">

	<cfif searchstr neq "">
		<cfset sql = "#sql# INNER JOIN CONTAINSTABLE(prod,*,'#searchstr#') as K ON prod.ballid = K.[KEY]">
	</cfif>			
			
	<cfset sql = "#sql# WHERE (prod.isCurrent = #isCurrent#)">
	
		
<cfif companyID neq "">
	<cfset sql = "#sql# AND company.companyID = #companyID#">
</cfif>


<cfset sql = "#sql# GROUP BY prod.ballID, prod.title, company.companyID, company.companyName, prod.line, prod.price">

<cfif searchstr neq "">
	<cfset sql = "#sql#, k.rank">
</cfif>

	<cfif review is "true">
		<cfset sql = "#sql# HAVING COUNT(review.reviewID) >= 5">
	</cfif>


<cfset sql = "#sql# UNION #replace(replace(sql,"prod.isCurrent = 1","prod.isCurrent = 0"),"1 as isCurrent","0 as isCurrent")#"> 

<cfset sql = "#sql# ORDER BY isCurrent DESC">

<cfif isDefined("url.orderby")>
	<cfswitch expression="#url.orderby#">
	<cfcase value="2">
		<cfset sql = "#sql#, prod.line, prod.price DESC, prod.title">
	</cfcase>
    <cfcase value="3">
		<cfset sql = "#sql#, prod.title">
	</cfcase>
    <cfcase value="4">
		<cfset sql = "#sql#, prod.price DESC, company.companyName, prod.line, prod.title">
	</cfcase>
    <cfcase value="5">
		<cfset sql = "#sql#, numReviews DESC, company.companyName, prod.price DESC, prod.line, prod.title">
	</cfcase>
    <cfcase value="6">
		<cfset sql = "#sql#, avgRating DESC, company.companyName, prod.price DESC, prod.line, prod.title">
    </cfcase>
	<cfdefaultcase>
		<cfset sql = "#sql#, company.companyName, prod.price DESC, prod.line, prod.title">
    </cfdefaultcase>
	</cfswitch>
<cfelseif searchstr neq "">
	<cfset sql = "#sql#, K.rank DESC">
<cfelseif review is "true">
	<cfset sql = "#sql#, avgRating DESC, numReviews DESC, company.companyName, prod.title">
<cfelse>
	<cfset sql = "#sql#, company.companyName, prod.price DESC, prod.line, prod.title">
</cfif>
link

answered 2011-12-02 at 12:15:45

Addicted2HD's gravatar image

Addicted2HD

    > <cfset sql = "#sql# AND company.companyID = #companyID#">

Oooh ... wait a minute.  Is that just for debugging or are you actually executing a big sql string like this?

   <cfquery ...>#sql#</cfquery>
link

answered 2011-12-02 at 12:26:01

_agx_'s gravatar image

_agx_

Executing. Like I said, it's how it was done in ASP years ago and I'm converting logic/testing in CF right now and will make logic updates in the next phase.

Is there a better way to do this?

The reason I've avoided it for right now is this line:

<cfset sql = "#sql# UNION #replace(replace(sql,"balls.isCurrent = 1","balls.isCurrent = 0"),"1 as isCurrent","0 as isCurrent")#">

It's a shortcut for sure. Typically I do my <cfif> between the <cfquery> tags but . . .
link

answered 2011-12-02 at 12:35:45

Addicted2HD's gravatar image

Addicted2HD

Then that's your problem.  With the rise in sql injection attacks, later versions of CF started automatically escaping any single quotes within variables because it's a common attack vector.  So while you are writing this:

      <cfset sql =  "INNER JOIN CONTAINSTABLE(prod,*,'#searchstr#')"

... once inside the cfquery the single quotes are doubled. So it's actually converted to this:

      <cfquery ...>
                INNER JOIN CONTAINSTABLE(prod,*,''#searchstr#'')   <=== 2 single quotes
      </cfquery>

The only way to avoid it is to wrap the entire thing in PreserveSingleQuotes. That prevents CF from doubling the quotes. But in practice it's a VERY bad idea because it leaves your database unprotected, just like in the old days.

         <cfquery ...>#PreserveSingleQuotes(sql)#</cfquery>

> Typically I do my <cfif> between the <cfquery> tags but . . .

Yep. I'd say convert it as soon as possible so you can start using cfif and cfqueryparam so your db won't be at risk.
link

answered 2011-12-02 at 12:39:36

_agx_'s gravatar image

_agx_

> ... once inside the cfquery the single quotes are doubled.

ie Causing a syntax error
link

answered 2011-12-02 at 12:49:01

_agx_'s gravatar image

_agx_

#replace(sql,"''","'","ALL")#

Thanks, never knew that and won't soon forget! I'm no longer driving myself crazy.
link

answered 2011-12-02 at 12:53:38

Addicted2HD's gravatar image

Addicted2HD

Yeah it's one of those gotchas that sticks with you. Sorry it took so long. If I'd known the query was using executing a string I'd have spotted it sooner ;-)
link

answered 2011-12-02 at 12:58:08

_agx_'s gravatar image

_agx_

No problem, my fault for not mentioning it. I learned a bit about the full text search while chasing my tail.
link

answered 2011-12-02 at 13:48:51

Addicted2HD's gravatar image

Addicted2HD

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×32
×1
×11

Asked: 12/02/2011 12:01

Seen: 208 times

Last updated: 12/02/2011 04:58