Man illustrationFlower illustration

Docy

Robusta RPA Database Connection and Query Guide

Estimated reading: 5 minutes 51 views

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.

  • jdbc:mysql://89.163.225.9:3306/ cloudrpa_learnRPA?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
  • jdbc:mysql://localhost:3306/rpa_turkey?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
  • jdbc:oracle:thin:@<hostName>:<portNumber>:<sid>;  (if there is sid)
  • jdbc:oracle:thin:@//<hostName>:<portNumber>/serviceName; (if there is oracle server)

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

DROP TABLE TBL_Training_NewTableDeletes the specified table.
INSERT INTO TBL_Training_Company_Info VALUES (1, ‘Florida_Tallahassee’, ‘Nadia Diaz’, ‘A’)Enters one line of data into the specified table.
INSERT INTO TBL_Training_Company_Info VALUES (1, ‘Florida_Tallahassee’, ‘Nadia Diaz’, ‘A’),(2, ‘Florida_Miami’, ‘Max Melon’, ‘B’)Enters data in more than one line.
INSERT INTO TBL_Training_Company_Info VALUES (${RegistrationNumber}, ‘${Location}’, ‘${TeamLead}’,’${CompanyName}’)Enters multi-line output in a loop.
UPDATE TBL_Training_Company_Info SET Location = “Florida_Miami” WHERE Registration_Number = 1;Updates the data with primary key information (Registration Number) 1 in the Location column 1 to Florida_Miami.

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.

SELECT * FROM TBL_Training_Company_Info;This query assigns the table that is called by the query to a dataset that is defined in the Result dataset name field.

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.

CONTENTS