Robusta RPA Database Connection and Query Guide

In this document, how to use Db activities is explained by using an exemplary process.

You can access to sample process by clicking here. A screenshot of sample process is down below.

Sample process is designed by using MySQL database management system (DBMS) to execute the desired database queries. You can find answers to questions like how to connect to MySQL from Robusta RPA and how to send queries. Even if sample process is designed for the MySQL database, you can follow the same steps for other databases. You can learn the relevant differences for other databases from documentation and apply those changes in your own processes.

1 – Connection

Open Connection activity under the Database component is used to connect to a database management system.

NOTE – Always check the connection to your database management system from a database management software before connecting from robot. Moreover, IP address of computer where Robot is installed must have access authorization to database. Robot cannot access a database management system that test connection is not established.

Open connection activity provides access to the desired database management system. Select the database that will be used from the Driver name list. Type the username information in the Database user and Database password fields. If the Database user field is used, the Database name should be left blank. The connection must be established with either the Database user field or the Database name field. A reference name is given to DBMS that will be connected to in the Connection name field and you will perform different processes later in the process, thanks to that reference name. In the Database URL field, idbc URL information is typed for MySQL.

NOTE – Even if Robusta product supports MySQL, Oracle, Cloudera, PostgreSQL, ibm.as400, sybase and Microsoft SQL Server, it works with all database management systems that have JDBC support.

2 – Execute Update

MySQL is a query language for establishing relationships between data. In Robusta RPA, the desired queries are sent through the Execute update activity.

The Execute update activity sends queries to the desired database management system. Its screenshot is on the right and its feature field is below. Type the query that will be sent in the QUERY SQL field. In this example, the query below is sent.

CREATE TABLE TBL_Training_NewTable
(
Registration_Number INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
Location nvarchar(80),
Team_Lead nvarchar(50),
Company_Name nvarchar(50)
)

EXPLANATION OF QUERY

• Create Table query creates a table in MySQL.
• TBL_Training_NewTable, which is written after Create Table query, is the name of the table.
• Registration_Number, Location, Team_Lead and Company_Name are the column headers of the table.
• INT and nvarchar are the data types of the table columns. [There are different data types in MySQL. These data types are INT, smallint, tinyint, monetary (for currencies), char (string), varchar, nvarchar (string that is in every language) and binary etc.
• The numbers in the parentheses are the number of characters. The possible maximum number is given. To establish a connection, the first table column is defined as primary key.
• UNSIGNED AUTO_INCREMENT PRIMARY KEY is used to define that the defined primary key will be searched in order.

3 – Queries That Sent in Other Execute Update Activities

4 – Execute Update

The Execute query activity sends the Select From query to the desired database management system. Its screenshot is on the right and its feature field is below. Select From query calls tables. In the Query sql field, the query below is used.

5 – Close Connection

The Close connection activity ends the connection with the database management system by using the reference name. Its screenshot is on the right and its feature field is below.

The use of Param or Parameters fields in the activities is not compulsory. Queries can be sent from Param or Parameters fields. If they are to be used, one of them should be used. In case both of them are used, the variables in the desired queries should be given. If the Parameters field will be used, the queries must be in JSON format.