Solved

Coldfusion 9 - Query of Queries runtime error - Table named [x] was not found in memory

Posted on 2010-09-01
22
1,249 Views
Last Modified: 2013-12-24
This code works in Coldfusion 7 and Coldfusion 8 (regardless of record counts returned)

It fails in Coldfusion 9 only if the first query returns 0 records (otherwise no issue)

Exact error:
Error Executing Database Query.

Query Of Queries runtime error.
Table named Get_X_Data was not found in memory. The name is misspelled or the table is not defined.
<cfquery name="Get_X_Data" datasource="#DSN#">

 EXEC dbo.Get_X_Data @X = '#url.x#'

</cfquery>



<cfquery name="Get_X_Reason" datasource="#DSN#">

 EXEC dbo.Get_X_Reason

</cfquery>



<cfquery name="Get_X_Data" dbtype="query">

	Select 	Get_X_Data.*, Get_X_Reason.X_Reason_ID

	FROM 	Get_X_Data, Get_X_Reason

	WHERE Get_X_Data.X_Reason = Get_X_Reason.X_Reason

        AND Get_X_Data.X_Calculation = Get_X_Reason.X_Calculation

</cfquery>

Open in new window

0
Comment
Question by:cusman
  • 11
  • 11
22 Comments
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
Unfortunately, QoQ's can be finicky.  

1) Is the query actually undefined when 0 records are returned or is that a misleading error message?
2) What is the procedure dbo.Get_X_Data doing?
3) Any difference if you use cfstoreproc instead of cfquery?


0
 

Author Comment

by:cusman
Comment Utility
A recordset is returned with 0 records by the stored procedure
Coldfusion 9 for whatever reason seems to be treating the Get_X_Data "query" as undefined
I think the problem is prior to the Query of Queries... but doesn't really make sense

Get_X_Data is returning a recordset of data
0
 

Author Comment

by:cusman
Comment Utility
<cfquery name="Get_X" datasource="#DSN#">
 EXEC dbo.Get_X @X = '#X#'
</cfquery>

<cfif isDefined("Get_X") EQ "false">
 Get_X not defined<br>
</cfif>

Is displaying the line
Get_X not defined

Even though the Stored Procedure returns a recordset

This problem is only happening on Coldfusion 9 (many other strange issues like this one)

Coldfusion 7 & 8 are fine

All my Coldfusion webservers are on IIS 7 on Windows Server 2008... only Coldfusion 9 which we are testing before moving to seems to be having these mystery issues
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
  I've definitely seen 1 or 2 completely "new" issues with QoQ's under CF9.  

>> I think the problem is prior to the Query of Queries... but doesn't really make sense

   What makes you say that?

>> Even though the Stored Procedure returns a recordset

   Just to rule out things... any change if you add a NOCOUNT?

   <cfquery name="Get_X" datasource="#DSN#">
       SET NOCOUNT ON
       EXEC dbo.Get_X @X = '#X#'
       SET NOCOUNT OFF
   </cfquery>


>> <cfif isDefined("Get_X") EQ "false">
 
  That's probably correct. But for grins could you try a cfdump instead? I want
  to be absolutely positive the query doesn't exist.
  <cfdump var="#get_x#">
0
 

Author Comment

by:cusman
Comment Utility
The CFDUMP I had tried and it would give error; So I tried the isDefined instead

The SET NOCOUNT ON / OFF is already in the procedure

I am now trying to use cfstoredproc but I haven't used these for a while so reading up on the requirements. I hope I don't have to have a cfprocparam for each possible parameter and just for the one I am trying to pass in

The stored procedure other non-required possible parameters which are ignored if not supplied
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
Okay.

>> . I hope I don't have to have a cfprocparam for each possible parameter and just for
>> the one I am trying to pass in

Unfortunately ... you probably will.  IIRC support for named parameter was dropped around CF8(?) supposedly because it's not supported in JDBC2.2?

This issue is starting to seem very familiar.  I don't recall if was solved (or is just a known bug). I have to head out. But let me see if I can dredge up the reference ..

0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
Comment Utility
Found it.  It's not exactly the same as your scenario. But this person did have problems with undefined queries in CF9.  There are several different suggestions for workarounds. But the last post on this thread suggests the issue was fixed in "CF 9.0.1 released on 7/13/2010:"

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

0
 

Author Comment

by:cusman
Comment Utility
I am going to try upgrading to 9.0.1 (still on 9.0.0) first and see if that helps
If not, then I am going to try the Cumulative Hot Fix 1 for 9.0.1 and see if that helps

It will take me 1-2 days to try these potential solutions out

Once I have the issue confirmed as resolved, I will return here to mark the solution you provided as "accepted". Thanks for your help
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 500 total points
Comment Utility
If you're so inclined, you could also try using the Microsoft driver first.  Just to see if it resolves the issue as one person suggested. But take your time.  I'm more interested in what resolves the issue than the points.
0
 

