-
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
-
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?
-
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.
-
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.
-
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.
-
6. Re: Working with varbinary (MSSQLServer)
shawkins Sep 22, 2014 9:38 AM (in response to bpiepers)1 of 1 people found this helpfulCan you use DV 6.0.2?
-
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...
-
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