Question Re: How to eliminate inserting Duplicate rows into database using JDBC Adapter in Technology Q&A

How to eliminate inserting Duplicate rows into database using JDBC Adapter

venky_b — Thu, 04 Oct 2007 11:10:07 GMT

File->Xi->JDBC

In above Scenario if the file has two rows their values are identical, then how can we eliminated inserting Duplicate rows into database using JDBC Adapter

Re: How to eliminate inserting Duplicate rows into database using JDBC Adapter

Former Member — Thu, 04 Oct 2007 11:27:07 GMT

Usage of a custom adapter module at File adapter would greatly help here.

Inside the adapter module to every row add a field <status> and set its value to true or false based on first occurance or repeated value. Inside the mapping map only the records that have the value true for status field.

Regards,

Jai Shankar

Re: How to eliminate inserting Duplicate rows into database using JDBC Adapter

Former Member — Thu, 04 Oct 2007 11:33:12 GMT

You can handle this situation in your mapping.

Best would be to use a java mapping, having the coding done to eliminate the duplicate records

Re: How to eliminate inserting Duplicate rows into database using JDBC Adapter

Former Member — Thu, 04 Oct 2007 11:36:06 GMT

Check this

Re: How to eliminate inserting Duplicate rows into database using JDBC Adapter

Former Member — Thu, 04 Oct 2007 11:45:18 GMT

Hi Venkatesh,

usually the DB handels that for itself, you cant insert 2 same rows as the keys would be same as well. You would get an error.

To avoid getting errors use action UPDATE_INSERT, double entries are now ignored.

<a href="http://help.sap.com/saphelp_nw2004s/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm">Document Formats for the Receiver JDBC Adapter</a>

Regards,

Udo

Re: How to eliminate inserting Duplicate rows into database using JDBC Adapter

Former Member — Thu, 04 Oct 2007 14:09:24 GMT

Duplicate Insertions can be handled at the DB level only when yo have a primary key set in the table.

Only then it will automatically not allow such insertions

Otherwise you need to do the checks at the Mapping level.

Thanks

Re: How to eliminate inserting Duplicate rows into database using JDBC Adapter

Former Member — Thu, 04 Oct 2007 15:46:15 GMT

Database is a consumer of a SERVICE (SOA!!!!!!).

Database plays a business system role here!!!!

Mapping is part of an ESB service

Adaptor is a technology adapted to ESB framework to support specific protocol.

ESB accomplish ESB duties such as transformation, translation, routing. Routing use a protocol accepted by the consumer. In a JDBC consumer it is JDBC protocol and hence it a JDBC adaptor.

There is clear separation on responsibilities among business system and ESB. ESB do not participate in business decision or try to get into business system data layer.

So who ever are asking people to check duplicate check as part of mapping (an ESB service) may not understand integration practice.

Please use an adaptor module which will execute the duplicate check with business system in a plug and play approach and separate that from ESB service so that people can build integration using AGILE approach.

Thanks

Re: How to eliminate inserting Duplicate rows into database using JDBC Adapter

venky_b — Tue, 06 Nov 2007 10:40:45 GMT

Its been Solved

Re: How to eliminate inserting Duplicate rows into database using JDBC Adapter

prabhu_s2 — Tue, 06 Nov 2007 10:45:44 GMT

hi venkat

could u share with community on the solution.

Re: How to eliminate inserting Duplicate rows into database using JDBC Adapter

Former Member — Thu, 13 Dec 2007 20:01:23 GMT

Hi Venkatesh,

Could u pls share the solution for this problem, bcoz i too faced the same problem.

Eagerly awaiting ur reply.