Using the Query Operator with JDBC Tables

Introduction to Querying JDBC Data

The Query operator allows you to perform read, write, update, and delete operations on data in a supported SQL database connected to StreamBase through a JDBC connection.

A Query operator connected to a JDBC Table has two sources of incoming data: the stream of tuples on its input port, and rows of data retrieved from the associated data source, if any. You can use the Query operator with a JDBC database to accomplish many tasks, including:

Query the associated database and emit the result on the operator's output stream.
Add, replace, or supplement the database with fields from the input stream.
Store incoming tuple data in the database.
Add or replace data from the associated database to the input stream to form the output stream.

The Query operator lets you query the associated database using standard SQL statements, such as SELECT, INSERT, UPDATE, and DELETE. The exact SQL syntax to use depends on the brand and vendor of database in use.

Tip

JDBC Query operators that specify a select statement generate a typecheck error when working in an environment that does not have connectivity to the database server. As a workaround, you can specify an explicit schema for the SQL query in the operator's Result Settings tab. If you anticipate having database connectivity when composing the operator, but none later, use the Execute query and populate fields link that derives the schema from executing the SQL query.

Associating With and Configuring a JDBC Table

To associate a Query Operator with a JDBC Table data construct in StreamBase Studio, create an arc connecting the bottom port of the operator and the port on the top of the JDBC Table. Each Query operator can be associated with only one data construct, but a JDBC Table can be associated with multiple Query operators.

Before you can define the properties of the Query operator, you must configure the JDBC connection to the database with the following steps:

To give StreamBase Studio runtime access to the JAR file that implements the JDBC driver for your database, add the JAR file to the Java Build Path for your Studio project:
- Select the project of interest in the Package Explorer view.
- Right-click and select Build Path → Add External Archives.
- Navigate to the folder that contains your database vendor's JDBC JAR file and select it.
- Click Open.
Independent of the previous step, you must also specify the path to the JDBC driver's JAR file in the server configuration file, usually named sbd.sbconf. Add a <jar> child element to the <java-vm> element of this file, like the following example for Windows, showing the full path to an Oracle JDBC JAR file:
```
<java-vm>
  <param name="jvm-args" value="-Xms512m -Xmx1024m" />
  <jar file="c:/lib/JARs/ojdbc14.jar" />
</java-vm>
```
Use the forward slash path separator, even for Windows paths. For UNIX, the server configuration file is like the following example:
```
<java-vm>
  <param name="jvm-args" value="-Xms512m -Xmx1024m" />
  <jar file="/usr/share/java/ojdbc14.jar" />
</java-vm>
```
For both Studio and StreamBase Server run independent of Studio, set up a <data-source> element of the server configuration file that names and configures a JDBC connection to your database. See Configuring a JDBC Data Source.
In the server configuration file, specify any parameters for your database connection. In particular, notice that the default value of jdbc-reconnect-attempts is zero, which specifies no reconnection attempts. See JDBC Data Source Options for a summary of the various database reconnection parameters.
In Studio, select the JDBC Table's icon in your application, and go to the Data Source tab of its Properties view. From the JDBC Data Source drop-down list, select the name of the data source connection you configured in the previous step.

These steps are explained in more detail in Configuring a JDBC Data Source.

The remainder of this topic describes the actions you can take on each tab of the Query operator's Properties view when connected to a JDBC Table data construct.

Properties: General Tab

Name: Use this field to specify or change the component's name, which must be unique in the application. The name must contain only alphabetic characters, numbers, and underscores, and no hyphens or other special characters. The first character must be alphabetic or an underscore.

Enable Error Output Port: Select this check box to add an Error Port to this component. In the EventFlow canvas, the Error Port shows as a red output port, always the last port for the component. See Using Error Ports and Error Streams to learn about Error Ports.

Description: Optionally enter text to briefly describe the component's purpose and function. In the EventFlow canvas, you can see the description by pressing Ctrl while the component's tooltip is displayed.

Properties: Query Settings Tab

Use the Query Settings tab to specify the SQL statement to send to the associated database. This tab can be edited only after you connect the Query operator to a configured and working JDBC Table data construct.

