-
1. Re: Working with varbinary (MSSQLServer)
Steven Hawkins Sep 16, 2014 8:44 AM (in response to Bas Piepers)What version are you on? You could be seeing https://issues.jboss.org/browse/TEIID-2833
-
2. Re: Working with varbinary (MSSQLServer)
Bas Piepers Sep 16, 2014 9:39 AM (in response to Steven Hawkins)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?
-
3. Re: Working with varbinary (MSSQLServer)
Steven Hawkins Sep 16, 2014 9:49 AM (in response to Bas Piepers)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.
-
4. Re: Working with varbinary (MSSQLServer)
Bas Piepers Sep 16, 2014 10:48 AM (in response to Steven Hawkins)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.
-
5. Re: Working with varbinary (MSSQLServer)
Bas Piepers Sep 22, 2014 9:33 AM (in response to Steven Hawkins)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.
-
6. Re: Working with varbinary (MSSQLServer)
Steven Hawkins Sep 22, 2014 9:38 AM (in response to Bas Piepers)1 of 1 people found this helpfulCan you use DV 6.0.2?
-
7. Re: Working with varbinary (MSSQLServer)
Bas Piepers Oct 17, 2014 8:26 AM (in response to Steven Hawkins)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...
-
8. Re: Working with varbinary (MSSQLServer)
Steven Hawkins Oct 17, 2014 8:53 AM (in response to Bas Piepers)> 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