How the Query Operator Works with JDBC Tables

This page describes the steps a Query operator takes when executing a SQL statement against a JDBC Table data source, and describes the most essential property settings.

SQL Statement Execution and Parameter Expression Substitution

A Query operator has a single input port. When the operator processes an input tuple, it executes a single SQL statement, which is specified on the Query Settings tab in the operator's Properties view. The operator creates and uses a JDBC connection to the associated JDBC Table’s data source.

Before it executes the SQL statement, the operator evaluates any parameter expressions in the SQL statement using the current runtime context of the operator. This evaluation includes any values from the current input tuple, and substitutes the result of those evaluations into the SQL statement, after converting the expression values to suitable JDBC-compatible data types. See SQL Statement Parameter Expression Type Conversions below.

Result Set Processing

The execution of some SQL statements produces a result. If that result is either a result set or a cursor, then the Query operator iterates over each row of the result set. The operator extracts the values in the columns of the row and converts them to values of fields in a SQL Results tuple whose qualifier is table.

You can use the table qualifier in expressions in the Output Settings tab of a Query-JDBC operator's Properties view. For example, input1.OrderSize + table.OrderSize. This qualifier refers only to the result set returned from any SQL statement that contains a SELECT or comparable statement.

The operator converts the values of the columns to StreamBase data type values and sets the converted values into corresponding fields in the table tuple. The row to table-tuple conversion is controlled by the SQL Result fields setting on the Result Settings tab of the Query operator's Properties view.

Creating SQL Result Field Schemas from JDBC Result Set Metadata

If the SQL Result fields property is set to Result set from SQL Query, then the operator obtains the metadata for the SQL statement’s result set. This metadata is used to create a field in the SQL Results schema for every column in the result set row. When processing a result set row, each column’s value is converted using an internal set of type mappings, which are described in Result Set Column Type Conversions below.

When creating the table tuple’s schema field names from metadata, the names of table tuple fields are the same as the label or the name of the row’s columns. The column label is preferred when different from the column name. The field name is set to columnX when both the column name and column label are null, empty, or invalid StreamBase identifiers, where X is the one-based position of the column within the row.

If there is a column whose type cannot successfully convert to a StreamBase type, then the entire metadata-based schema conversion fails to typecheck, and the Result set from SQL Query option cannot be used.

Using an Explicitly Declared Result Schema

If you specify the Explicitly declare fields below option for the SQL Result fields property, then the SQL Result schema has fields with the names specified in the Field Name column of the Result Settings Fields grid. These field names are used to get column values from the result set row. The values are converted to the StreamBase data types specified in the Type field, where such conversions are supported.

Output Schema Creation and Output Tuple Processing

The operator creates and emits an output tuple for each row of the result set on the operator’s single output port. The operator constructs output tuples according to the property settings in the Output Settings tab. The output schema fields can be a combination of fields from the SQL Result Fields (table prefix), the input tuple (input1 prefix), and any Additional Expression fields.

For SQL Statements whose execution does not return a value or whose result set is empty, the Query operator by default does not produce any output tuples, or can optionally be configured to emit a single null tuple.

SQL Statement Parameter Expression Type Conversions

The table in this section shows the correspondence between StreamBase data types and java.sql.Types used when evaluating parameter expressions in a Query operator's SQL statement.

Consider this table a general guideline. It has not been exhaustively tested for validity with all JDBC databases supported by StreamBase.

StreamBase Data Type java.sql.Type

boolean

BOOLEAN

double

DOUBLE

int

INTEGER

long

BIGINT

string

VARCHAR or CLOB, depending on how the JDBC database defines the column or parameter

timestamp

TIMESTAMP

timestamp (interval)

TIME

blob

BLOB

tuple

Unsupported

capture

Unsupported

list

Unsupported

function

Unsupported

Result Set Column Type Conversions

The following table shows how java.sql.Types are converted to StreamBase data types in result set columns returned from the associated JDBC data source on execution of the Query operator's SQL statement.

Consider this table a general guideline. It has not been exhaustively tested for validity with all JDBC databases supported by StreamBase.

java.sql.Type StreamBase Data Type

BIT

boolean

BOOLEAN

boolean

TINYINT

int

SMALLINT

int

REAL

double

FLOAT

double

DOUBLE

double

DECIMAL

double

NUMERIC

double

BIGINT

long

DATE

timestamp

TIME

timestamp

TIMESTAMP

timestamp

CHAR

string

VARCHAR

string

LONGVARCHAR

string

BLOB

blob

CLOB

string, up to maxint() characters

LONGVARBINARY

blob

VARBINARY

blob

All others

Unsupported