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.