Menu

riyaz.net

Tech Tips and Tutorials for SAP Professionals and Bloggers

Home › SAP › How to Configure JDBC Adapter in SAP PI

How to Configure JDBC Adapter in SAP PI

/ — SAP

JDBC-adapter

This article will help you understand how to configure the sender and receiver JDBC adapter in SAP PI/XI. We will discuss a simple example wherein we will use sender and receiver JDBC adapter to talk to an Oracle Database.

The JDBC adapter is used to connect to different database systems via SAP PI. The adapter converts database content to XML messages and vice versa.

Sender JDBC Adapter is used to read data from databases while the receiver JDBC adapter writes data from SAP PI to the relevant databases. Database content can be read with any SQL statement. To write data to database, a predefined XML format needs to be used. You can execute SQL INSERT, UPDATE, SELECT, DELETE, or stored procedure statements. Direct arbitrary SQL statement can as well be used. However, this should be used only for test purposes.  Point to note is that a message is always processed in exactly one database transaction.

To be able to use JDBC adapter with a particular database, you must install the corresponding JDBC drivers on your SAP PI server. The driver files can be obtained from the database vendor.

Now let us configure a simple scenario to understand JDBC adapters.

Let’s say we have a simple table which stores employee details in an Oracle Database. The table has 4 fields corresponding to Employee Number, Name, Department and a status field to indicate new entries. Our aim is to read new entries from this table using SAP PI. After reading a record, the status field should be set appropriately so that the record is not picked up again. We will configure this using a JDBC sender adapter. Now whenever an employee is transferred from one department to other, this data should get updated back in the Oracle database. We will accomplish this using JDBC receiver adapter.

Configuring the Sender JDBC Adapter

The figure below shows a typical configuration for JDBC sender adapter. JDBC Driver and Connection parameters shown correspond to Oracle database. These parameters differ depending on the database you are using. The correct parameters for your database can be found out from the database vendor.

Typical Configuration of SAP PI JDBC Sender Adapter

Poll Interval specifies how often JDBC adapter polls the database. Query SQL Statement should contain the actual SQL SELECT statement using which you want to query the database. e.g  in our case –

SELECT E_NO as Emp_Number, E_NAME as Emp_Name, DEPT as Emp_Dept from EMP_TAB where E_NEW = 'Y' order by E_NO

Corresponding to your SQL SELECT statement’s output, you need to create a data type and a message type in IR/ESR. In our case it would look something like below:

Standard XML structure for Sender JDBC Adapter

The standard format shown must be followed. The JDBC adapter always returns records in the XML format like one shown below. Note that <row> is in all lowercase.

<MessageType>
      <row>
            ...
            ...
      </row>
      <row>
            ...
            ...
      </row>
           ...
           ...
</MessageType>

Document Name in the adapter configuration specifies the Message Type.

Update SQL Statement should contain the UPDATE statement so that previously read data is not read again when the adapter polls the database next time. e.g. in our case

                UPDATE EMP_TAB SET E_NEW = 'N' WHERE E_NEW = 'Y'

For test purposes, you might want to read the same data again and again. In this case, put the string <test> (including the angle brackets) in the Update SQL Statement field.

Configuring the Receiver JDBC Adapter

 Now we want to update the database from PI. Following figure shows required configuration for JDBC receiver adapter.

Typical Configuration of SAP PI JDBC Receiver Adapter

When you use XML SQL Format as Message Protocol, the data type and message type created for sending the data through JDBC adapter must follow a standard XML format shown below.

Standard XML structure for Receiver JDBC Adapter

When sending the message, the action attribute should contain the type of SQL statement e.g. INSERT, UPDATE, DELETE etc. The table field should contain the actual database table name.

The access node should be used to specify the fields that wish to insert/update in the database while the key node specifies the where criteria of SQL statement.

When you use Native SQL String as Message Protocol, the above format is not necessary. In that case, the JDBC adapter expects the actual SQL formatted string as the payload. The Native SQL String protocol should be used only for test purposes however.

