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

  1. Create DataType/MessageType for Proxy Structure
    • Here include fields which are required to be fetch from SAP-Table
  2. 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
  3. Create Service Interface Inbound Asynchronous
    • Here select message type designed for JDBC table querries
    • For e.g. SI_ProxyToJdbc_AI
  4. Create Service Interface Outbound Asynchronous
    • Here select message type designed for Proxy
    • for. e.g. SI_ProxyToJdbc_AO
  5. Create Message Mapping
    • Mapping to transform Proxy structured payload to JDBC-Querry specific structured payload
  6. Create Operation Mapping
    • For e.g. OM_ProxyToJdbc

 

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

  1. Sender Communication Component
    • BS_SAP  (a configured business system of SAPR3 in SAP-PI)
  2. Receiver Communication Component
    • BC_NonSAP (a business component on behalf of Receiver
      JDBC system)
  3. Sender Communication Channel
    • Its not required, because it’s a proxy scenario which class will be invoked from SAP-ECC.
  4. 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
  5. Receiver Determination
    • Sender Communication Component =BS_SAP
    • Sender Interface                                =SI_ProxyToJdbc_AO
    • Sender Namespace                           =urn:ProxyToJdbc
    • Configured Receiver                          = BC_NonSAP
  6. 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
  7. Sender Agreement
    • Its not required, because it’s a proxy scenario which class will be invoked from SAP-ECC.
  8. 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,

  1. Request-Map will help to convert Dynamic-data-query into and message format of SELECT query supported by JDBC-Adapter.
  2. 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:
  • AttributeDescription (comparison-operator)
    EQEquals (default value)
    NEQDoes not equal
    LTLess than
    LTEQLess than or equal to
    GTGreater than
    GTEQGreater than or equal to
    LIKELike (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.

New NetWeaver Information at SAP.com

Very Helpfull

User Rating: Be the first one !