Skip to Content
avatar image
Former Member

PI JDBC Response issue

Hi Experts,

I'm inserting customer data to a SQL server data base using RFC to JDBC method.

(ex: Customer_ID,Customer_Name, Customer_Address

Primary key is Customer_ID)

I know how to get the insert_count using response data type.

So can you please explain me how to get the Inserted "Customer_ID" list with the Response.

Chaminda

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    Jul 21, 2010 at 11:50 AM

    Hi Chaminda,

    this is only possible with a Business Process:

    RFC request-> BP

    BP -> JDBC insert

    JDBC response -> BP

    BP: Check insert_count

    BP: merge RFC requet list into response

    BP -> RFC response

    Regards,

    Udo

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Experts,

      Thank you very much for all the responds... i'm not very much expert on BPM. anyway i'm trying to do it using BPM.. since i do not have full access to the Data Base end.

      Thanks

      Chaminda

  • Jul 21, 2010 at 01:25 PM

    Hi,

    Write stored procedure on SQL server(insert data and return customerID) and execute it from PI with sync interface.

    Would you like to use BPM ??

    Regards

    M.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 21, 2010 at 01:43 PM

    Hi Chaminda,

    When u are inserting the records in the database table, either all the records will be inserted or none will be inserted...

    hence once you get insert_count as something other than 0 , you can be sure that all teh records were inserted and obviously u know the customer id's of the the records u are sending

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 21, 2010 at 01:06 PM

    Hi Chaminda,

    alternative option (approach with BPM described above is valid) is to create a trigger in DBMS and write Customer_IDs into second table, afterwards poll / delete it asynchronously with PI. Benefit: you will avoid ccBPM and synch communication.

    The SQL code could look like:

    create or replace trigger <YOUR SCHEMA>.MY_TRIGGER

    after insert or update on <YOUR SCHEMA>.<YOUR TABLE>

    for each row

    begin

    insert into <YOUR SCHEMA>.<YOUR 2ND TABLE > (Customer_ID)

    values (:new.Customer_ID);

    end;

    BR

    Rudolf

    Add comment
    10|10000 characters needed characters exceeded