Contents
Each Query operator connects to and interacts with one StreamBase data construct per Query operator. You can connect a Query operator to a JDBC Table or Query Table. The Query operator shows different tabs in its Properties view, and shows different controls on each tab, according to the data construct it is connected to and the options selected.
Note
This page describes the Query operator only when used with the JDBC Table data construct.
See Using the Query Operator to learn about the Query operator when used with Query Tables.
For clarity, the Query operator described on this page is sometimes referred to as the Query-JDBC operator.
The Query-JDBC operator allows you to perform any valid
SQL operation on data in a supported SQL database connected to StreamBase through a JDBC connection.
A Query operator connected to a JDBC Table has two possible sources of incoming data:
-
The stream of tuples on its input port.
-
If the SQL statement is a SELECT or other statement that returns data from the connected database, then the rows of data retrieved from the associated data source.
You can use the Query-JDBC operator with a JDBC database to accomplish many tasks, including:
-
Created, populate, or drop tables in the associated database.
-
Query a table in 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 all incoming tuple data in the database.
-
Supplement the operator's input stream by adding fields from the associated database to form the output stream.
-
Modify the input stream by replacing input fields with similar fields in the database.
-
Modify the input stream by recalculating input fields based on field information in the database.
The Query-JDBC operator lets you query the associated database using standard SQL statements, such as SELECT, INSERT, UPDATE, DELETE, CREATE, and DROP. 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 a current connection 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 EventFlow module, but none later, use the Execute query and populate fields link in the JDBC Query operator. This derives the schema from executing the SQL query and stores the schema for use when the operator cannot reach its configured database.
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 as described 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.
Name: Use this required field to specify or change the name of this instance of this component, which must be unique in the current EventFlow module. 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 to learn about Error Ports.
Description: Optionally enter text to briefly describe the component's purpose and function. In the EventFlow Editor canvas, you can see the description by pressing Ctrl while the component's tooltip is displayed.
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 from this operator 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. - Timeout
-
Use this field to specify an integer time in milliseconds after which this query times out. Enter zero or leave the field blank to specify no timeout. You can still use the
jdbc-query-timeoutoption 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, 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 SQL 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}.In composing your expressions, refer to SQL Statement Parameter Expression Type Conversions.
-
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 connected 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.
-
This tab only appears for Query-JDBC operators. 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 CallableStatement 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
-
This control manages which of the fields in the result set returned from the Query Settings tab's SQL statement are to be included in this operator's 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, CREATE, or DROP. 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.
In composing your expected result set, refer to Result Set Column Type Conversions.
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, 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 Project Explorer view. | One EventFlow module. | All JDBC Query operators in that module. |
| A Studio project. | All JDBC Query operators in the modules you select from that project. | |
| The > menu. | One EventFlow module. | All JDBC Query operators in that module. |
| A Studio project. | All JDBC Query operators in the modules you select from that project. |
|
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, Gather, and non-JDBC Query operators:
-
For SQL Statements that return a result set or a cursor, this tab has three grids: SQL Result Fields, Input Fields, and Additional Expressions.
-
Otherwise, 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:
-
First, for SQL Statements that return a result set or a cursor, the output tuple includes any fields in the SQL results tuple specified in the Output Settings tab. 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.
-
Next, for all Query-JDBC operations, fields in the incoming tuple of this operator, if any are specified here.
-
Next, any additions, subtractions, or reorderings for input tuple fields specified in top-down grid order.
-
Finally, any additions, subtractions, or reorderings for any of the above fields as determined by expressions in the Additional Expressions grid.
Use the field grids in this operator as described in Using Field Grids. The difference in using Output Settings field grids with JDBC data sources is a different set of qualifiers to distinguish same-named fields in expressions:
| input. | Field from the input tuple. |
| table. |
If the SQL query includes a SELECT or comparable statement that returns a
result set or a SQL cursor, then the Query-JDBC operator iterates over each
row of the result set. Use the table. qualifier to reference field values
in the result set row currently being processed. The table. qualifier is comparable to the
current. qualifier in the
non-JDBC Query operator.
See Result Set Processing for further details. |
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.