Binary data type support in teiid-oData

[dks.ait]

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 :-

CHARCOL	char(10)	YES
VCHARCOL	varchar(10)	YES
DECIMALCOL	decimal(15,5)	YES
NUMERICCOL	decimal(15,5)	YES
SMALLCOL	smallint(6)	YES
INTEGERCOL	int(11)	NO	PRI
REALCOL	double	YES
FLOATCOL	double	YES
DOUBLECOL	double	YES
LVCOL	longtext	YES
BITCOL	bit(1)	NO
TINYINTCOL	tinyint(4)	YES
BIGINTCOL	bigint(20)	YES
BINCOL	binary(10)	YES
VARBINCOL	varbinary(10)	YES
LVARBINCOL	longblob	YES
DATECOL	date	YES

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.

[shawkins]

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.

[dks.ait]

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.

[shawkins]

> 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.