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
- INSERT
- When record insertion into JDBC table is in scope
- UPDATE
- When record updation into JDBC table is in scope
- UPDATE_INSERT
- When record insertion/updation simultaneously required
- DELETE
- When we need to delete records from JDBC table
- 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
[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
- Example: if multiple employee records need to be inserted in single query, then message format should be like below:
[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
- Example:
[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
- Example:
[IV] UPDATE_INSERT
- We use an UPDATE_INSERT statement to change and add table values.
- JDBC requires below message format for UPDATE_INSERT operation
- Example:
[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
- and as a output JDBC adapter returns below message format data
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:
- To avoid query operations without conditions, then select 'Key Tags Mandatory' in the adapter configuration.