SAP Cloud Integration (CPI) – JDBC Adapter – Step-by-step guide – Pre-requisites and Configuration- JDBC Batch INSERT CPI

Hello CPI People,

This Blog is all about JDBC Adapter(Setup, Configuration, drivers…etc.)

JDBC Configuration(JDBC Driver/JDBC Data Source Setup) – Pre-requisites and Configuration

I have a requirement to connect to an SQL On-Prem DataBase and to perform Operations called INSERT, SELECT and DELETE.

So, here are the steps:

STEP 1: Cloud Connector Configuration:-

To complete this step, you will need Internal Host and Internal Port of the database.
So, in Cloud Connector, in the Cloud to On Premise, Add System Mapping. 

Back-end Type: Non-SAP System
Protocol: TCP
Internal Host: The IP Address received for SQL Database.
Internal Port: 1433 (For SQL) / 1741 (For Oracle)
Virtual Host: Could be same same as Internal Host, but advisable that you keep a different one.
Virtual Port:Could be same same as Internal Port, but advisable that you keep a different one.

STEP 2: Cloud Integration (CPI) – Add JDBC Drivers:-

Download the drivers of  SQL from their official website, for example, the below one for SQL driver. Don’t download the latest version. Download Microsoft JDBC Driver 8.4 for SQL Server (zip)

Add it in the JDBC Material > JDBC Driver

STEP 3: Cloud Integration (CPI) – Add JDBC Data Source:-

Name: Name of the Data Source, as per your naming convention
Description: A brief description
Database Type: Microsoft SQL Server/ Oracle/ …
User ID: User ID to access the database.
Password: Password to the User ID mentioned.
JDBC URL: The below is how you would form the JDBC URL:

  SQL:-  jdbc:sqlserver://virtualHost:virtualPort;DatabaseName=databaseName;
  Oracle:-  jdbc:Oracle:thin:@virtualHost:virtualPort:databaseName
Location ID: CPI’s Location ID.
Cloud Connector: Check the checkbox.

STEP 4: Cloud Integration (CPI) – Design Integration Flow with JDBC Adapter:-

The below is how the iflow will look like for sending poll request, connecting to DB and polling the data to further process the data.

In JDBC Adapter Provide the JDBC Data Source Alias(which is previously deployed in JDBC Data Source).

Increase the Time out for Connection and Response as required.

SQL OPERATIONS

SELECT OPERATION

select top 10 * from [dbo].[TableName]

OUTPUT:-

XML format will be your Output.

JDBC Sample Response Data

<ROOT>
  <select_response>
    <row>
      <Name>MPP Phase 4</Name>
      <HIC_BU>806000313 - St Malo Total MP Input</HIC_BU>
      <HIC_Project>305-00372.25.01:St. Malo Field Multi-phase Pumps</HIC_Project>
      <ProjectClass>OFD - PROJECT EXECUTION - FACILITIES</ProjectClass>
      <Year>2020</Year>
      <Period>3</Period>
      <Type>Capex_Gross</Type>
      <Product/>
      <Currency>Local</Currency>
      <Value>3675940.0</Value>
      <YearLoaded>2021</YearLoaded>
      <MonthLoaded>3</MonthLoaded>
      <Updated_On>2021-03-02 07:17:24.817</Updated_On>
    </row>
  </select_response>
</ROOT>

INSERT OPERATION

As per SAP NOTE, Batch Processing Cannot Be Processed By JDBC adapter. But I am going to tell you It is very simple and we can do it.

INCOMING PAYLOAD:-

<ROOT>
    <row>
      <Name>chaitanya</Name>
      <HIC_BU>806000313 - St Malo Total MP Input</HIC_BU>
      <HIC_Project>305-00372.25.01:St. Malo Field Multi-phase Pumps</HIC_Project>
      <ProjectClass>OFD - PROJECT EXECUTION - FACILITIES</ProjectClass>
      <Year>2020</Year>
      <Period>3</Period>
      <Type>Capex_Gross</Type>
      <Product/>
      <Currency>Local</Currency>
      <Value>3675940.0</Value>
      <YearLoaded>2021</YearLoaded>
      <MonthLoaded>3</MonthLoaded>
      <Updated_On>2021-03-02 07:17:24.817</Updated_On>
    </row>
	<row>
      <Name>Sachin</Name>
      <HIC_BU>806000313 - St Malo Total MP Input</HIC_BU>
      <HIC_Project>305-00372.25.01:St. Malo Field Multi-phase Pumps</HIC_Project>
      <ProjectClass>OFD - PROJECT EXECUTION - FACILITIES</ProjectClass>
      <Year>2020</Year>
      <Period>3</Period>
      <Type>Capex_Gross</Type>
      <Product/>
      <Currency>Local</Currency>
      <Value>3675940.0</Value>
      <YearLoaded>2021</YearLoaded>
      <MonthLoaded>3</MonthLoaded>
      <Updated_On>2021-03-02 07:17:24.817</Updated_On>
