Need help database automation

[naveen.s]

Hi All,

I need to load the input data to Database mssql, once it is added the initial status should mark as 0, while processing(in progress )the status should changes to 1, if the transaction is successful then status should update to 2, if any exceptions like business or system exceptions bot should mark the status as 3 along with the reason .
I need to do this using UiPath , give me the solution
Please can you give me the workflow based on the example of RPA challenge automation

[naveen.s]

@prashant1603765 ,

I am using reframe work

[prashant1603765 (Prashant Kumar)]

@naveen.s

Ok you can use Reframework

1. Init State (Initialization)

• Connect to Database (using Connect activity).
• Fetch New Transactions (Status = 0) using Execute Query.
• Add Transactions to Queue in Orchestrator.
• Update Status to 1 (In Progress) in the database.

UPDATE Transactions SET Status = 1 WHERE ID = @TransactionID;

---

2. Process Transaction (Handling Each Item)

• Get Transaction from Queue.
• Perform RPA Challenge Automation.
• If successful, update status to 2 (Success) in the database.

  UPDATE Transactions SET Status = 2 WHERE ID = @TransactionID;
  

• If failed (business/system exception), update status to 3 (Failed) with the error message.

  UPDATE Transactions SET Status = 3, Reason = @ExceptionMessage WHERE ID = @TransactionID;
  

---

3. End Process (Cleanup)

• Close Database Connection using Disconnect.
• Log Process Completion.
• Close Applications if required (Close Application or Kill Process).

---

Workflow Breakdown

1. Init State

• Connect to Database
• Execute Query to fetch pending transactions (Status = 0)
• Add Queue Item (Add to Orchestrator Queue)
• Execute Non Query (Update Status to 1 - In Progress)

2. Process Transaction

• Get Transaction Item from Queue
• Process the RPA Challenge
• If Success → Execute Non Query (Update to Status 2)
• If Exception → Execute Non Query (Update to Status 3 with Reason)

3. End Process

• Disconnect from Database
• Log Message for completion
• Close Application (if needed)

[sharazkm32 (Sharaz K M)]

@naveen.s

If you are using RE Framework. Kindly follow below steps to make your code standard

1. Create reusable workflows first under Database Folder as below

2. Each workflow should have activities corresponding to their purpose. Example in Insert Data. Hope you are aware about the connection and the queries to be used.

3. Note that the insert data should happen from the Dispatcher process. Ideally Dispatcher and Performer should be 2 different process and RE is mainly for the performer.

Dispatcher - Process which add records to queue. When you add to queue - add to DB also as a next step with Initial Status as 0 using queue reference

Performer - Getting queue items and processing one after other

-------------------------------Pre-requisite’s ready -----------------------------------

3. From the performer - Inside Process transaction State → Inside Process.Xaml

Invoke UpdateData.xaml and pass the query inside to update the transaction and make the Initial Status as 1 using queue reference

4. In case of Technical Error / Business exception, It will be in the catch block of Process. Note Process should be within try catch. Based on the type of excpetion - Before throwing error , Update the DB with status as 3 using queue reference and give comments as well invoking UpdateData xaml file

5. At the end of process without going to any catch block → Update the DB with status as 2 using queue reference and the same invoke code with different value in the query

the above will give a framework idea. You can improvise on top of this

Hope it helps. Happy Automation

Tip: Since you are saving values in DB, I would suggest to create column for each of the values you need and store maximum details in DB and minimal details in Queue. Using Queue reference and select data workflow - Get all values to datatable row and use throughout the execution