cancel
Showing results for 
Search instead for 
Did you mean: 

JDBC receiver, continue processing after an error

0 Kudos

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.

Accepted Solutions (1)

Accepted Solutions (1)

RaghuVamseedhar
Active Contributor
0 Kudos

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

0 Kudos

Hi Raghu, Thank you for reply.

Unique key constraint for the database table is business requirement. We dont want to insert duplicate records in table. For this purpose we need to ignore duplicate records.

Anand.

Answers (2)

Answers (2)

roger_alluivall
Participant
0 Kudos

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!

Former Member
0 Kudos

>

> 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.

0 Kudos

Hi spantaleoni, Thank you for reply.

Stored procedure is definitely flexible option. However since this fix is supposed to be for production environment, and i necessarily want to have something which can be resolved with simple configurations (without need of transports).

Anand.

baskar_gopalakrishnan2
Active Contributor
0 Kudos

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