11 Replies Latest reply on Jan 16, 2007 10:16 AM by BKBK

    When is a query not a query?

    town
      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!)
        • 1. Re: When is a query not a query?
          Dan Bracuk Level 5
          This is very strange. If you turn on de-bugging, can you see the sql?
          • 2. Re: When is a query not a query?
            town Level 1
            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 :(

            • 3. Re: When is a query not a query?
              MikerRoo Level 1
              Yes, the odbc drivers blow chunks and really shouldn't be used with MX7.

              Change the datasource to use CF's native JDBC driver.
              • 4. When is a query not a query?
                town Level 1
                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...
                • 5. Re: When is a query not a query?
                  BKBK Adobe Community Professional & MVP
                  #DataSourceNumberTwo# ..employees e

                  Are the two dots there by choice or by accident?

                  • 6. Re: When is a query not a query?
                    town Level 1
                    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... :/
                    • 7. When is a query not a query?
                      BKBK Adobe Community Professional & MVP
                      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?
                      • 8. Re: When is a query not a query?
                        town Level 1
                        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' :(
                        • 9. When is a query not a query?
                          BKBK Adobe Community Professional & MVP
                          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.




                          • 10. When is a query not a query?
                            town Level 1
                            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! :D
                            • 11. Re: When is a query not a query?
                              BKBK Adobe Community Professional & MVP
                              Cheers.