ANNOYING ERROR USING CFQUERY

[drforbin1970]

Have a SQL Query that works fine in Query Analyzer and as a stored procedure.

But when I insert the code between two CFQUERY tags for testing, I get the following message:

The value of the attribute query, which is currently "my_query", is invalid.

The query is returning a result set. Stranger even is it WORKS on one system where the CF connection to the database is a SQL Server connection in CF Admin.

Where it doesn't work is on another system where the connection to the DB is via ODBC set up under Windows Control Panel / Administrative / ODBC / etc.

Both databases, tables, etc. are identical on each system. So are logins, user rights, etc.

The ODBC connection is working, I can execute a simple SELECT * FROM my_table with no problems.

Both versions of SQL and CF are identical also. The only difference is how they connect to their DBs.

When I convert the code to a stored proc, it works fine. But sometimes I just want to copy it over from QA and run it in my template(s).

I haven't included the code since it's quite involved. But it passes syntax check and executes successfully as stated above, except for the platform with ODBC connections.

One possible thought is how / if CF caches table definitions?

Any ideas?

[Guest]

Is the name of the query literally "my_query"? Or is that an example? Could you be using a reserved word that chokes the ODBC driver? Are you using the Microsoft SQL Server ODBC driver or another flavor?

[drforbin1970]

"my_query" is an example, I thought of the reserved word problem and changed query name a few times, same result.

I'm using the SQL Server ODBC driver.

After some research on net, seems the problem could be either CF or the DB caching the table definition. So when I update the table definition with say, a new column, and run the query, the new column is not in the cached version. Suggestions are to restart SQL Server or JRun. I've restarted all CF services but that did not help.

Thanks for your suggestions!

quote:

---

Is the name of the query literally "my_query"? Or is that an example? Could you be using a reserved word that chokes the ODBC driver? Are you using the Microsoft SQL Server ODBC driver or another flavor?

---

[Fernis]

I'm sorry to make you repeat yourself, but let's verify:

Do you have any nested queries on your .cfm page?
Are you referring to any values of your my_query, like a=my_query.somevalue ?
Which ColdFusion version are you using - Exactly the same on both systems?

If you answered YES to either of the first two questions, there might be light in the end of the tube. If, however, your .cfm page has _nothing_ except the query itself surrounded by the <cfquery> tags, then I can't help you right away...

[drforbin1970]

No nested queries.
No a=my_query.somevalue
Just <cfoutput query="my_query">

Doing research it's pointing towards query caching in CF. I'm fiddling with the cachedAfter attribute in CFQUERY to see if I can disable query caching. Also checked query caching settings in CF Admin. I'll post more when I find the answer.

Thanks for the help. Keep it comin' if you have more

quote:

---

Originally posted by: Fernis
I'm sorry to make you repeat yourself, but let's verify:

Do you have any nested queries on your .cfm page?
Are you referring to any values of your my_query, like a=my_query.somevalue ?
Which ColdFusion version are you using - Exactly the same on both systems?

If you answered YES to either of the first two questions, there might be light in the end of the tube. If, however, your .cfm page has _nothing_ except the query itself surrounded by the <cfquery> tags, then I can't help you right away...

---

[JMGibson3]

"I haven't included the code since it's quite involved" seems a bit of a give away. I can almost guarantee it's not caching. CF itself doesn't cache anything unless you use the cachedwithin parameter. I think you may be misinterpreting your Googling.

I can almost guarantee it IS ODBC vs. JDBC. Driver differences are REAL. Usually they are keyword related, ie, the DB Engine allows a keyword but the boneheads who wrote the driver decided to suppress keywords in an attempt to call himself "ANSI" compliant. But it could actually be anything ("quite involved"). I've come across cases where for no discernable reason the answer was to ALIAS or NOT ALIAS a field. Or it'll allow subselects in the fields but not in the WHERE. The IN clause can be problematic. Problem is this has happened so many times in the past I can't recall if it was SQLServer, Oracle, or Access. Post the code, and the full error message, and maybe someone will spot something that rings a bell. One thing you could try meanwhile is placing brackets, [xxx.xxx] around anything that remotely resembles a keyword and making sure all fields are fully qualified and aliased as needed.

[Fernis]

What does #isQuery(my_query)# right before the <cfoutput> say?
What does <cfdump var="#my_qurey#"> say about the variable type?

[Stefan_K_]

Looks like my_query is not a query object.

<cfdump var="#GetMetaData(my_query)#">

and compare that to cfdump-GetMetaData() of a query that is known to work !

[drforbin1970]

Thanks for all the suggestions from everyone. I looked into query caching in CF and added the cachedwithin="0". No difference but useful for future reference.

There was one change I made that did solve the problem. The query had a SQL comment as the first line:

ex. --QUARTERLY REPORT

This executed successfully in Query Analyzer and as a stored procedure. But when pasted between <CFQUERY> tags, the query bombed as if there was no query executed in the CF template. When I removed the comment and live SQL was the first line, the query executed successfully. I re-tested a few times and was able to duplicate the error.

Though it looks like that was the culprit, I'm still going to check a few other leads to make sure.

And yea, I know if I posted the code, that would have helped, but my current job frowns on posting code outside of the network.

Thanks to all!

[JMGibson3]

Note to self, YADD (yet another Driver Difference): Comment lines.