APPLY Statement

APPLY [PARALLEL [number_of_instances BY field_identifier]] 
    MODULE {"file_name.ssql" | "file_name.sbapp"}
    ['('parameter_list')']
  FROM input_stream_identifier_1=stream_identifier_input_1[, ...]
       [, module_query_table_1=query_table_1[, ...]]
  INTO output_stream_identifier_1=stream_identifier_output_1[, ...]
  [ERROR INTO stream_identifier];

Or

APPLY [PARALLEL [number_of_instances BY field_identifier]] 
    MODULE {"file_name.ssql" | "file_name.sbapp"}
    ['('parameter_list')']
  FROM input_stream_identifier_1=stream_identifier_input_1[, ...]
  INTO output_stream_identifier_1=stream_identifier_output_1[, ...]
       [, module_query_table_1=query_table_1[, ...]]
  [ERROR INTO stream_identifier];

These two formats are identical with the exception of the placement of the inner_module_query_table=outer_module_query_table assignments, which are optionally in either the FROM or INTO clause.

APPLY [PARALLEL] JAVA "java_class_name" ['('parameter_list')']
  [AS operator-name]
  FROM input_stream_identifier_1[, ...]
  INTO output_stream_identifier_1[, ...]
  [ERROR INTO stream_identifier];

APPLY [PARALLEL] JAVA "java_class_name" '('parameter_list')'
  [AS adapter-name]
  FROM stream_identifier | INTO stream_identifier
  [ERROR INTO stream_identifier];

APPLY [PARALLEL] JDBC jdbc_datasource_name "sql_statement" 
  [(result_set_schema)] 
  FROM input_stream_identifier
  INTO output_stream_identifier
  [ERROR INTO stream_identifier];

For all forms of APPLY, you can append an ERROR INTO clause just before the closing semicolon. The StreamSQL ERROR INTO clause is analogous to the Enable Error Output Port check box for operators and adapters in EventFlow applications.

Use ERROR INTO with the name of a stream, which must already exist. This sets up an Error Port for the operator or module, which is much like a local catch mechanism for errors from that operator or module.

See Using Error Ports and Error Streams for a discussion of StreamBase error handling mechanisms.

To use a StreamSQL or EventFlow application as a module within another StreamSQL application, use the APPLY statement. Since the module application can have multiple input and output streams, you must specify a mapping between the streams in the StreamSQL application corresponding to each of the module's streams.

The name of the file that identifies the module must be within quotation marks. The input_stream_identifier=stream_identifier_input entries following the FROM keyword map each of the module's input streams to streams in the referencing StreamSQL application or module. The output_stream_identifier=stream_identifier_output entries following the INTO keyword map each of the module's output streams to streams in the referencing StreamSQL application or module. If the module only has one input or output stream, list the name of the corresponding stream in the referencing StreamSQL application or module. That is:

stream_identifier_input

Rather than:

module_input_stream_identifier=stream_identifier_input

When a referencing StreamSQL application or module implements a shared query table that an inner module accesses, include the inner_module_query_table=outer_module_query_table assignment(s) in the FROM clause. When a referencing StreamSQL application or module accesses a shared query table implemented in an inner module, include the inner_module_query_table=outer_module_query_table assignments in the INTO clause; the identifier outer_module_query_table is declared in a CREATE TABLE outer_module_query_table statement within the outer StreamSQL application or module.

The optional number_of_instances BY field_identifier clause can only be used if the optional PARALLEL is specified. The use of this clause allows multiple instances of the module to be instantiated. The type of field_identifier must be an integer or be converted to an integer. If the type of field_identifier is a string, use the hash(field_identifier) function to generate an integer from the value of the string. Tuples with the same field_identifier value are guaranteed to be processed by the same module instance. Use of the PARALLEL keyword without the optional clause creates only a single instance of the module, which processes all incoming tuples.

The optional parameter_list clause, used to pass parameter values to the module, consists of one or more parameter_name=value entries, separated by commas. When passing parameters to a parameterized module, value must always be enclosed in quotation marks ("value") regardless of its type; if value is a string type, then it must also be enclosed within escaped quotation marks ("\"value\""). Modules that utilize parameters have default values for the parameters set at design time. The parameter_list entries are only required for parameters that need to be reset to a different value at run time.

To use a Java operator within a StreamSQL application, provide, in quotation marks, the full package and class name of the implementation class, and enclose in parentheses the parameters used by this class. These are the same parameters that are set on the Parameters tab of the EventFlow Java operator. The source of tuples submitted to the Java operator is the stream referenced in the FROM clause, and the tuples emitted by the operator are written to the stream referenced in the INTO clause.

The APPLY JDBC statement allows you to run SQL queries from within your StreamSQL application against tables in an external database.

The first argument of the statement references the name of an external data source. As soon as the statement is entered, StreamBase Studio connects to the data source. If the connection fails (for example, an "unknown data-source" message is displayed), the StreamSQL Editor cannot typecheck the application. Therefore, a <data-source> must be configured within the project's sbd.sbconf configuration file before the APPLY JDBC statement can be coded. See examples for data-source configurations in <data-sources> Examples and <data-source> Examples.

In the following StreamSQL code, the application references the myDB data source configured above, then queries a table, and finally sends the result to the application's output stream:

CREATE INPUT STREAM in (symbol string, price double, SKU int);
CREATE OUTPUT STREAM out;

APPLY JDBC myDB "SELECT * (symbol string, id int) 
    FROM table WHERE column_name={symbol}" FROM in INTO out;

Within the SQL statement, string literals are demarcated by single quotation marks. Since braces ({ and }) are used to demarcate parameters, braces used within the SQL statement must be escaped with the backslash (\) character.

As the preceding example shows, APPLY JDBC statements process data from both the external database and an input stream in the application:

To use a Java embedded adapter in a StreamSQL application, provide, in quotation marks, the full package and class name of the implementation class, and enclose in parentheses the parameters used by this class. These are the same parameters that are set on the Adapter Settings tab of the EventFlow embedded adapter. Parameter values that are strings must be enclosed in double quotation marks. Parameter values are not positionally defined and can be entered in any sequence.

For Java embedded adapters, you can pass a schema in your parameter_list. (This feature is available only in StreamSQL, not in EventFlow applications). The schema is specified as a parenthesized, comma-separated list of field names and types in the tuple produced by the adapter, in the following form:

schema-name=(field-name type[,field-name type...])

The sequence of types must be the same as the data in the input file.

For example, this statement from the CSV Reader adapter includes a schema:

APPLY JAVA "com.streambase.sb.adapter.csvreader.CSVReader"
  (FileName="example.csv", Delimiter=",", QuoteChar="\"",
  TimestampFormat="MM/dd/yyyy hh:mm:ss aa", Period=0, Repeat=1,
  Schema=(Timestamp timestamp, Username string, IP string))
  INTO OutputStream1;

If the adapter reads data from an external input adapter, then an INTO clause identifies the named stream that will receive tuples from the adapter. If the adapter writes data to an external output adapter, then a FROM clause identifies the named stream from which the adapter receives tuples.

StreamNames.0="namedStream1",
StreamNames.1="namedStream2"

Schema=
"<?xml version=\"1.0\" encoding=\"UTF-8\"?>
 <schema name=\"schema:OutputAdapter1\"
   uuid=\"D41D8CD98F00B204E9800998ECF8427E\"/>
   <field name=\"f0\"
           size=\"4\" type=\"string\"/>
   <field name=\"f1\"
           type=\"int\"/>
 </schema>"