JDBC input connector

The JDBC input connector retrieves data from relational databases. In the connector configuration you specify the appropriate JDBC driver and Connection URL settings to connect to the database, and the SQL query to execute on the selected database.

The SQL query is executed at a time interval (Polling interval) specified in the connector settings. This means the JDBC input connector uses the same time interval when retrieving data from the database.

Comments

• This connector must be used together with a MessageIN Event. • The MessageIN Event must use an SXD file with one field element for each column in the database table stated in the SQL query.

The connector settings are described below.

Connector type

Select Java.

Java class

Select JDBCInConnector. When you click the button attached to this field, all available Java input connector classes are displayed. To activate the JDBC input connector settings you must select the java class JDBCInConnector.

JDBC driver

The JDBC driver to use to connect to the database. You can select the following drivers from the drop-down list:

To use this driver...	...select this option
JDBC-ODBC bridge	sun.jdbc.odbc.JdbcOdbcDriver
DataDirect SQL Server driver	com.streamserve.www.jdbc.sqlserver.SQLServerDriver
DataDirect Oracle driver	com.streamserve.www.jdbc.oracle.OracleDriver
DataDirect DB2 driver	com.streamserve.www.jdbc.db2.DB2Driver

If you are using other drivers than the above, you can enter the corresponding driver expression in this field.

Connection URL

The connection URL to connect to the database. You can select and edit the following Connection URLs from the drop-down list:

If you are using this driver...	...select and edit this option
JDBC-ODBC bridge	Option: jdbc:odbc:DB Example: jdbc:odbc:myDatabase
DataDirect SQL Server driver	Option: jdbc:streamserve:sqlserver://server[:port];databaseName=DB Example: jdbc:streamserve:sqlserver://localhost:1433;databaseName=myDatabase
DataDirect Oracle driver	Option: jdbc:streamserve:oracle://server[:port];SID=DB Example: jdbc:streamserve:oracle://localhost:1433;SID=myDatabase
DataDirect DB2 driver	Option: jdbc:streamserve:db2://server[:port];DatabaseName=DB Example: jdbc:streamserve:db2://localhost:50000;DatabaseName=myDatabase

If you are using other drivers than the above, you can enter the corresponding Connection URL in this field.

User name

A user name to access the database. Overrides any other user names specified in the Connection URL.

Password

A password to access the database. Overrides any other passwords specified in the Connection URL.

SQL file

You can create an SQL file where you enter the query to submit to the database. This query overrides any query specified in the SQL command field described below.

If you use the SQL file option to specify the query, you can create more complex queries using several lines of statements.

Example 30

Query in SQL file

SELECT *

FROM CustomerData

WHERE CustNo>200

 

SQL command

You can enter a one-line query in the SQL command field.

Example 31

Query in SQL command

SELECT * FROM CustomerData WHERE CustNo>200

 

Event name

The name of the MessageIN Event that retrieves the data via this input connector.

Mode

Specifies what to do with each table row in the database table after it is read.

Move (with delete)	Delete the table row after it is read.
Copy (no delete)	Do not delete the table row after it is read.

Row level

Specifies how to handle each table row retrieved from the database table when delivering data to the MessageIN Event.

Objects as blocks	Each row is added as a block instance to the MessageIN Event. Using this option, the input job starts when the first row is retrieved and ends when the last row is retrieved. All rows are added as block instances to the same MessageIN Event.
Objects as events	Each row triggers a new MessageIN Event. Using this option, the input job also starts when the first row is retrieved and ends when the last row is retrieved. Each row triggers a new MessageIN Event.
Objects as jobs	Each row starts a new input job. Using this option, each row represents a separate input job and triggers a new MessageIN Event.

Create SXD

Used when creating an SXD file. Enter the path to the SXD file to create, for example:

C:\resources\SXD\JDBCretrieve.sxd

See Creating an SXD file for more information.

License Password

Password for the JDBC driver (if required). If you are using any of the DataDirect JDBC drivers included in the StreamServe installation you should leave this field blank.

Time-out

Specifies a time-out for the connector. StreamServer uses job-end sequences in the input data to determine when all data in an input job is received. If there are no job-end sequences in the input data, StreamServer will not know when to stop waiting for more input. To prevent this from happening, you can define a time-out for the input connector.

Polling interval

The interval at which the SQL query is submitted to the database, and at which this connector retrieves data from the same database.