Skip to Content
Technical Articles
Author's profile photo Nisha Shrivansh

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

Assigned tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Frank Li
      Frank Li

      Thanks a lot for sharing.

      Author's profile photo Frederick Hill
      Frederick Hill

      Thanks for sharing such great information, the post you published have some great information which is quite beneficial for me.

       

       

      mymilestonecard