Operate PostgreSQL Database on Premise From SAP Integration Suite with JDBC Adapter

[Jacky_Liu]

One customer want to know how to operate  database on premise in SAP Integration Suite with JDBC adapter. I want to provide a live demo for this. Intalling a database will take many resource in my laptop and my time. So I will use docker to run PostgreSQL database on my laptop. I will share step by step document in this blog which maybe useful for you.

Prerequisites:

• You have installed SAP Cloud Connector.
• You have installed Docker.
• You have  SAP Integration Suite gone live with Developer Role authorization .

Steps:

Step 1 , Run the following command to  run PostgreSQL database in docker.

docker run -d  --name postgres  --restart always    -p 5432:5432   -e POSTGRES_PASSWORD=root123     -e PGDATA=/var/lib/postgresql/data/pgdata     -v /tmp/postgres/data:/var/lib/postgresql/data   postgres

Step 2, Run the following command to create database and table in PostgreSQL. Create user tom with password root123 and grant the authorization to user tom .

docker exec -it postgres bash
psql -U postgres
create database customer;
\c customer
create table userinfo(id int primary key, name char(32), age int);
insert into userinfo values(1, 'xw', 18); 
select * from userinfo;
CREATE USER tom WITH PASSWORD 'root123';
grant insert on userinfo to tom;
grant select on userinfo to tom;
grant update on userinfo to tom;

Step 3, Expose database in SAP Cloud Connector.

Step 4, Create JDBC datasource in SAP Integration Suite .

Database Type: PostgreSQL(On-Premise)

User: tom

Password: root123

JDBC Url: jdbc:postgresql://jackydb:5433/customer?ssl=false&loglevel=2

Location ID: jackymacscc " from SAP Cloud Connector

Step 5, Create and Deploy Iflow to insert table userinfo in SAP Integration Suite .

 

<root>
<Insert_Statement1>
<dbTableName action="INSERT">
<table>userinfo</table>
<access>
<id>121</id>
<name>test121</name>
<age>19</age>
</access>
</dbTableName>
</Insert_Statement1>      
</root>

JDBC Data Source Aliase come from previous step. Click on Save and Deploy .

Step 6, Check iflow run result.

 

Step 7, Check result in database with the following commands.

docker ps
docker exec -it f0e748998892 bash
psql -U postgres
\c customer
select * from userinfo;

 

The Ends!

Thanks for your time!

Best regards!

Jacky Liu