Rollback of JDBC Statement on error - Stored Procedure

[Former Member]

Hi All,

We are using the JBDC receiver adapter for inserting the record and for that we are using the Strored Procedure and we have N number of the records for insertion in a single mapping so, we have given the occurence of the Statement 1 : unbounded as, multiple statement will be executed so, my query is if one of statement fails because of some error(Mapping error or data error) what will happen to the statement which are already executed, do they rollback? or it is not possible in case of stored procedure?

Regards,

Gaurav

[Former Member · Accepted solution]

Yes ,

you can manage Rollback / Commit in stored procedure in exception handling block,Just catch the error and write conditions for rollback..

[Former Member · Accepted solution]

Hi,

This is one of the solution but i guess there is one more issue, if we have 10 statements and third statement shows error and Stored Exception exception branch is able to catch then it will rollback the previous statement but can we able to restrict another 7 pending statement for execution or not?

Regards,

Gaurav

[Former Member · Accepted solution]

you can write EXIT statement in stored procedure, It will exit execution from that point.

Advisable is create one intermediate table ,dump all the interface data there, and then run stored procedure based on that data and do all the operations...

[Former Member · Accepted solution]

You can control the same in SP with rollback in Exception block of SP ,if one fails after inserting some records then it will rollback all the records provided the insertion started with in start transcation

Rajesh

[prateek]

what will happen to the statement which are already executed, do they rollback?

No, in my opinion, they will not rollback.

You may handle transaction handling in stored procedure but I guess it won't be possible in this case of yours. Rollback or commit could only be handled for each SP call but you have multiple such calls.

Regards,

Prateek