Continue Pentaho transform after rollback

Question

Using Kettle-Spoon 5.1, I have a transform that:

1. pulls a row from a database (oracle)
2. sends the row to a REST api (via REST Client)
3. uses a Switch/Case step that looks at the result. If the result is good, I remove the record from the database (Execute SQL), otherwise, I roll back the transaction

Note, the rollback is depending on the result not necessarily an error/exception. If a particular transaction gets rolled back, I still want to keep processing the other rows (don't want to the transform to stop).

Two questions:

1. Is there a clean way to force a rollback? (currently I've got a javascript step with a "throw"
2. How do I force the transaction to continue after the rollback?

Answer 1

Within a transformation, Pentaho processes rows within a stream concurrently, all in the same transaction. To roll back a single row, you would need a separate transaction for each row, which Pentaho doesn't support.

I recommend using another approach to achieve the same effect. Two possibilities:

1. Don't make the database changes until after the REST result is received. That way, there is nothing to roll back. You can, of course, stage the changes in memory (in stream fields), or in temporary database tables, so you know exactly what they will be.
2. Somehow keep track of the database changes you have made, so that you can undo them if the REST result indicates that you should do so. Depending on how you are doing things, you can keep track of those changes in stream fields, or in temporary database tables.