Associated Connection

A read-only field that identifies the JDBC Table data construct connected to this Query operator.

Options

Use this option to change the default response to a SQL statement that returns no values. The default behavior is that no tuple is output for an empty return from the SQL statement. Select the check box for Output null tuple on empty Result Set to return a tuple that contains the reserved value null.

This option is only available for EventFlow applications. There is no analogous option in StreamSQL to change the default behavior.

Timeout

Use this field to specify an integer time in milliseconds after which this query times out. You can still use the jdbc-query-timeout option in the <data-source> element of the server configuration file to specify a default timeout for all queries to that data source.

SQL statement

Query operations on the associated database are performed using the associated database's native SQL variant, not StreamBase functions. Use this field to specify the operations you need to perform, including selecting, inserting, deleting, and updating values, and calling stored procedures. Enter a single, tested, known-working SQL statement in the SQL statement field.

Consider the following points when designing and testing the statements you enter in the SQL statement field.

Develop SQL outside of Studio first. Use your database vendor's SQL query utility, or a third-party application such as DbVisualizer, to develop and test your SQL statement outside of StreamBase Studio. This allows you to separate SQL syntax and format errors from errors derived from StreamBase connection issues. Once you have a known working SQL statement, copy it to the SQL statement field.
Limit query results. By default, the Query operator includes the entire result set of your SQL statement in its output tuple. To omit all of or a portion of the JDBC Table query results, use the Result Settings tab.
SQL Syntax. The syntax of your SQL query in the SQL statement field varies with the database vendor, and is documented by your database vendor. For example, if your external data source is an Oracle server, use Oracle PL/SQL syntax. There is no StreamBase-specific syntax for the SQL statements to send, other than the brace-enclosed field names described next.
Use StreamBase Fields and Expressions. Use braces to delimit StreamBase field names and expressions in your SQL statement. For example, the following statement inserts three fields from the Query operator's input stream into the associated table:

INSERT INTO Bikes values ({id}, {make}, {model})

Your brace-delimited expression can include any valid expression in the StreamBase expression language, including the names of dynamic variables. For example: {abs(id)}, {id+1000}, {my-dynamic-var}.
Studio Typechecking and SQL. StreamBase Studio provides some assistance as you work. Whenever StreamBase Studio performs a typecheck, the statements in your SQL Statement field are sent to the external database. If your code contains SQL syntax errors, the database server may return an error to StreamBase. If automatic typechecking is enabled, such errors are passed through to the Typecheck Errors view. On the EventFlow canvas, the background color of the Query operator changes colors to indicate a typecheck error, including SQL errors.
Stored Procedures. If your external database supports stored procedures, you can create, modify, and call stored procedures in the SQL statement field. Use your database vendor's SQL syntax when calling or changing stored procedures.

When calling a stored procedure in the SQL statement field, you must provide a schema for the query's expected result set. In the Result Settings tab of the Query operator's Properties view, select Explicitly Specify Result Set Schema in the Output Schema drop-down list. Then, specify the fields of the schema in the Fields table. The names of fields in the schema must match the column names returned from the query.

For some databases, such as Oracle, there are additional steps required when calling stored procedures, as discussed in Using Stored Procedures.

Properties: Result Settings Tab

This tab only appears when the Query operator is connected to a JDBC Table. This tab has the following controls:

Use CallableStatement to execute stored procedures

This control enables special handling for the result sets from called stored procedures or stored functions from Oracle databases. Enable this control ONLY when querying a stored procedure in an Oracle database. Most supported databases do not require this setting when calling stored procedures. (StreamBase may support other database systems in the future that also require this setting.)

JDBC parameter index with result

This control is disabled unless Use CallableStatements is enabled. Specify the one-based JDBC index number of the parameter in the called stored procedure that contains the output of interest. See Using Stored Procedures for further explanation and an example.

SQL Results fields

Controls which of the fields in the result set returned from the Query Settings tab's SQL statement are to be included in the output stream. Choose one of the following options:

Result set From SQL Query

Use the option to include all result set fields in this operator's output tuple. This is the default behavior.

Explicitly declare fields below

