JDBC Table Sample

Contents

About This Sample
Importing This Sample into StreamBase Studio
Running This Sample in StreamBase Studio
This Sample's Files
Sample Location

About This Sample

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.

Note

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.conf and 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.jdbcdatasource to specify a name for your data source and its login parameters. You can use one of the files in the config-examples folder 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.

Importing This Sample into StreamBase Studio

In StreamBase Studio, import this sample with the following steps:

  • From the top-level menu, click File>Import Samples and Community Content.

  • Enter jdbc to narrow the list of options.

  • Select Query operator used with a JDBC data source from the Data Constructs and Operators category.

  • Click Import Now.

StreamBase Studio creates a new project for the sample.

Running This Sample in StreamBase Studio

  1. Configure a HOCON file in src/main/configurations like the provided file jdbc-mysql5.conf to describe the connection details for your database instance, or edit the provided file to add your database instance.

  2. Make sure the database you are connecting to is up and active.

  3. 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 Updating, Downloading, Building, or Rebuild project messages finish before you proceed.

  4. Open the src/main/eventflow/packageName folder.

  5. If you know that a table named OrderBook already exists on your database instance with the expected schema (perhaps created by a colleague running this sample), proceed to step 11.

  6. Open the CreateLoadTable.sbapp module. In the OrderBook table's Data Source tab, from the dropdown menu, select the JDBC Data Source name you defined in your configuration file. Notice that the instance name MySQL5 appears in the dropdown list, as well as any other database instance name you have added.

  7. Click the Run button. This opens the SB Test/Debug perspective and starts the module.

  8. In the Manual Input view, select the InsertIn stream. Enter any values and click Send Data. If the OrderBook table exists, the Output Streams view shows a successful insert operation. If not, the response is an evaluation exception.

  9. To create the OrderBook table, select the CreateOBTable stream and click Send Data. 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.txt for a few suggestions.

    Optional

    If the table exists, but you want to start from a clean slate, you can use the DropOBTable stream before the CreateOBTable stream. Check to make sure this action does not step on the work of a colleague who may be running tests against a previously populated OrderBook table.

  10. When the OrderBook table exists, press F9 or click the Terminate EventFlow Fragment button.

  11. Open the JDBCQuery.sbapp module. Again select your configured database instance's name In the OrderBook table's Data Source tab, from the dropdown menu in the JDBC Data Source control.

  12. Click the Run button.

  13. In the Test/Debug perspective, go to the Feed Simulations tab, select OrderBookFeedSim from the list, and click the Run button. Rows are inserted to the OrderBook table through the InsertIn input stream. If you view the InsertOut output stream, you can see those data records as they are entered.

  14. In the Feed Simulations tab, click the Stop button.

  15. Switch to the Manual Input view. Select the ReadIn input stream from the dropdown menu, and click Send Data. This issues a query that returns all rows in the table where Side='bid'.

  16. 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 Send Data.

    Run step 15 again to confirm that this QuoteID was deleted from the OrderBook table.

  17. When done, press F9 or click the Terminate EventFlow Fragment button.

This Sample's Files

The following files are included in the sample:

src/main/eventflow/packagename/JDBCQuery.sbapp

The primary EventFlow module for this sample project. Its OrderBook table can be queried using any of three streams (ReadIn, DeleteIn, and InsertIn). Results from each Query operator are sent to its connected output stream.

src/main/eventflow/packagename/CreateLoadTable.sbapp

An EventFlow module to create the OrderBook table if it does not exist, to delete the table if it does, and to send in tuples that load the table. (This module's SQLQueryToLoad operator and the above module's WriteOrderBook operator are identical.)

src/main/configurations/jdbcdatasource.conf

A configuration file in which to define the connection URL, credentials, and optional parameters for your database instance.

config-examples/*.conf

This folder contains a number of configuration file samples for different databases supported by StreamBase.

src/main/resources/OrderBookFeedSim.sbfs

A simple feed simulation to populate the OrderBook table.

src/main/resources/create-table.txt

A text file containing SQL code snippets for creating the OrderBook table for several database vendors. Insert the snippet for your configured database type in the SQLQueryToCreate operator in the CreateLoadTable.sbapp module.

Sample Location

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.

Important

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: 

studio-workspace/sample_jdbc-query

See Default Installation Directories for the default location of studio-workspace on your system.