How the Query Operator Works with JDBC Tables

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.

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.

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.

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.

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.

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.

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.