cfquery object undefined

[Guest]

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

[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.

[Guest]

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.

[Adam Cameron.]

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

[Guest]

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.

[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.

[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.

[mikenicholls]

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.

[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

[AdamWinterSL]

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.

[Guest]

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.

[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.

[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]

[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

[coldfusionbstephens]

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.

[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

[Mr_Vball]

I don't believe this is true. I don't see the bug ID on the list. Also, we have applied this hotfix and continue to experience this error. Has anyone found another solution for this?

[Richard Davies]

Yes, it was fixed in 9.0.1. The change list reports that bugs 80384 and 81153 were fixed with this release. My testing also confirmed this to be true. If you're still having problems, please verify that you've correctly installed the update. If the problem persists maybe you've found a new bug.

[CFCoder2000]

We also have applied the update with no luck.  It did solve the problem in one instance where the cfquery was executing dynamic sql, but in the case where it was executing a stored proc it still throws an error.

When the dynamic sql is run directly in the db the message is this:

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

(0 row(s) affected)

When the stored proc is executed via the db the message is this:

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

Note there is no "rows affected" message in the second case.

Has anyone else found this to be true?

[CFCoder2000]

OK so the above proc had a "SET NOCOUNT ON" statement in it.  When removed it works.  Still seems odd as it worked in all other versions of CF.