How to use Roll BAck and Commit transactions of SQL using uipath

Help
1

Scenario: I have data 50 rows in excel. Need to insert all the row in SQL table. The Bot should insert either all 50 rows or none. If there is an exception with any single row of data then bot should not insert any data.

Please suggest how to use Rollback and commit transactions of SQL.

2

Hello…
Suggestion:
Better you can use the data validator before insertion only, because it takes more effort to rollback all the transactions processed.

3

DAta Validator: Do you mean validate data before trying for insert??? If that is what your suggestion… we are already doing this. We would like to know if we have direct approach of using Rollback and Commit

4

Okay my bad, will update if I find something.

If you get resolved keep posted :blush:

5

Use start transaction activity, it will take care of commit and rollback.
check details here [https://docs.uipath.com/activities/docs/database-transaction](http://Start Transaction)

6

Hi,

for some reason, no documentation of UIPath mention, how to use this very basic functionality (database transactions).

personal remark

from my experimence short experience with the platform, this seems to be a common problem with UIPath

By trial and error I eventually figgured out, how are you supposed commit or rollback inside Start Transaction Activity container (UiPath.Database.Activities.DatabaseTransaction):

It only rollbacks if exception is thrown from withing the activity container.
But from SQL transaction you probably want to rollback when something in SQL code went wrong, not in studio. So you have to add your own “rollback” exception and either:

  • and check continue on error in the DatabaseTransaction activity (you cannot catch any studio-thrown exception this way!)
  • or wrap your SQL code not only into DatabaseTransaction container, but also into try-catch container where you ignore selfthrown “rollback exceptions” but deal with real exceptions with for example Rethrow.

I am aware that author probably already found his solution/workaround. I haven’t found any useful info on this subject on forum nor anywhere else. Hopefully this might help someone in the future.

Cheers.