Author Comment

by:cusman
Comment Utility
The Coldfusion 9.0.1 fixes the following:
http://kb2.adobe.com/cps/847/cpsid_84726.html
79382
cfquery
In the case of nested queries, the containing query’s datasource changes to the datasource of the nested query.

80384
cfquery
cfquery does not return a named query variable in certain instances. For example, a query that includes a LEFT JOIN and GROUP BY & HAVING COUNT with no data in the LEFT JOIN result set.

81153
cfquery
If you run an SQL query using the tag cfquery and if the results are empty, it fails to set the variable to the empty query object, especially when using a CONTAINS statement with double-quotes in the value.

82073
cfquery
cfquery type="query" (Query of Query) scans the first 50 rows of each column of the parent query to determine the datatype before it runs the child query cfquery type="query" (Query of Query). An error occurs if the parent query has more than 50 rows of data and one of its columns has the first 50 rows null.

0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
That sounds promising. I assume the highlighted issue might apply to your stored proc?

(On a side note, I'd love to know the low down technical explanation behind that issue ;-)
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:cusman
Comment Utility
The update to Coldfusion 9.0.1 did not fix the issue
Specifying a result="X_Result" makes sure that cfdump="#X_Result#" exists but that cannot be used in a query of queries

Gives this error:
Error casting an object of type to an incompatible type. This usually indicates a programming error in Java, although it could also mean you have tried to use a foreign object in a different way than it was designed.

So it looks like I just need to add special code handling on the page code so that it will function even on CF9 if no results returned (guess this also puts off the will to move from CF8 to CF9 anytime soon)
0
 

Author Comment

by:cusman
Comment Utility
You mentioned using a different Microsoft driver. Could you give me a more direct reference?
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
Sorry for the delay.  It's been a hectic week.

>> Error casting an object of type to an incompatible type.
>>       WHERE Get_X_Data.X_Reason = Get_X_Reason.X_Reason
>>        AND Get_X_Data.X_Calculation = Get_X_Reason.X_Calculation


What are the data types of the columns used in the QoQ? It could be that you need to add an explicit CAST.  Since that's simple to add, let's try that first.

I'll look up the driver details and post back shortly.

0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 500 total points
Comment Utility
The driver mentioned in the other thread was Microsoft JDBC 2.0
http://msdn.microsoft.com/en-us/library/ms378526.aspx
http://www.microsoft.com/downloads/details.aspx?FamilyID=99b21b65-e98f-4a61-b811-19912601fdc9&displaylang=en

Just download the driver jar, add it to the CF class path and restart CF.  Then create an "Other" datasource in the CF Admin. For details on jdbc url's see:
http://msdn.microsoft.com/en-us/library/ms378428.aspx

Going strictly off of memory, the important dsn settings are something like

Driver class:    com.microsoft.sqlserver.jdbc.SQLServerDriver
JDBC URL:        jdbc:sqlserver://localhost:1433;
* 1433 is the port number
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 500 total points
Comment Utility
Correction, that should be:

Driver class:    com.microsoft.sqlserver.jdbc.SQLServerDriver
JDBC URL:        jdbc:sqlserver://localhost:1433;databaseName=YourDatabase
* 1433 is the port number
0
 

Author Comment

by:cusman
Comment Utility
Have not been able to try latest suggestions yet. Just a quick update
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
Thanks for the update.  I've been busy too.  So take your time.
0
 

Author Comment

by:cusman
Comment Utility
Please confirm:

I have extracted the MS SQL Server JDBC 2.0 and 3.0 to

C:\Microsoft SQL Server JDBC Driver 2.0
C:\Microsoft SQL Server JDBC Driver 3.0

I would need to be on Coldfusion Administrator screen and on Java and JVM screen, add the additional class paths by providing
C:\Microsoft SQL Server JDBC Driver 2.0\sqljdbc_2.0\enu\sqljdbc4.jar,C:\Microsoft SQL Server JDBC Driver 3.0\sqljdbc_3.0\enu\sqljdbc4.jar

Then once that is saved and CF service restarted... I can add an "Other" DSN with the instructions you have provided?
0
 

Author Comment

by:cusman
Comment Utility
FYI

I put the Microsoft SQL Server JDBC Driver 3.0 (since it was more recent) and used that in the DSN and then tried replicating the error. Error gone.

Finally fixed (pretty sure the 2.0 would have also fixed it but did not try)

Not sure if the update from 9.0.0 to 9.0.1 was necessary but it is a good stable update in experience so far and did add a few new features (Amazon S3 support, IIS7 native support, etc)

Thanks for the helps/direction. Much appreciated
0
 

Author Closing Comment

by:cusman
Comment Utility
Solution through guidance. Iterative trouble-shooting process. Much appreciated
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
Great. Thanks for letting us know how it turned out.  I hadn't tried the 3.0 jar yet, but good to know it works too.

0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now