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.
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:
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.
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.
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.
Nice Blog, I would expect more information on the receiver JDBC channel parameters like “Key Tags” and database transaction levels.
Regards
Anandh.B
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
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
Can you please rephrase your question. I could not understand you.
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.
Hi Riyaz
Thank you for your help
best regards.
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..
You can try using action=”EXECUTE” statement to call a stored procedure/function. See this link for document formats.
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
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.
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
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.
Do you have an example of configuring a stored procedure for sender adapter?
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
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
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
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?
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?
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?
How to rectify error when u r using Synchronous scenario?
Can u explain the advantages n disadvantages of CTS+ transport system?
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
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
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
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?