Conversion of type binary to blob from Database not supported error

[Sharad Honavar]

Hello,

I am using Microsoft SQL server 2019, and using the microsoft jdbc driver that is also used in our Spotfore Datastreams product. I am using it to develop custom Streambase 10.6 /Eventflow apps. If I define a Query Operator ona Jdbc table, with blob fields in the schema named the same as the binary(n) (or varbinary(n)) columns in the DB, I get an error at runtime 'Conversion from Binary to Blob not supported'

Is there something I can do about some setting or the driver to make it work

Thank You.

[Steve Barber]

There is a documentation page that has two tables that describe the data type mappings between StreamBase Data Types and java.sql.Type data types:

 

SQL Statement Parameter Expression Type Conversions

Result Set Column Type Conversions

Notice that these two conversion tables can be thought of as going in opposite directions. Parameter Expression conversions are for "going from StreamBase to SQL" and Result Set conversions are for "going from SQL to StreamBase." Notice also that these conversion tables are not symetrical in all cases. As a relevant example, StreamBase knows how to convert a SQL VARBINARY value to a StreamBase blob in a result set context, but it does not know how to convert a StreamBase blob value to a SQL VARBINARY in a SQL Statement expression.

 

In a nutshell, if you don't see your SQL-ish datatype in those tables, the JDBC Query operator isn't going to know what to do with your data type conversion.

What can you do to help it Two possibilities:

- Write your SQL Statement to do a conversion that accepts or results in a data type that StreamBase understands. Using Transact-SQL stored procs or functions might be very helpful here.

- In some cases, you can use the JDBC Query operator's Result Settings > SQL Result Fields: (Explicitly declare fields below) schema definition table to tell the operator what StreamBase type you want to convert the field's values to. This can (sometimes) override the built-in conversions but I've noticed that sometimes this does what I want, and sometimes it doesn't -- the conversion error may occur because the runtime is looking at the type provided by the result set metadata first, and gets upset, even though with the conversion defined it probably should have happily ignored the metadata. That is, try this if you haven't already, but don't get too hopeful, as it may or may not do what you want.

 

A third possibility is to ask Product Support or Product Management for a product enhancement to do the conversion(s) you want, but that's a longer path. (And looking at MS SQL's Transact SQL documentation, I'd say it isn't obvious that StreamBase blobs and Transact SQL's BINARY and VARBINARY should ALWAYS automatically map to each other -- there are plenty of semantic possibilities for BINARY/VARBINARY conversions, so that specifying the functionality would not be a simple thing to do, necessarily. But that's just my off the cuff opinion. But I'm sure it would at least be a discussion.)

[Sharad Honavar]

Thank You Steve, sorry actually this was meant to be a comment not an answer, but could not delete it

I got around it in a convoluted way, doing it manually. Explicitly delaring the SQL binary as Streambase blob gives the error. SoI convert MS Sql binary in the result setto varchar in T-SQL before passing it to Streambase. I used the CONVERT in T-SQLbeing very cognizant ofthe style parm in the CONVERT. I receive and store it explicitly declaring as a string in Streambase. Myapp only requires to send it back to SQL Server later, where I convert it back to varbinary. The important thing to recognize is each binary character translates to 2 varchar characters.The T-SQL style parm in CONVERT controls wheter '0x' is prepended to the binary and has to match both directions.

On a side note, seems some other TIbco products like BPM, Ative Matrix used (or still use) the Data Direct jdbc drivers, I tried but SB did not like, Streambase and Spotfire DS, camewith Microsoft jdbc drivers.