JDBC External Adapter

A sample application is included with the kit. It is installed in the sample directory, as jdbc. The sample contains an example .sbapp file, highs_and_lows.sbapp and a configuration file, highs_and_lows.sbconf, can help you get started using your JDBC adapter. For details, please refer to the sample's README file.

Zero or more StreamBase endpoint blocks, defining named connections to StreamBase Servers that can be used by any number of queries.

<sb-endpoint name="[name]" uri="[uri]"/>

<sb-endpoint name="sb1" uri="sb://localhost:1000/"/>

One or more JDBC Connection blocks, which define named connections to remote databases using JDBC, that can then be used by any number of queries.

<jdbc-connection name="[name]" driver="[driver]"
     connection-url="[curl]" username="[user]" password="[password]"/>

<jdbc-connection name="c1" driver="oracle.jdbc.driver.OracleDriver"
     connection-url="jdbc:oracle:thin:@mydb:1521:db"
     username="scott" password="tiger"/>

Zero or more Startup query configuration blocks. Startup queries are executed when the adapter starts, and run only once. They are all executed in the order in which they appear in the configuration file. Startup queries execute any query on a database, requiring a db-connection attribute, and may optionally retrieve data from the database and enqueue the results back onto a StreamBase Input Stream, requiring then both a sb-connection and dest-stream attributes.

The adapter by default will execute each Startup Query, one at a time, blocking until each query has finished. Optionally, if you have queries that may execute at the same time, you can use a background option that allows a query to execute in the background, allowing the adapter to continue on to the next startup query. Regardless of background settings, the adapter will always wait for all startup queries to finish before running any other non-startup queries.

<startup db-connection="[db]" [name="name"] [sb-connection="[sb]" dest-stream="[stream]"]>
        <param name="sql" value="[sql]"/>
        [<param name="run-in-background" value="[background]"/>]
        [<param name="fetch-size" value="[f]"/>]
        [<param name="streambase-buffering-size" value="[b]"/>]
        [<param name="enqueue-on-query-completion" value="[tuple]"/>]
</startup>

<startup db-connection="c1" name="start1">
   <param name="sql" value="DELETE FROM DATA"/>
 </startup>

<startup db-connection="c1" sb-connection="sb1" dest-stream="Preload"
          name="start2">
   <param name="sql" value="SELECT ID, VALUE FROM PRELOAD"/>
   <param name="enqueue-on-query-completion" value="-1/">
</startup>

Zero or more DML statements: queries that modify table contents such as INSERT or UPDATE queries) configuration blocks. DML blocks define passive statements that are executed when certain conditions are met, without enqueueing any results back onto the StreamBase Server. Currently, the only supported condition is the appearance of a tuple on a specified Output Stream from a StreamBase Server. The statement can pull values from the tuple that caused it to be executed.

<dml db-connection="[db]" [name="name"] sb-connection="[sb]" trigger-stream="[stream]">
    <param name="sql" value="[sql]"/>
    [<param name="use-prepared-statement" value="[prepare]"/>]
    [<param name="batching-type" value="[batchtype]"/>]
    [<bindings>
         (<parameter pos="[n]" field="[f]"/>)+
     </bindings>]
</dml>

<dml db-connection="c1" sb-connection="sb1" name="clear" trigger-stream="DeleteAll">
   <param name="sql" value="DELETE FROM DATA"/>
 </dml>

<dml db-connection="c1" sb-connection="sb1" name="clear2" trigger-stream="DeleteID">
   <param name="sql" value="DELETE FROM DATA WHERE ID = ?"/>
   <param name="batching-type" value="sb-match"/>
   <bindings>
      <parameter pos="1" field="id"/>
   </bindings>
 </dml>

Zero or more Query configuration blocks. Query blocks define passive statements that are executed when certain conditions are met, and unlike DML blocks, expect a result set that will be enqueued back onto the StreamBase Server. The two supported conditions are the appearance of a tuple on a specified Output Stream from a StreamBase Server, and a timer that periodically executes the query. The query can use bindings to values from the tuple that caused it to be executed in the former case.

<query db-connection="[db]" [name="name"] sb-connection="[sb]" type="[type]"
   dest-stream="[dstream]" [trigger-stream="[tstream]" | interval="[interval]"]>
    <param name="sql" value="[sql]"/>
    [<param name="fetch-size" value="[f]"/>]
    [<param name="streambase-buffering-size" value="[b]"/>]
    [<param name="enqueue-on-query-completion" value="[tuple]"/>]
    [<bindings>
         (<parameter pos="[n]" field="[f]"/>)+
     </bindings>]
</query>

<query db-connection="c1" sb-connection="sb1" name="poll" type="poll" 
 interval="5000" dest-stream="LastUpdates">
 <param name="sql" value="SELECT * FROM Last_Update"/>
</query>

<query db-connection="c1" sb-connection="sb1" name="feed1" type="triggered"
    trigger-stream="ReadAll" dest-stream="AllData">
    <param name="sql" value="SELECT * FROM DATA"/>
</query>

<query db-connection="c1" sb-connection="sb1" name="feed2" type="triggered"
    trigger-stream="ReadAll" dest-stream="AllData">
    <param name="sql" value="SELECT * FROM DATA WHERE ID = ?"/>
    <bindings>
      <parameter pos="1" field="id"/>
    </bindings>
</query>

The StreamBase Adapter for JDBC does not ship with any JDBC database drivers. The relational database vendor will provide JDBC drivers in a JAR file. The JDBC adapter will need to execute with this JAR file in its classpath.

On Linux, the classpath is extended by editing the file streambase-directory/bin/sb-jdbc. The classpath for the JDBC adapter is defined in this file. Include the classpath for a JDBC driver by editing the line that executes the adapter; this line looks something like exec $java -cp "$sb_all:$ad_jar" $adapter_class $@.

Similarly, the classpath used to execute the JDBC driver needs to be extended on Windows. This is accomplished by editing the file streambase-install-dir\bin\sb-jdbc.ini. The line that defines the classpath, which looks something like Class Path=streambase-install-dir\lib\sb-jdbc.jar;streambase-install-dir\lib\sbclient.jar, needs to be extended to include the vendor's JDBC JAR file.

The adapter expects one argument: the file name of its configuration file. It also accepts:

The adapter will read the configuration file, and if no errors are found, will begin executing every startup query first. Once all these have completed, the adapter will load and begin processing DML and query blocks.

If an error occurs during the processing of any query, that query will report the error and shutdown, but all other queries will continue unaffected. If no queries remain running, the adapter shuts down.

If large fetching or buffering parameters are set for configured queries, it is recommended to increase the default heap size Java allocates to the adapter. For this, add the -Xmx and -Xms JVM options to the command line. See the Java documentation for more details on these options.

Back to Top ^