Skip to Content
Technical Articles
Author's profile photo Ilia Stepanov

Access an On-Premise Database Using SAP Cloud Platform Integration

SAP Cloud Platform Integration offers a JDBC adapter which allows you to access databases provides in the SAP Cloud Platform. However, this adapter still does not support connecting to on-premise databases deployed in on-premise networks.

In this blog, I’ll show you steps that are required to connect to your on-premise databases and execute an SQL lookups from the SAP Cloud Platform Integration tenant. On the on-premise side we will use SAP Process Orchestration/ SAP Process Integration (SAP PI) and SAP Cloud Connector.

Prerequisites:
– SAP Cloud Platform Integration tenant
– SAP Cloud Connector
– SAP PI

 

Configuration of the Database

In this blog, I use the MSSQL server of SAP NetWeaver. With the following SQL commands you create an ’employee’ table and fill it with some data:

CREATE TABLE LKG.cpiconn.employee (
empl_id INT PRIMARY KEY IDENTITY (1, 1),
first_name VARCHAR (50) NOT NULL,
last_name VARCHAR (50) NOT NULL,
status VARCHAR(20)
);


INSERT into LKG.cpiconn.employee (
first_name,
last_name,
status
) VALUES ('bill','mustermann' , 'ACTIVE' )

Configuration of SAP PI

For exposing an on-premise database via SAP PI you will need to create an simple pass-through integration flow object in SAP PI with SOAP sender channel and JDBC receiver channel.

  1. Create an integration flow:
  2. Configure a simple pass-through integration flow:
  3. Sender settings:
    • Component: CPITenantSOAP
    • Interface: EmployeeTransferSQLRequest
    • Namespace: urn:sap.com:CPI:Demo
    • Channel Name: SOAPSenderEmployee
    • Adapter Type: SOAP / SAP BASIS 7.50
  4. Receiver settings:
    • Component: EmployeeDatabaseJDBC
    • Interface: EmployeeTransferSQLRequest
    • Namespace: urn:sap.com:CPI:Demo
    • Channel Name: JDBCReceiverEmployee
    • Adapter Type: JDBC / SAP BASIS 7.50
    • Message Protocol: XML SQL Format
    • JDBC Driver:  com.microsoft.sqlserver.jdbc.SQLServerDriver
    • Connection: jdbc:sqlserver://dbhost:1433;databasename=LKG;SelectMethod=cursor;SendStringParametersAsUnicode=true
  5. Save, activate and deploy the integration flow.

 

 

Configuration of SAP Cloud Connector

  1. Connect the Cloud Connector to the SAP Cloud Platform Integration tenant
  2. Use a unique Location ID if you connect multiple Cloud Connector instances to the same tenant.
  3. Create a ‘Mapping Virtual To Internal System’ to expose the SAP PI.
  4. Expose the ‘/XISOAPAdapter/MessageServlet’ resource (sender SOAP Adapter)

 

Configuration of SAP Cloud Platform Integration

  1. Create a package “Demo PI OnPremise JDBC” and an integration flow “Employee Lookup”.
  2. Design an integration flow “Employee Lookup” with the following components:Specify components in the following way:
  3. Timer: Select’Run Once’ for this step
  4. Content Modifier: ‘Prepare SQL’. In the message body enter following SQL XML:
    <root>
     <StatementName>
      <dbTableName action="SELECT">
        <table>LKG.cpiconn.employee</table>
        <access>
          <empl_id/>
          <first_name/>
          <last_name/>
          <status/>
        </access>
        <key1>
          <empl_id>2</empl_id>
        </key1>
      </dbTableName>
     </StatementName>
    </root>
  5. Request/Reply “Lookup Employee” with SOAP adapter.
    This step performs a lookup against SAP PI. Use following settings

      • Address:
        http://vepo750n001:8080/XISOAPAdapter/MessageServlet?senderParty=&senderService=CPITenantSOAP&receiverParty=&receiverService=&interface=EmployeeTransferSQLRequest&interfaceNamespace=urn:sap.com:CPI:Demo
      • Proxy Type: On-Premise
      • Location ID: <location ID>
  6. Logging Groovy script
    In the log Groovy Script component use a simple logging script to log the employee data

    import com.sap.gateway.ip.core.customdev.util.Message;
    import java.util.HashMap;
    def Message processData(Message message) {
        def body = message.getBody(java.lang.String) as String;
        def messageLog = messageLogFactory.getMessageLog(message);
        if(messageLog != null){
            messageLog.addAttachmentAsString("Employee", body, "text/plain");
         }
        return message;
    }
  7. Save and deploy the integration flow

 

