Discussion:
[freetds] About hierarchyid datatype
Velichko Yuriy
2014-01-22 21:26:50 UTC
Permalink
Now the method:
dbcoltype()
returns SYBBINARY for hierarchyid datatype. in result no way to cast such
data to the string like '/1/2'

The CAST function can convert hierarchyid to varchar:

http://technet.microsoft.com/en-us/library/ms187928(v=sql.105).aspx

May be is not so hard to implement the proper behaviour for dbcoltype()
and dbconvert() functions to have ability to fetch data in "readable" form?

Thanks!
--
Best Regards!
Frediano Ziglio
2014-01-24 00:19:51 UTC
Permalink
-------- Messaggio originale --------
Da: Velichko Yuriy <velichko.yuriy at gmail.com>
Data: 22/01/2014 21:26 (GMT+00:00)
A: FreeTDS Development Group <freetds at lists.ibiblio.org>
Oggetto: [freetds] About hierarchyid datatype

Now the method:
??? dbcoltype()
returns SYBBINARY for hierarchyid datatype. in result no way to cast such
data to the string like '/1/2'

The CAST function can convert hierarchyid to varchar:

http://technet.microsoft.com/en-us/library/ms187928(v=sql.105).aspx

May be is not so hard to implement the proper behaviour? for dbcoltype()
and dbconvert() functions to have ability to fetch data in "readable" form?

Thanks!
--
Best Regards!
Frediano Ziglio
2014-01-24 00:30:01 UTC
Permalink
Post by Velichko Yuriy
dbcoltype()
returns SYBBINARY for hierarchyid datatype. in result no way to cast such
data to the string like '/1/2'
http://technet.microsoft.com/en-us/library/ms187928(v=sql.105).aspx
May be is not so hard to implement the proper behaviour for dbcoltype()
and dbconvert() functions to have ability to fetch data in "readable" form?
Thanks!
Well, there are actually a couple of problems.

1. It's not easy to add a new type;
2. We do not fully even support 2008 while this type is 2012;
3. Dblib has to be extended to support new types.

Frediano
Frediano Ziglio
2014-02-03 14:57:56 UTC
Permalink
Post by Frediano Ziglio
Post by Velichko Yuriy
dbcoltype()
returns SYBBINARY for hierarchyid datatype. in result no way to cast such
data to the string like '/1/2'
http://technet.microsoft.com/en-us/library/ms187928(v=sql.105).aspx
May be is not so hard to implement the proper behaviour for dbcoltype()
and dbconvert() functions to have ability to fetch data in "readable" form?
Thanks!
Well, there are actually a couple of problems.
1. It's not easy to add a new type;
2. We do not fully even support 2008 while this type is 2012;
3. Dblib has to be extended to support new types.
Hi,
I'll correct myself, this type was added in mssql 2008. Never
used... beside point 2 all others remains.

Frediano
Frediano Ziglio
2014-02-04 22:08:25 UTC
Permalink
Just a small update. I just pushed a patch with base support for this
type. Well... base support in the sense FreeTDS does not loose sync
with server when your queries return this type. The type mainly is
encoded similarly to VARBINARY(MAX) beside it has some additional
fields in the metadata to return the CLR type. Obviously some types
are defined by default (like hierarchyid). I don't really know what
should be the support on the client side. Does MS ODBC return this
type as binary or can even convert defined types (probably to string)
?

Frediano
Post by Frediano Ziglio
Post by Frediano Ziglio
Post by Velichko Yuriy
dbcoltype()
returns SYBBINARY for hierarchyid datatype. in result no way to cast such
data to the string like '/1/2'
http://technet.microsoft.com/en-us/library/ms187928(v=sql.105).aspx
May be is not so hard to implement the proper behaviour for dbcoltype()
and dbconvert() functions to have ability to fetch data in "readable" form?
Thanks!
Well, there are actually a couple of problems.
1. It's not easy to add a new type;
2. We do not fully even support 2008 while this type is 2012;
3. Dblib has to be extended to support new types.
Hi,
I'll correct myself, this type was added in mssql 2008. Never
used... beside point 2 all others remains.
Frediano
Velichko Yuriy
2014-02-05 14:22:17 UTC
Permalink
Thanks for support this request!
At this time I have different task, and can't to check the behaviour of
ODBC.

