Skip to Content

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:
  • 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.
To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Pedro Rodrigues

    Congratulations !! Nice Blog.

     

    How to make SELECT and with those conditions below:

     

    If  there is no local pi number in source message execute SQL query:

    SELECT * FROM TABLE”

     

    If local pi number was given in source message execute query:

    SELECT * FROM TABLE” WHERE local_pi_number IN (: localPiNumber (s))

     

    If Country Code or Location was given execute query:

    SELECT * FROM TABLE”  WHERE (UPPER(CAST(LOCAL_DRUG_PRODUCTS AS VARCHAR2(3999))) LIKE UPPER(: localDrugProducts ) AND NVL(NVL(COUNTRY_CODE, LOCATION), ”) IN (: countryCode ))

     

    (0) 
  2. Dilip Kumar KrishnaDev Pandey Post author

    Dear Pedro Rodrigues,

    For Select query, we can use JDBC Sender Adapter, where we can input our desired select query.

    For reference purpose, please see below scree n.

    and output we get in below xml format.

    <?xml version="1.0" encoding="UTF-8"?>
    <resultset>
       <row>
          <col1>1</col1>
          <col2>IN</col2>
          <col3>2017-06-15 00:00:00.0</col3>
          <col4>T</col4>
       </row>
       <row>
          <col1>2</col1>
          <col2>IN</col2>
          <col3>2017-06-15 00:00:00.0</col3>
          <col4>3</col4>
       </row>
    </resultset>

     

    Thanks & Regards,

    Dilip

    (0) 
    1. Dilip Kumar KrishnaDev Pandey Post author

      Dear

      I have updated the blog with details of SELECT query using JDBC Receiver Adapter.

      Thank you for highlighting this info, as it was not in my business scenario so never tried in past.

      With one test scenario, today I have tested SELECT with JDBC receiver as updated in blog.

      But I haven’t tried IN operation.

       

      Thanks & Regards,

      Dilip

      (0) 

Leave a Reply