Skip to Content
Technical Articles

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.

3 Comments
You must be Logged on to comment or reply to a post.
  • 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

  • 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

  • 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