cfqueryparam issue

[pirula08]

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

[JR__Bob__Dobbs]

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.

[pirula08]

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.

[-__cfSearching__-]

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.

[pirula08]

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.

[Dave Watts]

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/

[pirula08]

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]

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?

[pirula08]

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?

yep, in a query above (for example) if i remove cfqueryparams everything works fine.

the only possible explanation I can think of is related to the code that I have never changed! my partner  was the one who originally created the DSN connection.

as part of his strategy our application uses a real DSN name in <cfquery name="test" datasource="real DSN" while username and password are stored in Aplication.cfc!

here is the code:

Aplication.cfc

<cffunction name="setusername" access="public" returntype="string">
  <cfset username="username">
     <cfreturn username>
</cffunction>
               
<cffunction name="setpassword" access="public" returntype="string">
  <cfset password="password">
     <cfreturn password>
</cffunction>

whatever.cfm

<cfinvoke component="Application" method="setpassword" returnvariable="password">
<cfinvoke component="Application" method="setusername" returnvariable="username">

<cfquery name="topic" datasource="real DSN name" username="#username#" password="#password#">
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>

without cfqueryparams this query works perfectly!??

[Owainnorth]

I'd suggest turning on database logging (Enterprise only?) and if not taking a look at the network traffic to see what's actually being sent to the MySQL box.

[Adam Cameron.]

<cfquery name="topic" datasource="real DSN name" username="#username#" password="#password#">
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>

without cfqueryparams this query works perfectly!??

Get rid of them.  "science" and "all" are static values, so you're not gaining anything from parameterising them anyhow.

I'd also ditch the hosting provided too, though, if they're not prepared to let you sue a JDBC driver.  That's ridiculous.  If you do ask them again and they again refuse, I'd love to hear their rationalisation as to why.

--

Adam

[pirula08]

Get rid of them.  "science" and "all" are static values, so you're not gaining anything from parameterising them anyhow.

I already did

I'd also ditch the hosting provided too, though, if they're not prepared to let you sue a JDBC driver.  That's ridiculous

yeah, I know its ridiculous, the whole their support its ridiculous, at least those for CF. The only reason why I still use their service is because at the moment I can not afford better unfortunately I'm stuck with them for a few more months.

If you do ask them again and they again refuse, I'd love to hear their rationalisation as to why.

I literally spent three hours yesterday in a conversation with them, explaining my situation, but it was like I was talking to a wall !!! After a painstaking persuasion I did not accomplish anything, they of course again refused to deal with JDBC driver! As for a reasonable explanation, well as usual none was given, none whatsoever.

"luckily" (sarcasm) they provided me with a freaking list of supported drivers and gave me one of the usual CTRL-c - CTRL-v messages

"Please, address this question to a web designed or web site developers, unfortunately, the problem is outside of our support boundaries for the hosting product, however, our web design and programming team will be happy to fix it for you. It is part of the web design service we offer. Please check the details on our site at: "

[Dave Watts]

No, as a general rule, it won't work fine without hashes. At last not in normal use. In normal use, you're injecting a CF variable into SQL, more or less. If you have this:

<cfqueryparam value="science" cfsqltype="cf_sql_varchar" maxlength="45">

it will use the literal value "science". Is that really what you're trying to do?

If you're intentionally specifying a literal value, there's very little reason to use CFQUERYPARAM in the first place.

Dave Watts, CTO, Fig Leaf Software

http://www.figleaf.com/

http://training.figleaf.com/