on 01-21-2011 3:15 PM
I have SOAP to JDBC async scenario.
One source message has multiple line items, each line item converted to separate record and inserted in database table (Using graphical mapping and INSERT command). Receiver database table has composite unique primary key constraint. Due to this, in case of any duplicate record, whole message gets error out in receiver communication channel.
Is there any way, in case of any error due to 'Primary key violation', just ignore that record and continue with next record to insert in database.
Hi Anand More,
For this issue, I do not think you can do something in PI to avoid it. But you can do some changes in Database to solve the issue.
sapnote_0000831162 [Link1|https://websmp206.sap-ag.de/notes] Q: When I construct payload using UPDATE_INSERT, I get Cannot insert duplicate key in object XXX. How do I resolve this?
A: You need to create UNIQUE INDEX for the table with 'IGNORE_DUP_KEY' option.
For instance, if the table name is TEST and its primary key is id, then following statement may be executed using Query analyzer tool.
CREATE UNIQUE INDEX TEST_INDEX ON TEST(id) WITH IGNORE_DUP_KEY
After executing the above command, please re-run the scenario.
Regards,
Raghu_Vamsee
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello,
probably this issue was solved long time ago, but I guess the best solution is to use statement UPDATE_INSERT instead of INSERT.
Using this statement, all the existing records get updated and the new ones inserted.
Regards!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
>
> I have SOAP to JDBC async scenario.
> One source message has multiple line items, each line item converted to separate record and inserted in database table (Using graphical mapping and INSERT command). Receiver database table has composite unique primary key constraint. Due to this, in case of any duplicate record, whole message gets error out in receiver communication channel.
>
> Is there any way, in case of any error due to 'Primary key violation', just ignore that record and continue with next record to insert in database.
You can do this by calling a stored procedure, and do a "select exist check" into the SP.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
HI Anand,
This is a typical situation. DB side does not allow duplicate records. That is true and genuine.
Suggestion: If your input record size is not large volume, you can do jdbc lookup and based on the lookup result, you can
go for inserting the record.
pi 7.1 supports jdbc lookup. Check whether record exist for primary key value which comes from the source using jdbc lookup against target database , if exist dont insert otherwise insert.
This can also be achieved using simple java mapping too.
Only problem here is two times we connect to the database. one for lookup to see whether record exist and second is for the inserting.
But I dont see any problem with this. You are assured not to have duplicate records in the target system and processing errors due to duplicate records.
jdbc lookup help link
/people/jin.shin/blog/2008/02/15/sap-pi-71-mapping-enhancements-series-graphical-support-for-jdbc-and-rfc-lookups
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.