How to Prevent the Duplicate record insertion by JDBC Receiver?

[jagesh_lakdawala]

HI Experts,

I have File Sender to JDBC Receiver scenario.I am using SAP PI 7.1 system.

Purpose of this Scenario is to read the data from specified file and INSERT or UPDATE the same records in Oracle Database.

Before inserting the data I want to check in the same table for prventing runtime error(Key fields Duplicatin).

So if records with the same key fields are available in table then I want to UPDATE the same record instead of INSERT.

Please Suggest me the poosbile way.

Thanks & Regards

Jagesh

[Former Member · Accepted solution]

Hi,

You can use action INSERT_UPDATE to achieve it.

[jagesh_lakdawala · Accepted solution]

HI,

Thanks for your reply.

Is it possible using the JDBC Look up concept in PI 7.1?

Regards

Jagesh

[Former Member · Accepted solution]

best option is to use action as UPDATE_INSERT ,which will work as per your need means if record already exist update it.

[Former Member]

Hi Jagdesh,

Yes. PI 7.1 has Lookup concepts...

Please check this link for more information.

/people/jin.shin/blog/2008/02/15/sap-pi-71-mapping-enhancements-series-graphical-support-for-jdbc-and-rfc-lookups

Regards,

Kshitij

[Former Member]

Hi Jagdesh,

The JDBC receiver format has the following structure

<root>

-

---

<StatementName1>

-

---

<dbTableName action=u201DUPDATEu201D | u201CUPDATE_INSERTu201D>

-

---

<table>Table1</table>

-

---

<access>

-

---

<col1>val1</col1>

-

---

<col2>val2new</col2>

-

---

</access>

-

---

<key>

-

---

<col2>val2old</col2>

-

---

<col4>val4</col4>

-

---

</key>

This structure creates a query in this way :

UPDATE( OR UPDATE_INSERT) into Table1 set col1=val1 and col2 = val2new WHERE col2=val2old and col4=val4.

The key field works like a Where clause. So for the receiver format when you specify the key, it checks the DB with that field as the where criteria. Hence for a UPDATE_INSERT query, when you specify the key field, it checks whether that value specified within the key field exists in the DB or not. Then it proceeds to execute the statement for only those values.

Hence it makes sense to provide a primary key attribute in the key field of the JDBC receiver format.

Regards,

Kshitij

[Former Member]

Hi,

If you need to check for the key element you have that in the JDBC receiver structure as "KEY". Under this you can specify your key element.

Refer this type:

http://help.sap.com/saphelp_nw04/helpdata/en/64/ce4e886334ec4ea7c2712e11cc567c/content.htm

Regards,

Nithiyanandam