Skip to Content
Technical Articles
Author's profile photo Sookriti Mishra

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

 

 

Assigned Tags

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

      Thanks for informative blog. Do you know any limitations for using JDBC as reading or writing to database?

      Author's profile photo Sookriti Mishra
      Sookriti Mishra
      Blog Post Author

      Hi Haled,

      I will be publishing 2 more blogs covering your questions, will tag it here.

      Author's profile photo Sookriti Mishra
      Sookriti Mishra
      Blog Post Author

      Here is Part 2: https://blogs.sap.com/2021/09/20/sap-cloud-integration-cpi-hci-jdbc-adapter-part-2-update-the-db-using-jdbc-adapter-via-a-stored-procedure/

      Will be publishing the Part 3 soon.

      Author's profile photo Nisha Shrivansh
      Nisha Shrivansh

      nice blog Sookriti 🙂

      Author's profile photo James Bowie
      James Bowie

      Thank you for the blog.  I'm able to upload the Microsoft JDBC Driver for SQL Server, but I'm having trouble uploading the Oracle JDBC Driver.  I'm getting the following error message:

      "Deploy artifact failed with error: (deployJdbcDriver)Failed to deploy jdbc driver null for tenant <tenant name>"

      I went to the official Oracle website and downloaded multiple drivers (ojdbc11.jar, ojdbc10.jar, ojdbc8.jar, etc.).  I tried them all, and all fail with the same error.  So what .jar file is it exactly?

      Author's profile photo Sookriti Mishra
      Sookriti Mishra
      Blog Post Author

      Hi James,

      Did you try with ojdbc6.jar?

      Author's profile photo Kunal Rajendra Pawar
      Kunal Rajendra Pawar

      Hi Sookriti,

      Thanks for the blog, I am trying to upload a MySQL JDBC driver but seeing the same error as that of James's. Any particular version I need to upload?