How to use proprietary JDBC data types with Spotfire® Streaming JDBC Query Operators

The Spotfire® Streaming StreamBase EventFlow JDBC Query operator will automatically detect a number of datatypes in ResultSets that are java.sql.Types and conver them to StreamBase datatypes. These conversions are documented here: https://docs.streambase.com/latest/topic/com.streambase.sb.ide.help/data/html/authoring/jdbcworkings.html in the last two sections on that page.

However, sometimes one wants to be able to use JDBC datatypes that are not automatically converted, including datatypes that are proprietary to specific JDBC driver implementations. Perhaps the most common example of these are the Oracle database's NVARCHAR or NUMBER types.

The way that JDBC Query typechecking and thus the whole automation around figuring out the result set schema and mapping it into an output tuple schema for a Query (JDBC) operator works is that the operator tries to determine the schema of the result set from metadata of the result set returned by the query's SQL Statement. The operator actually issues a "metadata query" to the DB itself during typecheck -- that is, a query where it can figure out the result set meta data without actually doing a real query. What that query actually is -- that is database-dependent. For the list of databases documented as supported by Spotfire® Streaming, that query is well-known to the Streaming implementation and is quite reliable. However, there are many more JDBC drivers that are used with Spotfire® Streaming that aren't on the Supported list, and for those a default query is used, and that doesn't always work perfectly. If it doesn't work for a given JDBC driver, we can work around that with the same mechanism described below for mapping proprietary types.

This automatic schema-detection mechanism only knows only about the java.sql.Types listed in the Spotfire® Streaming documentation. If the metadata yields some unknown type -- like NVARCHAR, for example, then typechecking for the operator instance will throw an exception. To avoid this, we have to tell the operator what data type we want that column to be.

The way to do that is with the Explicitly declare fields below option for the SQL Result fields property of the Result Settings tab:

(The default setting for this property is Result set from Query -- that's the one that does the automated schema detection on each typecheck.)

What we want to do is to not have the operator always try to determine the schema on each typecheck, and also not do the type conversions automatically. Switch to the Explicitly declare fields below option, and then hit the Execute query and populate fields link there to automaticaly populate the result fields and their types. Then, for the field(s) that are coming back types that can't be automatically converted, set their Types explicitly. For example, NVARCHAR would be string, and NUMBER would likely be int, long, or double, depending on the semantics of the field in the application.

Perhaps unfortunately, for each JDBC Query operator instance, you can either do it the Result set from Query way or the Explicitly declare fields below way, but can't mix them. (You can switch back and forth, though, if connected to the database (if not connected to the database, you might lose the explicit settings with no automated way to reset them).)

If for some reason the operator is not able to successfuly determine any of the result set schema using Execute query and populate fields then you can just type in all the result field names and types manually.