Solved

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

Posted on 2010-09-01
22
1,281 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_
ID: 33581635
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
ID: 33581674
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
ID: 33581757
<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_
ID: 33581829
  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
ID: 33581935
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_
ID: 33582359
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
ID: 33582445
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
ID: 33587836
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
ID: 33589237
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
ID: 33591132
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_
ID: 33591657
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
Network it in WD Red

There's an industry-leading WD Red drive for every compatible NAS system to help fulfill your data storage needs. With drives up to 8TB, WD Red offers a wide array of solutions for customers looking to build the biggest, best-performing NAS storage solution.  

 

Author Comment

by:cusman
ID: 33592810
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
ID: 33592886
You mentioned using a different Microsoft driver. Could you give me a more direct reference?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 33599235
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
ID: 33599451
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
ID: 33600996
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
ID: 33622749
Have not been able to try latest suggestions yet. Just a quick update
0
 
LVL 52

Expert Comment

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

Author Comment

by:cusman
ID: 33628478
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
ID: 33632961
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
ID: 33632979
Solution through guidance. Iterative trouble-shooting process. Much appreciated
0
 
LVL 52

Expert Comment

by:_agx_
ID: 33648542
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

920 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

18 Experts available now in Live!

Get 1:1 Help Now