About client side: I want to get ability to show user the string of
ierarchy like
/
/1/
(http://technet.microsoft.com/en-us/library/bb677290.aspx)

For example dbForge can show this fields. ( of course they use another
protocol)
Post by Frediano Ziglio
Just a small update. I just pushed a patch with base support for this
type. Well... base support in the sense FreeTDS does not loose sync
with server when your queries return this type. The type mainly is
encoded similarly to VARBINARY(MAX) beside it has some additional
fields in the metadata to return the CLR type. Obviously some types
are defined by default (like hierarchyid). I don't really know what
should be the support on the client side. Does MS ODBC return this
type as binary or can even convert defined types (probably to string)
?
Frediano
Post by Frediano Ziglio
Il 22/gen/2014 21:27 "Velichko Yuriy" <velichko.yuriy at gmail.com> ha
Post by Velichko Yuriy
dbcoltype()
returns SYBBINARY for hierarchyid datatype. in result no way to cast
such
Post by Frediano Ziglio
Post by Velichko Yuriy
data to the string like '/1/2'
http://technet.microsoft.com/en-us/library/ms187928(v=sql.105).aspx
May be is not so hard to implement the proper behaviour for
dbcoltype()
Post by Frediano Ziglio
Post by Velichko Yuriy
and dbconvert() functions to have ability to fetch data in "readable" form?
Thanks!
Well, there are actually a couple of problems.
1. It's not easy to add a new type;
2. We do not fully even support 2008 while this type is 2012;
3. Dblib has to be extended to support new types.
Hi,
I'll correct myself, this type was added in mssql 2008. Never
used... beside point 2 all others remains.
Frediano
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
--
Best Regards!
Frediano Ziglio
2014-02-05 15:15:34 UTC
Permalink
I think that actually the support from ODBC is somethink like you get
binary and some informations (UDT type, some UDT specifications like
assembly type, schema) and you have to convert on your own.

However the format for hierarchyid, geometry and similar are
documented and known so a library to support should be fine (I think).

Frediano
Post by Velichko Yuriy
Thanks for support this request!
At this time I have different task, and can't to check the behaviour of
ODBC.
About client side: I want to get ability to show user the string of
ierarchy like
/
/1/
(http://technet.microsoft.com/en-us/library/bb677290.aspx)
For example dbForge can show this fields. ( of course they use another
protocol)
Post by Frediano Ziglio
Just a small update. I just pushed a patch with base support for this
type. Well... base support in the sense FreeTDS does not loose sync
with server when your queries return this type. The type mainly is
encoded similarly to VARBINARY(MAX) beside it has some additional
fields in the metadata to return the CLR type. Obviously some types
are defined by default (like hierarchyid). I don't really know what
should be the support on the client side. Does MS ODBC return this
type as binary or can even convert defined types (probably to string)
?
Frediano
Post by Frediano Ziglio
Il 22/gen/2014 21:27 "Velichko Yuriy" <velichko.yuriy at gmail.com> ha
Post by Velichko Yuriy
dbcoltype()
returns SYBBINARY for hierarchyid datatype. in result no way to cast
such
Post by Frediano Ziglio
Post by Velichko Yuriy
data to the string like '/1/2'
http://technet.microsoft.com/en-us/library/ms187928(v=sql.105).aspx
May be is not so hard to implement the proper behaviour for
dbcoltype()
Post by Frediano Ziglio
Post by Velichko Yuriy
and dbconvert() functions to have ability to fetch data in "readable" form?
Thanks!
Well, there are actually a couple of problems.
1. It's not easy to add a new type;
2. We do not fully even support 2008 while this type is 2012;
3. Dblib has to be extended to support new types.
Hi,
I'll correct myself, this type was added in mssql 2008. Never
used... beside point 2 all others remains.
Frediano
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
--
Best Regards!
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
Frediano Ziglio
2014-02-10 10:36:05 UTC
Permalink
Yesterday I added a test to check if it's possible to get this type.
Surprisingly you can get the binary data from ODBC as ODBC conversion
to binary is mainly returning what it's on the wire! Still to check
how proprietary library return this type but I think it will return
this type as binary the same way. Still the question is if it can
convert it to char/wchar.

Frediano
Post by Frediano Ziglio
I think that actually the support from ODBC is somethink like you get
binary and some informations (UDT type, some UDT specifications like
assembly type, schema) and you have to convert on your own.
However the format for hierarchyid, geometry and similar are
documented and known so a library to support should be fine (I think).
Frediano
Post by Velichko Yuriy
Thanks for support this request!
At this time I have different task, and can't to check the behaviour of
ODBC.
About client side: I want to get ability to show user the string of
ierarchy like
/
/1/
(http://technet.microsoft.com/en-us/library/bb677290.aspx)
For example dbForge can show this fields. ( of course they use another
protocol)
Post by Frediano Ziglio
Just a small update. I just pushed a patch with base support for this
type. Well... base support in the sense FreeTDS does not loose sync
with server when your queries return this type. The type mainly is
encoded similarly to VARBINARY(MAX) beside it has some additional
fields in the metadata to return the CLR type. Obviously some types
are defined by default (like hierarchyid). I don't really know what
should be the support on the client side. Does MS ODBC return this
type as binary or can even convert defined types (probably to string)
?
Frediano
Post by Frediano Ziglio
Il 22/gen/2014 21:27 "Velichko Yuriy" <velichko.yuriy at gmail.com> ha
Post by Velichko Yuriy
dbcoltype()
returns SYBBINARY for hierarchyid datatype. in result no way to cast
such
Post by Frediano Ziglio
Post by Velichko Yuriy
data to the string like '/1/2'
http://technet.microsoft.com/en-us/library/ms187928(v=sql.105).aspx
May be is not so hard to implement the proper behaviour for
dbcoltype()
Post by Frediano Ziglio
Post by Velichko Yuriy
and dbconvert() functions to have ability to fetch data in "readable" form?
Thanks!
Well, there are actually a couple of problems.
1. It's not easy to add a new type;
2. We do not fully even support 2008 while this type is 2012;
3. Dblib has to be extended to support new types.
Hi,
I'll correct myself, this type was added in mssql 2008. Never
used... beside point 2 all others remains.
Frediano
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
--
Best Regards!
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
Frediano Ziglio
2014-02-11 12:34:38 UTC
Permalink
Small update. MS library return same value converting to binary for
this type. Still to know if it can convert type to char. Also MS
provide some extension to get CLR type and other information. This
last part is missing, libTDS strip these information.

Frediano
Post by Frediano Ziglio
Yesterday I added a test to check if it's possible to get this type.
Surprisingly you can get the binary data from ODBC as ODBC conversion
to binary is mainly returning what it's on the wire! Still to check
how proprietary library return this type but I think it will return
this type as binary the same way. Still the question is if it can
convert it to char/wchar.
Frediano
Post by Frediano Ziglio
I think that actually the support from ODBC is somethink like you get
binary and some informations (UDT type, some UDT specifications like
assembly type, schema) and you have to convert on your own.
However the format for hierarchyid, geometry and similar are
documented and known so a library to support should be fine (I think).
Frediano
Post by Velichko Yuriy
Thanks for support this request!
At this time I have different task, and can't to check the behaviour of
ODBC.
About client side: I want to get ability to show user the string of
ierarchy like
/
/1/
(http://technet.microsoft.com/en-us/library/bb677290.aspx)
For example dbForge can show this fields. ( of course they use another
protocol)
Post by Frediano Ziglio
Just a small update. I just pushed a patch with base support for this
type. Well... base support in the sense FreeTDS does not loose sync
with server when your queries return this type. The type mainly is
encoded similarly to VARBINARY(MAX) beside it has some additional
fields in the metadata to return the CLR type. Obviously some types
are defined by default (like hierarchyid). I don't really know what
should be the support on the client side. Does MS ODBC return this
type as binary or can even convert defined types (probably to string)
?
Frediano
Post by Frediano Ziglio
Il 22/gen/2014 21:27 "Velichko Yuriy" <velichko.yuriy at gmail.com> ha
Post by Velichko Yuriy
dbcoltype()
returns SYBBINARY for hierarchyid datatype. in result no way to cast
such
Post by Frediano Ziglio
Post by Velichko Yuriy
data to the string like '/1/2'
http://technet.microsoft.com/en-us/library/ms187928(v=sql.105).aspx
May be is not so hard to implement the proper behaviour for
dbcoltype()
Post by Frediano Ziglio
Post by Velichko Yuriy
and dbconvert() functions to have ability to fetch data in "readable" form?
Thanks!
Well, there are actually a couple of problems.
1. It's not easy to add a new type;
2. We do not fully even support 2008 while this type is 2012;
3. Dblib has to be extended to support new types.
Hi,
I'll correct myself, this type was added in mssql 2008. Never
used... beside point 2 all others remains.
Frediano
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
--
Best Regards!
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
Loading...