[SAP CPI] – WORKING WITH JDBC ADAPTER RECEIVER ON SAP CPI AND SQL SERVER – INSERT STATEMENT

Hi guys, to be continue for series WORKING WITH JDBC ADAPTER RECEIVER ON SAP CPI AND SQL SERVER, today I want to setup one scenario only about Insert statement, working with many rows many table in SQL.

First, we will take a look about Insert Statement syntax in JDBC

For example, we design table in SQL server

And we want insert many rows into this table, example 3 rows, we have to create XML for Insert statement as below

With another system, JDBC can run one time and they can insert all rows into table of database. But with CPI-JDBC supports only one record at a time, check this link . You can use General Splitter after mapping and insert all the records into DB.

In CPI, we have to use component General Splitter with XPATH : //StatementName/dbTableName/access

Run simulation we will see that source message split to 3 messages

And after that, JDBC adapter will insert every message into database.

But, we also have another way, no need use Splitter Component. It is create structure XML with many segment Statement. In above example we will create XML syntax as

With this syntax, data also insert into database as well.

Second, we will consider some scenario

Scenario 01 : Insert data(s) into single table

This scenario, we considered above.

Scenario 02 : Insert data(s) into multiple table. Example Master – Detail table.

In this scenario, we need focus to data which insert into database have to the same master and data. If master cannot insert or detail cannot insert into database with any issue, all transaction will be ROLLBACK. OK, let’s config it

Example we have two table in database which related together TBL_002 and TBL_002A. Key relationship of two table is SAP_PO.

We need insert data from this XML structure into database through JDBC

Step 01 : Create XSD for source message

Step 02 : Create XSD for destination message (Insert aJDBC)

Step 03 : Create integration flow and mapping source message with destination message

Mapping for Header

After mapping is done, we test by run simulation

Add Filter component to get all Element Statement

After this step we will have payload as

Next we need wrap this payload into Root element by add Content Modifier

Because of we want consistency data so we need config BATCH mode in JDBC receiver adapter

Batch operation we have two mode

• Atomic : Considers each batch operation as a single unit. It updates the whole batch operation successfully or reverts the entire operation to its initial state if anything in the batch operation fails.
• Non-Atomic : This is based on the behavior of the database. It updates all the successfully executed records and throws an exception if anything fails. It does not revert the failed records to its initial state.

Kindly reference this link for more detail about JDBC adapter

Test case

Case 01 : Dupplicate at Item, no data insert database

Request

Response

Case 02 : Happy case

Request

Response

Check data in SQL

Case 03 : Error when config JDBC Data Source Wrong

This issue happen because we config JDBC invalid with Cloud Connector. We need check and config it valid. Please focus to option SQL (Cloud) or SQL (On-premise). IF choose on-premise we should be input location ID if we have many Cloud connector for one sub account CPI

Summary

In this article, we considered some scenario about Insert statement syntax in JDBC. We also config scenario insert data into multiple table in SQL server. This scenario is used for some case you want working with backend database by JDBC adapter receiver. Reference here for more JDBC statement

Thanks for your reading and any advise, kindly leave your comment below. Thanks.

JOSEPH.