PI JDBC Response issue

[Former Member]

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

[udo_martens · Accepted solution]

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

[Former Member · Accepted solution]

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

[former_member206760]

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

[maciej_jarecki]

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.

[Former Member]

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