3 Replies Latest reply on Jun 24, 2015 1:07 PM by Steven Hawkins

    Binary data type support in teiid-oData

    Devesh Singh Newbie

      Hi,

       

      I am trying to expose a Binary column from MYSQL table using teiid oData.

       

      My VDB view schema is something like :-

       

      CREATE FOREIGN TABLE gtableView (

          PK string OPTIONS (nameinsource 'INTEGERCOL'),

          CHARCOL string OPTIONS (nameinsource 'CHARCOL'),

          name string OPTIONS (nameinsource 'VCHARCOL'),

          amount string OPTIONS (nameinsource 'DECIMALCOL'),

          product string OPTIONS (nameinsource 'LVCOL'),

          dateCol string OPTIONS (nameinsource 'DATECOL'),

          binCol varbinary OPTIONS (nameinsource 'BINCOL'),

          CONSTRAINT PK0 PRIMARY KEY(PK)

      ) OPTIONS(nameinsource 'gtable', "UPDATABLE" 'TRUE');

       

      while the MYSQL table schema is :-

       

      CHARCOLchar(10)YES
      VCHARCOLvarchar(10)YES
      DECIMALCOLdecimal(15,5)YES
      NUMERICCOLdecimal(15,5)YES
      SMALLCOLsmallint(6)YES
      INTEGERCOLint(11)NOPRI
      REALCOLdoubleYES
      FLOATCOLdoubleYES
      DOUBLECOLdoubleYES
      LVCOLlongtextYES
      BITCOLbit(1)NO
      TINYINTCOLtinyint(4)YES
      BIGINTCOLbigint(20)YES
      BINCOLbinary(10)YES
      VARBINCOLvarbinary(10)YES
      LVARBINCOLlongblobYES
      DATECOLdateYES

       

      Everything works fine till I do not expose the the BINCOL.

       

      When I expose the BINCOL, I get "[B cannot be cast to org.teiid.core.types.BinaryType".


      Also, if I change the foreign table schema to : binCol blob OPTIONS (nameinsource 'BINCOL'), I get error :- "javax.sql.rowset.serial.SerialBlob cannot be cast to [B"

       

      Note :- If I try to access directly through the MYSQL connector using JDBC, I get all the columns and their values correctly.

      If I try to access through the teiid using the same JDBC code - I get errors "Unable to transform the column value javax.sql.rowset.ser... to a String." while reading the result as the toString() on binary column value object is failing.

        • 1. Re: Binary data type support in teiid-oData
          Steven Hawkins Master

          What version are you on?  There is [TEIID-3473] Selecting RAW(16) column via OData errors - JBoss Issue Tracker which was addressed in 8.11.

           

          > Also, if I change the foreign table schema to : binCol blob OPTIONS (nameinsource 'BINCOL'), I get error :- "javax.sql.rowset.serial.SerialBlob cannot be cast to [B"

           

          I believe that would also be addressed by TEIID-3473

           

          > If I try to access through the teiid using the same JDBC code - I get errors "Unable to transform the column value javax.sql.rowset.ser... to a String." while reading the result as the toString() on binary column value object is failing.

           

          Can you provide the full stacktrace?  It's not clear why a string conversion is being brought into this.

          • 2. Re: Binary data type support in teiid-oData
            Devesh Singh Newbie

            Thanks Steven,

             

            I would try the odata from 8.11. I hope the 8.11 odata war only replacement in otherwise EAP6.3/teiid8.10 setup should work fine.

             

            I think 3rd query might be the problem due to errors in convertors and might get solved due to the fix  TEIID-3473 - I would try that also with 8.11 and we can discuss further.

             

            Other than this, we were also tried mapping binary in MYSQL to clob in teiid(after blob and binary did not work). This case it worked but - did not work everytime(It worked on debug when I stopped it after obtaining the ResultFuture in the code pointed further). There seems to be some threading issue while handling the ResultReceiver callback in StatementImpl :-

             

            ResultsFuture<ResultsMessage> pendingResult = null;

                    try {

                        pendingResult = this.getDQP().executeRequest(this.currentRequestID, reqMsg);

                    } catch (TeiidException e) {

                        throw TeiidSQLException.create(e);

                    }

             

            in method :- private ResultsFuture<ResultsMessage> execute(final RequestMessage reqMsg, boolean synch) throws SQLException, TeiidSQLException

             

            I did not continue debugging it deeper into the DQP layers. I would google further to see if I can find anything similar in the bug database.

             

            Thanks,

            Devesh.

            • 3. Re: Binary data type support in teiid-oData
              Steven Hawkins Master

              > I would try the odata from 8.11. I hope the 8.11 odata war only replacement in otherwise EAP6.3/teiid8.10 setup should work fine.

               

              No you would want to upgrade fully to 8.11.