cfquery object undefined

Report · Feb 16, 2010

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

Report · Feb 16, 2010

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

Report · Feb 16, 2010

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

Report · Feb 16, 2010

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-7fae.html

Report · Feb 16, 2010

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

Report · Feb 16, 2010

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:

Check the ColdFusion documentation to verify that you are using the correct syntax.

Search the Knowledge Base to find a solution to your problem.

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)

Report · Feb 16, 2010

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.

Report · Feb 16, 2010

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.

Report · Feb 16, 2010

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

Report · Feb 16, 2010

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

Report · Feb 16, 2010

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

Report · Feb 16, 2010

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

Report · Feb 16, 2010

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

--

Adam

Report · Feb 17, 2010

What happens if you do this?

Report · Feb 17, 2010

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

Report · Feb 17, 2010

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.

Report · Feb 17, 2010

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

Report · Feb 25, 2010

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.

Report · Feb 26, 2010

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

Report · Feb 27, 2010

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

--

Adam

Report · Mar 01, 2010

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

Report · Mar 01, 2010

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

Report · Mar 01, 2010

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

Report · Mar 02, 2010

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:

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

Report · Mar 02, 2010

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.

cfquery object undefined

Variable TEST1 is undefined.

Variable QRYVALIDATEADMINLOGIN is undefined

Photos