×

Please give details of the problem

Docs

Find

SEC-JDBC Adapter

The "JDBC Adapter" allows you to access data stored in your on-premise databases from RunMyProcess workflows : it's a secured bridge between RunMyProcess and your legacy databases leveraging the Secure Enterprise Connector.

In this tutorial we assume:

  • The SEC manager is running on the server and a tunnel is open and configured (See how to install and configure the SEC).
  • The server has Java installed.
  • The ping port on the manager is 4444 (this can be configured in the adapter and the manager).
  • The manager is running on the same server as the adapter (127.0.0.1).

Install and Configure the Adapter

  • Download and unzip the SEC JDBC DB Connector zip file on your local server.
  • You must navigate to [installed-path]/configFiles and modify the Handler.config and the DBAgent.config files.

NOTE: We recommend you install the Adapter in a "Adapters" folder inside the SEC installation path.

The Handler.config file should look like this :

1
2
3
4
5
6
7
8
#Generic Protocol Configuration
protocol = DBAgent
protocolClass = com.runmyprocess.sec.DBAgent
handlerHost = 127.0.0.1
connectionPort = 5832
managerHost = 127.0.0.1
managerPort = 4444
pingFrequency = 300

Where:

  • protocol is the name to identify our Adapter.
  • protocolClass is the class of the Adapter.
  • handlerHost is where the Adapter is running.
  • connectionPort is the port of the adapter where data will be received and returned.
  • managerHost is where the SEC is running.
  • managerPort is the port where the SEC is listening for ping registrations.
  • pingFrequency is the frequency in which the manager will be pinged (at least three times shorter than what's configured in the manager).

In the DBAgent.config file, the Database configuration must be set. The adapter will look for the correct configuration depending on the DBType. The DBAgent.config file should look like this :

1
2
3
4
5
#DBAgent Configuration
MYSQL = {"sqlDriver" : "com.mysql.jdbc.Driver", "sqlSource" : "jdbc:mysql://localhost:3306/mydb?characterEncoding=UTF-8","sqlDriverPath" : "C:/\Program Files (x86)/\Fujitsu RunMyProcess/\Secure Enterprise Connector/\drivers_jdbc/\mysql/\mysql-connector-java-5.1.28-bin.jar"}
SQLSERVER_2004 = {"sqlDriver" : "com.microsoft.jdbc.sqlserver.SQLServerDriver", "sqlSource" : "jdbc:microsoft:sqlserver://localhost:1433/mydatabase?characterEncoding=UTF-8","sqlDriverPath" : "C:/\Program Files (x86)/\Fujitsu RunMyProcess/\Secure Enterprise Connector/\drivers_jdbc/\sql/\sql-connector-java-1.1.jar"}
SQLSERVER_2005 = {"sqlDriver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver", "sqlSource" : "jdbc:sqlserver://localhost:1433/mydatabase?characterEncoding=UTF-8","sqlDriverPath" : "C:/\Program Files (x86)/\Fujitsu RunMyProcess/\Secure Enterprise Connector/\drivers_jdbc/\sql/\sql-connector-java-1.1.jar"}
ORACLE_11_EXPRESS = {"sqlDriver" : "oracle.jdbc.driver.OracleDriver", "sqlSource" : "jdbc:oracle:thin://localhost:1521/XE/mydatabase?characterEncoding=UTF-8","sqlDriverPath" : "C:/\Program Files (x86)/\Fujitsu RunMyProcess/\Secure Enterprise Connector/\drivers_jdbc/\oracledb/\oracle-connector-java-1.1.jar"}

Each line of the config file must be populated with the information of the DB we are trying to connect to. Note that you must download the corresponding JDBC driver file, and provide its path, the driver name, the protocol, the port, and the name of the DB.

Also, use the escape characters to navigate through folders on Windows machines.

Here's a list of some JDBC drivers:

Running and Testing the Adapter

You can now run the Adapter by executing the rmp-sec-dbConector.jar in the installed path :

1
java -jar JDBC.jar

Note: Make sure that you have launched the Protocol Manager and it is running on port 8080.

If everything is configured correctly you can now place a request from RunMyProcess to retrieve information from the database. The POST body should look something like this :

1
2
3
4
5
6
7
8
9
{
"protocol":"JDBC",
"data":{
    "DBType":"MYSQL",
    "sqlUsername":"mysqlUser",
    "sqlPassword":"mysqlPassword",
    "sqlStatement":"SELECT * FROM mydatabase.pet Limit 10"
    }
}

Note: The DBType should coincide with the type in the configuration file. This value IS case sensitive. The expected return is a JSON object that should look like this :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
{
"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
    }
}

Automatic launching

In order to access adapter resources from RunMyProcess, the Connector Agent, Protocol Manager and the required adapters must be running. You can create a script (shell script or bat file) to launch all resources. For example, in windows, you can create a bat file that looks like this:

1
2
3
4
5
6
7
@echo off
cd %SECPATH%\data-connector-agent\bin
call start "Tunnel" runagent.bat
cd %SECPATH%\jetty7.6.11
start "Manager" java -jar start.jar
cd %SECPATH%\Adapters\JDBCAdapter
start "JDBCAdapter" java -jar rmp-sec-dbConector.jar

Note: for the example presented note that the %SECPATH% environment variable should point to the installation path of the SEC. Also note that the Adapter is inside an "Adapters" folder in the SEC installation folder. We recommend you follow this best practice.