If I use the DB transaction nodes inside the loop (as shown above), I’m able to run the workflow but in this above scenario for each iteration a new transaction is started.
But the requirement is that I need all the iteration in a single DB transaction as below,
For example - if the first iteration is successful and the second iteration is a failure it is expected to roll back the first iteration (successful run) data which is pushed into the DB.
Please advise on this as I’m getting loop errors If I try to have all the iterations inside a single DB transaction
I feel your pain! I’m guessing that it doesn’t like the transaction connection exiting the loop? At least that’s what I got when I mocked something up. It’s a pain because in theory what you have in your lower screenshot should be pretty much right for the job.
Having had a good think about this, I cannot see a way of wrapping a loop with the DB Transaction nodes. That doesn’t mean it cannot be done, but simply that every time I think of something I hit a wall.
So… what’s an alternatives to achieve what you need? OK, not totally the prettiest solution, but I think it’s achievable if we build our own transaction handling. I grabbed the KNIME-provided transaction demonstration workflow from the hub as a starting point, and then changed it about a bit to build it as a loop with manual transaction handling.
This is what I came up with. It needed to use a recursive loop teamed with a row splitter to provide an equivalent to a chunk loop that could not only terminate in the event of an error, but also feed back that an error had occurred, and then DB SQL Executors at either end provide the actual BEGIN TRANSACTION and COMMIT or ROLLBACK.
Take a look and see if you can fit something similar around your workflow. At the start of the demo workflow, I’ve added an IF switch. Manually configure this to either perform duplicates removal or not, so you can then see the workflow successfully writes 100 rows if duplicates are removed, or else writes no rows if a duplicate key exception occurs.
I know you are knew to the forum, but I don’t know how new you are to KNIME. Recursive loops can take a bit of getting used to initially. If you have questions on how this demo works, feel free to ask away, and I’ll do my best to assist.
I’m guessing that it doesn’t like the transaction connection exiting the loop? At least that’s what I got when I mocked something up. It’s a pain because in theory what you have in your lower screenshot should be pretty much right for the job.