Technical Articles
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
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
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,
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
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
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)
Can we do upsert operation also using same appraoch.
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
Hi Chaitanya Maddukuri
What if MS sql server is moved to Cloud?
How to configure for the same?
Hi chaitanya,
how to add the JDBC in JDBC Material in monitoring
thanks
hussain