8 Replies Latest reply on Oct 17, 2014 8:53 AM by shawkins

Working with varbinary (MSSQLServer)

bpiepers Sep 16, 2014 8:36 AM

My source tables have various columns that are of type "varbinary", even quite a few of the primary key columns (don't ask me why :-)). The Teiid designer initially imports this as type "Object" but if you would like to compare fields with each other, this is obviously not possible. Fictive example:

Select * from TableX inner join TableY ON TableX.id=TableY.id.

In case the "id" fields in the example above where varbinary fields and translated to Object by the Teiid designer then a join like the one above isn't accepted. It is when I change the type of the fields to "varbinary" (to be precise: varbinary : xs:base64Binary) but then at runtime when issuing a query on the corresponding VDB returns a conversion exception:

org.teiid.core.types.TransformationException: TEIID10076 Invalid conversion from type class org.teiid.core.types.BinaryType with value '002945343833413731412D324341352D3436' to type class java.lang.String

Some additional information: in the real situation, the values in the varbinary id fields are uuids, hence the large value in the exception above.

What is the best way to deal with these kind of field types? Any help would be appreciated.

1. Re: Working with varbinary (MSSQLServer)

shawkins Sep 16, 2014 8:44 AM (in response to bpiepers)

What version are you on? You could be seeing https://issues.jboss.org/browse/TEIID-2833
Actions
2. Re: Working with varbinary (MSSQLServer)

bpiepers Sep 16, 2014 9:39 AM (in response to shawkins)

Teiid designer: 8.3.3-Final. JDV: 6.0.0. Both aren't community versions but hopefully can still post questions here. As for the Jira issue: this appears to be an issue with null values in columns with type varbinary. In my case, there are no null values in the columns so perhaps this is something else?
Actions
3. Re: Working with varbinary (MSSQLServer)

shawkins Sep 16, 2014 9:49 AM (in response to bpiepers)

It's an issue with the metadata received by the client primarily. Can you run DV 6.0.2 or Teiid 8.7 or later as the server to see if the behavior is different.
Actions
4. Re: Working with varbinary (MSSQLServer)

bpiepers Sep 16, 2014 10:48 AM (in response to shawkins)

Not very easily done in this situation (at the customer site in an isolated environment) but may be able to reproduce on a local installation. I am happy to do things manually if I know how I am able to convert from varbinary to string. I have tried some intermediate examples like first making the source model column be of type BLOB and then trying to explicitly convert that to String.
Actions
5. Re: Working with varbinary (MSSQLServer)

bpiepers Sep 22, 2014 9:33 AM (in response to shawkins)

Some feedback from my side: teiid 8.7 is able to convert this properly. I do have to set the imported datasources back to varbinary, however, as it defaults to the Object type. This doesn't help me much, though, as we are not going to use a community version of JDV.
Actions
6. Re: Working with varbinary (MSSQLServer)

shawkins Sep 22, 2014 9:38 AM (in response to bpiepers)

Can you use DV 6.0.2?
1 of 1 people found this helpful
Actions
7. Re: Working with varbinary (MSSQLServer)

bpiepers Oct 17, 2014 8:26 AM (in response to shawkins)

You can manually set the type of these fields to be varbinary and you may also have to manually set the fields to be SEARCHABLE in Teiid and then a JOIN does work. To what fields can I convert this to get a string or integer representation of these kinds of fields? Databases like SQL Server, for instance, are able to convert these kinds of fields to a string representation or to a BIGINT value but if I try to use the CONVERT function in Teiid/JDV to cast the field to a String or Bigint then I get an exception indicating that a conversion to that type is not possible. According to Type Conversions - Teiid 8.4 - Project Documentation Editor, it seems to be possible to first convert it to Object and then to another type but that seems rather cumbersome...
Actions
8. Re: Working with varbinary (MSSQLServer)

shawkins Oct 17, 2014 8:53 AM (in response to bpiepers)

> To what fields can I convert this to get a string or integer representation of these kinds of fields?

We don't have any built-in conversions to any type other than blob for varbinary. There is the to_chars function that you can use to convert to a character representation if you specify the character set. That returns a clob, which you can cast to string -

cast(to_chars(vabinary_value, 'HEX') as string)

See String Functions - Teiid 8.9 (draft) - Project Documentation Editor
Actions

Go to original post