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 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.
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.|
Add a reference in the project's
sbd.sbconffile 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>
Add the JAR to the project's Java build path. Right-click the project folder in Studio's Package Explorer view, and select Java Build Path on the left, then select the Libraries tab. On that tab, select , then browse to and select the appropriate driver JAR file or files on your system. Click to close the dialog.→ (or invoke → in Studio's top-level menu). In the Properties dialog, select
Define your external JDBC data source in the project's
sbd.sbconffile 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.
In the OrderBook table's Data Source tab, select the(which you previously defined in
sbd.sbconf) from the drop-down menu.
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.
For Oracle JDBC sources, you must select the JDBC parameter index with result value. For the GetOrdersBySymbol stored procedure provided in the project'soption in the Query operator's Result Settings tab, and specify the correct
sql-snippets/create-proc.txtfile, 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.txtfile and send the stored procedure to the database.
Make sure the database you are connecting to is up and active.
To run the application, in the Package Explorer, right-click
JDBCQuery.sbappand select → . StreamBase Studio opens the Test/Debug perspective and starts the application.
In the Test/Debug perspective, go to the Feed Simulations tab, selectfrom 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
ReadIninput 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 stream in the Application Output tab.
From the Manual Input view, select the
DeleteIninput 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 tab, select the 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 Stop Running Application button.
In StreamBase Studio, import this sample with the following steps:
From the top menu, click→ .
Select this application from the Applications list.
StreamBase Studio creates a project for each sample.
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 location of
studio-workspace on your system.
In the default TIBCO StreamBase installation, this sample's files are initially installed in:
See Default Installation Directories for the default location of
studio-workspace on your system.