Configuration of JDBC adapter to poll data from Database in Cloud Integration(CPI)

Dear Reader,

In this blog I will explain each step required to poll data from any database system using JDBC adapter in Cloud Integration and post that data in xml file format to FTP server location.

I will only focus on configurations required for JDBC adapter in CPI, I already covered FTP adapter configuration in my previous blog:

https://blogs.sap.com/2021/09/23/configuration-of-ftp-and-sftp-adapter-in-cloud-integrationcpi/comment-page-1/#comment-593360

JDBC adapter enable us to connect cloud integration to cloud or on-premise databases.

Prerequisites to connect to database using JDBC adapter to cloud integration:

1. Driver deployment: Tenant admin has to upload JDBC driver (SQL/Oracle) to the tenant under Manage JDBC Material -> JDBC Driver
2. TCP connection: If we are connecting to on-premise database then we need to create a TCP connection in cloud connector. Login to cloud connector and go to Cloud to On-Premise -> add mapping virtual to internal systems.

   Check the reachability of on-premises database.

   

3. Data Source creation: To connect to database we need to create a data source under Manage JDBC material->JDBC Data SourceWe need to provide following information for Data Source:Name: Name of Data SourceDescription: provide relevant description for data source

   Database type: select your database type

   User: user id for database

   Password: Password for database

   JDBC URL: this we need to form

   SQL:  jdbc:sqlserver://virtualHost:virtualPort;DatabaseName=databaseName;
   Oracle:  jdbc:Oracle:thin:@virtualHost:virtualPort:databaseName

   Location ID: CPI tenant location ID

   Cloud Connector: check this check box

   Click on deploy after providing all above information.

    

4. JDBC adapter configuration: Configure JDBC adapter in integration flow to communicate with database.Once these prerequisites are done then we can create an iflow which will pull data from SQL database and generate a file at target FTP Server.

Integration Flow:

In content modifier we will write query to call stored procedure.

In table we need to provide stored procedure name which we want to call in database.

Then we will use Receiver JDBC adapter to call database and use request reply to get response from database.

In JDBC Data Store Alias provide the datastore name which we have created in previous section.

Once we receive data from database, we are sending it to the FTP server location in xml file format.

Now we will deploy the Integration flow.

XML files have been created at FTP server location.

So, we learnt how to poll data from database stored procedure using JDBC adapter in Cloud Integration aka Cloud Platform Integration.

Reference:

https://help.sap.com/viewer/368c481cd6954bdfa5d0435479fd4eaf/Cloud/en-US/88be64412f1b46d684dfba11f2767c5b.html

Happy reading 😊

Thanks & Regards,

Nisha Shrivansh