• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

When is a query not a query?

Community Beginner ,
Jan 12, 2007 Jan 12, 2007

Copy link to clipboard

Copied

I've been tasked with amending a site written by someone else, and i've got a very strange problem with a particular query.
When i run the query in Query Analyser (replacing the variables with their corresponding values) it all works fine, and even copy-pasting it out of Profiler and into Analyser works fine, but running it in the CF page produces weird results:

It claims not to be a query object, but has a recordcount property

When i cfdump it, i get "1"

Accessing SelectForSend.article_id works fine as a single value (the correct value of the first row returned) but attempting to convert that using ValueList() produces: The column article_id is not present in the query named SelectForSend. It is likely that you have misspelled the name of the column.

Attempting to use the query in a loop or output gives me Attribute validation error for tag cfloop.
The value of the attribute query, which is currently "SelectForSend", is invalid.


This all works fine on the live site on its CF5 server, but not on my testing server on MX7.

I've attached the offending query here, if anyone has any pointers on this it would be very much appreciated!! Example values for the vars in the query are as follows:

SelectSubscribers.EID is a string equal to the user's username
maxId is a int (1750, for example)

(Apologies for the formatting, or lack thereof!)
TOPICS
Advanced techniques

Views

618

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Community Beginner , Jan 16, 2007 Jan 16, 2007
UPDATE: I've fixed it - the query was being run within a loop, which was looping over another query from further up selecting users to query with (the SelectSubscribers query). The query had been done to select *, and it turns out that the users table had a field called SelectForSend in it as well!!

Moral of the story: Never use select * in your SQL queries!! (and probably also 'post the entire page if you post a code sample'...)

Cheers for your persistence with this BKBK - if it wasn't for te...

Votes

Translate

Translate
LEGEND ,
Jan 12, 2007 Jan 12, 2007

Copy link to clipboard

Copied

This is very strange. If you turn on de-bugging, can you see the sql?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Jan 12, 2007 Jan 12, 2007

Copy link to clipboard

Copied

My thoughts precisely Dan! :)

It's all at work and i'm at home now so i can't test over the weekend. The SQL that's going to the database appears to be fine though as i've picked the final query out using SQL Profiler, put it in Query Analyser and it runs fine, returning the recordset as it should....

It's all just very weird - the only thing i can think is that there's some discrepancy in the odbc drivers between CF5 and MX7, and that this query is falling over because of that. Other queries in the same page run fine :(

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advisor ,
Jan 12, 2007 Jan 12, 2007

Copy link to clipboard

Copied

Yes, the odbc drivers blow chunks and really shouldn't be used with MX7.

Change the datasource to use CF's native JDBC driver.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Jan 15, 2007 Jan 15, 2007

Copy link to clipboard

Copied

Cheers for that technical analysis MikerRoo ;)

The dbtype attribute of cfquery doesn't have any way to explicitly select JDBC, and nor does the datasource in administrator. I'm guessing therefore that it'll use JDBC by default, which doesn't work either!

Cheers for your help anyway, time's running out on this so i'll probably just work around it with a non-working version of the site. If i do happen to figure out a solution i'll post it here...

Edit: The query runs fine on a page on its own within the same application... it just gets stranger! Must be something within the page that's throwing it off...

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 15, 2007 Jan 15, 2007

Copy link to clipboard

Copied

#DataSourceNumberTwo# ..employees e

Are the two dots there by choice or by accident?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 15, 2007 Jan 15, 2007

Copy link to clipboard

Copied

a way to reference another database on the same sql server
why not just db1.table1, db2.table2, etc., with one dot instead of two? Also, though you speak of referencing another database, the value evaluated seems to be a datasource, namely, DataSourceNumberTwo. Is there any confusion there?

Does it help to add the aliases f, e and a, thus, f.filt_article_code , f.filt_area_code, f.filt_topic_code, GROUP BY e.Eid, a.Article_ID?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Jan 15, 2007 Jan 15, 2007

Copy link to clipboard

Copied

BKBK - they're there by choice. I haven't used it before myself but it's a way to reference another database on the same sql server. That bit works fine on the separate page... 😕

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Jan 16, 2007 Jan 16, 2007

Copy link to clipboard

Copied

Mainly due to the reason that db1.table1 doesn't work ;)

As far as i can see, the .. syntax is basically saying "i don't know who the database owner is but see if you can connect anyway..." the normal syntax would be db1.owner.table1. #DataSourceNumberTwo# is just a string that holds the name of the database to connect to.

I tried qualifying the article_ fields but to no avail. Cheers for your help anyway, i think i might just have to file this under 'anomalies' 😞

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 16, 2007 Jan 16, 2007

Copy link to clipboard

Copied

What happens if you leave out the attribute dbType="odbc"? You might be having the problem because it is one of Coldfusion's deprecated attributes.




Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Jan 16, 2007 Jan 16, 2007

Copy link to clipboard

Copied

UPDATE: I've fixed it - the query was being run within a loop, which was looping over another query from further up selecting users to query with (the SelectSubscribers query). The query had been done to select *, and it turns out that the users table had a field called SelectForSend in it as well!!

Moral of the story: Never use select * in your SQL queries!! (and probably also 'post the entire page if you post a code sample'...)

Cheers for your persistence with this BKBK - if it wasn't for testing out your suggestions i would have given up a long time ago and moved on! 😄

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 16, 2007 Jan 16, 2007

Copy link to clipboard

Copied

LATEST
Cheers.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation