Top Stories
Stay Connected
Untangling the Web of Database Connectivity in UiPath
(How to work with a different type of Database connectivity with UiPath)
You probably have already worked with database activity while automating your RPA project to execute some queries to save results into a transactional database or retrieved information from Database to process them in UiPath using Database Activities provided by Uipath.
But you will agree with me that the documentation isn’t that much great, and it does not talk about connecting with different variants of Databases.
You can successfully connect with Oracle but when you are connecting with SQLSERVER or MySQL databases you are facing challenges.
So …In this detailed guide, I’ll guide you step by step through connecting with the most popular databases with UiPath which will help you in your next Automation and save your time.
If you frequently work with automating and interacting with the database in your RPA project you will love this detailed article.
Let us get started!
Pre-requisites –
Installing, configuring, and troubleshooting database issues is beyond scope of this article. So, at this point I assume.
- You have successfully installed Database & Drivers to Connect to the Uipath
- You are able to connect your database using credentials
- You have required permission to execute query and non-query-based transaction
However, we will list out some of the known issues while connecting with Uipath to help you during your process automation.
What is Database Activity?
As per UiPath Documentation “The Database Activities Pack enables the user to connect to a database and perform an entire palette of actions within it, such as executing transactions or queries and non-queries”
Database activities are pretty simple and easy to use. It has 6 Activities that are used to connect the database, perform select, insert, update, delete based transaction.
We will discuss UiPath.Database.Activities.DatabaseConnect properties in more detail as for connecting with different databases require different settings and connection string.
How to enable is Database Activities?
By Default, this activity will not be included in your project as a dependency, so in case you want to perform database operation in your workflow you need to install the package first.
You can install the same by navigating to Manage Package -> Search for Database Activity -> Click Install -> Save.
Please remember even if you have installed the UiPath.Database.Activities you won’t be able to connect to the database you don’t have Database drivers installed for specific database variant you are trying to use.
For example, if you wish to connect with Oracle Database then apart from the Package you need to install SQL Driver on your machine to communicate with Database instance.
As the UiPath Database Activity works as a wrapper on top of your SQL Client to manage the various operation. So, we will also discuss the details of which Driver you required for which database.
Uipath Database pack enables the user to connect to a database and perform different actions within it, such as executing queries, transactions, and non-queries.
Let’s discuss these activities one by one in detail.
- Connect: This activity requests a connection string to establish a database connection and a ProviderName. The connection string must contain a data source, a username, and a password. This activity outputs a DatabaseConnection variable which you can use throughout further activities.
- Disconnect: This activity closes a connection to a database.
- Execute Non Query: This activity executes a non-query (Update, Insert, and Delete, etc.) statements that do not return any result sets. This method returns the number of rows affected by either any of the non-query statements.
- Execute Query: Executes the query on a database and returns the query result in the data table. SQL command to be executed to get the results.
- Insert: This activity inserts data table records into an existing database table. For this, you need to provide the table name and the data table variable that will be inserted into the table in the properties panel of Insert activity.
- Start Transaction: This activity is used to connect to a database and can include any database related activities in it. It gives us the output with a database connection variable that can be passed as input to other database activities as input for the connection string. If UseTransaction is set to true, the contained operations are executed in a single transaction and applied at the end if none of them failed. If UseTransaction is set to false, all operations are committed individually. When this activity ends, the connection to the database is closed.
A Closer Look at Different Types of Database Connectivity
Now that we’ve covered the basics, let’s delve into the specifics of connecting UiPath with various database types.
Connecting UiPath with SQL Server
Microsoft SQL Server is a widely-used relational database management system. To connect UiPath with SQL Server, you’ll need to install the appropriate .NET data provider and construct a connection string.
Interfacing UiPath with MySQL
MySQL is another popular choice for many developers. Connecting UiPath with MySQL involves a similar process to SQL Server but with a different .NET data provider and connection string format.
Linking UiPath with Oracle Database
Oracle Database is known for its robust features, making it a favorite among large corporations. Connecting UiPath with Oracle Database requires an Oracle Data Provider for .NET and a uniquely formatted connection string.
Integrating UiPath with PostgreSQL
PostgreSQL is an advanced, open-source relational database system. To connect UiPath with PostgreSQL, you will need the Npgsql .NET data provider and a PostgreSQL-specific connection string.
Communicating with Other Databases
UiPath’s flexibility doesn’t stop at these popular databases. It’s capable of connecting with other databases such as SQLite, DB2, and even cloud-based databases like Azure SQL Database and Amazon RDS.
Working With UiPath MySQL Database
Prerequisite
- You should have MySQL Instance up running and you have access to connect using credentials.
- MySQL ODBC Connector is installed
Important Links for MySQL Setup
In case you don’t have MySQL Database not installed, You can follow the below links to get the setup done.
MySQL Download link
Steps to install MySQL
- https://www.mysqltutorial.org/install-mysql/
- https://www.edureka.co/blog/install-mysql/
MySQL ODBC Connector installer
Steps to connect MySQL with UiPath and Execute Query
Step #1 – Install the database package from manage packages. In case you already have this package installed you can go to step 2.
Step #2 – Create a new sequence in UiPath Studio. Search for “Database” in the activities panel you will see various database activities as shown below.
Step #3 – Now, It’s time to connect MySQL Database with UiPath. For this, you need a hostname, port, username, and password.
These details will be mostly provided by your DBA Team, However, if you are setting up Connection locally you can get those details using MySQL workbench.
You can click on the settings icon to view the connection details, Note down the hostname, port number, and user name and password from below the screen.
Step #4 – Now go to UiPath and Drag the “Connect” activity in the sequence.
Connect: This activity requests a connection string to establish a database connection and a ProviderName. The connection string must contain a data source, a username and a password. This activity outputs a DatabaseConnection variable which you can use throughout further activities.
Package: Uipath.Database.Activities.DatabaseConnect
Click on the Configure Connection or connection String under properties panel and write the connection string that we have noted in the step 3 above.
- Server=127.0.0.1;port=3306;database=world;uid=root;password=*****;
- provide the provider name as “sql.connect” under Properties panel.
Now provide the output variable of the connection so that the same connection properties can be used in other database activities.
DatabaseConnection = dbtest
Now if you see the connection established successfully and warning is not visible in connect activity.
Drag the activity “Execute Query” in the sequence.
Execute Query: This activity executes a query on a database and returns the result of the query in the datatable.
Package: Uipath.Database.Activities.ExecuteQuery
In this activity, three important properties always should be in mind:
- Existing Connection: Use the output of the connect activity to use its connection properties in execute query.
- SQL: Write the SQL query as a string.
- Datatable: Store the result in the datatable variable. (Create the datatable variable as “OutputDT”
Now write the SQL query; For that first go to MySQL and see the tables present in the connected database. For my example I have database named as “world” (e.g.- City, Country, Country, language). You can create your own query to after running into MySQL workbench to ensure that there is no issue with the Query itself.
Now run the select Query in the MySQL manually to see the results present in the table as shown in above picture.
You can go Back to UiPath workflow and write the same query “Select * from City” in the string.
As the result is stored in the datatable, take a write range to write the result of datatable in the workbook or excel file. For that take a write range activity and provide the name of the file and reference of the output datatable of executing the query.
Result of datatable that we have write in the workbook as shown below.
In this way you can write the different types of queries (like Select, Update, Delete, Drop,
Truncate, etc.) And get the results.
Example of Insert
Go to MySQL and create a new table (e.g. “Test”) as shown below.
Now go to UiPath workflow and take the read range to read the excel file “Results.xlsx” in which we
have 5 records that will be inserted into the table. Create a datatable variable and associate it with
the reading range.
Now drag the “Insert” activity below the connect activity and provide the table name where the records need to be inserted and the datatable variable.
The table name is “test” that we have created in MySQL.
- Datatable name is “testDT”
- Existing connection: “dbtest”
AffectedRecords to check the no of records that will insert into the table by default variable type is int32. Now take the Write Line activity to see the output.
Also In read range activity keep the range as blank to read all the data.
Run the workflow and check the MySQL table.
All the records inserted successfully. Now go to MySQL and check you will see that all the records are shown that were there in the excel file.
So in this way you can easily connect with database and run the queries in UiPath.
Working with SQL SERVER Databases
SQL Server Express Connections
Prerequisite –
- Install the SQL Server Express 2019
- https://www.microsoft.com/en-us/sql-server/sql-server-downloads
- Install the SQL Server Management Studio
- https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15
Steps to connect with UiPath-
- Take and Sequence and Drag the “Connect” activity and configure the connections between the UiPath and SQL Server
- Click on the “Connection Wizard” button
Select the “Microsoft SQL Server” Data source and click OK, On clicking the Connection Properties window opens where you have to select the Server Name (Automatically appears in the list), and Database Name (automatically appears in the drop-down).
Note: If you have your authentication credentials then select the “Use SQL Server Authentication” and provide the username and password for the same.
Now click on the “Test Connection” button and click ok once succeeded.
Connection string is appear as :
“Data Source=DESKTOP-XXXXXX\SQLEXPRESS;Initial Catalog=TestDB;Integrated Security=True”
Connection established successfully. Now Rest of the activities will work as it is in MySQL example above. The same type of configurations will be done from all other databases like (Oracle, PostgreSQL, etc.).
Working with Oracle Databases
Working with Oracle database is somewhat different than other databases as if there is an issue with your SQL Client or TNS Details or Mismatch of Version between SQL driver or UiPath will run into issues.
Please noted that, until July of 2018, UiPath has only version 32bit, so that you need oracle InstantClient for 32 bit, not 64 bit.
For UiPath, there are 2 ways to connect to oracle DB
- Using Microsoft ODBC Drivers – No More Recommended as there is limited support available on this.
- Using Oracle Client – Recommended way to working with Oracle Databases.
Prerequisite –
- Install Oracle Instant Client or Full Client (Make sure to download the client of the same version as Oracle DB. (In my case, it was 12.1.0.2.0))
- Add the path of instant client folder to System variables (Path and TNS_ADMIN)
- Create the tnsnames.ora file inside the instant client folder. (if not there) or add required details.
Steps to Connect –
Steps will be similar to what we have done above for MYSQL Databases. The only thing that you need to change here is the below properties of connection Wizard to help to connect with UiPath.
- ProviderName: “System.Data.OracleClient”
- ConnectionString: “SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myHostNam)(PORT=myPort))(CONNECT_DATA=(SERVICE_NAME=mySID)));uid=myUsername;pwd=myPwd;”
Once Connection gets established. You should be able to perform the required steps.W
Managing Connections in UiPath: Best Practices
Working with databases isn’t just about establishing connections and executing queries. It’s also about managing those connections effectively and securely.
Securing Your Connection String
Connection strings contain sensitive data. Therefore, it’s critical to secure them. UiPath provides several mechanisms for this, including Windows Authentication and storing sensitive information in the Windows Credential Manager or Orchestrator Assets.
Managing Database Connections
Good connection management involves closing connections promptly after use to prevent resource leaks and potential application crashes. UiPath’s ‘Disconnect’ activity serves this purpose, cleanly severing the connection between the robot and the database.
Error Handling and Recovery
UiPath provides several tools and techniques for handling errors during database operations. Using ‘Try Catch’ blocks and transaction activities, you can ensure that your robot can recover gracefully from any hiccups in the database operations.
FAQs
- How can I connect UiPath to a database?
UiPath connects to a database using the Database Activities Pack. You’ll need to configure a connection string, which serves as the ‘address’ of your database.
- Can UiPath connect to any type of database?
UiPath supports connectivity with a wide range of databases, including SQL Server, MySQL, Oracle, PostgreSQL, and many others. The connection process varies slightly depending on the database type.
- Is it secure to connect UiPath to a database?
Yes, as long as you follow best practices for securing your connection string and managing database connections. UiPath provides several mechanisms for securing sensitive data.
- What is a connection string in UiPath?
A connection string is a line of text that contains the information UiPath needs to connect to a database, including server name, database name, username, and password.
- How does UiPath execute SQL commands?
UiPath provides several activities for executing SQL commands, such as ‘Execute Non Query’, ‘Execute Query’, and ‘Execute Scalar’.
- Can UiPath handle database transactions?
Yes, UiPath can handle database transactions using the ‘Start Transaction’ and ‘Commit/Rollback Transaction’ activities.
Conclusion
Database connectivity is a vital aspect of UiPath, opening up a world of possibilities for automation processes. By understanding how to work with different types of database connectivity with UiPath, you unlock the full potential of RPA. No matter the database, UiPath has the tools and capabilities to get the job done. So, dive in and make your automation dreams a reality!