JDBC-JDBC Synchronous scenario

[Former Member]

Dear All,

We have a scenario below and required your valuable inputs:

From Sender DB system, we have to read 1000 records and update the flag from 0 to 1. PI does the required transformation and using SQL statements, it has to insert in the Target DB system.

Ex: If 100 records failed in Target DB due to some erros in execution of SQL stmt, we need to send these 100 records back to sender and update the flag 1 to 2.

FYI.. There are no Stored Procedures at Target DB system.

We created both Sender/Receiver interfaces as Synchronous interfaces and in the response structure we defined 2 fields to read the primary key of the records, but the response mapping is getting failed.

because JDBC Receiver adapter is allowing to have only insert_count field as response structure.

Is there any way that I can read the failed records.

Please suggest.

Regards,

Srinivas

[Former Member]

answered

[Former Member]

Hi Srinivas,

Can u please let me know how you achieved this. Have the same requirement as you.

[Former Member]

Hi Prem,

We achieved this by using Java Proxies.

The flow goes like this:

Pick the data from Sender system in batch say 1000 records and create a Java Proxy. Get it deployed on XI server.

This Java Proxy makes connection with Database and insert all the records into the target database. The logic can be written to connect to database and to collect all error records in an array. Now while sending the response back to sender system, use the structure of response and update the status at the sender side using JDBC receiver channel.

Please let me know if you need any more information in detail and points if solution is helpful.

Regards,

Srini

[Former Member]

Hi Srinivas,

You need get the access to write a stored procedure in the Target System.

In the Stored Procedure u needs to update based on response from the Source System with the primary key fields it would do.

Regards

Venkata Rao .G

[Former Member]

Hi Venkat,

Thanks for your reply.

But we are not supposed to create any Stored procedures to use at target system else we have the solution in hand..

Anyways, thanks for your efforts.

Regards,

Srini

[Former Member]

Hi Srinivas,

Kindly do like this: Create a JDBC Lookup based on the response with BPM with the Primary Key Fields, Also refer Martens provided blog will helps u a lot.

For creating JDBC Lookups Please refere the following Blogs:

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

/people/sap.user72/blog/2005/12/06/optimizing-lookups-in-xi

/people/siva.maranani/blog/2005/08/23/lookup146s-in-xi-made-simpler

Also refer the following threads:

Regards

Venkata Rao .G

[udo_martens]

Hi Srivinas,

first i think you would need to access the source DB asynchronous like described in [ DB to DB Scenario using BPM step by step guide |https://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/14302] [original link is broken] [original link is broken] [original link is broken];:

1. Source DB -> BP (aynchronous select)

2. BP <-> Target DB (synchronous insert)

3. BP -> Source DB (asynchronous update)

You will get back the number of inserted rows or an error msg, this can be catched in the process (configuring the send step). Inside one statement all inserts will fail or none.

If you want to make them independend from each other then create for each row to be inserted a new statement.

Regards,

Udo