JDBC output connector

The JDBC output connector is used to insert or update data in 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.

XMLOUT Process

The output sent via the JDBC output connector is configured using an XMLOUT Process. The XML structure is shown in the example below.

<?xml version="1.0"?> <document> <job> <event> <block> <field name="column_1">value_1</field> <field name="column_2">value_2</field> .... <field name="column_N">value_N</field> </block> </event> </job> </document>

Figure 1

XML structure for output delivered via a JDBC output connector.

 

XML element	Description
<event>	The <event> element contains all the rows to be added to the database table.
<block>	Each row in the database table is represented by a <block> element.
<field>	Each column in a table row is represented by a <field> element. • The name attribute represents the column (column_1, column_2, etc.). The attribute value must be the same as the column name in the database. • The field value represents the value to add to the corresponding column (value_1, value_2, etc.). The field value is retrieved from a field or variable defined in the Event.

JDBC output connector settings

Connector type

Select Java.

Java class

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

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 32

Query in SQL file

INSERT INTO myTable *

 

SQL command

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

Example 33

Query in SQL command

INSERT INTO myTable *

 

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.