receiver jdbc adapter stored procedure

[Former Member]

Hi,

In the JDBC to IDOC scenario we are using stored procedure to SELECT and UPDATE from SQL database. There is a flag in SQL table which we update to 'X' when all data is pulled into PI . This update is done when PI executes the stored procedure.

If the network connection or system goes down goes down when PI is executing the stored procedure will the data be picked up by PI without updating the flag or will it pick just half the data and leave the rest behind ?

I just want to make sure that either all the data is picked by PI or nothing. Is this controlled in the stored procedure or PI ?

Thanks,

Teresa

[Former Member · Accepted solution]

To the best of my knowledge, the updating of the flag is managed by PI and not the stored procedure, but yes, if the network goes down then none of the data will be transferred , it will not be a half-here, half-there scenario.

[former_member181985 · Accepted solution]

When we use a stored procedure we should completely rely on the stored procedure logic. The update statement from Sender CC channel might have side effects.

[baskar_gopalakrishnan2]

IMO, if the SP handles both Select and Update logic  and if the network connectivity issues goes during that time, entire transaction results are maintained as per SP execution.  The result could be commit or rolled back.  Since we dont write query or update logic in the sender comm channel, PI will have no control on this.  This execution is completely relied on SP.

[former_member181985]

It will be controlled by stored procedure, meaning it will only return result list after it has updated flag.

[Former Member]

Therefore data wont be pulled into PI if while during SELECT and UPDATE in the stored procedure  the network goes down correct?

[former_member181985]

Obviously, the sender JDBC adapter channel will be in error.