How to Ensure Same Records Are Not Inserted Again in Database Using Jdbc After a Failure in Sap Pi

Overview:

• Requirement is to insert/update SAP records in non-sap organisation'south Oracle Table.
• To attain this, we create a Proxy-to-JDBC Asynchronous Outbound Scenario in SAP-PI.
• In SAPEcc, 1 ABAP function module is required to extract information from sap-tabular array and laissez passer information technology 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 Architect ": Repository objects

1. Create DataType/MessageType for Proxy Construction
   • Hither include fields which are required to be fetch from SAP-Table
2. Create DataType/MessageType for JDBC Tabular array Query Operations
   • For example JDBC query DataType would be like as below for Table 'EmpTable'
   • 
   • Based on our requirement, hither can provide dissimilar action method as follows
     1. INSERT
        • When record insertion into JDBC tabular array 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 nosotros need to fetch records from JDBC table
3. Create Service Interface Inbound Asynchronous
   • Here select message type designed for JDBC table querries
   • For e.thousand. SI_ProxyToJdbc_AI
4. Create Service Interface Outbound Asynchronous
   • Hither select message type designed for Proxy
   • for. east.thou. 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.k. OM_ProxyToJdbc

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

1. Sender Communication Component
   • BS_SAP  (aconfigured concern organisation of SAPR3 in SAP-PI)
2. Receiver Communication Component
   • BC_NonSAP (a business component on behalf of Receiver
     JDBC organization)
3. Sender Communication Aqueduct
   • Its non 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 connectedness details
   • 
   • Channel Parameters are as follows:
   • JDBC Driver: oracle.jdbc.commuter.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 proper name
     • user credentials of Oracle database
5. Receiver Determination
   • Sender Advice Component =BS_SAP
   • Sender Interface                                =SI_ProxyToJdbc_AO
   • Sender Namespace                           =urn:ProxyToJdbc
   • Configured Receiver                          = BC_NonSAP
6. Interface Determination
   • Sender Advice 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 Advice Aqueduct       =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 tabular array, beneath SQL query is been used
  • INSERT INTO TableName  (col1, col2) VALUES('val1', 'val2')
•  and JDBC requires beneath bulletin format for INSERT operation

  •                   <StatementName>    <dbTableName action="INSERT">       <tabular array>TableName</table>       <admission>          <col1>val1</col1>          <col2>val2</col2>       </access>       <access>          <col1>val</col1>       </admission>    </dbTableName> </StatementName>                

  • Enter the new cavalcade values in the <access> cake.
  • The statement must have at least 1 <access> element
• Example: if multiple employee records demand to be inserted in single query, then bulletin format should exist like below:

  •                   <?xml version="1.0" encoding="UTF-eight"?> <ns0:MT_JdbcInsert xmlns:ns0="http://Test_01">    <StatementName>       <dbTableName activeness="INSERT">          <table>EmpTable</tabular array>          <access>             <EmployeeId>3722188032001</EmployeeId>             <Proper name>Dilip</Proper name>             <Department>IT</Department>             <Grade>DEF</Grade>                     </access>          <access>             <EmployeeId>3722188032002</EmployeeId>             <Name>Dhriti</Name>             <Department>Air-conditioning</Department>             <Grade>Z</Grade>              </access>          <access>             <EmployeeId>3722188032003</EmployeeId>             <Proper name>Rose</Name>             <Department>IT</Department>             <Form>Ten</Grade>              </access>       </dbTableName>    </StatementName> </ns0:MT_JdbcInsert>                

[Two]          DELETE

• To delete records from JDBC table, beneath SQL query is been used
  • DELETE FROM TableName  WHERE ((col2='val' AND col4='val') OR (col2='val'))
•  and JDBC requires below bulletin format for DELETE operation

  •                   <StatementName>    <dbTableName action="DELETE">       <table>TableName</tabular array>       <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 exist deleted in i or more than <key> elements.
  • Column values inside a <central> element are combined with a logical AND.
  • different <key> elements are combined with a logical OR.
  • Note:  If no condition is specified, then entire tabular array gets deleted.
• Example:
  • if nosotros want to delete all record from 'EmpTable', which have
    • Grade 'X'
    • EmployeeId 3722188032001 of Department 'IT'
  • so its message format is every bit beneath

  •                   <?xml version="1.0" encoding="UTF-viii"?> <ns0:MT_JdbcDelete xmlns:ns0="http://Test_01">    <StatementName>       <dbTableName action="DELETE">          <table>EmpTable</table>          <cardinal>             <EmployeeId>3722188032001</EmployeeId>             <Department>IT</Department>          </central>          <key>             <Course>X</Grade>          </key>       </dbTableName>    </StatementName> </ns0:MT_JdbcDelete>                

