Skip to main content
P
pirula08
Inspiring
March 30, 2011
Question

cfqueryparam issue

  • March 30, 2011
  • 3 replies
  • 1729 views

I'm getting the following error when i put matching (varchar  =  cf_sql_varchar etc.) cfqueryparam tag within cfquery tag that uses order by clause!

Error Executing Database Query.

Communication link failure: Unknown command

As I can see and judging by Adobe's livedocs documentation, cfqueryparam tag can not be used side by side with the order by clause !??

<cfquery name="topic" datasource="datasource">
SELECT title, desc, img
FROM topic
WHERE CAT = <cfqueryparam value="science" cfsqltype="cf_sql_varchar" maxlength="45"> OR CAT = <cfqueryparam value="All" cfsqltype="cf_sql_varchar" maxlength="45">
ORDER BY DATE DESC LIMIT 10
</cfquery>

I am running CF MX 7.0.2, MySQL 5.0.41 and MySQL ODBC 3.51 Driver

    This topic has been closed for replies.

    3 replies

    D
    Community Expert
    Dave WattsCommunity Expert
    Community Expert
    March 30, 2011

    Unless you have a typo in your example, you're using the literal value "science" in your CFQUERYPARAM. Wrap the value in hashes:

    <cfqueryparam value="#science#" ...>

    Dave Watts, CTO, Fig Leaf Software

    http://www.figleaf.com/

    http://training.figleaf.com/

    Dave Watts, Eidolon LLC
    P
    pirula08Author
    Inspiring
    March 31, 2011

    Wrap the value in hashes

    there is no need to wrap value in hashes, it should easily work without it! Anyway even if hashes are added in any query including "#form.whatever#", the problem still persists.

    Owainnorth
    Owainnorth
    Inspiring
    March 31, 2011

    I think Dave was just pointing a potential issue that you may have spotted, he was not suggesting it would fix your problem.

    Try putting the values as hardcoded string and running the query. This works? And you're saying that by changing simply to queryparams it doesn't work?

    -
    -__cfSearching__-
    Inspiring
    March 30, 2011

    SELECT title, desc, img

    DESC is a reserved keyword in most (if not all) databases. Try escaping it with back ticks (mySQL). If that resolves the issue, you should rename the column if possible.

    P
    pirula08Author
    Inspiring
    March 30, 2011

    DESC is a reserved keyword in most (if not all) databases. Try escaping it with back ticks (mySQL).

    nope it's not that, this is just an example query, the issue occurs every time in whatever query when the order by clause is involved.

    J
    JR__Bob__Dobbs
    Inspiring
    March 30, 2011

    In my experience CFQUERYPARAM can be used in queries which contain an ORDER BY clause.  I suspect your error is related to a bug either in mySQL or the database driver.  Try using a JDBC driver rather than the ODBC driver.

    P
    pirula08Author
    Inspiring
    March 30, 2011

    I wish I could use JDBC instead of ODBC driver but my hosting company made it clear a year ago that they simply do not want to instal the JDBC driver. I'm gonna ask again.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices