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
Case 01 : Dupplicate at Item, no data insert database
Case 02 : Happy case
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
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.