JDBC Adapter

The JDBC Adapter is used to access and retrieve data stored in on-premise databases from RunMyProcess DigitalSuite. With one instance of the adapter, you can address several databases of the same or different types.

Prerequisites

The following prerequisites must be fulfilled to install and run the JDBC adapter:

You must install the adapter on a local system in your environment. This can either be the system where you have installed the DigitalSuite EnterpriseConnect Agent, or a different one which is able to connect to the Agent's host.
You can install the adapter several times in your environment, for example, to access databases on different systems. The identifier of the adapter (protocol setting in the handler.config configuration file) must be unique for each of the installations.
A JDBC driver appropriate for the type of database you want to work on must be available on the local system. The driver must be set up and able to access the database. You need to know the driver's path, name, protocol, and port, as well as the name of the database.

For example, You can download the JDBC drivers here:
- Oracle
- SQL Server
- MySQL
- DB2/400

Installing the Adapter

To install the adapter:

Download the following ZIP file: unified-adapter-[version].zip

[version] is the current version number

The ZIP file contains executables, licences, configuration files, and reference files for several adapters.
Extract the ZIP file to a local folder. The following path is recommended:

[parent-folder]\adapters\jdbc

[parent-folder] is a folder of your choice. If the EnterpriseConnect Agent is installed on the same machine, use its installation folder as the [parent-folder], for example, C:\ProgramFiles (x86)\dsec-agent.
Copy the configuration files for the JDBC adapter, JDBC.config and handler.config, from the configFiles\jdbc.reference subfolder to the configFiles folder, for example:
```
copy configFiles\jdbc.reference\*.config configFiles
```
Overwrite existing files in the configFiles folder.
If desired, delete obsolete files. Only the following folders and files are required in the jdbc folder to use the adapter:
```
jdbc
├── configFiles
│   ├── handler.config
│   └── JDBC.config
├── lib
│   └── unified-adapter-[version].jar
└── log.properties
```
In addition, we recommend you keep the following:
- runAdapter.bat batch file for starting the adapter on Microsoft Windows
- jdbc.reference subfolder in the configFiles folder for reference purposes

Configuring the Adapter

Configuration settings for the JDBC adapter are required in the following configuration files:

handler.config
JDBC.config

Edit the files with a plain text editor.

handler.config

The handler.config file contains general configuration settings for connecting the adapter to the EnterpriseConnect Agent:

#Generic Protocol Configuration
protocol = JDBC
protocolClass = org.runmyprocess.sec2.JDBC
agentHost = 127.0.0.1:8080
pingFrequency = 1000
adapterConnectionInterval = 1000
offlineLogsMaxSize = 100
maxNumberOfLogsInBatch = 100
clusterEnabled = false
clusterAgentsHttpAddressList = localhost:8071,localhost:8072,localhost:8073

The settings have the following meaning:

protocol: The identifier of the adapter. If you install the adapter several times in your environment, the identifier must be unique for each of the installations, for example, JDBC1 and JDBC2.
protocolClass: The adapter's Java class.
agentHost: The IP address and port of the EnterpriseConnect Agent.
pingFrequency: The frequency in milliseconds in which the adapter pings the EnterpriseConnect Agent.
adapterConnectionInterval: The frequency in milliseconds in which the adapter tries to connect to the EnterpriseConnect Agent.
offlineLogsMaxSize: The maximum number of log entries that the adapter collects locally and sends to the EnterpriseConnect Agent when it re-connects to it after it was disconnected.
maxNumberOfLogsInBatch: The maximum number of log entries the adapter sends to the EnterpriseConnect Agent at a time. A value of 0 means that the number of log entries sent at a time is unlimited.
clusterEnabled: true if the EnterpriseConnect Agent is configured and running as a cluster, false otherwise.
clusterAgentsHttpAddressList: The IP addresses and ports of the EnterpriseConnect Agent cluster, if clusterEnabled is set to true.

JDBC.config

The JDBC.config file contains specific settings for the adapter.

