cusman
asked on
Coldfusion 9 - Query of Queries runtime error - Table named [x] was not found in memory
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.
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>
ASKER
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
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
ASKER
<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
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
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#">
>> 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#">
ASKER
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
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
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 ..
>> . 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 ..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The Coldfusion 9.0.1 fixes the following:
http://kb2.adobe.com/cps/8
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.
http://kb2.adobe.com/cps/8
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.
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 ;-)
(On a side note, I'd love to know the low down technical explanation behind that issue ;-)
ASKER
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)
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)
ASKER
You mentioned using a different Microsoft driver. Could you give me a more direct reference?
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.
>> 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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Have not been able to try latest suggestions yet. Just a quick update
Thanks for the update. I've been busy too. So take your time.
ASKER
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\sqljdb
Then once that is saved and CF service restarted... I can add an "Other" DSN with the instructions you have provided?
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\sqljdb
Then once that is saved and CF service restarted... I can add an "Other" DSN with the instructions you have provided?
ASKER
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
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
ASKER
Solution through guidance. Iterative trouble-shooting process. Much appreciated
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.
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?