Skip to Content
Author's profile photo Dilip Kumar Krishnadev Pandey

SAP PI Proxy to JDBC Scenario

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.

Assigned Tags

      23 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      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 ))

       

      Author's profile photo Dilip Kumar Krishnadev Pandey
      Dilip Kumar Krishnadev Pandey
      Blog Post Author

      Dear Former Member,

      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

      Author's profile photo Evgeniy Kolmakov
      Evgeniy Kolmakov

      Hi Dilip!

      One suggestion: NEVER use UPDATE statement without WHERE class matching the same in SELECT statement, even in test scenarios.

      Regards, Evgeniy.

      Author's profile photo Dilip Kumar Krishnadev Pandey
      Dilip Kumar Krishnadev Pandey
      Blog Post Author

      Dear Evgeniy Kolmakov,

      Thanks for the suggestion, sure, will consider it.

       

      Thanks & Regards,

      Dilip

      Author's profile photo Former Member
      Former Member

      Dilip,

       

      Its receiver and not sender.

       

      How must be the structure for such selects ?

       

      THanks

      Author's profile photo Dilip Kumar Krishnadev Pandey
      Dilip Kumar Krishnadev Pandey
      Blog 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

      Author's profile photo Jorge Imoberdorff
      Jorge Imoberdorff

      I Dilip.

      I'm new in SAP PI, and i don't know how can i find this fields:

      • JDBC Driver:  oracle.jdbc.driver.OracleDriver
      • Connection:    jdbc:oracle:thin:@<IP>:<PORT >:<DBNm>

      I'm trying to connect to DB2 (AS400) and i don't know where is this information to configure the CC.

      Can you gide me with this?.

       

      Thaks in advance!.

      Author's profile photo Evgeniy Kolmakov
      Evgeniy Kolmakov

      Hi Jorge !

      You should ask someone from your DB team for assistance.

      Regards, Evgeniy.

      Author's profile photo Jorge Imoberdorff
      Jorge Imoberdorff

      Thanks! .

      So, this information isn't in PI? From SLD?

      Author's profile photo Evgeniy Kolmakov
      Evgeniy Kolmakov

      Hi Jorge!

      This information sould be provided by DB administrators.

      Regards, Evgeniy.

      Author's profile photo Dilip Kumar Krishnadev Pandey
      Dilip Kumar Krishnadev Pandey
      Blog Post Author

      Dear Jorge Imoberdorff

      Please refer below info, that may help you:

      • First above info (Oracle database details), are not related to PI or SLD, this is related to the 3rd part system, which database you want to connect from PI
      • Database & Table:
        • If you want to connect DB2 (AS400) , and here I assume, say DataBase name is DB2 and Table name is AS400.
      • DB IP:
        • Next you require Database server (system)’s IP, where oracle database is running/installed, you can ask to DB admin or if you have access just do ipconfig from cmd prompt and get IPv4 addres
        • say you got IP like “xxx.xxx.xxx.xxx”
      • DB Port:
        • it will remain default 1521 in case of oracle database
      • Thus in CC, you have to configure parameters like
        • JDBC Driver:  oracle.jdbc.driver.OracleDriver
        • Connection:    jdbc:oracle:thin:@xxx.xxx.xxx.xxx:1521:DB2
      • And Table name will be passed in request structure.

       

      Thanks & Regards:

      Dilip

       

      Author's profile photo Salomão Lopes
      Salomão Lopes

      Hi Dilip

      Your example with SELECT it's clear.

      Is it possible to demonstrate an example with an INSERT?

      Some questions:

      1. This instruction must be ASSYCRONUS, can I use it at SYNCHRONOUS?

      2. I only receive form PI/PO the return of send, how can I get the return of SQL instruction process? (ex: success insert or duplicate key?)

      Solution present for other tech was:

      1. Realize INSERT

      2. After realizing SELECT to confirm the record at DB

      In some instance, if its mandatory use ASYNCHRONOUS instruction to INSERT, there are place that can I consut after the result code of SQL?

      Cheers,

      Salomão Lopes

      Author's profile photo Dilip Kumar Krishnadev Pandey
      Dilip Kumar Krishnadev Pandey
      Blog Post Author

      Dear Salomão Lopes,

      Example for 'INSERT' is given in the blog, plz refer it.

      Same PI-scenario can be handled in synchronous mode, here JDBC will return a response message confirming success/error of INSERT statement. There is a fix response strcture you will get in return.

       

      Regards,

      Dilip

      Author's profile photo Salomão Lopes
      Salomão Lopes

      Tks!!

       

      Author's profile photo Akash Chauhan
      Akash Chauhan

      Hi Dilip,

      I have a Proxy to JDBC scenario in which i need to DELETE the data first in the dB and then perform update operation, can you please clarify on the below points,

      1. I am working on SAP PO 7.5(Single stack) do i need to create Sender CC in this case.
      2. If the following structure for the receiver end is correct

      <StatementName>

       

      <StatementName>
         <dbTableName action="DELETE">
            <table>TableName</table>
            <key1>
               <col2>val</col2>
               <col4>val</col4>
            </key1>
            <key2>
               <col2>val</col2>
            </key2>
         </dbTableName>
      </StatementName>​

       

      Author's profile photo Akash Chauhan
      Akash Chauhan

      Hi Dilip,

      Nice blog !!!

      I have a proxy to JDBC scenario in which i need to first DELETE data in dB(not using key) and then INSERT the new data, can you please clarify on the below points

      1. As i am working on SAP PO 7.5(Single Stack/JAVA Stack) do i need to create a Sender CC?
      2. Can you please help me how do i create the DT for receiver end?

      Thanks

      Akash Chauhan

      Author's profile photo Dilip Kumar Krishnadev Pandey
      Dilip Kumar Krishnadev Pandey
      Blog Post Author

      Hi Akash,

      Thanks and plz find below comments w.r.t. your querries:

      1. In SAPPO-7.5, for proxy-to-JDBC scenario, yes, you need to create Sender SOAP with XI protocol.
      2. For DELETE operation, respective supporting structure is been given in above blog, please refer, you have to create dataType messageType for same.

       

      Thanks & Reagrds,

      Dilip

      Author's profile photo Akash Chauhan
      Akash Chauhan

      Hi Dileep,

      I have a PROXY to JDBC scenario in which i need to UPDATE_INSERT the data in DB temp table, than need to EXECUTE the stored procedure at DB temp table which puts the data to DB main table,

       

      How can i write UPDATE_INSERT and EXECUTE in one structure,

      Please provide your feedback

       

      Thanks & Regards,

      Akash Chauhan

      Author's profile photo Dilip Kumar Krishnadev Pandey
      Dilip Kumar Krishnadev Pandey
      Blog Post Author

      Hi Akash,

      I haven't tried yet any scenario having UPDATE_INSERT and EXECUTE in one structure.

      Will definitely ping you, if get anything related.

      Thanks & Regards,

      Dilip

      Author's profile photo Akash Chauhan
      Akash Chauhan

      Hi Dileep,

      As we can add multiple StatmentName in one structure

      https://help.sap.com/saphelp_nwpi711/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm?loaded_from_frameset=true

       

      So can i use first StatementName1 as Update_Insert and StatementName2 as Storedprocedure? Will that work?

      Waiting for your quick response !!!

       

      Thanks & regards,

      Akash Chauhan

      Author's profile photo Dilip Kumar Krishnadev Pandey
      Dilip Kumar Krishnadev Pandey
      Blog Post Author

      Hi Akash,

      You can give a try, till now I havn't used any scenario with multiple StatementName.

      Thanks & Regards,

      Dilip

      Author's profile photo Nishanth Kiran
      Nishanth Kiran

      Hi Dilip,

      Can I use single JDBC query and inset into multiple Database tables ?

      Like if I have data in below XML and want header data in SQL_HEAD & item data in SQL_ITEM?

      <Entity>
      <HeaderField1>123</HeaderField1>
      <HeaderField2>ABC</HeaderField2>
      <ItemField1>789</ItemField1>
      <ItemField2>XYZ</ItemField2>
      </Entity>

       

      Author's profile photo Dilip Kumar Krishnadev Pandey
      Dilip Kumar Krishnadev Pandey
      Blog Post Author

      Hi Nishanth,

      Sorry for very late reply, by now you would have already addressed the issue.

      However, you can do it like below:

      • You can split the message into two target msgs (as per JDBC insert query structures dedicated to one for SQL_HEAD and other for SQL_ITEM)
      • Now, you will be having one common Sender_Outbound_Interface which will be used in two operation maps one for Header other for Item
      • You will have two receiver components attached with two JDBC_Receiver_Channel
      • like vise respective config need to be completed.

      Thanks & Regards,

      Dilip P.