</ROOT>

This Payload needs to Convert into SQL Query.

By using a simple Groovy Script converts this payload in to a SQL Query.

Groovy Script

import com.sap.gateway.ip.core.customdev.util.Message;
import java.util.HashMap;

def Message processData(Message message) {
    def body=message.getBody(java.lang.String) as String;
    def sql_statement=new StringBuffer();
    def xml=new XmlSlurper().parseText(body);
    xml.row.each{
        sql_statement=sql_statement.append("INSERT INTO [dbo].[Enersight_HIC_Testing]([Name],[HIC_BU],[HIC_Project],[ProjectClass],[Year],[Period],[Product],[Currency],[Value],[YearLoaded],[MonthLoaded],[Updated_On])VALUES(");
        sql_statement=sql_statement.append("'").append(it.Name.text()).append("',");
        sql_statement=sql_statement.append("'").append(it.HIC_BU.text()).append("',");
        sql_statement=sql_statement.append("'").append(it.HIC_Project.text()).append("',");
        sql_statement=sql_statement.append("'").append(it.ProjectClass.text()).append("',");
        sql_statement=sql_statement.append(it.Year.text()).append(",");
        sql_statement=sql_statement.append(it.Period.text()).append(",");
        sql_statement=sql_statement.append("'").append(it.Product.text()).append("',");
        sql_statement=sql_statement.append("'").append(it.Currency.text()).append("',");
        sql_statement=sql_statement.append(it.Value.text()).append(",");
        sql_statement=sql_statement.append(it.YearLoaded.text()).append(",");
        sql_statement=sql_statement.append(it.MonthLoaded.text()).append(",");
         sql_statement=sql_statement.append("'").append(it.Updated_On.text()).append("'");

        sql_statement=sql_statement.append(");\n");
    }
    
    message.setBody(sql_statement.toString());
    return message;
    }

CPI FLOW:-

OUTPUT AFTER GROOVY:-

INSERT INTO [dbo].[Enersight_HIC_Testing]([Name],[HIC_BU],[HIC_Project],[ProjectClass],[Year],[Period],[Product],[Currency],[Value],[YearLoaded],[MonthLoaded],[Updated_On])VALUES('MPP Phase 4','806000313 - St Malo Total MP Input','305-00372.25.01:St. Malo Field Multi-phase Pumps','OFD - PROJECT EXECUTION - FACILITIES',2020,3,'','Local',3675940.0,2021,3,'2021-03-02 07:17:24.817');
INSERT INTO [dbo].[Enersight_HIC_Testing]([Name],[HIC_BU],[HIC_Project],[ProjectClass],[Year],[Period],[Product],[Currency],[Value],[YearLoaded],[MonthLoaded],[Updated_On])VALUES('MPP Phase 4','806000313 - St Malo Total MP Input','305-00372.25.01:St. Malo Field Multi-phase Pumps','OFD - PROJECT EXECUTION - FACILITIES',2020,3,'','Local',3675940.0,2021,3,'2021-03-02 07:17:24.817');

This is Our Final Payload which is going to JDBC Receiver.

As per SAP limitation, 100000 Records can be  INSERTED at a single shot.

JDBC RESPONSE:-

As the payload is having multipule Records with INSERT statement but JDBC counts it as a single row.

When you check your DataBase all the Records will sucessfully Inserted.

Hence JDBC adapter is a better option if you are connecting to your databases via CPI. You can perform all the operations on your table.

Hope you had a good read.

If you wish to know more or have any queries on this blog, please feel free to drop a comment.

Thanks & Regards,

Chaitanya