JDBC Table Data Construct Sample

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 not be completely configured until you specify the exact JDBC data source you want to use. Before the sample can run, you must edit the provided sbd.sbconf file to specify the path to the JAR file that accesses your JDBC server, and must specify a JDBC data source name there and in the Query Table data construct. You must also add the path to the JAR file to the project's build path, as described below.

The sample uses a JDBC Table Data Construct named OrderBook, which is linked to an external JDBC table by means of a <data-source> specification in the project's server configuration file. To interact with an external JDBC data source, you must specify the appropriate JDBC driver JAR file in the server configuration file in the <jar> element. Obtain the JDBC driver JAR file for your database from the database vendor's web site.

The JAR files listed in the comments in this sample's sbd.sbconf file are meant only as examples, and are not provided with the sample. The appropriate JAR file name to use could be different on your system.

Importing This Sample into StreamBase Studio

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

  • From the top-level menu, click FileLoad StreamBase Sample.

  • Type jdbc to narrow the list of options.

  • Select jdbc-query from the Data Constructs and Operators list.

  • Click OK.

StreamBase Studio creates a project for the sample.

Running This Sample in StreamBase Studio

  1. Add a reference in the project's sbd.sbconf file to the JDBC driver JAR file provided by your database vendor. This is done using the <jar> child element under the <java-vm> element. For example, to use Microsoft SQL Server 2008:

    <java-vm>
         ...
         <jar file="/path/to/sqljdbc4.jar"/>
         ...
    </java-vm>
  2. Add the JAR to the project's Java build path. Right-click the project folder in Studio's Project Explorer view, and select Build PathConfigure Build Path (or invoke ProjectProperties in Studio's top-level menu). In the Properties dialog, select Java Build Path on the left, then select the Libraries tab. On that tab, select Add External JARs, then browse to and select the appropriate driver JAR file or files on your system. Click OK to close the dialog.

  3. Define your external JDBC data source in the project's sbd.sbconf file in the <data-sources> element. For example, to add a Microsoft SQL 2008 data source:

    <data-sources>
        ...
        <data-source name="mssql2008" type="jdbc">
          <uri value="jdbc:sqlserver://mssql2008.server.com"/>
          <driver value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
          <param name="user" value="sqluser"/>
          <param name="password" value="sqlpass"/>
        </data-source>
        ...
    </data-sources>

    Configure the URI, username, and password parameters appropriately for your installation.

  4. In the OrderBook table's Data Source tab, select the JDBC Data Source (which you previously defined in sbd.sbconf) from the drop-down menu.

  5. Optional step for stored procedures: If your database supports stored procedures, the GetOrdersBySymbol Query operator can call a stored procedure to select data records. To enable this, you might need administrative access to the database system. Select the GetOrdersBySymbol Query operator and click its General tab. Commands in database-specific syntax to execute the procedure are listed in the Description field. Copy the one you need, then click the Query Settings tab and paste the copied command into the SQL statement field.

    Note

    For Oracle JDBC sources, you must select the Use CallableStatement option in the Query operator's Result Settings tab, and specify the correct JDBC parameter index with result value. For the GetOrdersBySymbol stored procedure provided in the project's sql-snippets/create-proc.txt file, the correct index value is 1.

    Use a database browser client application to start and connect to the database. Copy the appropriate code snippet from the create-proc.txt file and send the stored procedure to the database.

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

  7. In the Project Explorer, open the sample you just loaded.

  8. Open the src/main/eventflow folder.

  9. Open the package folder (most samples contain a single package folder. Open the top-level package folder if your sample contains more than one folder).

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

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

    If red marks do not resolve themselves in a moment, select the project and right-click MavenUpdate Project from the context menu.

  11. 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.

  12. 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 Application Output tab.

  13. 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.

  14. Optional step for stored procedures: If you configured for stored procedure support in step 5, call it as follows: In the Manual Input tab, 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.

  15. When done, press F9 or click the Stop Running Application 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. The application includes optional support for a stored procedure, GetOrdersBySymbol, used in the GetOrdersBySymbol Query operator.

sbd.sbconf

The project's server configuration file, which defines connection details for your JDBC data source and the location of the JDBC driver JAR file.

OrderBookFeedSim.sbfs

A simple feed simulation to populate the OrderBook table.

sql-snippets/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.

sql-snippets/create-proc.txt

A text file containing SQL code snippets for creating the GetOrdersBySymbol stored procedure on various external JDBC data sources. The GetOrdersBySymbol Query operator's Description field lists syntaxes for running this stored procedure on specific database servers.

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.