Revert inserted lines if errors

[Alinea]

Hi,

We have a simple job that takes all data in a table A (mysql), where a flag value is 0 and put it in another database table B (oracle).

When it's done, we update all lines in A to put the flag value to 1. So next time the job is run, lines are not inserted a second time.

Problem is that, sometimes, another job is inserting data in A when we try to update, so there's a lock. 

1. The job is always OK. subjob is not in error (tWarn_1 and 2 are activated). tMysqlInput_1_NB_LINE gives the number of line that sould have been updated, but since there was a lock, in fact no line is updated. Is it normal? How can we handle this to have an error? 

2. How can we revert datas inserted in B if there's an error in our update of A?

Thanks for your help.

[Anonymous]

Use a tOracleConnection component and use that shared connection for your DB B component. Then, at the end of the job (once you are happy that everything is OK) use a tOracleCommit component to commit the transactions. You will need to use a RunIf link or similar to do this.

[Alinea]

Thanks for your reply.

But the problem now is that when there's a lock, there's no error. I see you propose to do it with a "run if", but what condition will be fulfilled when i have an SQL error and also lock errors?

Thanks again for your help.

[Anonymous]

Can you show the job that you have now? It is very difficult to give an answer without seeing your current layout

[Alinea]

EDIT : I don't know if it's more helpful. Tell me if you need more informations.

For now, if there's a lock, lines are not inserted (tMysqlOuput_1) but OnSubjobOk is triggered. That's my problem.

Thanks again for your help