Level of Difficulty: Beginner – Senior.
When working with databases, the use of transactions is a very healthy habit to get used to. It will save you from those update or delete statements without a where clause that cannot be undone (especially in prod… Imagine…). UiPath presents the Start Transaction activity which claims to do the same as a traditional transaction. In this post, we will compare the functionality of a transaction in SQL and the use of transactions in UiPath.
Basic Principle of Transactions in SQL
A transaction is traditionally used as a SQL concept which allows the developer to test the outcome of a query without applying any changes to the dataset, or at the very least, undoing any changes made to the dataset. A transaction is started by using the BEGIN TRAN syntax and ended with either ROLLBACK TRAN (undoing any changes made to the data) or COMMIT TRAN (applying the changes made to the data).
This is often used in SQL to handle errors and exceptions. The idea behind this is that if a query or stored procedure contains any errors during execution, all changes will be rolled back. In a case where there were no errors encountered, the changes to the dataset(s) could be applied ‘safely’.
The above query consists of a transaction that is rolled back, meaning that the changes to the data are ‘undone’ or ‘not applied’. The first set of results in the output window show the previous state of the data (before any changes were made), the second set of results show the results after the update statement has been applied. The rollback is executed after the second select statement which means that the third data set shows the current state of the data (with the results of the update statement rolled back). If you were to replace ‘ROLLBACK‘ with ‘COMMIT‘, the second and third sets of data would look exactly the same.
So what happens when there’s a statement that has a very clear error in it? Like, for example, updating a field with the value of 0 divided by 0 (which is undefined). Well, the query will error out and the field won’t be applied:
What happens when we add a query that is supposed to succeed, like inserting records:
Okay… So what if we have multiple statements and one fails? How do we undo all changes? Well… That’s where the exception handling comes in:
What would the results look like if we remove the query with the error? Well… The query would succeed and the changes that result from the queries executed within the transaction would be committed:
So how do we use this in UiPath?
UiPath Implementation of Transactions (Start Transaction)
As per the UiPath docs, the Start Transaction activity within the UiPath.Database.Activities package “connects to a database and features a Sequence which can perform multiple transactions with the database. If UseTransaction is set true, the contained operations are executed in a single transaction, and applied at the end if none of the them failed. If UseTransaction is set false, all operations are committed individually. When this activity ends, the connection to the database is closed. This activity returns a DatabaseConnection variable.”
Let’s test this.
Start by creating a new UiPath process which, for the sake of this example, is named TransactionManagement. Ensure that the UiPath.Database.Activities package is installed:
Drag the Start Transaction activity over into the sequence:
Hit Configure Connection, then select Connection Wizard. The Connection Wizard will ask you what data source (or provider) you’d like to use – select Microsoft SQL Server (SqlClient). Add your server name, authentication details and database name.
Ensure that the provider has been selected and is System.Data.SqlClient:
Set the output of Database Connection to a variable; in this instance I named it databaseConnection:
The databaseConnection variable will be parsed through to each of the queries that exist as part of your transaction:
When executed, you will receive the same error:
If you don’t want the error to be thrown, set the ContinueOnError parameter to True:
If you execute the sequence, there won’t be an error thrown. So does this mean that, if there’s an error that isn’t thrown, that the preceding actions will be rolled back? Let’s test this by adding the insert query:
If you execute this, no error will be raised but the insert statement will persist – the action will not be rolled back. So does this approach work? Not quite… So how can I get this working in UiPath?
Suggested Approach
A good design practise would be to separate responsibilities. If SQL is responsible for manipulating data (which it should be), then the transaction should be executed within SQL and not necessarily in UiPath.
This means you would need to create the Stored Procedure in SQL Server:
CREATE PROC ExecuteUpdate AS
BEGIN
SET NOCOUNT ON;
BEGIN TRAN
BEGIN TRY
delete from AutoForm.ProjectCreation where ProjectName = 'Test'
select top(5) AuthMethod from AutoForm.ProjectCreation
INSERT INTO [AutoForm].[ProjectCreation]
([ProjectID]
,[ProjectName]
,[ProjectPath]
,[Server]
,[Database]
,[Schema]
,[Tables]
,[AuthMethod]
,[Username]
,[Password]
,[Created]
,[Submitter]
,[DateSubmitted])
VALUES
(newid()
,'Test'
,'Test'
,'Test'
,'Test'
,'Test'
,'Test'
,'Test'
,'Test'
,'Test'
,0
,'Test'
,getdate())
select top(5) AuthMethod from AutoForm.ProjectCreation
update AutoForm.ProjectCreation set AuthMethod = 0/0
select top(5) AuthMethod from AutoForm.ProjectCreation
COMMIT TRAN;
END TRY
BEGIN CATCH
ROLLBACK TRAN;
END CATCH
select top(5) AuthMethod from AutoForm.ProjectCreation
END;
Call the stored procedure in UiPath:
Change the Command Type to Stored Procedure and enter ONLY the Stored Procedure name into the Sql parameter.
Did this work for you? Drop a comment below if you’ve faced any issues.
The JPanda 