SAP PI Proxy to JDBC Scenario

dilipkkp2412 · ‎11-10-2017

Overview:

Requirement is to insert/update SAP records in non-sap system’s Oracle Table.

To achieve this, we create a Proxy-to-JDBC Asynchronous Outbound Scenario in SAP-PI.

In SAPEcc, one ABAP function module is required to extract data from sap-table and pass it to PI-Proxy-structure and invoke the SAP-PI-Proxy.

Scenario creation steps:

Scenario is called as SAP-PI's "Proxy-to-JDBC Asynchronous Outbound scenario".

[I] Steps in "Enterprise Services Builder": Repository objects

Create DataType/MessageType for Proxy Structure
- Here include fields which are required to be fetch from SAP-Table

Create DataType/MessageType for JDBC Table Query Operations
- For example JDBC query DataType would be like as below for Table 'EmpTable'
- Based on our requirement, here can provide different action method as follows
  1. INSERT
    - When record insertion into JDBC table is in scope
  2. UPDATE
    - When record updation into JDBC table is in scope
  3. UPDATE_INSERT
    - When record insertion/updation simultaneously required
  4. DELETE
    - When we need to delete records from JDBC table
  5. SELECT
    - When we need to fetch records from JDBC table

Create Service Interface Inbound Asynchronous
- Here select message type designed for JDBC table querries
- For e.g. SI_ProxyToJdbc_AI

Create Service Interface Outbound Asynchronous
- Here select message type designed for Proxy
- for. e.g. SI_ProxyToJdbc_AO

Create Message Mapping
- Mapping to transform Proxy structured payload to JDBC-Querry specific structured payload

Create Operation Mapping
- For e.g. OM_ProxyToJdbc

[II] Steps in "Configuration: Integration Builder": Configuration objects

Sender Communication Component
- BS_SAP (a configured business system of SAPR3 in SAP-PI)

Receiver Communication Component
- BC_NonSAP (a business component on behalf of Receiver
  JDBC system)

Sender Communication Channel
- Its not required, because it’s a proxy scenario which class will be invoked from SAP-ECC.

Receiver Communication Channel
- Select a JDBC Adapter with below connection details
- Channel Parameters are as follows:
- JDBC Driver: oracle.jdbc.driver.OracleDriver
- Connection: jdbc:oracle:thin:@<IP>:<PORT >:<DBNm>
- Where:
  - IP is System IP where Oracle database resides
  - Port is default oracle port 1521
  - DBNm is Oracle Database name
  - user credentials of Oracle database

Receiver Determination
- Sender Communication Component =BS_SAP
- Sender Interface =SI_ProxyToJdbc_AO
- Sender Namespace =urn:ProxyToJdbc
- Configured Receiver = BC_NonSAP

Interface Determination
- Sender Communication Component =BS_SAP
- Sender Interface =SI_ProxyToJdbc_AO
- Sender Namespace =urn:ProxyToJdbc
- Receiver Communication Component=BC_NonSAP
- Receiver Interfaces
  - Operation Mapping =OM_ProxyToJdbc
  - Name =SI_ProxyToJdbc_AI
  - Namespace =urn:ProxyToJdbc

Sender Agreement
- Its not required, because it’s a proxy scenario which class will be invoked from SAP-ECC.

Receiver Agreement
- Create receiver agreement with below config details:
- Sender Communication Component =BS_SAP
- Receiver Communication Component =BC_NonSAP
- Receiver Interface =SI_ProxyToJdbc_AI
- Receiver Namespace =urn:ProxyToJdbc
- Receiver Communication Channel =CC_JDBC_Receiver

JDBC Adapter's Message formats for SQL query operations:

To perform table queries using JDBC adapter, we have to create DataType/MessageType for different operation as shown below:

[I] INSERT

To insert records into JDBC table, below SQL query is been used
- INSERT INTO TableName (col1, col2) VALUES('val1', 'val2')

and JDBC requires below message format for INSERT operation

<StatementName>

   <dbTableName action="INSERT">

      <table>TableName</table>

      <access>

         <col1>val1</col1>

         <col2>val2</col2>

      </access>

      <access>

         <col1>val</col1>

      </access>

   </dbTableName>

</StatementName>

Enter the new column values in the <access> block.

The statement must have at least one <access> element

Example: if multiple employee records need to be inserted in single query, then message format should be like below:

<?xml version="1.0" encoding="UTF-8"?>

