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

      13 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo SAP BTP2009
      SAP BTP2009

      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?

      Author's profile photo Dinesh M
      Dinesh M

      Hi Sookrithi,

      I tried uploading ojdbc6.jar, ojdbc7.jar & the latest ojdbc11.jar, mssql driver files(version 8,10,11)

      Getting same error as everyone for both the SQL and oracle.

      Is there any prerequisite we need to do before deploying the JAR file? Please let us know

      Thanks,

      Dinesh

      Author's profile photo Fábio Teixeira
      Fábio Teixeira

      Hi Dinesh,

       

      for SQL you can use this version 8.4 (https://go.microsoft.com/fwlink/?linkid=2137600). For oracle i have tried so many versions and doesn´t work any of then .

      Can anyone clarify witch version work on CPI?

       

      Thanks,

      Fábio Teixeira

      Author's profile photo Jovita Coutinho
      Jovita Coutinho

      Hi Fábio,

       

      Have you got the oracle version that is supported for SAP CPI?

       

      Thanks,

      Jovita

      Author's profile photo Fábio Teixeira
      Fábio Teixeira

      Hi Jovita,

       

      No, I have tried many versions and none worked.

       

      Thanks,

      Best Regards,

      Fábio Teixeira

      Author's profile photo Dinesh M
      Dinesh M

      Hi Sookriti, 

      One generic query. The JAR we are uploading since CPI by default supports only HANA and PostreSQL DBs right, Anything other than this DB then we need to upload the JARs before using, Am I correct?

      Thanks,

      Dinesh

      Author's profile photo Raffaele Di Fluri
      Raffaele Di Fluri

      Hi, I have an issue with the SQL Adapter in CPI (MS Sql Server).

      The jdbc string is jdbc:sqlserver://virtualHost:virtualPort;DatabaseName=databaseName;instanceName=instanceName.

      In cloud connector I have configured a TCP connection: localhost:1433 (TCP)

      If I execute a query from CPI Iflow I get the error: "An internal server error occured: Login failed for user 'xxxxx'".

      It seems the instanceName in the jdbc string is not considered from CPI system.

      Is someone facing this issue?

       

      Thanks

      Raffaele