TRUNCATE TABLE and Transactions

CloverDX Platform
1

I’m importing a file into a MySQL database and I’m performing a TRUNCATE TABLE command via DBExecute component first. My concern is that I’d like to roll that truncate table command back if the rest of the graph fails for some reason so I’m not left with an empty table. Is there any way to accomplish this with transactions?

Thanks,
Andrew

2

Hi Andrew,

In order to wrap statements in DBExecute with DBOutputTable DML statements into one transaction, you will need to do the following:

  • Set threadSafeConnection attribute to false - in Database connection dialog go to Advanced tab and uncheck Thread-safe connection check-box. See our documentation for more information on this.

  • Make sure DBExecute is in a phase with lower number than the one which is used for DBOutputTable.

  • Set DBExecute’s attribute Transaction set to Never commit.

  • In DBOutputTable dialog set Action on error attribute to Rollback

I would also recommend you using DELETE instead of TRUNCATE since TRUNCATE causes implicit mapping, therefore you won’t be able to rollback the statement in case of DBOutputTable failure. For more information on implicit mapping refer to MySQL documentation (5.7).

Hope it helps.