Skip to Content
avatar image
Former Member

JDBC-JDBC Synchronous scenario

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Jun 02, 2009 at 09:15 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 02, 2009 at 10:21 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member 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:

      jdbc-lookup-and-populate-fields-for-output

      jdbc-lookup

      jdbc-lookup-udf-to-fetch-multiple-values

      Regards

      Venkata Rao .G

  • avatar image
    Former Member
    Jun 10, 2009 at 10:26 AM

    answered

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member 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