topic Re: Revert inserted lines if errors in Talend Studio

Revert inserted lines if errors

Alinea — Wed, 19 Sep 2018 14:15:04 GMT

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.

Re: Revert inserted lines if errors

Anonymous — Wed, 19 Sep 2018 14:28:17 GMT

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.

Re: Revert inserted lines if errors

Alinea — Fri, 21 Sep 2018 09:36:40 GMT

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.

Re: Revert inserted lines if errors

Anonymous — Fri, 21 Sep 2018 10:51:58 GMT

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

Re: Revert inserted lines if errors

Alinea — Thu, 27 Sep 2018 10:02:07 GMT

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