JDBC Delivery Method

The JDBC delivery method allows STEP data to be delivered directly to tables in Relational database management systems (RDBMS) like Oracle, MySQL, MS SQL Server, and PostgreSQL. Though the ways the JDBC plugin can be deployed are various, one of its prime uses is to send STEP data to data analytics tools for the purpose of displaying STEP data in a data analytics dashboard. This delivery method requires CSV format and is available in both the Export Manager and in outbound integration endpoints (OIEPs).

In an OIEP, the delivery method is displayed on the Configuration tab of the editor in the Delivery Method section.

For more information on the Export Manager option, refer to JDBC Delivery Method in the Export Manager documentation here.

Prerequisites

Important: For complete setup requirements, refer to the Exporting Data via JDBC with CSV Format topic in the Data Format documentation here.

Install the required drivers

JDBC specification 4.1-compliant drivers should be placed in a directory accessible from all application servers. These drivers can then be made available for the delivery plugin via the dynamic properties JDBCDeliveryPlugin.DriverPath.[n] and JDBCDeliveryPlugin.DriverClass.[n]. For more information regarding applicable Java drivers, review the RDBMS vendor's homepage on the web.

Configure data for the dropdown parameters

Changes to the properties file, outlined below, are implemented when the server is restarted.

  1. Prior to configuration, clicking the Driver Location dropdown parameter displays the required property name. Provide a selection for the dropdown parameter via the sharedconfig.properties file on the STEP application server using the case-sensitive JDBCDeliveryPlugin.DriverPath.[n] property. As an example:
JDBCDeliveryPlugin.DriverPath.1 = L:/shared/mysql-connector-java-5.1.42-bin.jar

In this example, the drivers are stored on the application server's L:/shared drive.

  1. Prior to configuration, clicking the Driver Class dropdown parameter displays the required property name. Provide a selection for the dropdown parameter via the sharedconfig.properties file on the STEP application server using the case-sensitive JDBCDeliveryPlugin.DriverClass.[n] property. As an example:
JDBCDeliveryPlugin.DriverClass.1 = com.mysql.jdbc.Driver

In this example, the drivers class used is 'com.mysql.jdbc.Driver.'

  1. Prior to configuration, clicking the Database URL dropdown parameter displays the required property name. Provide a selection for the dropdown parameter via the sharedconfig.properties file on the STEP application server using the case-sensitive JDBCDeliveryPlugin.URL.[n] property. 
JDBCDeliveryPlugin.URL.1 = jdbc:mysql://localhost:3306/mydb

In this example, the database URL used is 'jdbc:mysql://localhost:3306/mydb.'

Configuration

For information on a parameter, hover over the parameter field to display help text.

In the OIEP editor on the Configuration tab, navigate to the Delivery Method section, then click Edit Delivery.

  1. In Select Delivery Method, choose JDBC from the dropdown.

  2. In Driver Location, select from the dropdown one of the paths to the relevant .jar file.

  3. In Driver Class, select from the dropdown one of the pre-configured driver class.

  4. In Database URL, select from the dropdown one of the pre-configured URLs to the destination database.

  5. In Username, enter the username required to access the destination database.

  6. In Password, enter the password required to access the destination database.

  7. In Table Name, enter the name of the table in the destination database to which STEP will be publishing data

  8. In Key Columns, list the names of the columns appearing on both the exported CSV file and the table in the destination database, separated by commas (and no spaces), into which STEP will publish data

  9. In Delete Key Columns, list the names of the columns appearing on both the exported CSV file and the table in the destination database, separated by commas (and no spaces), from which STEP will delete data. The headers contained in this field can differ from the headers in the 'Key Columns' field, but they must also be part of the upsert key definition.

  10. In Convert "NULL", choose Yes if the string “NULL” should be converted to the value null. This may, for instance, be used for clearing a value in a column in the target database. This parameter defaults to No.

  1. On the Edit Delivery Configuration dialog, click the OK button to save the delivery method.