<ns0:MT_JdbcInsert xmlns:ns0="http://Test_01">

   <StatementName>

      <dbTableName action="INSERT">

         <table>EmpTable</table>

         <access>

            <EmployeeId>3722188032001</EmployeeId>

            <Name>Dilip</Name>

            <Department>IT</Department>

            <Grade>DEF</Grade>           

         </access>

         <access>

            <EmployeeId>3722188032002</EmployeeId>

            <Name>Dhriti</Name>

            <Department>AC</Department>

            <Grade>Z</Grade>    

         </access>

         <access>

            <EmployeeId>3722188032003</EmployeeId>

            <Name>Rose</Name>

            <Department>IT</Department>

            <Grade>X</Grade>    

         </access>

      </dbTableName>

   </StatementName>

</ns0:MT_JdbcInsert>

[II] DELETE

To delete records from JDBC table, below SQL query is been used
- DELETE FROM TableName WHERE ((col2='val' AND col4='val') OR (col2='val'))

and JDBC requires below message format for DELETE operation
- ```
<StatementName>

   <dbTableName action="DELETE">

      <table>TableName</table>

      <key1>

         <col2>val</col2>

         <col4>val</col4>

      </key1>

      <key2>

         <col2>val</col2>

      </key2>

   </dbTableName>

</StatementName>
```
- Enter the condition under which the table values are to be deleted in one or more <key> elements.
- Column values within a <key> element are combined with a logical AND.
- different <key> elements are combined with a logical OR.
- Note: If no condition is specified, then entire table gets deleted.

Example:

if we want to delete all record from 'EmpTable', which have
- Grade ‘X’
- EmployeeId 3722188032001 of Department 'IT'

then its message format is as below

<?xml version="1.0" encoding="UTF-8"?>

<ns0:MT_JdbcDelete xmlns:ns0="http://Test_01">

   <StatementName>

      <dbTableName action="DELETE">

         <table>EmpTable</table>

         <key>

            <EmployeeId>3722188032001</EmployeeId>

            <Department>IT</Department>

         </key>

         <key>

            <Grade>X</Grade>

         </key>

      </dbTableName>

   </StatementName>

</ns0:MT_JdbcDelete>

[III] UPDATE

To change records into JDBC table, below SQL query is been used
- UPDATE TableName SET col1='val', col2='newVal' WHERE ((col2='oldVal' AND col4='val') OR (col2='oldVal'))

and JDBC requires below message format for INSERT operation

<StatementName>

   <dbTableName action="UPDATE">

      <table>TableName</table>

      <access>

         <col1>val</col1>

         <col2>newVal</col2>

      </access>

      <key>

         <col2>oldVal</col2>

         <col4>val</col4>

      </key>

      <key>

         <col2>oldVal</col2>

      </key>

   </dbTableName>

</StatementName>

Enter the new column values in the <access> element.

In the <key> element, enter the condition that can find the data records whose column values are to be changed.

Example:

If we want to update Department & Grade for all the records which has
- Department as 'IT'
- EmployeeId '3722188032002' with Department 'AC'

then below message format is required

<?xml version="1.0" encoding="UTF-8"?>

<ns0:MT_JdbcUpdate xmlns:ns0="http://Test_01">

   <StatementName>

      <dbTableName action="UPDATE">

         <table>EmpTable</table>

         <access>

            <Department>SAP-PI/Fiori</Department>

            <Grade>SPF</Grade>

         </access>

         <key>

            <EmployeeId>3722188032002</EmployeeId>

            <Department>AC</Department>

         </key>

         <key>

            <Department>IT</Department>

         </key>

      </dbTableName>

   </StatementName>

</ns0:MT_JdbcUpdate>

[IV] UPDATE_INSERT

We use an UPDATE_INSERT statement to change and add table values.

JDBC requires below message format for UPDATE_INSERT operation

<StatementName>

   <dbTableName action="UPDATE_INSERT">

      <table>TableName</table>

      <access>

         <col1>newVal</col1>

         <col2>newVal</col2>

      </access>

      <key1>

         <col2>oldVal</col2>

         <col4>val</col4>

      </key1>

      <key2>

         <col2>oldVal</col2>

      </key2>

   </dbTableName>

</StatementName>

Enter the new column values in the <access> element.

In the <key> element, enter the condition that can find the data records whose column values are to be changed.

Example:

Here, if we have a case like,
- Check in table, if record found with respect to key details, then update same record
- else, if record not found, then insert record

for e.g., if we want update Department & Grade of those records which:
- ManagerId=5064
- has Department=IT and Grade= X

if no such case found then insert Department and Grade in table 'DeptTable'

