The objective is to transfer table SINGER from SOURCE_DB to XML row, XML SQL, then to TARGET_DB, using SAP PI JDBC sender and receiver adapter. The JDBC driver is SQLite that deployed in previous post.
STEP 1: From “SOURCE_DB” to “XML ROW”
Reference : Configuring the Sender JDBC Adapter
The SOURCE_DB’s SINGER table have additional SYNCED column, to use as flag to know which record already synced to TARGET_DB’s SINGER table. Configure the Sender Agreement as well.
STEP 2:FROM” XML ROW” to “XML DB STATEMENT”.
Reference: Defining XML Documents for Message Protocol XML SQL Format
action = “UPDATE_INSERT”, mean if found existing record, then do an update, else do an insert.
table = “SINGER”, access = “SINGERID” and “FULLNAME”, key = “SINGERID”
STEP 3: FROM “XML DB STATEMENT” to “TARGET_DB”
Reference : Configuring the Receiver JDBC Adapter
Configure the Receiver JDBC Adapter and Receiver Agreement as well.
Keep on learning!
JDBC Adapter – SourceDB Send Table to XML format and Receive to TargetDB
Nice presentation Yee-Loon 🙂
Just would like to know, in JDBC sender adapter which SQL language we need to use for “Query SQL Statement” and “Update SQL Statement” fields?
Does it according to the database we are using or any generic syntax to follow?
Thanks.
Hi,
Thanks! Yes, you are right, the SQL depend on the database we are using, different DB driver. If using standard generic SQL (mean basic CRUD and not DB specific) then should working fine too.
Hi I am beginner to sap pi/po
I am trying to excute jdbc to file i have given update SQL statement filed in jdbc sender as so it’s continuously triggering the same data for every 60 sec but I want it stop reterving the data where null value so what SQL statement need to be updated.
Please let me know.