- Index

< Previous |  | Next >

# Feed Simulation with a JDBC Data Source

## JDBC Data Source Overview

You can configure a feed simulation to use the response of a SQL query to a JDBC database as the source of input tuples for the feed simulation.

### Important

The JDBC generation method cannot be used to generate input data for an input stream whose schema contains a field of type list or tuple. Blob fields, however, are supported.

To use this option, you must obtain from the database vendor the JAR file that implements the JDBC driver for the target database. When you have downloaded the vendor's JAR file from the vendor's web site, you must either:

- Install the JAR file in a particular location in the StreamBase installation directory, or

- Set up the `STUDIO_BOOT_CLASSPATH` environment variable pointing to a different installed location.

Independent of the above steps, you must also include the location of the JDBC JAR file in the standard `CLASSPATH` environment variable so that command-line sbfeedsim can locate and use it.

These configuration requirements are described in more detail in the following sections.

## JDBC Driver Setup

StreamBase Studio must have access to the database vendor's JDBC JAR file so that it is available when Studio runs a feed simulation. The simplest solution is to copy the JAR file to the `STREAMBASE_HOME/jdk/jre/lib/ext` directory, which is the `lib/ext` directory of the JDK bundled with your StreamBase installation. The ability to add a file to the default `STREAMBASE_HOME` location may require administrator rights, especially on UNIX.

Use the following examples as guidelines; the exact location may be different for your installation.

Windows

  Copy the JDBC JAR file to:

      %STREAMBASE_HOME%\jdk\jre\lib\ext

  For example:

      C:\TIBCO\sb-cep\n.m\jdk\jre\lib\ext

UNIX

  Copy the JDBC JAR file to:

      $STREAMBASE_HOME/jdk/jre/lib/ext

  For example:

      /opt/tibco/sb-cep/n.m/jdk/jre/lib/ext

## Environment Variable Alternative

Your site may require administrator rights in order to add files to `C:\Program Files` on Windows or `/opt` on UNIX. In these cases, you may not have permission to copy the vendor's JDBC JAR file to the `STREAMBASE_HOME` location, as recommended in the previous section.

As an alternative, you can specify the environment variable `STUDIO_BOOT_CLASSPATH`, and point it to the full, absolute path to the vendor's JDBC JAR file. Use the following examples as guidelines.

Windows

  Either set the variable globally using the Windows `System` control panel, or set the variable in a StreamBase Command Prompt and start the sbstudio.exe process from that command prompt. If the path to the JAR file has any directories with spaces in their names, you must enclose the entire path in single quotes.

  The following example for Windows 7 specifies the path to an Oracle JDBC driver's JAR file. Be sure to specify the JAR file name appropriate for your target database:

      set STUDIO_BOOT_CLASSPATH=C:\Users\username\Documents\JDBC-Drivers\ojdbc14.jar

UNIX

  Set the variable in the terminal environment from which you will launch the sbstudio process. For example:

      export STUDIO_BOOT_CLASSPATH=/home/sbuser/jdbc-drivers/ojdbc14.jar

## Classpath Setup for sbfeedsim

When you have saved a feed simulation file that specifies data generation from a JDBC data source, you can run that file from the command line independent of Studio, using the sbfeedsim utility.

For this to work, the JDBC driver JAR file must be in the classpath. You can set the `CLASSPATH` environment variable, or use another standard classpath setting method. If you use the `CLASSPATH` environment variable, append to the existing classpath the full path to the JDBC JAR file, using a command like the following examples:

Windows

      set CLASSPATH=%CLASSPATH%;"C:\TIBCO\sb-cep\n.m\jdk\jre\lib\ext\ojdbc14.jar"

UNIX

      export CLASSPATH=$CLASSPATH:/opt/tibco/sb-cep/n.m/jdk/jre/lib/ext/ojdbc14.jar

## JDBC Data Source Options Dialog

In the Generation Method section of the Feed Simulation Editor, select the JDBC option, then click Options.

In the JDBC Data Source Options dialog, specify the information required to connect to a JDBC-compliant database, and specify a SQL query that will generate a response from the database to populate the selected input stream.

This dialog represents the entirety of configuration available when using a JDBC data source for a feed simulation. That is, JDBC configuration parameters such as `jdbc-fetch-size` in the server configuration file only affect Query operator access to a JDBC Table data structure, and do not affect feed simulation data sources.

The table below describes the options in the JDBC Data Source Options dialog.

Option | Default | Description
--- | --- | ---
Driver Class | None | Required field. Select or enter the fully qualified name of the class that implements the JDBC driver for the database you want to use. The drop-down list for this field is populated with example JDBC driver class names. The class name to enter in this field is determined by the actual JDBC driver you obtain from your database vendor and may have changed from these examples. You can select an example driver class name and then edit it, if your driver's class name has changed.
URI | None | Required field. Enter (or select and edit) the JDBC URI that connects to the target database at your site. The drop-down list for this field is populated with example JDBC URI strings, with placeholders for site-specific information such as hostname and database name. These example URI strings cannot be used as provided. They must be edited to specify the correct local information for your target database.
User Name | None | Optional field. If access to your target database requires it, enter a user name that has the authorization level necessary to run the SQL query specified in the SQL field.
Password | None | Optional field. If access to your target database requires it, enter the password for the user name specified in the previous field.
SQL | None | Required field. Enter a fully tested and known working SQL statement that returns rows with the columns in the correct order for the schema of the specified input stream. Use the SQL syntax of your target database to construct your SQL statement. TIBCO strongly recommends using your database vendor's command line query tool or a third-party database query tool to develop the SQL query to use in this field. Get the SQL query to a known, working state outside of StreamBase Studio before attempting to use it with a feed simulation. If your SQL SELECT statement returns the right columns in the wrong order, then adjust your SQL statement to return columns that line up by data type with the schema of the specified input stream.
JDBC Fetch Size | 0 (disabled) | Optional field. Specify an integer to designate a JDBC fetch size, which gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed. The fetch size is a standard feature of JDBC drivers, and does not designate a row limit. Some JDBC drivers ignore the fetch size. Consult your database vendor's documentation to learn about methods of determining the optimum fetch size for your target database.
Connect timeout | 15 seconds | Optional field. Specify an integer number of seconds for the JDBC driver to wait for results before declaring an error.

## Related Topics

- Using the Feed Simulation Editor

- Feed Simulations View

Back to Top ^