4 Replies Latest reply on Nov 17, 2011 2:09 PM by Steven Hawkins

    CHAR FOR BIT DATA in Teiid

    Andriy Rokhmanov Newbie

      Hi,

       

      Does Teiid supports DB2's CHAR FOR BIT DATA columns?

       

      SELECT f1 from s1;

       

      F1

      ----------------

      0C9E2C1D06F1F2F0

       

       

      SELECT * from s1 WHERE s1.f1 = x'0C9E2C1D06F1F2F0';

      org.teiid.api.exception.query.QueryParserException Parsing error: Encountered "\'0C9E2C1D06F1F2F0\'"

       

      Thanks!

        • 1. Re: CHAR FOR BIT DATA in Teiid
          Steven Hawkins Master

          Andriy,

           

          There is no direct support for a fixed byte type, nor is our blobtype comparable, and we don't have a binary literal escape syntax.  That said, there are a couple of things you could do.  Assuming that you can retrieve the byte[] value using getBytes, then you can model the column type as blob.  To make it comparable you would have to convert to string:

           

          select * from s1 where toChars(s1.f1, 'ISO-8859-1') = 'target bytes encoded in ISO-8859-1'

           

          The choice of charset just needs to be something where each character is represented by a single byte and each byte will map to a character value.  This would allow for comparisons to be performed in Teiid.  However pushdown would be an issue. 

           

          To go further and support pushdown, you would either need to stay with the blob type add support for the toChars function (which depending on your usage of charsets may not be possible), or model the column as string and customize the translator to properly convert literal bind values etc. when the native type is CHAR FOR BIT DATA.

           

          It seems like we probably need an enhancement here to introduce handling for binary strings.  Even if it's as simple as modeling the source coulmn type as string and having an extension metadata property for the charset.

           

          Steve

          • 2. Re: CHAR FOR BIT DATA in Teiid
            Andriy Rokhmanov Newbie

            Thanks Steve for feedback, we deal with EBCDIC and binary content, so the some sort of hex string enhancement would be really nice to have in future.

            • 3. Re: CHAR FOR BIT DATA in Teiid
              Ramesh Reddy Master

              +1, log a feature request at https://issues.jboss.org/browse/TEIID

               

              Support would be good step toward integrating the legacy systems. Curious, how are intending currently to handle the conversion, are you writing a custom translator for this?


              Ramesh..