The file can contain one or serveral lines, each of them containing the configuration for a specific database and the related JDBC driver in the following format:

#DBAgent Configuration
[ID] = {"sqlDriver" : "[driver-name]", "sqlSource" = "jdbc:[type]://[database]","sqlDriverPath" = "[driver-path]" }

The elements have the following meaning:

[ID]: A unique identifier for the configuration. This identifier needs to be specified later in requests to the JDBC adapter.
[driver-name]: The name of the JDBC driver.
[type]: The type of the database to be accessed.
[database]: The URL of the database to be accessed.
[driver-path]: The path of the JDBC driver. When using Microsoft Windows notation, make sure to insert the appropriate escape characters for example: C:\\Users\\RMP\\Desktop\\

Examples:

#DBAgent Configuration
MYSQL = {"sqlDriver" : "com.mysql.jdbc.Driver", "sqlSource" : "jdbc:mysql://localhost:3306/mydb?characterEncoding=UTF-8","sqlDriverPath" : "/home/mydrivers/mysql-connector-java-5.1.XXX-bin.jar"}
SQLSERVER_2004 = {"sqlDriver" : "com.microsoft.jdbc.sqlserver.SQLServerDriver", "sqlSource" : "jdbc:microsoft:sqlserver://localhost:1433/mydatabase?characterEncoding=UTF-8","sqlDriverPath" : "D:/mydrivers/sql-connector-java-1.XXX.jar"}
SQLSERVER_2005 = {"sqlDriver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver", "sqlSource" : "jdbc:sqlserver://localhost:1433/mydatabase?characterEncoding=UTF-8","sqlDriverPath" : "D:\\mydrivers\\sql-connector-java-1.XXX.jar"}
ORACLE_11_EXPRESS = {"sqlDriver" : "oracle.jdbc.driver.OracleDriver", "sqlSource" : "jdbc:oracle:thin://localhost:1521/XE/mydatabase?characterEncoding=UTF-8","sqlDriverPath" : "/home/mydrivers/oracle-connector-java-1.XXX.jar"}

Starting the Adapter

The adapter needs to be running to be able to access databases by JDBC.

Before you start the adapter, make sure that the DigitalSuite EnterpriseConnect Agent is running.

To start the adapter:

Change to the jdbc installation folder.
Execute the following command, depending on the operating system:
- On Microsoft Windows: Execute the runAdapter.bat batch file.
- On Linux: Execute the following command directly, or create and execute a corresponding shell script to do so:
```
java -XX:+UseG1GC -XX:+ExitOnOutOfMemoryError -Djava.util.logging.config.file=./log.properties -cp lib/*: org.runmyprocess.sec2.AdapterHandler
```

Using the Adapter

If everything is configured and running correctly, you can place requests from RunMyProcess DigitalSuite to retrieve data from your local databases.

Request: POST on http://[agent-host]:[port]/, where [agent-host] and [port] are the IP address and port of the EnterpriseConnect Agent.

Content Type: application/json

Accept: application/json

Content (example):

{
"protocol":"JDBC",
"data":{
    "DBType":"MYSQL",
    "sqlUsername":"mysqlUser",
    "sqlPassword":"mysqlPassword",
    "sqlStatement":"SELECT * FROM mydatabase.pet Limit 10"
    }
}

DBType is the identifier of the configuration in the JDBC.config file which is to be used for this request. The value is case-sensitive.

You can find a sample request like the one above in the jdbc.reference\InputJSONExample.txt file in the jdbc installation folder.

Return Object:

The expected return is a JSON object that looks as follows:

{
"SECStatus":200,
"DBData":{
    "result":"success",
    "data":[
        {"birth":"1996-02-05","death":"2010-06-06","name":"Pancho","owner":"Malcolm","specie":"dog"},
        {"birth":"1999-04-09","death":"2012-08-09","name":"Skeff","owner":"Malcolm","specie":"dog"},
        {"birth":"2012-03-01","death":null,"name":"Tuffy","owner":"Axel","specie":"hamster"}
        ],
    "rows":3
    }
}