hierarchyid Data Type Not Supported in ColdFusion 9

Posted: April 7, 2011 in ColdFusion

Microsoft SQL 2008 introduced the hierarchyid datatype helping with child/parent relationships. However this causes an issue if you try to select its data via ColdFusion’s Microsoft SQL Server driver. This breaks in ColdFusion 9.0.1 and I assume prior versions.

Example Table:

CREATE TABLE Org_T1
(
EmployeeId hierarchyid PRIMARY KEY,
EmployeeName nvarchar(50)
)

Example SQL Call:

SELECT * FROM Org_T1

Now first of all you should try to avoid “SELECT *” when possible anyway to increase efficiency. Otherwise the table columns are looked up before querying for results, adding an extra process step.

The way around this would be to list the columns you actually need in the select statement. If you need the data from the “EmployeeId” column you could append the “.ToString()” function to the column name. Example:

SELECT EmployeeId.ToString(), EmployeeName
FROM Org_T1

This will in effect change what data you receive however. The raw data looks something like “0xf0”. The result of the ToString() method looks something like “\1\” and I believe will be of varchar type.

There is a bug entry with Adobe here: http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbugtracker/main.html#bugId=83590 . Feel free to go vote on this issue to increase its visibility with the ColdFusion team.

Advertisements
Comments
  1. Dirk says:

    adobe changed the bugtracker. i’m looking for this issue but i can’t found it in the new bugbase:(

    btw: hierarchyid works with CF 8

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s