SAP Cloud Integration (CPI/HCI) || JDBC Adapter (Part 1) || Everything you need to know, A step-by-step guide || Pre-requisites and Configuration

Hello CPI Enthusiasts,

This series covers all about JDBC Adapter, and here are list of blogs:
Part 1: Pre-requisites and Configuration
Part 2: Update the DB using JDBC adapter via a Stored Procedure
Part 3: Update the DB using JDBC adapter via a Stored Procedure (Parameter as XML Type)

And, this is Part 1:

This blog post will cover everything I have experienced so far, configuring a JDBC adapter. I will mention what requirement I got i.e. the scenario and what I did in order to establish the connectivity.

I have a requirement to connect to an SQL and Oracle Database, poll data from various tables using a Stored Procedure and post it in SAP.

So, here are the steps:

STEP 1: Cloud Connector || Cloud to On Premise, Mapping Virtual to Internal System:

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/ Oracle 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 aka CPI aka HCI || Add JDBC Drivers

Download the drivers of Oracle and SQL from their official website, for example the below one for Oracle. Always download the latest version.

After you have downloaded, add it in the JDBC Material > JDBC Driver

Pretty easy, right?

STEP 3: Cloud Integration aka CPI aka HCI || 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 aka CPI aka HCI || 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 the Content Modifier’s Message Body, write the request for Stored procedure call as the below:

<?xml version="1.0" encoding="UTF-8"?><ROOT>
    <SELECT>
        <ROW action="EXECUTE">
	     <table>
		      <!-- Stored Procedure Name-->
		      StoredProcedureName
	     </table>
        </ROW>
    </SELECT>
</ROOT>

In the JDBC adapter, just mention the Data Source name which you created in STEP 3.

 

That’s it.
Deploy the IFlow.

And, it’s a wrap! 🎆

Hope you had a good read.

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

Follow me on linked in by clicking here.

Thanks & Regards,

Sookriti Mishra