<?xml version="1.0" encoding="UTF-8"?>

<ns0:MT_JdbcUpdateInsert xmlns:ns0="http://Test_01">

   <StatementName>

      <dbTableName action="UPDATE_INSERT">

         <table>DeptTable</table>

         <access>

            <Department>SASP-PI/Fiori</Department>

            <Grade>SPF</Grade>

         </access>

         <key>

            <Department>IT</Department>

            <Grade>X</Grade>

         </key>

         <key>

            <ManagerId>5064</ManagerId>

         </key>

      </dbTableName>

   </StatementName>

</ns0:MT_JdbcUpdateInsert>

[V] SELECT

If we need to perform SELECT query with JDBC Receiver Adapter, then we need to design a Synchronous scenario. Here, two maps are required,

Request-Map will help to convert Dynamic-data-query into and message format of SELECT query supported by JDBC-Adapter.

Response-Map will help to convert JDBC-returned message output into custom-business-format.

SELECT Query operation:

To fetch specific column records from table based on conditions, below SQL query is been used
- SELECT col1, col2, col3 FROM TableName WHERE ((col2 = 'val' AND col2 <> 'val') OR (col3 > 'val'))

and equivalent JDBC adpater requires below request message format for SELECT operation

   <StatementName>

      <dbTableName action="SELECT">

         <table>EmpTable</table>

         <access>

            <col1/>

            <col2/>

            <col3/>

         </access>

         <Key1>

            <col1>val</Department>

            <col2 compareOperation="NEQ">val</Grade>

         </Key1>

         <Key2>

            <col3 compareOperation="GT">val</AGE>

         </Key2>

      </dbTableName>

   </StatementName>

and JDBC returns output response in below message format

<StatementName_response>

   <row>

      <col1>val</col1>

      <col2>val</col2>

      <col3>val</col3>

   </row>

   <row>

      <col1>val</col1>

      <col2>val</col2>

      <col3>val</col3>

   </row>

</StatementName_response>

For Example:

If we need to fetch all records from table 'EmpTable' which is
- of 'IT' department which grade is not equal to X
- having AGE greater than 55

Then my SQL select query would be as

And JDBC Adapter requires below request message format

<?xml version="1.0" encoding="UTF-8"?>

<ns0:MT_JdbcSelect xmlns:ns0="http://Test_01">

   <StatementName>

      <dbTableName action="SELECT">

         <table>EmpTable</table>

         <access>

            <EmployeeId/>

            <FullName/>

            <Department/>

            <GRADE/>

            <AGE/>

         </access>

         <Key>

            <Department>IT</Department>

            <Grade compareOperation="NEQ">X</Grade>

         </Key>

         <Key>

            <AGE compareOperation="GT">55</AGE>

         </Key>

      </dbTableName>

   </StatementName>

</ns0:MT_JdbcSelect>

and as a output JDBC adapter returns below message format data

<?xml version="1.0" encoding="UTF-8"?>

<ns1:MT_JdbcSelect_response xmlns:ns1="http://Test_01">

   <StatementName_response>

      <row>

         <EmployeeId>2</EmployeeId>

         <FullName>DilipPandey</FullName>

         <Department>IT</Department>

         <Grade>Y</Grade>

         <Age>25</Age>

      </row>

      <row>

         <EmployeeId>3</EmployeeId>

         <FullName>DHRITI</FullName>

         <Department>IT</Department>

         <Grade>Y</Grade>

         <Age>30</Age>

      </row>

      <row>

         <EmployeeId>9</EmployeeId>

         <FullName>Kanchan</FullName>

         <Department>AC</Department>

         <Grade>X</Grade>

         <Age>57</Age>

      </row>

   </StatementName_response>

</ns1:MT_JdbcSelect_response>

Note: JDBC Adapter returns above message format having Message-type-name same as of request with postFix '_response'.

About <Key> element:

<Key> elements are equivalent to 'WHERE' conditions of sql query'

Column values within a <key> element are combined with a logical AND.

Different <Key> elements are combined with a logical OR.

Attribute 'compareOperation' can have values as given in following table for different comparison-operator:

Attribute	*Description (comparison-operator)*
EQ	Equals (default value)
NEQ	Does not equal
LT	Less than
LTEQ	Less than or equal to
GT	Greater than
GTEQ	Greater than or equal to
LIKE	Like (strings). In the corresponding value, the SQL placeholders “%” or “_” can then also be used.

To avoid query operations without conditions, then select 'Key Tags Mandatory' in the adapter configuration.