Skip to Content
Technical Articles
Author's profile photo Chaitanya Maddukuri

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

Assigned Tags

      7 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Sai Krishna
      Sai Krishna

      Hi Chaitanya,

      wonderful blog and great insight on the DB operation.

      you have mentioned that batch processing isn't possible through standard options have you tried with XML SQL format. If so can you let us know why it's isn't suitable.

      please find the link below describing to perform these operations in batch in SAP CPI.

      https://help.sap.com/docs/SAP_NETWEAVER_750/5cf7d2de571a45cc81f91261668b7361/2e96fd3f2d14e869e10000000a155106.html?version=7.5.4&locale=en-US

      Regards,
      Sai Krishna V

      Author's profile photo Nagendra Krishna
      Nagendra Krishna

      Dear Chaitanya Maddukuri,

      Nice blog,  I found your blog crisp and up to the point with clear explanation for anyone who wants to develop their first JDBC scenario in cloud integration.  Appreciate your time and efforts in writing this blog.

       

      Thanks

      Nagendra

       

      Author's profile photo Julio Ruiz Angeles
      Julio Ruiz Angeles

      Hi Chaitanya,

      Thank you for this blog. Do you have an example for Oracle?

      In this cloud connector setup, internal port 1741 for Oracle, is that the port found on the TNSnames entry?

      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)

      Author's profile photo Lav Mishra
      Lav Mishra

      Can we do upsert operation also using same appraoch.

      Author's profile photo BalaVignan Gopisetty
      BalaVignan Gopisetty

      Hi Chaitanya Maddukuri

       

      We are facing the below issue.

      The processing has failed due to error in creating metadata table. : com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open server "localhost" requested by the login.  The login failed. ClientConnectionId:2bd182ce-2392-4357-8d54-696a76a0e1e9

       

      Could you please suggest for resolutions

      Author's profile photo BalaVignan Gopisetty
      BalaVignan Gopisetty

      Hi Chaitanya Maddukuri

       

      What if MS sql server is moved to Cloud?

      How to configure for the same?

      Author's profile photo kasim kowluri
      kasim kowluri

      Hi chaitanya,

      how to add the JDBC in JDBC Material in monitoring

      thanks

      hussain