1 2 Previous Next 43 Replies Latest reply on Feb 4, 2011 3:52 AM by CFCoder2000

    cfquery object undefined

    Chiwi8888

      Hey Everyone

       

      I am experience something really unsual with CF9 connecting to MS SQL 2005 using the "Microsoft SQL Server" driver provided.

       

      Heres the code:

       

      <cfquery name="test1" datasource="#dsn#">
      SELECT DISTINCT 
      b.ID AS brandid, p.ID, p.D_ErfassungsDatum, p.N_Preis, p.N_KundenVPE, p.C_Lead_D AS C_Lead, p.C_Titel_D AS C_Titel, b.C_Titel AS BrandTitle, ca.b_showPKImages
      
      FROM Products AS p INNER JOIN
      Brands AS b ON p.BrandsID = b.ID INNER JOIN
      product_categories AS c ON c.ProductsID = p.ID INNER JOIN
      Categories AS ca ON ca.ID = c.CategoriesCatID INNER JOIN
      Stock AS st ON st.ProductsID = p.ID INNER JOIN
      Sizes AS sz ON sz.ID = st.SizesID INNER JOIN
      SizeGroups AS sg ON sg.ID = sz.SizeGroupsID LEFT OUTER JOIN
      OrderDetails AS od ON od.StockID = st.ID AND od.N_Anzahl > 0
      
      WHERE (p.C_Sex LIKE '%f%') AND (ca.n_mandant = 1)
      AND EXISTS
           (SELECT     ID
           FROM          Ausverkauf_ProductColors AS a_p
           WHERE      (ID_Products = p.ID))
      AND (ca.B_Kind = 1) AND (p.ID IN (10258, 10261))
      
      GROUP BY b.ID, p.ID, p.D_ErfassungsDatum, p.N_Preis, p.C_Lead_D, p.C_Titel_D, b.C_Titel, st.N_Lagerbestand, st.n_reserviert, p.N_KundenVPE, st.B_Rest, ca.b_showPKImages
      
      HAVING      ((st.N_Lagerbestand + st.n_reserviert) / p.N_KundenVPE - ISNULL(SUM(od.N_Anzahl), 0) >= 1) OR (st.B_Rest = 0)
      
      ORDER BY C_Titel, p.ID
      </cfquery>
      
      <cfdump var="#test1#">
      

       

      Then I get an error for the cfdump:  Variable TEST1 is undefined.

       

      Which is weird because I was pretty sure that the query object is always available?  I haven't found anything in the CF documentation to say otherwise.

       

      The SQL code is valid and executes fine in MS SQL Management Studio.

       

      The SQL statement at the moment returns no rows, which is all correct and fine.  What is really odd though is; when I change the DB contents so that the exact same statement returns rows, the the cfdump works fine.

       

      Another way to get it working regardless if rows are returned or not, is to add "and p.b_activ = 1" to the where clause.  But this doesn't give me the results I am after, and isn't exactly solving the problem anyway.

       

      Have I missed something here?  I can't think of anything else but this being a bug either in CF or the supplied DB driver.

       

      Thanks for your help in advance.

       

      Cheers

        • 1. Re: cfquery object undefined
          Dan Bracuk Level 5

          Does the debugging information give any indication that the query ran?

          • 2. Re: cfquery object undefined
            Chiwi8888 Level 1

            Dan Bracuk wrote:

             

            Does the debugging information give any indication that the query ran?

            Hi Dan

             

            The query is running, or at least its getting to the DB server.  Using the SQL Server Profiler I can see the sql statement being run.  Any more ideas?

             

            Cheers

            • 3. Re: cfquery object undefined
              Chiwi8888 Level 1

              Just an update....

               

              I have tried using the result attribute from the cfquery tag:

               

              <cfquery name="test1" datasource="#dsn#" result="test1result">
              

               

              A cfdump of "test1result" works fine, I get to see the execution time, record count and the sql statement.

               

              So this is really weird, the statement is run on the db server, no exception thrown by cfquery, with cfdump "test1result" is defined BUT "test1" is not.

               

              I also seem to be not the only person to of come across this problem, see user comments in the cf9 livedocs for cfquery: http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7f ae.html

              • 4. Re: cfquery object undefined
                Adam Cameron. Level 5

                Back to what Dan was asking... whast does the DEBUG (the CF debug) info say?

                 

                Is the <cfdump> immediately after the <cfquery> tag?

                 

                Is the query perhaps in a CFC, and is the query variable not VARed?

                 

                --

                Adam

                • 5. Re: cfquery object undefined
                  Chiwi8888 Level 1

                  A Cameron wrote:

                   

                  Back to what Dan was asking... whast does the DEBUG (the CF debug) info say?

                   

                  Is the <cfdump> immediately after the <cfquery> tag?

                   

                  Is the query perhaps in a CFC, and is the query variable not VARed?

                   

                  --

                  Adam

                  Hi Adam

                   

                  The code is exactly like I posted, in its own .cfm file.

                   

                  When you say CF debug do you mean the debugging output with stack trace?

                   

                  Cheers

                   

                  Variable TEST1 is undefined.

                  The error occurred in C:\web\development\trunk\test.cfm: line 28
                  26 : </cfquery>
                  27 :
                  28 : <cfdump var="#test1#">
                  29 :

                  Resources:

                   

                  Browser  Mozilla/5.0 (Windows; U; Windows NT 6.1; en-GB; rv:1.9.1.7) Gecko/20091221 Firefox/3.5.7
                  Remote Address  ::1
                  Referrer 
                  Date/Time  16-Feb-10 09:08 PM

                   

                  Stack Trace
                  at cftest2ecfm318568091.runPage(C:\web\development\trunk\test.cfm:28)                 

                  coldfusion.runtime.UndefinedVariableException: Variable TEST1 is undefined.
                       at coldfusion.runtime.CfJspPage._get(CfJspPage.java:377)
                       at coldfusion.runtime.CfJspPage._get(CfJspPage.java:339)
                       at coldfusion.runtime.CfJspPage._autoscalarize(CfJspPage.java:1447)
                       at cftest2ecfm318568091.runPage(C:\web\development\trunk\test.cfm:28)
                       at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:231)
                       at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:416)
                       at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65)
                       at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:363)
                       at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:48)
                       at coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:40)
                       at coldfusion.filter.PathFilter.invoke(PathFilter.java:87)
                       at coldfusion.filter.LicenseFilter.invoke(LicenseFilter.java:27)
                       at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:70)
                       at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:28)
                       at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38)
                       at coldfusion.filter.NoCacheFilter.invoke(NoCacheFilter.java:46)
                       at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38)
                       at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22)
                       at coldfusion.filter.CachingFilter.invoke(CachingFilter.java:53)
                       at coldfusion.CfmServlet.service(CfmServlet.java:200)
                       at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:89)
                       at jrun.servlet.FilterChain.doFilter(FilterChain.java:86)
                       at coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:42)
                       at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:46)
                       at jrun.servlet.FilterChain.doFilter(FilterChain.java:94)
                       at jrun.servlet.FilterChain.service(FilterChain.java:101)
                       at jrun.servlet.ServletInvoker.invoke(ServletInvoker.java:106)
                       at jrun.servlet.JRunInvokerChain.invokeNext(JRunInvokerChain.java:42)
                       at jrun.servlet.JRunRequestDispatcher.invoke(JRunRequestDispatcher.java:286)
                       at jrun.servlet.ServletEngineService.dispatch(ServletEngineService.java:543)
                       at jrun.servlet.jrpp.JRunProxyService.invokeRunnable(JRunProxyService.java:203)
                       at jrunx.scheduler.ThreadPool$DownstreamMetrics.invokeRunnable(ThreadPool.java:320)
                       at jrunx.scheduler.ThreadPool$ThreadThrottle.invokeRunnable(ThreadPool.java:428)
                       at jrunx.scheduler.ThreadPool$UpstreamMetrics.invokeRunnable(ThreadPool.java:266)
                       at jrunx.scheduler.WorkerThread.run(WorkerThread.java:66)
                  • 6. Re: cfquery object undefined
                    Dan Bracuk Level 5

                    Regarding

                    When you say CF debug do you mean the debugging output with stack trace?

                     

                    Right church, wrong pew.  If the query ran, you should be able to see the sql that went to the db, and the recordcount, and maybe the execution time.  However, since you were able to dump the result variable, something is amiss.

                     

                    I assume that when you said exactly, there is no if/else logic separating the query from the dump.

                    • 7. Re: cfquery object undefined
                      Adam Cameron. Level 5

                      The code is exactly like I posted, in its own .cfm file.

                       

                      Weird!

                       

                       

                      When you say CF debug do you mean the debugging output with stack trace?

                       


                      No, I mean when you switch CF debugging on, it will output all DB activity.  What does it show about that particular query.

                       

                      How far can you simplify that query and still have the issue (ie: rip out some of the joined tables, etc).  It'd be good to get a slightly more wieldy replication case to take to Adobe.  It sounds like a bug to me.

                       

                      If you can get it down to a minimum number of tables, could you post the table-create queries and queries to populate same with sample data which will create enough of a DB so as to be able to try to replicate this out of your own environment.

                       

                      --

                      Adam

                      • 8. Re: cfquery object undefined
                        Chiwi8888 Level 1

                        I assume that when you said exactly, there is no if/else logic separating the query from the dump.

                        Yep exactly the same, no other code what so ever.

                        • 9. Re: cfquery object undefined
                          Chiwi8888 Level 1

                          Hi Adam

                           

                          First to answer your Q's:

                           

                          Now I understand what debuggin info you mean:

                           

                          SQL Queries

                           

                          test1 (Datasource=dev_underwearsql, Time=5ms, Records=0) in C:\web\development\trunk\test.cfm @ 21:43:49.049

                           

                           

                          SELECT DISTINCT 
                          b.ID AS brandid, p.ID, p.D_ErfassungsDatum, p.N_Preis, p.N_KundenVPE, p.C_Lead_D AS C_Lead, p.C_Titel_D AS C_Titel, b.C_Titel AS BrandTitle, ca.b_showPKImages

                          FROM Products AS p INNER JOIN
                          Brands AS b ON p.BrandsID = b.ID INNER JOIN
                          product_categories AS c ON c.ProductsID = p.ID INNER JOIN
                          Categories AS ca ON ca.ID = c.CategoriesCatID INNER JOIN
                          Stock AS st ON st.ProductsID = p.ID INNER JOIN
                          Sizes AS sz ON sz.ID = st.SizesID INNER JOIN
                          SizeGroups AS sg ON sg.ID = sz.SizeGroupsID LEFT OUTER JOIN
                          OrderDetails AS od ON od.StockID = st.ID AND od.N_Anzahl > 0

                          WHERE (p.C_Sex LIKE '%f%') AND (ca.n_mandant = 1)
                          AND EXISTS
                               (SELECT     ID
                               FROM          Ausverkauf_ProductColors AS a_p
                               WHERE      (ID_Products = p.ID))
                          AND (ca.B_Kind = 1) AND (p.ID IN (10258, 10261))

                          GROUP BY b.ID, p.ID, p.D_ErfassungsDatum, p.N_Preis, p.C_Lead_D, p.C_Titel_D, b.C_Titel, st.N_Lagerbestand, st.n_reserviert, p.N_KundenVPE, st.B_Rest, ca.b_showPKImages

                          HAVING      ((st.N_Lagerbestand + st.n_reserviert) / p.N_KundenVPE - ISNULL(SUM(od.N_Anzahl), 0) >= 1) OR (st.B_Rest = 0)

                          ORDER BY C_Titel, p.ID

                           

                          So everything ran fine on the db server, and the query came back, its just that the cfquery name is undefined.

                           

                          I will try to put a demo together like you mentioned, although it might be a week or so.  I have heaps of work at the moment and I am moving on the weekend.

                           

                          I might try using a ODBC socket connection, to see if its a DB driver or CF problem.  But there is no way we could afford to switch to a different driver with all the testing involved.

                           

                          Thanks for your help.  If you have any other ideas until I have a demo, im all ears.

                           

                          Cheers

                          • 10. Re: cfquery object undefined
                            Adam Cameron. Level 5

                            By the name of the DSN, this is on a dev box.  Restarting CF doesn't help?

                             

                            Yeah, I know the repro case is gonna be hard to contrive.  If poss it'd be good to sort it out without you going to that hassle.

                             

                            I'm short of ideas though.

                             

                            --

                            Adam

                            • 11. Re: cfquery object undefined
                              Chiwi8888 Level 1

                              A Cameron wrote:

                               

                              By the name of the DSN, this is on a dev box.  Restarting CF doesn't help?

                               

                              Yeah, I know the repro case is gonna be hard to contrive.  If poss it'd be good to sort it out without you going to that hassle.

                               

                              I'm short of ideas though.

                               

                              --

                              Adam

                              Hi Adam

                               

                              Yeah its on a dev box.  I just restarted CF, same problem.

                               

                              I also just FTP'd the file to our staging and production installations, same problem.  All the platforms pretty exact config wise, so I would be suprised if the issue was there.

                               

                              With a ODBC socket connection to the native MS SQL driver, it works but we are pretty much committed to using the driver installed with CF and can't afford to switch.

                               

                              Thanks again.  Cheers

                               

                              Duncan

                              • 12. Re: cfquery object undefined
                                Adam Cameron. Level 5

                                OK.  Maybe try the DataDirect drivers that shipped with CF8 rather than CF9 then.  Just to see if there's any difference...

                                 

                                --

                                Adam

                                • 13. Re: cfquery object undefined
                                  Dan Bracuk Level 5

                                  What happens if you do this?

                                   

                                  <cfset x = QueryNew("a")>

                                  <cfdump var = "#x#">

                                  • 14. Re: cfquery object undefined
                                    Chiwi8888 Level 1

                                    Hi Dan

                                     

                                    If i run that code, i get a CFDUMP with the column name "A" and no rows.  Just as I would expect.  Or did I misunderstand you?

                                     

                                    Cheers

                                    • 15. Re: cfquery object undefined
                                      Dan Bracuk Level 5

                                      You understood me.  The purpose was to verify that you could actually cfdump a query.  Are there any other datasources available that you can try?  A simple query like

                                       

                                      select count(*) x

                                      from sometable

                                      where 1 =3

                                       

                                      would work.

                                      • 16. Re: cfquery object undefined
                                        Chiwi8888 Level 1

                                        Thanks for the ideas Dan.

                                         

                                        I just replaced the WHERE statement with "WHERE 1=0", so the sql is:

                                         

                                        SELECT DISTINCT 
                                        b.ID AS brandid, p.ID, p.D_ErfassungsDatum, p.N_Preis, p.N_KundenVPE, p.C_Lead_D AS C_Lead, p.C_Titel_D AS C_Titel, b.C_Titel AS BrandTitle, ca.b_showPKImages
                                        
                                        FROM Products AS p INNER JOIN
                                        Brands AS b ON p.BrandsID = b.ID INNER JOIN
                                        product_categories AS c ON c.ProductsID = p.ID INNER JOIN
                                        Categories AS ca ON ca.ID = c.CategoriesCatID INNER JOIN
                                        Stock AS st ON st.ProductsID = p.ID INNER JOIN
                                        Sizes AS sz ON sz.ID = st.SizesID INNER JOIN
                                        SizeGroups AS sg ON sg.ID = sz.SizeGroupsID LEFT OUTER JOIN
                                        OrderDetails AS od ON od.StockID = st.ID AND od.N_Anzahl > 0
                                        
                                        WHERE 1=0
                                        
                                        GROUP BY b.ID, p.ID, p.D_ErfassungsDatum, p.N_Preis, p.C_Lead_D, p.C_Titel_D, b.C_Titel, st.N_Lagerbestand, st.n_reserviert, p.N_KundenVPE, st.B_Rest, ca.b_showPKImages
                                        
                                        HAVING      ((st.N_Lagerbestand + st.n_reserviert) / p.N_KundenVPE - ISNULL(SUM(od.N_Anzahl), 0) >= 1) OR (st.B_Rest = 0)
                                        
                                        ORDER BY C_Titel, p.ID
                                        

                                         

                                        The CFDUMP will work in this case.

                                         

                                        So it seems something is going crazy because of the more complicated WHERE statement, and only when no rows are returned.  Its so odd, it just has to be a bug.

                                         

                                        Cheers

                                        • 17. Re: cfquery object undefined
                                          mattmccabe2004

                                          Was curious if a solution was ever found for this. We are currently upgrading to CF9 and I have also experienced this problem where the query object is undefined when the query returns 0 results. Database is also MS SQL 2005.

                                          • 18. Re: cfquery object undefined
                                            Chiwi8888 Level 1

                                            Hi Matt

                                             

                                            No a solution has not been found yet.  I have not had the oppurtunity to compile a bug report yet to submit to Adobe.  I recently upgraded a ecommerce site from CF 5 to 9 and its been amazing how many little bugs that popped up once going live, despite the amount of testing.  So I am pretty busy with that.

                                             

                                            It would be great though if you could also share some examples of queries that have the same problem, they may help us find a solution and/or Adobe getting a bug fix done.

                                             

                                            You might find a workaround helpful if the problem is only caused on a few select statments:  I just added a isdefined check in the same cfif statement the call to .recordcount was in.

                                             

                                            But its only a work around and the problem needs a real solution.

                                             

                                            Cheers

                                            • 19. Re: cfquery object undefined
                                              Adam Cameron. Level 5

                                              Did you try my suggestion of trying the CF8 DB drivers instead of the CF9 ones?

                                               

                                              --

                                              Adam

                                              • 20. Re: cfquery object undefined
                                                Chiwi8888 Level 1

                                                No I didn't get around to it last week, but made the time just now.

                                                 

                                                It all works fine using the CF8 drivers, so this must be DB driver issue.  CF8 ships with the version 3.6 DataDirect drivers and CF9 ships with 4.0 DataDirect  drivers.

                                                 

                                                Any one know if there are any huge differences/major benefits in the 4.0 driver.  Im wondering if I should downgrade to the 3.6 driver or try out the latest an greatest 4.1 driver.  Opinions?

                                                 

                                                Cheers

                                                • 21. Re: cfquery object undefined
                                                  Adam Cameron. Level 5

                                                  I can't answer that, sorry.  But if I was in your position, I'd probably give it a go.  Maybe just lab any new-to-CF9 DB stuff you're doing (like the Hibernate stuff).  I can't see any older CF stuff being impacted by using the previous drivers.

                                                   

                                                  Obviously you should lab-test your whole app, really, as this change will hit everything.  And you might want to give some attention to performance, too.  DB driver updates usually come with performance improvements (a notable exception being the MySQL DataDirect drivers shipped with CF9...)

                                                   

                                                  Also: pls raise a bug, so Adobe know to fix it, or get on to DataDirect about it.

                                                   

                                                  http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbugtracker/main.html

                                                   

                                                  If you report back the bug reference, I'll vote for it, and... um... see if I can get it in front of the people that fix such things.

                                                   

                                                  --

                                                  Adam

                                                  • 22. Re: cfquery object undefined
                                                    mattmccabe2004 Level 1

                                                    Here is the portion of my query that i have narrowed down where the problem is. I am still trying to reproduce with some basic tables, but from my troubleshooting it appears to be something with having a "Group By" in the sub query that is being joined to and then conditioning off of a value from that left joined sub query that contains the group by. I saw the same thing in the query in this forum post as well as the query that is having this problem that was posted in the livedocs http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7f ae.html

                                                     

                                                    Here is my code:

                                                     

                                                    <cfquery name="qTest" datasource="xxxxx" result="res">
                                                         Select DISTINCT r.* 
                                                    From
                                                         requisition r
                                                              left join (Select max(oac.appID) as appID, max(oac.insertDate) as insertDate, max(oac.lastModifiedDate) as lastModifiedDate, psc.supplierOrgID 
                                                                             FROM orgAppConfigs oac, predictSupplierConfig psc
                                                                             Where psc.clientorgid = oac.orgid
                                                                               and oac.appid in (11,12,13) AND oac.expirationDate > getDate() 
                                                                             group by psc.supplierOrgID) ps ON r.mfgID = ps.supplierOrgID
                                                    Where 
                                                         1 = 1
                                                         AND
                                                         (
                                                              ps.insertDate > #getSinceDate#
                                                              OR ps.lastModifiedDate > #getSinceDate#
                                                         )
                                                    </cfquery>
                                                    

                                                     

                                                    If you I remove the conditions on the "ps" aliased table and add in another condition on the requistion table that returns 0 results I don't get the bug. As I said I am trying to come up with a replicable scenario with some simple tables. I am planning on testing out the Data Direct 4.1 Driver to see if the same problem exists.

                                                     

                                                    We are currenty on CF8 and are not experiencing the problem. In regards to your performance questions with the DataDirect 3.6 vs 4.0 we did see some noticeable improvements in many of our heavier hitting queries which was a factor in the move to CF9.

                                                     

                                                    Hopefully this is some good information.

                                                     

                                                    -Matt

                                                    • 23. Re: cfquery object undefined
                                                      AdamWinterSL

                                                      I can reproduce this error with a much simplier example and know exactly where it breaks. First, create two simple tables:

                                                       

                                                      CREATE TABLE [dbo].[Test1](
                                                          [ID] [int] NOT NULL,
                                                          [Name] [varchar](50) NOT NULL
                                                      ) ON [PRIMARY]

                                                       

                                                      CREATE TABLE [dbo].[Test2](
                                                          [ID] [int] NOT NULL,
                                                          [Name] [int] NOT NULL,
                                                          [TestID] [int] NOT NULL
                                                      ) ON [PRIMARY]

                                                       

                                                      Then, put a single record in the first table like this:

                                                       

                                                      INSERT INTO Test1 (ID, Name) VALUES (1,'Joe')

                                                       

                                                      Now, create a stored proc like this:

                                                      CREATE PROCEDURE [dbo].[spwebTest] AS
                                                      SELECT  Test1.Name, Test2.Name
                                                      FROM    dbo.Test1
                                                              LEFT OUTER JOIN dbo.Test2 ON Test1.ID = Test2.TestID
                                                      GROUP BY Test1.Name, Test2.Name
                                                      HAVING  COUNT(Test2.ID) > 0

                                                       

                                                      Then, create a ColdFusion page with the following code:

                                                      <cfstoredproc procedure="spwebTest" datasource="YourDataSource" username="YourUserName" password="YourPassword">
                                                        <cfprocresult name="qryValidateAdminLogin" resultset="1">
                                                      </cfstoredproc>

                                                       

                                                      <cfdump var='#qryValidateAdminLogin#'>
                                                      <cfoutput>RC:#qryValidateAdminLogin.recordcount#</cfoutput>

                                                       

                                                      The problem lies in the HAVING clause when referencing the Test2 table.  This query above won't return any records, but DOES return an empty recordset inside SQL studio manager.  ColdFusion pukes on it saying:

                                                      Variable  QRYVALIDATEADMINLOGIN is undefined

                                                       

                                                      If you rewrite the query to this, it'll operate fine with no errors:

                                                      ALTER PROCEDURE [dbo].[spwebTest] AS
                                                      SELECT  Test1.Name, Test2.Name
                                                      FROM    dbo.Test1
                                                               INNER JOIN dbo.Test2 ON Test1.ID = Test2.TestID
                                                      GROUP BY Test1.Name, Test2.Name

                                                       

                                                      Definitely a bug in the driver or ColdFusion.

                                                      • 24. Re: cfquery object undefined
                                                        AdamWinterSL Level 1

                                                        One more note in my posting above.  CF only errors when there's a record in the Test1 table and no matching records in Test2.  If you take that record out of Test1, the stored proce with the HAVING clause works fine.

                                                        • 25. Re: cfquery object undefined
                                                          AdamWinterSL Level 1

                                                          One more discovery, using the JDBC 2.0 driver from Microsoft seemed to help resolve this issue.  I'm using the sqljdbc4.jar in my java path.

                                                          • 26. Re: cfquery object undefined
                                                            Chiwi8888 Level 1

                                                            Thanks Adam for the taking the time for a simplified version.

                                                             

                                                            For those of you who are interested Adam has already posted the bug #82311: http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbugtracker/main.html#bugId=82311

                                                             

                                                            I also see the bug is closed, but with no comments whatsoever from who ever closed it, go figure.

                                                            • 27. Re: cfquery object undefined
                                                              mikenicholls

                                                              Here's a very simple testcase with one table and no stored procedures.

                                                               

                                                              <cfset dsn = "someSQLServerDSN">

                                                              <!--- Set up a test table --->
                                                              <cfquery name="createTestTable" datasource="#dsn#">
                                                                   CREATE TABLE test_numbers(number INTEGER, otherNumber INTEGER);
                                                              </cfquery>
                                                              <!--- Add some test data --->
                                                              <cfquery name="addTestData" datasource="#dsn#">
                                                                   INSERT INTO test_numbers VALUES (<cfqueryparam value="1" cfsqltype="cf_sql_integer">, <cfqueryparam null="true">);
                                                              </cfquery>

                                                              <!--- This is the dodgy query --->
                                                              <cfquery name="dodgyQuery" datasource="#dsn#">
                                                                   SELECT number AS dummy
                                                                   FROM test_numbers
                                                                   GROUP BY number
                                                                   HAVING COUNT(otherNumber) = 1;
                                                              </cfquery>

                                                              <cftry>
                                                                   <cfdump var="#dodgyQuery#" label="hooray">
                                                                   <cfcatch>
                                                                        <cfdump var="#cfcatch#" label="boourns">
                                                                   </cfcatch>
                                                              </cftry>

                                                               

                                                              This issue has brought our planned CF9 upgrades to a screeching halt, as it's so difficult to detect until it actually happens (which is partially dependant on the data) and there's no obvious workaround.

                                                              • 28. Re: cfquery object undefined
                                                                Adam Cameron. Level 5

                                                                For those of you who are interested Adam has already posted the bug #82311: http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbugtracker/main.html#bugId=82311

                                                                 

                                                                I also see the bug is closed, but with no comments whatsoever from who ever closed it, go figure.

                                                                 

                                                                Other than it being marked as a duplicate, that is ;-)

                                                                 

                                                                Probably of this one:

                                                                http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbugtracker/main.html#bugId=80384

                                                                 

                                                                --

                                                                Adam

                                                                • 29. Re: cfquery object undefined
                                                                  Chiwi8888 Level 1

                                                                  Other than it being marked as a duplicate, that is ;-)

                                                                   

                                                                  Oops ok thanks adam for the heads up.  That flag doesn't really jump out at you though.

                                                                  • 30. Re: cfquery object undefined
                                                                    jpa5n

                                                                    Is there any update on this problem? Didn't see it in the hotfix.

                                                                     

                                                                    And what's the best way to downgrade the data-direct drivers? We did a CF7 => CF9 transition and are hitting this error in several queries

                                                                    • 31. Re: cfquery object undefined
                                                                      AdamWinterSL Level 1

                                                                      The only fix that worked for us was to use Microsoft's JDBC 2.0 driver.  These instructions for getting that setup are rather old, but still apply.  We're using Windows 2008 R2 IIS 7.5, ColdFusion 9 Enterprise, with SQL 2005 and it works great with this driver.

                                                                      http://kb2.adobe.com/cps/186/tn_18652.html

                                                                       

                                                                      To get the driver, just Google the terms [microsoft sql jdbc driver] and you'll get links for the documentation and download of the driver.

                                                                      • 32. Re: cfquery object undefined
                                                                        Chiwi8888 Level 1

                                                                        Im not sure about the best way, but an easy way is to replace the "macromedia_drivers.jar" and "DDJDBCAuth04.dll" files in the CF9 lib folder.

                                                                         

                                                                        For instance is you use the drivers from a CF8 install it will work.  But as I think Adam pointed out earlier in the thread, orm/hibirnate might break.  Its also important to note you are pretty much downgrading all the packaged drivers.

                                                                        • 33. Re: cfquery object undefined
                                                                          kipopov

                                                                          One reason you may be getting this error is that when results are grouped using GROUP BY you may get warnings like:


                                                                          Warning: Null value is eliminated by an aggregate or other SET operation.

                                                                           

                                                                          In that case the new driver seems to pick this warning up and does not create the query object. A workaround would be to use

                                                                           

                                                                          SET ANSI_WARNINGS OFF

                                                                           

                                                                          before the query itself.

                                                                          • 34. Re: cfquery object undefined
                                                                            mikenicholls Level 1

                                                                            You're a genius, the "Null value is eliminated" warning does seem to be the root cause (at least for the scenario I've encountered and for Adam's example).  The real-world query we first noticed this with is deliberately eliminating null values, so I'll have to rework it a little to avoid the warning.

                                                                            Unfortunately this problem could occur anywhere that HAVING or an aggregate function is used on a column which allows nulls, so although there's a definite workaround it's a huge amount of work to audit and fix up every query.  I guess it's also possible that other non-fatal warnings could cause the same problem, so we definitely still need a proper fix from Adobe.

                                                                            • 35. Re: cfquery object undefined
                                                                              TFFMan2010

                                                                              To make this a global change:

                                                                               

                                                                              <cffunction name="onApplicationStart">

                                                                               

                                                                                <cfquery name="anything" datasource="#datasource#">
                                                                                 SET ANSI_WARNINGS OFF;
                                                                                </cfquery>

                                                                               

                                                                              </cffunction>

                                                                               

                                                                              The above will be persistant across all the SQL connections.

                                                                               

                                                                              We are not yet sure what happens when the SQL connection recycles.  In this case, we could put the above query in onRequestStart, of course that would not be ideal.

                                                                              • 36. Re: cfquery object undefined
                                                                                coldfusionbstephens

                                                                                We're seeing the same query undefined error for queries without aggregate functions or outer joins. We're using SQL server's containstable functionality for full text searching. What is odd is that not every query that returns 0 results throws the error. The global workaround does NOT work, and the only thing we can do is add this after out query:

                                                                                 

                                                                                <cfif NOT isDefined("getResults")>
                                                                                  <cfset getResults = queryNew('myRank')>
                                                                                </cfif>

                                                                                 

                                                                                Here is an excerpt of the join in the query. It is a phrase search, and for some reason when we add "and" into it like below, we get the query undefined error. It's weird because you can see the query in the debugging code and all appears normal.

                                                                                 

                                                                                INNER

                                                                                 

                                                                                 

                                                                                JOIN CONTAINSTABLE

                                                                                 

                                                                                 

                                                                                (subject, searchindex, '("install, configure and manage")') AS KEY_TBL

                                                                                 

                                                                                ON subject.id_subject = KEY_TBL.[KEY]

                                                                                • 37. Re: cfquery object undefined
                                                                                  dhumphreys88

                                                                                  We had the exact same problem with full text searching using the built-in SQL Server driver in ColdFusion. The problem lies, as you said, in the actual search string. SQL Server has certain words called 'noise words' that it doesn't like to match with in full text searches. It will always complain about these noise words with this message: "Informational: The full-text search condition contained noise word(s)." You can see if you are getting this message by running the query in  SQL Server Management Studio and checking the Message tab. As stated above, these warning messages will cause the query to return as undefined in ColdFusion, even though the query will run perfectly fine in SQL Server.

                                                                                   

                                                                                  We solved this, for the time being, by creating a second datasource in ColdFusion that uses ODBC to connect to SQL Server instead of the default built-in driver. The ODBC driver does not have the problem of returning an undefined query when warning messages are given by SQL Server. This isn't the best solution, but so far running our full text searches under this secondary datasource is the only thing we have found to work 100% of the time. You could also strip out all the noise words, unless you need 'and' or single characters/numbers to be in your search.

                                                                                   

                                                                                  You can read more about noise words here:

                                                                                  http://retrowebdev.blogspot.com/2006/09/removing-sql-server-full-text-noise.html

                                                                                  • 38. Re: cfquery object undefined
                                                                                    coldfusionbstephens Level 1

                                                                                    Yeah, it's definitely stopwords related, good call. I had to reference post #6 on http://forums.adobe.com/thread/230023 to get odbc working for win2008 64bit. Seems a shame there is no hotfix for this yet, it can't be that hard to fix.

                                                                                    • 39. Re: cfquery object undefined
                                                                                      Richard Davies

                                                                                      This bug was fixed with CF 9.0.1 released on 7/13/2010:

                                                                                      http://www.adobe.com/support/coldfusion/downloads_updates.html#cf9

                                                                                      1 2 Previous Next