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

    Working with varbinary (MSSQLServer)

    Bas Piepers Novice

      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.