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 a StreamBase module to read from, write to, update, and query an external, persistent JDBC data source.

Note

As shipped, this sample cannot run until you complete configuration steps, which may include downloading and installing your database vendor's JDBC JAR files.

Before this sample can run, you must:

  • Install into your local Maven repository the JDBC driver JAR file or files that provide JDBC access to your database of interest.

  • Configure a HOCON file of type jdbcdatasource to specify a name for your data source and its logon parameters.

  • 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 an external JDBC table by means configured specifications in the project's configuration files.

Note

As shipped, this sample is not completely configured. You must provide setup in your Maven repository to include the required JDBC JAR files. These JAR files must be added to your local Maven repository (or to a site-specific repository) before the samples will properly run. See Using External JAR Files for instructions on installing JAR files.

You must also edit the JMS server configuration to provide site-specific information, such as the host name of your database server.

You must obtain the JAR files that enable JDBC access to your database from the database vendor, unless the vendor places those files in a universally accessible repository such as Maven Central. The sample ships with a configuration file ready to run the MySQL database, which is one of the vendors with its JAR files on Maven Central. Most vendors require you to download the JAR files from the vendor's web site and install them manually.

Importing This Sample into StreamBase Studio

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

  • From the top-level menu, click File → Load StreamBase Sample.

  • 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 OK.

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.

  2. In the OrderBook table's Data Source tab, select the JDBC Data Source (which you previously defined in your configuration file) from the drop-down menu.

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

  4. In the Project Explorer view, open the sample you just loaded.

    If you see red marks on a project folder, wait a moment for the project to load its features.

    If the red marks do not resolve themselves after a minute, select the project, right-click, and select Maven → Update Project from the context menu.

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

  6. Open the JDBCQuery.sbapp file and click the Run button. This opens the SB Test/Debug perspective and starts the module.

  7. 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 JDBC OrderBook table through the InsertIn input stream. If you view the InsertOut output stream, you can see those data records as they are entered.

  8. Switch to the Manual Input view. Select the ReadIn input stream from the drop-down menu, and click Send Data. This issues a query that returns all rows in the table where Side='bid'. To see those results, select the ReadOut stream in the Output Streams view.

  9. From the Manual Input view, select the DeleteIn input stream. Based on the results from the previous Read query, type a known value for the QuoteID field, and click Send Data. You can then run step 9 again to confirm that this QuoteID was deleted from the OrderBook table.

  10. Optional step for stored procedures: If you configured for stored procedure support in step 5, call it as follows: In the Manual Input view, select the StoredProcIn input stream. In the Symbol field, enter a known value in the table. The feed simulation supplied by this project populates the table with the Symbols TIBX, GOOG, IBM, and MSFT. Click Send Data and view the results returned on the StoredProcOut output stream.

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

This Sample's Files

The following files are included in the sample:

JDBCQuery.sbapp

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

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 on various external JDBC data sources. Use a SQL client application to execute the SQL code on your database server.

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.