Skip navigation
Currently Being Moderated

cfquery object undefined

Feb 16, 2010 7:55 AM

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

 
Replies 1 2 Previous Next
  • Currently Being Moderated
    Feb 16, 2010 9:16 AM   in reply to Chiwi8888

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

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 16, 2010 12:03 PM   in reply to Chiwi8888

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 16, 2010 12:22 PM   in reply to Chiwi8888

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 16, 2010 12:26 PM   in reply to Chiwi8888

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 16, 2010 1:13 PM   in reply to Chiwi8888

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 16, 2010 1:42 PM   in reply to Chiwi8888

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

     

    --

    Adam

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 17, 2010 5:17 AM   in reply to Adam Cameron.

    What happens if you do this?

     

    <cfset x = QueryNew("a")>

    <cfdump var = "#x#">

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 17, 2010 6:51 AM   in reply to Chiwi8888

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 25, 2010 2:58 PM   in reply to Chiwi8888

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 27, 2010 3:33 PM   in reply to Chiwi8888

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

     

    --

    Adam

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 1, 2010 2:21 AM   in reply to Chiwi8888

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 1, 2010 7:38 AM   in reply to Chiwi8888

    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/WSc3ff6d0ea77859461 172e0811cbec22c24-7fae.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

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 2, 2010 1:37 PM   in reply to Chiwi8888

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 2, 2010 1:39 PM   in reply to AdamWinterSL

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 2, 2010 2:11 PM   in reply to AdamWinterSL

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 3, 2010 4:59 PM   in reply to Chiwi8888

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 8, 2010 9:40 AM   in reply to Chiwi8888

    For those of you who are interested Adam has already posted the bug #82311: http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbugtracker/main.html#b ugId=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#b ugId=80384

     

    --

    Adam

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 15, 2010 10:17 AM   in reply to Chiwi8888

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 15, 2010 11:16 AM   in reply to jpa5n

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    May 17, 2010 6:56 PM   in reply to mikenicholls

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    May 17, 2010 8:57 PM   in reply to kipopov

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 5, 2010 12:50 PM   in reply to Chiwi8888

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 15, 2010 9:47 AM   in reply to TFFMan2010

    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]

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 23, 2010 9:31 AM   in reply to coldfusionbstephens

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 6, 2010 3:14 PM   in reply to dhumphreys88

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 15, 2010 11:35 AM   in reply to Chiwi8888

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

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

     
    |
    Mark as:
1 2 Previous Next

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points