Use this option to limit the result set fields to be included in this operator's output tuple, or to specify the fields expected from a stored procedure call that returns a result set. Use this option only when sending a SELECT statement or when calling a stored procedure or stored function. Do not use this option for SQL statements that return only a status or return code or for non-SELECT statements such as INSERT or CREATE. Do not use for stored procedures or stored functions that do not return a result set.

When you select this option, Studio displays the SQL Results fields grid. Edit the grid to specify a subset of the fields resulting from your SQL query. You can use the Execute query and populate fields link to send the SQL statement to the database. This returns the columns of the queried table, which then automatically populates the Fields grid. Populating the Fields grid in advance has the advantage of not requiring a database query during typechecking.

Timesaver

The expected result set from your SQL statement may have a schema that you use elsewhere in your application. In this case, set up and specify a named schema for the Fields grid.

When the SQL statement includes a call to a stored procedure or stored function, if that stored object returns a result set, you must specify the schema of the result set. In this case, the names of fields in the schema must match the column names returned from the query.

Refactor All JDBC Query Operators

Studio has a wizard that walks through a module and inspects all Query operators connected to JDBC data sources. If the SQL Statement in such a Query operator returns a result set, the wizard changes the operator to use the Explicitly declare fields below option, and populates the SQL Results fields grid based on executing the SQL statement and examining the result set.

This use of the wizard is the same as opening each JDBC Query operator in turn and going to its Properties view, selecting Explicitly declare, then clicking the Execute query link. You can also run the wizard to revert all JDBC Query operators back to the Result set from SQL Query option.

Invoke this wizard in the following ways:

Invocation	Current Selection	Runs on
The Refactor context menu on the canvas.	—	All JDBC Query operators in the current module.
The StreamBase context menu in the Package Explorer view.	One EventFlow module.	All JDBC Query operators in that module.
The StreamBase context menu in the Package Explorer view.	A Studio project.	All JDBC Query operators in the modules you select from that project.
The EventFlow → Refactor menu.	One EventFlow module.	All JDBC Query operators in that module.
The EventFlow → Refactor menu.	A Studio project.	All JDBC Query operators in the modules you select from that project.

Properties: Output Settings Tab

Use the Output Settings tab specify the schema of tuples emitted from Query operations.

The Output Settings tab uses one or two field grids plus an Additional Expressions grid like the ones used by the Map, Query, and Gather operators:

For Query Read operations, this tab has three grids: SQL Result Fields, Input Fields, and Additional Expressions.
For Query Update, Delete, and Insert operations, this tab has two grids: Input Fields and Additional Expressions.

Field grids and the Additional Expressions grid operate the same way they do in the Output Settings tabs for the Map and non-JDBC Query operators. That is, the changes you specify in this tab are applied in top-down order in two ways: top to bottom in the order of grids in the tab, and top to bottom in the order of field expressions in each grid. That is, the output tuple is assembled in the following way:

For Query-Read operations only, fields in the SQL results tuple, if any are specified. These fields are either columns retrieved from the JDBC database as a result of the SQL query, or the set of explicitly declared fields, depending on the setting of the SQL Result fields control on the Result Settings tab.
For all Query operations, fields in the incoming tuple of the Query operator, if any are specified.
Any additions, subtractions, or reorderings for input tuple fields in grid order.
Any additions, subtractions, or reorderings for any of the above fields as determined by expressions in the Additional Expressions grid.

Use the field grids as described for the Map operator in Using Field Grids.

Properties: Concurrency Tab

In the Query operator's Properties view, the Concurrency tab only appears when the operator is connected to a JDBC Table data construct. The tab is absent with other connections.

Use the Concurrency tab to specify parallel regions for this instance of this component, or multiplicity options, or both. The Concurrency tab settings are described in Concurrency Options, and dispatch styles are described in Dispatch Styles.

Caution

Concurrency settings are not suitable for every application, and using these settings requires a thorough analysis of your application. For details, see Execution Order and Concurrency, which includes important guidelines for using the concurrency options.

If you have multiple Query operators connected to the same JDBC Table, parallel operations may also allow you to avoid database deadlocks. For further information, see JDBC Data Source Overview.