Highlighted

Query Of Query of ordered result set error in CFSCRIPT in CF10

Explorer ,
Jul 08, 2014

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
CACHEDfalse
EXECUTIONTIME4
SQLSELECT * 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 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.

Views

429

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Query Of Query of ordered result set error in CFSCRIPT in CF10

Explorer ,
Jul 08, 2014

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
CACHEDfalse
EXECUTIONTIME4
SQLSELECT * 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 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.

Views

430

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Jul 08, 2014 0
Adobe Community Professional ,
Jul 09, 2014

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?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 09, 2014 0
Explorer ,
Jul 09, 2014

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 09, 2014 0
Explorer ,
Jul 09, 2014

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 09, 2014 0
Adobe Community Professional ,
Jul 09, 2014

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?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 09, 2014 0
Explorer ,
Jul 10, 2014

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 10, 2014 0
BKBK LATEST
Adobe Community Professional ,
Jul 10, 2014

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 10, 2014 0