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.
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
jdbcdatasourceto 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.
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.
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.
In the OrderBook table's Data Source tab, select the(which you previously defined in your configuration file) from the drop-down menu.
Make sure the database you are connecting to is up and active.
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→ from the context menu.
JDBCQuery.sbappfile and click the Run button. This opens the SB Test/Debug perspective and starts the module.
In the Test/Debug perspective, go to the Feed Simulations tab, select
OrderBookFeedSimfrom the list, and click the button. Rows are inserted to the JDBC OrderBook table through the
InsertIninput stream. If you view the
InsertOutoutput stream, you can see those data records as they are entered.
Switch to the Manual Input view. Select the ReadIn input stream from the drop-down menu, and click. 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.
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. You can then run step 9 again to confirm that this QuoteID was deleted from the OrderBook table.
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 and view the results returned on the StoredProcOut output stream.
When done, press F9 or click the Terminate EventFlow Fragment button.
The following files are included in the sample:
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.
A simple feed simulation to populate the OrderBook table.
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.
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.