tracker issue : CF-3341910

select a category, or use search below
(searches all categories and all time range)
Title:

SQL Server 2008 (or newer) HierarchyID datatype not supported by CF10 jdbc driver (works in CF8)

| View in Tracker

Status/Resolution/Reason: Closed/Withdrawn/Duplicate

Reporter/Name(from Bugbase): Bob Albright / Bob Albright (Bob Albright)

Created: 10/05/2012

Components: Database

Versions: 10.0

Failure Type: Non Functioning

Found In Build/Fixed In Build: Final /

Priority/Frequency: Critical / Most users will encounter

Locale/System: English / Windows 7 SP1 64-bit

Vote Count: 4

Duplicate ID:	CF-3217374

Problem Description:
The SQL Server 2008 datatype does not work when cfquery tries to pull it into memory in CF10.  Note that this *does* work in CF8.0.1.

This is a duplicate of bug# 3041769 filed against CF9.  Bug #3041769 was closed for "NotEnoughTime".  Can you explain why this worked in CF8 and does not work in newer versions of ColdFusion?

Steps to Reproduce:
1) Create a table on SQL Server 2008 or newer with the hierarchyID datatype:
CREATE TABLE [dbo].[HierarchyIDBug]
(
	[node] [hierarchyid] NOT NULL
	,[name] [varchar](50) NOT NULL
)

2) Run a query against this table that reads from the node column.

Actual Result:
Oct 5, 2012 15:18:44 PM Error [ajp-bio-8014-exec-2] -  [Macromedia][SQLServer JDBC Driver]Data type not supported: Unknown: 0xf0

Expected Result:
0xf0 in a string (or another datatype).

Any Workarounds:
Call .toString() all on hierarchyID columns before pulling into memory.

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	3341910

External Customer Info:
External Company:  
External Customer Name: Bob Albright
External Customer Email:  
External Test Config: My Hardware and Environment details:

SQL Server 2008 (or newer), CF10

Attachments:

Comments:

+1 We rely on the hierarchyId data type for complex analysis, and we have a large base of ColdFusion 8 code we are trying to upgrade to CF10. We're reluctant to try a custom JDBC driver or change all of our usage of hierarchyId columns to call .toString()..
Vote by External U.
17712 | October 05, 2012 02:37:23 PM GMT
We use the hierarchyID datatype extensively throughout our application. We are testing the upgrade from CF8 to CF10 before purchasing multiple CF10 licenses and this is essentially a no-go for us until this bug is resolved.
Vote by External U.
17713 | April 23, 2013 05:16:17 AM GMT
Test case : <cfquery name="qry" datasource="sqlserver2008_pubs" > CREATE TABLE EmployeeDemo ( [node] [hierarchyid] NOT NULL ,[name] [varchar](50) NOT NULL ) </cfquery> <cfquery name="qry1" datasource="sqlserver2008_pubs" > INSERT EmployeeDemo (node, name) VALUES (hierarchyid::GetRoot(),'scott') ; </cfquery> <cfquery name="qry2" datasource="sqlserver2008_pubs" > SELECT * from EmployeeDemo </cfquery>
Comment by Asha K.
17710 | September 24, 2013 06:11:28 AM GMT
This has bitten a number of my development team today and we've had to downgrade from CF10 to CF9 as a result.
Vote by External U.
17714 | October 14, 2013 07:38:52 AM GMT
We are trying to finish build on a commercial product and this has caused us delays in downgrading from CF10 to CF9, which has a far more unreliable Java container to top things off!
Vote by External U.
17715 | October 14, 2013 07:51:47 AM GMT
We are following up with DataDirect on this. You can track the status of the bug CF-3217374 as both essentially deal with the same issue.
Comment by Nimit S.
17711 | April 12, 2014 04:26:56 AM GMT