[Three]         UPDATE

• To change records into JDBC tabular array, beneath 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">       <tabular array>TableName</tabular array>       <access>          <col1>val</col1>          <col2>newVal</col2>       </access>       <key>          <col2>oldVal</col2>          <col4>val</col4>       </cardinal>       <key>          <col2>oldVal</col2>       </cardinal>    </dbTableName> </StatementName>                

  • Enter the new column values in the <access> chemical element.
  • In the <key> chemical element, enter the condition that can observe the data records whose column values are to be changed.
• Example:
  • If we desire to update Department & Grade for all the records which has
    • Department as 'IT'
    • EmployeeId '3722188032002' with Department 'Air conditioning'
  • then below bulletin format is required

  •                   <?xml version="one.0" encoding="UTF-8"?> <ns0:MT_JdbcUpdate xmlns:ns0="http://Test_01">    <StatementName>       <dbTableName action="UPDATE">          <tabular array>EmpTable</table>          <admission>             <Department>SAP-PI/Fiori</Section>             <Class>SPF</Grade>          </access>          <key>             <EmployeeId>3722188032002</EmployeeId>             <Department>Air-conditioning</Section>          </fundamental>          <key>             <Department>It</Department>          </cardinal>       </dbTableName>    </StatementName> </ns0:MT_JdbcUpdate>                

[IV]        UPDATE_INSERT

• We use an UPDATE_INSERT statement to alter and add tabular array values.
• JDBC requires below message format for UPDATE_INSERT operation

  •                   <StatementName>    <dbTableName action="UPDATE_INSERT">       <tabular array>TableName</table>       <admission>          <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> chemical element.
  • In the <key> element, enter the condition that tin can find the data records whose cavalcade values are to be changed.
• Case:
  • Here, if nosotros accept a instance like,
    • Check in tabular array, if record institute with respect to key details, and so update same record
    • else, if record not found, then insert record
  • for due east.chiliad., if we want update Department & Grade of those records which:
    • ManagerId=5064
    • has Department=IT and Grade= Ten
  • if no such instance found then insert Department and Grade in table 'DeptTable'

  •                   <?xml version="ane.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</Section>             <Grade>SPF</Grade>          </access>          <cardinal>             <Department>It</Department>             <Form>10</Grade>          </key>          <fundamental>             <ManagerId>5064</ManagerId>          </key>       </dbTableName>    </StatementName> </ns0:MT_JdbcUpdateInsert>                

[Five]         SELECT

If we need to perform SELECT query with JDBC Receiver Adapter, and then we need to design a Synchronous scenario . Hither, 2 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 volition help to catechumen JDBC-returned message output into custom-business-format.

SELECT Query performance:

• To fetch specific column records from table based on weather, 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 activity="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 beneath 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 'Information technology' section which grade is not equal to X
  • having Historic period greater than 55
• So my SQL select query would be as
• And JDBC Adapter requires below asking message format

  •                   <?xml version="1.0" encoding="UTF-8"?> <ns0:MT_JdbcSelect xmlns:ns0="http://Test_01">    <StatementName>       <dbTableName action="SELECT">          <table>EmpTable</table>          <admission>             <EmployeeId/>             <FullName/>             <Department/>             <Form/>             <Historic period/>          </admission>          <Key>             <Section>It</Section>             <Grade compareOperation="NEQ">10</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>          <Form>Y</Grade>          <Age>25</Age>       </row>       <row>          <EmployeeId>3</EmployeeId>          <FullName>DHRITI</FullName>          <Department>IT</Department>          <Course>Y</Course>          <Age>30</Age>       </row>       <row>          <EmployeeId>9</EmployeeId>          <FullName>Kanchan</FullName>          <Department>AC</Department>          <Class>X</Grade>          <Age>57</Age>       </row>    </StatementName_response> </ns1:MT_JdbcSelect_response>                

  • Note: JDBC Adapter returns higher up bulletin format having Message-type-proper name aforementioned 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.
• Dissimilar <Central> elements are combined with a logical OR.
• Attribute 'compareOperation'tin have values every bit given in following table for different comparing-operator:
• To avert query operations without weather condition, so select 'Key Tags Mandatory' in the adapter configuration.
• 

williamshisomed49.blogspot.com

Source: https://blogs.sap.com/2017/11/10/sap-pi-proxy-to-jdbc-scenario/

Share this post