JDBC scenario’s in SAP PI are most common and in this blog, we will talk about how to configure Sender and Receiver JDBC Adapter in SAP PI.
The JDBC adapter enables you to connect database systems to the Integration Server. The adapter converts database content to XML messages and vice-versa.
You can read database content with any SQL statement and even stored procedures.
Pre-requisite:
- Install the JDBC driver for the database to which you want to connect. The required Java libraries are product-specific and must be obtained from the database vendor.
Sender JDBC Adapter Configuration
When configuring the Sender JDBC adapter in SAP PI keep in mind this can be used in only one sender agreement similar to the File adapter.
You can have SQL query or Stored Procedure in sender JDBC adapter in SAP PI.
Using SQL queries:
We have to write a SQL query in JDBC Adapter to retrieve the rows from the required table.
It will be something as below
SELECT * FROM <tablename> WHERE processed = 0;
But we should add an indicator in a table that specifies the processing status of each data record in the adapter (processed/not processed) to the database table.
For that, we need to use UPDATE statement example
UPDATE <tablename> SET processed = 1 WHERE processed = 0;
Using Stored Procedure.
This is more similar because logic to retrieving rows will be written in DB side and our adapter will only call this to get the required rows.
Receiver JDBC Adapter Configuration
This will be used when we want to convert XML to table format. The receiver, side configuration is a bit different because we have to use XML SQL format as shown below
Key fields in this format
Action: Can have UPDATE, INSERT, UPDATE_INSERT, DELETE, SELECT, EXECUTE.
Table: will be the name of the table
The rest of the configuration is straightforward as shown below.
For more information refer SAP documentation
https://help.sap.com/saphelp_nw73/helpdata/en/22/b4d13b633f7748b4d34f3191529946/frameset.htm
You may also like our Adapter series other blogs
https://www.techtalkzone.com/sap-pi/idoc-adapter-in-sap-pi-2/