Skip to main content
R
rmurdoch
Participant
November 20, 2008
Question

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

  • November 20, 2008
  • 3 replies
  • 751 views
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!
    This topic has been closed for replies.

    3 replies

    -
    -__cfSearching__-
    Inspiring
    November 21, 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.
    R
    rmurdochAuthor
    Participant
    November 21, 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.
    -
    -__cfSearching__-
    Inspiring
    November 21, 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">
    )

    A
    Anonymous
    November 21, 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.

    -
    -__cfSearching__-
    Inspiring
    November 21, 2008
    > Don't you mean arrayToList()??

    Yep. Fortunately the OP recognized what I really meant.
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices