Technical Articles
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.
STEP 3: Cloud Integration aka CPI aka HCI || Add JDBC Data Source
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.
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,
Thanks for informative blog. Do you know any limitations for using JDBC as reading or writing to database?
Hi Haled,
I will be publishing 2 more blogs covering your questions, will tag it here.
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.
nice blog Sookriti 🙂
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?
Hi James,
Did you try with ojdbc6.jar?
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?
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
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
Hi Fábio,
Have you got the oracle version that is supported for SAP CPI?
Thanks,
Jovita
Hi Jovita,
No, I have tried many versions and none worked.
Thanks,
Best Regards,
Fábio Teixeira
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
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