JDBC Receiver Insert : Integrity Constraint Violation

[suman_saha]

Hello,

Scenario:

One IDOC will create two different messages to update 2 different tables. One table is having constraint to other table i.e. while updating or inserting data in one table, it looks for key values whether present in the other table.

Approach:

Used 2 different operation mappings.

Issue:

In some cases we are facing message failure due to integrity constraint violated - parent key not found.

Solution provided:

To put some delay in message mapping for updating the table having constraint.

My question is: Is there any other way to avoid this issue? As this solution can't eliminate the issue, it can just reduce the probability of failure.

Regards,

Suman

[sandip_hole · Accepted solution]

Hi Suman,

You may want to use the Maintain Order at Runtime feature of the ICO. If you have entered multiple inbound interfaces(receiver Interfaces in ICO) then they all will pass in a the same sequence entered.

check Defining the Integrated Configuration - Integration Directory - SAP Library

If multiple inbound interfaces are assigned by the integrated configuration, the messages are forwarded according to the quality of service Exactly Once in Order (EOIO) at runtime. This means that the order in which the inbound interfaces are entered in the integrated configuration determines the order in which the messages are received.

You can change this default by deselecting the Maintain Order at Runtime checkbox. In this case, the quality of service Exactly Once (EO) is used to forward the message to the inbound interfaces.

Recommendation

We recommend this setting for adapters that do not support quality of service EOIO processing. This setting is also recommended if you want to avoid a single message that develops errors during delivery from stopping all subsequent messages from being delivered.

Regards,

Sandip

[Former Member]

Hi,

If i understand correctly the issue. Using the IDoc you are trying to update the first table (let's call it as parent table). Then you also update the second table (let's call it as child table).

You are checking for a primary key in parent table before updating the values child table.

Now you are facing an issue while updating the child table because the values are yet to be populated/in processing between IDoc --> parent table. So you want to give a delay so that after the values are populated in parent table, then the values can be checked in the child table.  Right ??

If my understanding is correct, the an easier solution would be to create the same primary key in child table, so that you don't need to have an dependency on the parent table.

Please let me know if it helps.

Thanks,

Sudhansu

[suman_saha]

Thanks Sudhansu.

Actually we don't want to change anything in database level. Is there anything in PI we can configure?

[Former Member]

Dear Suman,

You can use a Data base look up for first table or Update_Insert command in place of Insert.

1. By using Look up at mapping time you can check if Value is present in DB or Not .

2 By Using_Insert Command , If value is already present it will update else it will insert the value in DB.

In your case I will suggest for look up.

Regards,

[suman_saha]

Thanks Rudra.

We are using update_insert and there is no possibility of the value to be present already.

We only need to be sure whether the key value is present in another table related with constraint.

Is there any chance to retry if on 1st attempt the updation fails?(Although QoS is exactly once)

I want to avoid using JDBC lookup as it will also throw an error if the other table is not updated already (scheduled to be updated).

[former_member184720]

<<removed my reply as similar answer provided by sandip>>

[Former Member]

(Not sure ) there is a option in channel if query failed once it will retry.. let me find out ... try to update u soon

[Former Member]

Dear Suman,

Please check the point no 4 on given link.

4. Defining Exactly-Once Handling :- redo

Configuring the Receiver JDBC Adapter - Advanced Adapter Engine - SAP Library 

hope this will help.

Regards,

[suman_saha]

Hi Rudra,

That Redo is to managing conflict. If the data is already present in the same table,it will rewrite(in case of redo) or throw error(while selected error).

It will not solve my issue

Regards,

Suman

[Former Member]

Hi Suman,

As your single IDoc is creating both the records for the 2 tables, you can try to merge the database statements into a single message type.

On the target side you can have the structure as:

Statements

Statement1

-Database Table for Parent Table

.

.

.

Statement2

-Database table for Child table

Thus, when the JDBC Receiver is executing the statements sequentially all the Parent records will be inserted / updated prior to the child records.

Regards,

Alka.