This sample demonstrates how to use the JDBC Table Data Construct in an EventFlow module to read from, write to, update, and query an external, persistent JDBC data source.
As shipped, this sample is configured to use the MySQL database because the JDBC drivers for MySQL are publicly available on Maven Central sites. This saves the downloading and installing of a JDBC driver required by other database vendors. However, the two EventFlow modules still do not typecheck because they cannot connect to the example JDBC URL in the included configuration file.
Before this sample can run, you must:
If you are connecting to a MySQL database, edit
src/main/configurations/jdbc-mysql5.confand provide valid serverURL, userName, and password settings for your actual MySQL instance.
If you are connecting to a database from a different vendor, download and install into your local Maven repository (or to a site-specific repository) the JDBC driver JAR file or files that provide JDBC access to that database. See Using External JAR Files for instructions on Maven-installing JAR files.
Configure a HOCON file of type
com.tibco.ep.streambase.configuration.jdbcdatasourceto specify a name for your data source and its login parameters. You can use one of the files in the
config-examplesfolder as a starting point.
Select the specified data source name in the Query Table properties for this sample.
The sample uses a JDBC Table Data Construct named OrderBook, which is linked to the external JDBC table specified in the project's configuration.
In StreamBase Studio, import this sample with the following steps:
From the top-level menu, click> .
jdbcto narrow the list of options.
Select Query operator used with a JDBC data source from the Data Constructs and Operators category.
StreamBase Studio creates a new project for the sample.
Configure a HOCON file in
src/main/configurationslike the provided file
jdbc-mysql5.confto describe the connection details for your database instance, or edit the provided file to add your database instance.
Make sure the database you are connecting to is up and active.
In the Project Explorer view, open this sample's folder.
Keep an eye on the bottom right status bar of the Studio window. Make sure any
Rebuild projectmessages finish before you proceed.
If you know that a table named
OrderBookalready exists on your database instance with the expected schema (perhaps created by a colleague running this sample), proceed to step 11.
CreateLoadTable.sbappmodule. In the OrderBook table's Data Source tab, from the drop-down menu, select the name you defined in your configuration file. Notice that the instance name
MySQL5appears in the drop-down list, as well as any other database instance name you have added.
Click the Run button. This opens the SB Test/Debug perspective and starts the module.
In the Manual Input view, select the
InsertInstream. Enter any values and click . If the
OrderBooktable exists, the Output Streams view shows a successful insert operation. If not, the response is an evaluation exception.
To create the
OrderBooktable, select the
CreateOBTablestream and click . Look for a success message in the Output Streams view.
You may need to adjust the SQL statement in the SQLQueryToCreate operator for your database vendor's standards. See
src/main/resources/create-table.txtfor a few suggestions.
If the table exists, but you want to start from a clean slate, you can use the
DropOBTablestream before the
CreateOBTablestream. Check to make sure this action doesn't step on the work of a colleague who may be running tests against a previously populated
OrderBooktable exists, press F9 or click the Terminate EventFlow Fragment button.
JDBCQuery.sbappmodule. Again select your configured database instance's name In the OrderBook table's Data Source tab, from the drop-down menu in the control.
Click the Run button.
In the Test/Debug perspective, go to the Feed Simulations tab, select
OrderBookFeedSimfrom the list, and click the button. Rows are inserted to the
OrderBooktable through the
InsertIninput stream. If you view the
InsertOutoutput stream, you can see those data records as they are entered.
In the Feed Simulations tab, click thebutton.
Switch to the Manual Input view. Select the
ReadIninput stream from the drop-down menu, and click . This issues a query that returns all rows in the table where
From the Manual Input view, select the DeleteIn input stream. Based on the results from the previous Read query in the Output Streams view, type a known value for the QuoteID field from near the bottom of the list, and click.
Run step 15 again to confirm that this QuoteID was deleted from the OrderBook table.
When done, press F9 or click the Terminate EventFlow Fragment button.
The following files are included in the sample:
The primary EventFlow module for this sample project. Its
OrderBooktable can be queried using any of three streams (
InsertIn). Results from each Query operator are sent to its connected output stream.
An EventFlow module to create the
OrderBooktable if it doesn't exist, to delete the table if it does, and to send in tuples that load the table. (This module's
SQLQueryToLoadoperator and the above module's
WriteOrderBookoperator are identical.)
A configuration file in which to define the connection URL, credentials, and optional parameters for your database instance.
This folder contains a number of configuration file samples for different databases supported by StreamBase.
A simple feed simulation to populate the
A text file containing SQL code snippets for creating the
OrderBooktable for several database vendors. Insert the snippet for your configured database type in the
SQLQueryToCreateoperator in the
When you load the sample into StreamBase Studio, Studio copies the sample project's files to your Studio workspace, which is normally part of your home directory, with full access rights.
Load this sample in StreamBase Studio, and thereafter use the Studio workspace copy of the sample to run and test it, even when running from the command prompt.
Using the workspace copy of the sample avoids permission problems. The default workspace location for this sample is:
See Default Installation Directories for the default location of
studio-workspace on your system.