×

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 is a secured bridge between RunMyProcess and your legacy databases leveraging the Secure Enterprise Connector.

The following is assumed :

  • The SEC manager is running on the server and a tunnel is open and configured. You can find instructions on how to install and configure the SEC here.
  • 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).
  • The Unified Adapters zip file has been extracted to your local server as described here.

1 Configuring the Adapter

The Adapter is configured by modifying the handler.config and by creating a DBAgent.config file in the [SEC-PATH]\configFiles folder. A reference configuration file can be found in the configFiles\jdbc.reference folder.

NB: It is recommended that the Adapter be installed 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 database type.

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 database you are trying to connect to.

Note: You need to 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:

2 Running and Testing the Adapter

You can now run the Adapter as follows:

On Windows:

1
runAdapter.bat

On Unix:

1
java -jar unified-adapter_${version}.jar

or (recommended):

1
java -server -Xmx512m -Xss256m -Djava.util.logging.config.file=./log.properties -jar unified-adapter_${version}.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 value 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
    }
}

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%\sec-agent-manzanillo\bin
call start "Tunnel" runagent.bat
cd %SECPATH%\jetty7.6.11
start "Manager" java -jar start.jar
cd %SECPATH%\Adapters\JDBC
start "JDBCAdapter" java -jar unified-adapter_${version}.jar

Where version is the current unified adapter version installed on your server.

Note: For the above example, note that the %SECPATH% environment variable points 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.