Now simply create a new employee record in the Oracle database with E_NEW = ‘Y’ and the sender JDBC adapter will pick it up when it polls the database. To test the receiver JDBC adapter, you might need to configure rest of the scenario or you can send an XML message in the above format from the Test Message tab of Runtime workbench.

User Reviews and Comments

  1. Nice Blog, I would expect more information on the receiver JDBC channel parameters like “Key Tags” and database transaction levels.

    Regards
    Anandh.B

  2. Hi Riyaz

    Thanks for you effort .

    I have a question an hope you can help me with it.

    Suppose I have the following structure

    1 — 1

    1–n

    The sender JDBC returns

    How can I replicate it in the Data type for the JDBC sender
    .

    Thanks

  3. Sorry , I try again

    Invoice
    Header 1 — 1
    f1
    f2
    f3
    /Header
    Item1–n
    f1
    f2
    f3
    /Item
    /Invoice

    The sender JDBC will probably return r
    Invoice
    row
    f1
    f2
    f3
    /row
    /Invoice

  4. Hi Riyaz

    Thanks for the quick reply and sorry about the miss

    I have to map a data from oracle to an invoice Idoc

    I have to fetch data form 2 tables , one for the header and one for the details
    the structur in the tables is as the following

    Header 1 — 1
    f1
    f2
    f3
    /Header
    Item1–n
    f1
    f2
    f3
    /Item

    how should the Data type for JDBC sender look like .

    and plz bear with me with also this question.
    suppose there are more than one records (including header and items), how can do the mapping that
    the same number of Idocs is created .

    do I have ti change the occurrence in message mapping in both source and target structures.

    I really apreciate you help and thanks once more

    • To fetch data from multiple tables, you can use joins in the database query. The data type should be exactly as described above in the article. The fields returned by database should be under row node in the same sequence as returned by the query.

  6. Hi,

    There is an interface which is currently fetching data from a table.
    Instead of table, there needs to be a jdbc call on oracle function.
    The team wants to fetch the details by a function call.How to do this..

  7. Hi Riyaz,
    Thanx for the quick reply.
    How to use the EXECUTE statement.Below are the arguments to be passed..how to give these details ?
    Function Name; FN_ATTENDANCE_DATA_SAP

    Arguments:
    PERNR – Number type
    Flag – Number type
    swipe_in – String type
    swipe_out – String type
    d_date – String type

    Returns:
    Result – Number type

    • As described in the above article, you need to create data types/message types as per the document formats given in the link I shared earlier. Map the input/output parameters to these message types as usual. You need to map a constant value ‘EXECUTE’ to the attribute named action.

      • I have tried as you suggested by I see the following error message stating that it is not a stored procedure. I think we can use it with Stored procs not with functions,

        Error

        com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Error when executing statement for table/stored proc. ‘SAP_PI_TEST’ (structure ‘Statement’): java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00221: ‘SAP_PI_TEST’ is not a procedure or is undefined ORA-06550: line 1, column 7: PL/SQL: Statement ignored

  8. You you should change the page name title How to Configure JDBC Adapter in SAP PI | riyaz.net to something more better for your blog post you create. I loved the post all the same.

  9. Hi,

    We need to pick exchange rates from a website, which contains a fixed html table format. How do we do this from PI.
    The sample source for the html page is as below:
    The Central Bank of the Russian Federation has set from 09.12.2010 the following exchange rates of foreign currencies against the ruble without assuming any liability to buy or sell foreign currency at the above rate

    Num сodeChar сodeUnitCurrencyRate
    036
      AUD
    1
      Australian Dollar
    30.6275

    944
      AZN
    1
      Azerbaijan Manat
    39.0635

    thanks

    neeraj

  10. The source got converted to html, am reposting it:

    The Central Bank of the Russian Federation has set from 09.12.2010 the following exchange rates of foreign currencies against the ruble without assuming any liability to buy or sell foreign currency at the above rate

    ‘
    ‘Num сodeChar ‘сodeUnitCurrencyRate
    ‘036
    ‘  AUD
    ‘1
    ‘  Australian Dollar
    ‘30.6275

    ‘944
    ‘  AZN
    ‘1
    ‘  Azerbaijan Manat
    ‘39.0635

    thanks

    • If the website provides web services related to your requirement, you can make use of those. Alternatively, you may consider writing a java snippet that would parse the html and provide you the required XML. You can make this java snippet available as a local web service in your environment.

  12. Hi All,
    I created a scenario of File-To-JDBC. In where JDBC adapter will be used in the receiver end. I have created a virtual Database through the OpenOfficeOrg Base in my local machine. Now I do not know what should be the credentials for the option “JDBC Driver” and “Connection” in the receiver channel connection parameter.

    Kindly help me out .
    Regards

  13. Hi Riyaz,

    How to insert the JPEG file in to Databse…..explain each step plz also wanted to know about how to use the stored procedure in sender and receiver JDBC channel

  14. Hi Riyaz,

    Nice blog and it is very helpful. Could you please tell me the procedure to integrate sybase with SAP PI. I am searching for this document from past 5 days but couldn’t find any. It will be a great favor.

    Regards,
    Suryakanth

  15. Nice Blog…but I have a few questions;
    1/ If an update of the database were to occur after the select but before the update would entries be lost or is there some type of locking procedure on the record?
    2/ Do you have and example of how to execute an Oracle stored procedure from the Communication Channel configuration?

  16. Hi ,

    I have a scenario which is exactly similar to what you have described in the sender JDBC channel description.
    And the scenario is JDBC (sender) to SOAP (receiver). I planned to make it asynchronous. So when the SOAP call fails what happens to the UPDATE statement in the sender channel. Will that be executed? If so, then what happens to the record which is already updated? It will not be fetched again right? What should be the best solution?

  17. Hi Riyaz,

    Your blog is very helpful for me. But i have one doubt. i.e.
    Can we use function library in multiple swcv?Let me explain briefly?

  20. HI Riyaz,

    In RFC adapter having LOgon uname, pwd n client fields there.which system uname, pwd will give either ecc or pi system?
    can u explain about it?

    Thanks,
    Veera

  21. We have received an output from PI, that looks like the following:

    Healy_Hudson_GmbH_Abnahme_Bestellung<ID>9700025170</ID><MsgType>Bestellung</MsgType>8/6/2012_10:13:36_AMBestellsystem_-_procureCA <orderinfo@voith-info.com>"edi.orders@kennametal.com"<edi.orders@kennametal.com>application/octet-stream;name="=?iso-8859-1?Q?220595deul8XwP=2Exml?="2012-08-06T08:14:22Z<201208060813.q768DaWR003227@relay.voith-info.com>PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0iaXNvLTg4NTktMSI/Pg0KPE9SREVSIHZlcnNp
    b249IjEuMCI+DQoJPE9SREVSX0hFQURFUj4NCgkJPENPTlRST0xfSU5GTz4NCgkJCTxHRU5FUkFU
    T1JfSU5GTz5DcmVhdGVkIGJ5IENhT3BlblRyYW5zIFYzLjA8L0dFTkVSQVRPUl9JTkZPPg0KCQk8
    L0NPTlRST0xfSU5GTz4NCgkJPFNPVVJDSU5HX0lORk8+DQoJCQk8QUdSRUVNRU5UIHR5cGU9ImJ1
    eWVyIj4NCgkJCQk8QUdSRUVNRU5UX0lEPjE1ODY8L0FHUkVFTUVOVF9JRD4NCgkJCTwvQUdSRUVN
    RU5UPg0KCQk8L1NPVVJDSU5HX0lORk8+DQoJCTxPUkRFUl9JTkZPPg0KCQkJPE9SREVSX0lEPjIw
    MTEvOTcwMDAyNTE3MC9IMTQ8L09SREVSX0lEPg0KCQkJPEFMVF9DVVNUT01FUl9PUkRFUl9JRD4y
    MjA1OTU8L0FMVF9DVVNUT01FUl9PUkRFUl9JRD4NCgkJCTxPUkRFUl9EQVRFPjIwMTItMDgtMDY8
    L09SREVSX0RBVEU+DQoJCQk8REVMSVZFUllfREFURT4NCgkJCQk8REVMSVZFUllfU1RBUlRfREFU
    ….
    ….
    so, anything that follows the tag can not be read. Is this a translation problem?
    Can you help?

    Kind regards,
    Wolfgang

  22. We have received an output from PI, that looks like the following:
    “”
    “Healy_Hudson_GmbH_Abnahme_Bestellung<ID>9700025170</ID><MsgType>Bestellung</MsgType>8/6/2012_10:13:36_AMBestellsystem_-_procureCA <orderinfo@voith-info.com>"edi.orders@kennametal.com"<edi.orders@kennametal.com>application/octet-stream;name="=?iso-8859-1?Q?220595deul8XwP=2Exml?="2012-08-06T08:14:22Z<201208060813.q768DaWR003227@relay.voith-info.com>”
    “PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0iaXNvLTg4NTktMSI/Pg0KPE9SREVSIHZlcnNp
    b249IjEuMCI+DQoJPE9SREVSX0hFQURFUj4NCgkJPENPTlRST0xfSU5GTz4NCgkJCTxHRU5FUkFU
    T1JfSU5GTz5DcmVhdGVkIGJ5IENhT3BlblRyYW5zIFYzLjA8L0dFTkVSQVRPUl9JTkZPPg0KCQk8
    L0NPTlRST0xfSU5GTz4NCgkJPFNPVVJDSU5HX0lORk8+DQoJCQk8QUdSRUVNRU5UIHR5cGU9ImJ1
    eWVyIj4NCgkJCQk8QUdSRUVNRU5UX0lEPjE1ODY8L0FHUkVFTUVOVF9JRD4NCgkJCTwvQUdSRUVN
    RU5UPg0KCQk8L1NPVVJDSU5HX0lORk8+DQoJCTxPUkRFUl9JTkZPPg0KCQkJPE9SREVSX0lEPjIw
    MTEvOTcwMDAyNTE3MC9IMTQ8L09SREVSX0lEPg0KCQkJPEFMVF9DVVNUT01FUl9PUkRFUl9JRD4y
    MjA1OTU8L0FMVF9DVVNUT01FUl9PUkRFUl9JRD4NCgkJCTxPUkRFUl9EQVRFPjIwMTItMDgtMDY8
    L09SREVSX0RBVEU+DQoJCQk8REVMSVZFUllfREFURT4NCgkJCQk8REVMSVZFUllfU1RBUlRfREFU
    RT4yMDEyLTA4LTA2PC9ERUxJVkVSWV9TVEFSVF9EQVRFPg0KCQkJCTxERUxJVkVSWV9FTkRfREFU
    RT4yMDEyLTA4LTA2PC9ERUxJVkVSWV9FTkRfREFURT4NCgkJCTwvREVMSVZFUllfREFURT4NCgkJ
    CTxPUkRFUl9QQVJUSUVTPg0KCQkJCTxCVVlFUl9QQVJUWT4NCgkJCQkJPFBBUlRZPg0KCQkJCQkJ
    PFBBUlRZX0lEIHR5cGU9InN1cHBsaWVyX3NwZWNpZmljIj43MDAxNDk0MzwvUEFSVFlfSUQ+DQoJ”….. etc…..
    ….
    so, anything that follows the tag can not be read. Is this a translation problem?
    Can you help?

    Kind regards,
    Wolfgang

  23. hi,

    i need help please!,

    i’m trying to do a asyncronoUs scenary with two adpater: JDBC to RFC. I have to get data from two tables in SQL (jdbc) and later, call a RFC witht two structures like parameters.

    but i have a problem. When the communication channel is sender, y can add only one statement sql AND I NEED TO ADD ONE STATEMENT FOR EACH TABLE

    Do you konw how can i do it?

About Riyaz

riyaz.net is a popular technology site with how-to guides, tips and tutorials on personal technology, blogging, social media, web apps, personal finance and SAP.

riyaz.net was launched way back in 2005 by web designer, blogger and SAP Consultant Riyaz Sayyad from Pune, India. Over the years the site has grown into a full featured online community with thousands of visitors daily from around the world. Read more.