Limit line in select query throwing error after working for 2 years+

Report · Nov 20, 2008

I assume the webhost updated their Coldfusion software ( was CF7 ) which caused this error to start to occur, but I can't figure out what is wrong with the code below.
The error says the Limit line is the problem, looks like it's putting single quotes around the first number in the limit line for some reason, but I can't prevent this from happening even with Int().
FWIW, the limit line that worked forever was LIMIT #page*10#,10 I just tried updating it to what is below today to fix it. The error says this:
Error Executing Database Query.
Syntax error or access violation: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0',10' at line 10
212 : )
213 : ORDER BY pPartNumber
214 : LIMIT <cfqueryparam cfsqltype="int" value="#Int(page*10)#">,10
215 : </cfquery>
216 :

SQL select distinct * from products where pActive = 1 AND ( pName LIKE (param 1) OR pDescription LIKE (param 2) OR pPartNumber LIKE (param 3) ) ORDER BY pPartNumber LIMIT (param 4) ,10
DATASOURCE dsn
VENDORERRORCODE 1064
SQLSTATE 42000
Resources:

Any help is greatly appreciated, Thanks!

Report · Nov 20, 2008

> <cfqueryparam cfsqltype="varchar"
> <cfqueryparam cfsqltype="int"

It could have something to do with the fact that those are not valid cfsqltypes. CF may be ignoring the invalid values and using the default (cf_sql_char) instead.
http://livedocs.adobe.com/coldfusion/8/Tags_p-q_18.html

> <cfif ArrayLen(catChildArray) gt 0>
> OR pCatId = <cfqueryparam cfsqltype="int" value="#catChildArray[1]#">
> <cfloop from=2 to=#ArrayLen(catChildArray)-1# index=i>
> OR pCatId = <cfqueryparam cfsqltype="int" value="#catChildArray[ I ]#">
> </cfloop>
> OR pCatId = <cfqueryparam cfsqltype="int" value="#catChildArray[ArrayLen(catChildArray)]#">
> </cfif>

Why three steps? Just loop through the entire array in one shot, or better yet use a single IN clause with cfqueryparam's list attribute?

OR pCatId IN (
<cfqueryparam value="#listToArray(catChildArray)#" list="true" cfsqltype="cf_sql_integer">
)

Report · Nov 20, 2008

quote:

Originally posted by: -==cfSearching==-
Why three steps? Just loop through the entire array in one shot, or better yet use a single IN clause with cfqueryparam's list attribute?

OR pCatId IN (
<cfqueryparam value="#listToArray(catChildArray)#" list="true" cfsqltype="cf_sql_integer">
)

Don't you mean arrayToList()??

Mikey.

Report · Nov 21, 2008

> Don't you mean arrayToList()??

Yep. Fortunately the OP recognized what I really meant.

Report · Nov 20, 2008

>It could have something to do with the fact that those are not valid >cfsqltypes. CF may be ignoring the invalid values and using the default >(cf_sql_char) instead.
> http://livedocs.adobe.com/coldfusion/8/Tags_p-q_18.html

Tried that, now it throws the " Communication link failure: Unknown command" error. Cutting the loop out of the query and changing most of the query to numbers ( removing variables ) save for one or two cfqueryparam tags still brings the above error..

Googling that, I found this which seems to make sense, so I emailed the host with it
http://www.fusetalk.com/blog/blogpost.cfm?threadid=92542&catid=144

>Why three steps? Just loop through the entire array in one shot, or >better yet use a single IN clause with cfqueryparam's list attribute?

Admittedly I was very new to coldfusion when I wrote this, unfortunately it hasn't been touched since then. Thanks for your help.

Report · Nov 20, 2008

> Admittedly I was very new to coldfusion when I wrote this,
> unfortunately it hasn't been touched since then.

Ah, okay. I did not know if you were aware of the "list" option. So I thought it would not hurt to mention it.

> Tried that, now it throws the " Communication link failure: Unknown command" error.

Yes, that is a known issue. Hopefully the TechNote will do the trick.