Copy link to clipboard
Copied
I have come across a very interesting error when doing a query of a query inside a CFSCRIPT tag...
I get a result set back from a SQL Server stored procedure. It is a list of metric names (METRIC_ID) and the actual metric measurements.
I need to pull the METRIC_ID from this result set to do a JOIN on another query that has display and other formatting attributes for display in a web page.
The following code executes with no errors when the query is unordered:
var displayDataQuery = new Query();
displayDataQuery.setName("joinedQuery");
displayDataQuery.setDBType("query");
displayDataQuery.setAttributes(qry1 = fullMetricsListQuery);
displayDataQuery.setAttributes(qry2 = arguments.cubeData);
displayDataQuery.setSQL("SELECT METRIC_ID FROM qry2");
var tempResultSet = displayDataQuery.execute();
var cubeMetricIDList = tempResultSet.getResult();
When I take the same exact query results from the same stored procedure, but it's ordered by an ORDER BY clause, this statement;
var tempResultSet = displayDataQuery.execute();
throws the following error - "The value '' cannot be converted to a number." That's 2 single quotes, signifying an empty string between "value" and "cannot."
Wait, what??? I just want a list of metric names, varchars. Why is there a conversion error...?
The error ultimately points back to ColdFusion10\cfusion\CustomTags\com\adobe\coldfusion\base.cfc, line 445.
OK, this is where this takes an interesting turn...
Starting at line 444 in base.cfc, Line 445 bolded in red:
<cfif sqlType neq "" and arraylen(sqlParams) gt 0>
<cfloop index="i" from="2" to="#ArrayLen(sqlArray)#">
<cfif (i-1) lte arraylen(sqlParams)>
<cfqueryparam attributeCollection="#sqlParams[i-1]#"/>
</cfif>
#getPreserveSingleQuotes(sqlArray)#
</cfloop>
</cfif>
So if I understand this correctly, when I have an unordered query, this code in base.cfc reads the SQL set in displayDataQuery.setSQL("SELECT METRIC_ID FROM qry2"), but doesn't when the query is ordered by METRIC_ID...?
Ah, wait... the CFDUMP when using an ordered data set says something else...
query | |
---|---|
RESULTSET | |
CACHED | false |
EXECUTIONTIME | 4 |
SQL | SELECT * FROM qry1 ORDER BY METRIC_ID, LOCATION |
I'm pretty much stumped why the same data in unordered set works while in an ordered set causes this error.
Any and all helpful suggestions or clues will be greatly appreciated!!!
Do you mean the error occurred without the order-by clause?
Since the error doesn't occur with an unordered data set, I'm a little confused why you would ask this...?
I followed your suggestion:
Run the same code, but then use the cfquery tag instead of cfscript. Does the error still occur?
My assumption was your suggestion was to test against an ordered data set as that's what was causing the original error.
Unfortunately the error occurs in both tag-based and script-based scenarios.
This mak
...Copy link to clipboard
Copied
The explanation got a bit confusing towards the end. You first said the query that caused the issue is "SELECT METRIC_ID FROM qry2 ORDER BY METRIC_ID". Later on you mention the query "SELECT * FROM qry1 ORDER BY METRIC_ID, LOCATION". Which one is it?
Run the same code, but then use the cfquery tag instead of cfscript. Does the error still occur?
Copy link to clipboard
Copied
Oops, you caught a defect in my post!
The SQL should be "SELECT METRIC_ID FROM qry2 ORDER BY METRIC_ID" in both places. I copied/pasted the wrong CFDUMP output.
The entire CFC is in a CFSCRIPT tag, but I'll do a little rework and see if your suggestion works.
Copy link to clipboard
Copied
This code threw the exact same error at the beginning of the CFQUERY:
<cfcomponent>
<cffunction name="getDisplayDataValueList"
returntype="any"
access="public"
displayname="getDisplayDataValueList"
description="I return a value list of the metric ids to be displayed">
<cfargument name="cubeData" type="query" required="true">
<cfquery name="cubeMetricIDList" dbtype="query" >
SELECT METRIC_ID
FROM arguments.cubeData
</cfquery>
<cfreturn ValueList(cubeMetricIDList.metric_id)>
</cffunction>
</cfcomponent>
Copy link to clipboard
Copied
Golden Jet wrote:
This code threw the exact same error at the beginning of the CFQUERY:
<cfquery name="cubeMetricIDList" dbtype="query" >SELECT METRIC_ID
FROM arguments.cubeData
</cfquery>
Do you mean the error occurred without the order-by clause?
Copy link to clipboard
Copied
Do you mean the error occurred without the order-by clause?
Since the error doesn't occur with an unordered data set, I'm a little confused why you would ask this...?
I followed your suggestion:
Run the same code, but then use the cfquery tag instead of cfscript. Does the error still occur?
My assumption was your suggestion was to test against an ordered data set as that's what was causing the original error.
Unfortunately the error occurs in both tag-based and script-based scenarios.
This makes absolutely no sense why this occurs. The order of a data set should have no bearing on a Query of a Query on a column defined as a varchar.
If there are null values in the other columns defined as some type of numeric, big deal, the code in question isn't trying to evaluate those columns. Even more interesting is why unordered vs. ordered makes a difference.
Copy link to clipboard
Copied
My suggestion was indeed to test against an ordered data set, as that is what was causing the original error. However, the query you apparently used - which I quoted - is unordered.