In this article, we will learn how to query SQL database using UiPath. SQL is used to create, maintain and retrieve the relational database. Process automation with SQL is common use case which involve retrieving data from databases through queries. Let’s understand how to connect and query using UiPath.
We are using AdventureWorks Database in this tutorial. This is Microsoft product sample for an online transaction processing (OLTP) database. The AdventureWorks Database supports a fictitious, multinational manufacturing company called Adventure Works Cycles. You can download it from here.
Prerequisites: You will need to have database activities package installed in UiPath studio. You can install these activities using package manager.
Let’s get started!
First activity we need is to create connection. “Connect” activity sets up the connection between UiPath studio and Database. Drag and drop Connect activity on the page.
Click ‘Configure Connection’ button.
On Connection settings dialog, click “Connection Wizard” button to select data source. Select ‘Microsoft SQL Server’ from list. After that, select data provider for SQL server from Data provider drop down list. Click OK.
‘Connection Properties’ dialog will be opened then Select your database server name from drop down list. Provide authentication details as required. After that, Select the database you want to connect to.
Click ‘Test Connection’ button to verify that UiPath is able to connect to the database.
Click OK then click Ok again on ‘Edit Connection Settings’ dialog box. You will be able to see connection string with all the details. Select ‘SQL Client’ from dropdown to specify Provider name.
These steps create a connection between database and UiPath. You will need to store in a variable of type ‘DatabaseConnection’. Create a variable and assign it to output property of ‘Connect’ activity. We have ‘connAdventureWorks’ variable for this.
Once we have a connection established, now we can execute queries. Drag and drop ‘Execute Query’ activity from the list.
Click on ‘Configure Connection’ button to open settings. After that, mention connection variable name. Select OK and get back to Execute Query activity.
Click ‘Edit Query’ button and mention the SQL query you want to run.
This query when runs successfully, returns a table. So we need a data table variable to hold these values.
Then to see how many rows we’ve got in data table, we are writing it to excel file using ‘Write Range’ activity.
Below is the list of products from SQL database.
Using database activities, we can query SQL database and get results in the data table using ‘Execute Query’ activity. If it’s required to execute INSERT, UPDATE, DELETE queries, we have to use ‘Execute Non Query’ activity in similar way. This activity returns number of rows affected.
String Manipulations with UiPath is very helpful in RPA projects as RPA developers use String data types more often than not. Data handled with UiPath comes in many types and most often it will be either text, numbers, dates and times, lists or tables. Text is one of the most common data type hence knowing about Data of all kinds is stored in variables and used in Visual Basic expressions to configure activities. All activity properties have predefined types and expect expression values accordingly. Most of the variables used with UiPath will fit into one of the following categories:
Scalar variables: These will be holding a single value of a fixed type, for example Characters, Booleans, Numbers and DateTimes, Collections like Arrays, Lists and Queues.
Non Scalar variables: Inferring from what we learnt above, non-scalar data is data which is not singular. It is a data structure with multiple data points.
As text is basically a sequence of characters, we may understand String as an extended type of a simple collection. Below are some String Manipulations with UiPath.
This function is utilized to check whether the substring happens inside a given string or not. It returns a Boolean value based on occurrence of search text. If search string is found, this method returns True. If no match is found, it returns False. In example given below, we check if test variable contains the value ‘input’. As this is the valid case, we should see True message. ‘Contains’ function is case-sensitive so if there is any mismatch with case, this function will return false.
EndsWith function will test the end segment of a String. It checks whether one String ends with the characters in another. If it matches, then it returns True value otherwise it returns false. This function call is case-sensitive so the case should match exactly to have positive value returned.
In our example, we check if given string starts with ‘Doe’. We have our input as ‘John Doe’. With syntax as given below, we’ll get true value as name starts with ‘Doe’.
StartsWith tests the initial segment of a String. It checks whether one String begins with the characters in another. If it matches, then it returns True value otherwise it returns false. This function call is case-sensitive.
In our example, we check if given string starts with ‘Jo’. We have our input as ‘John Doe’. With syntax as given below, we’ll get true value as name starts with ‘Jo’.
This function determines whether two strings instances are equal or not. ‘Equals’ is case sensitive hence this function returns true value only if both strings have same value as well as same case. If values are same but case does not match it will still return false value.
So those were 4 commonly used String Manipulations with UiPath that we found in this article. . In the following article, we will demonstrate some more string activities that are utilized in RPA projects. I trust this article was valuable and I thank you for review it.
After installing all pre-requisites, please check whether Database is accessible from Orchestrator server/not. It should be accessible.
Start the set-up.
Accept terms and click on “Advanced” button.
You can see 3 things available installation:
Studio– For Development of workflows
Robot– Execution of bot on target VM where bots are actually going to run (Target VM)
Orchestrator Website– Monitor and schedule jobs/robots (Orchestrator server)
In order to install only Orchestrator, select “Entire feature will be installed on local hard drive” from Orchestrator Website option and select “Entire feature will be unavailable” for others two (Studio, Robot).
Then click next.
Please follow the steps given at below link to install Orchestrator (Single Node) as given at below link:
Elastic search and Kibana are optional. If you want to install them, then download set-up of version mentioned in “Orchestrator Server” table above and use below ELK Guide .pdf for installation steps of only Elastic search and Kibana.