Execute the Scenario

On deployment the integration flow is executed (triggered by the run-once timer). Use the following steps to monitor the processed message on the SAP Cloud Platform Integration tenant and in the SAP PI system.

Steps to perform in SAP Cloud Platform Integration:

  • Open Web UI ‘Operations View’ and select a tile ‘Monitor Message Processing’ to check the execution status
  • Click on the message and open an ‘Employee’ attachment
  • The ‘Employee’ attachment contains the response from SAP PI JDBC adapter in XML format.

 

Steps to perfrorm in In SAP PI

  • Open the PI Monitoring Home ( /pimon ), go to Adapter Engine -> Communication Channel Monitor
  • Check the communication channels for the processed message

 

In this blog I showed you how to load data from an on-premise database into SAP Cloud Platform Integration integration flow using the on-premise SAP PI system.

Starting from SP17 the SAP PI will also support the JDBC adapter in cloud integration content. Instead of defining an ICO or a PI integration flow in eclipse, the same scenario will be possible to configure using a cloud integration content, deployed into the PI system. For more details, please check SAP Notes 2804254 and 2864042.

Assigned Tags

      8 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Rakesh damera
      Rakesh damera

      Thanks Ilya, this opens up a lot of possibilities. Are JDBC lookup errors also propagated back to CPI? Or only the sender SOAP errors? A brief note on the possible error scenarios and limitations would make this blog complete!

      Regards,

      Rakesh

      Author's profile photo Naresh Dasika
      Naresh Dasika

      Hello llya,

      if my understanding is correct, we are involving multiple middleware's (On-premise and Cloud) to Integrate with On-premise database.

      CPI trigger Input payload  --> Cloud Connector --> SAP PI JDBC adapter--> SAP CPI Output display.

      Does the throughput high in case of higher loads?

      Regards,

      Naresh

      Author's profile photo Jacky Liu
      Jacky Liu

      Hi Stepanov,

      If customer does not have PO/PI , then there is another way. We can expose a MSSQL precedure which is used to create/update a table as soap api. Then use SOAP adapter to call the SOAP API on OP with help of cloud connector .

      Best Regards!

       

      Jacky Liu

       

       

       

      Author's profile photo TCS User1
      TCS User1

      Hello Jacky,

      Can you please elaborate on how this can be achieved as i am not able to figure out a solution for seamlessly connecting MS Access DB from CPI (without SAP PI on our landscape).

       

      Thanks,

      Rakesh K

      Author's profile photo Johan Brits
      Johan Brits

      HI Rakesh,

       

      I have the same requirement now to upsert data into Microsoft SQL server through CPI,Have you got any solution in your case.

       

      Regards,

      Mahesh

      Author's profile photo Jacky Liu
      Jacky Liu

      Hi Stepanov,

       

      The following is the method to expose a mssql procedure as a webservice . Hope it helps .

      https://www.developer.com/net/asp/article.php/3767311/Creating-Native-Web-Services-in-SQL-Server.htm

      Best regards!

      Jacky  Liu

       

       

      Author's profile photo TCS User1
      TCS User1

      Hello All,

      We do not have SAP PI on our landscape, but we do have a requirement to connect with MS Access DB using CPI. Can anyone help on how to get this scenario work from CPI to MS access database using the JDBC connector available in CPI.

       

      Thanks

       

      Author's profile photo Philippe Addor
      Philippe Addor

      To other readers: Please be aware that this approach is often not required anymore since CPI JDBC adapter is now able to connect to local databases. However, it currently only supports MSSQL, Oracle, DB2 and Hana. So for any other DB, the approach through PI/PO is still valid.