How to perform Select Query operation in Leapwork using a Database Block?
How to perform Select Query operation in Leapwork using a Database Block?
Introduction
There are various scenarios where a user wants to connect to any database (using DSN) in order to validate the data getting stored in the database. Moreover, there are scenarios where a user needs to insert data into the database through automation.
In this article, we will be talking about how we can achieve these scenarios by executing different database commands using a Leapwork Database Block.
How to perform SELECT Query operation in Leapwork using a Database Block?
In this scenario, we will learn how to perform a SELECT query operation in Leapwork using the Database Block.
To begin with, we need to first check some prerequisites. For example - Setting up ODBC drivers.
Please refer to the following article for more information - Access SQL-Database via ODBC driver in Leapwork.
Please note that we will be using SQL Server Database to execute the commands for this example.
The two input fields viz. Connection & Query are mandatory fields and should be populated as below:
Connection
The " Data Source Name" of configured ODBC connection on the computer where the test case will run.
Query
The query statement will be executed on the excel file. It has a special syntax which is an ODBC-compliant SQL statement. The dynamic fields can be specified and used as Tokens while creating query statements.
Note: We can execute the below query (either through Leapwork or directly in the SQL Server Query Manager) to create a new table in the Database:
create table LW_USER_DETAILS([ID][int],[FirstName][varchar](25),[LastName][varchar](25),[Email][varchar](25),[Address][varchar](50),[Department][varchar](25))
Once the user provides both the above details correctly and clicks on the " Refresh" button on the Database blocks, it loads up the respective columns present in the table.
In this case, any of the 2 provided input fields, i.e. Connection or Query is incorrect, Leapwork throws an error after clicking on the "Refresh" button.
So, once the connection is successfully connected, it is time to say that the user is up and ready to execute the SELECT query through Leapwork. We can add a Log message block to validate if the values are fetched as expected from the Database.
Under the Hood
As per the above image, the method used is Iterate which can fetch out the top 10 values available in the database.
The other 2 options like First Row and Row Index can also be used as per the scenarios which will read the value of the first row and any specific row index provided respectively.
In the above image, the Log message block is fetching dynamic values from the Database block.
The dynamic fields have been specified and used as Tokens in the Log message block while fetching dynamic contents from the Database block.
If the query is not provided correctly, then the Error connector will be triggered.
Quick Start
- Download the attached SELECT.zip file.
- Import the attached use case, SELECT.zip, in the Leapwork Studio.
Please refer to: How to Export and Import automation flows. 3. Open the use case with the title, " Select Query"
Configuration and Use
After the flow has been imported, you can move the flow to any specific folder and then execute the same to see the behavior. Please refer to the following article for more: Organizing Daily Work in Leapwork.
Attachments:
If you have any questions, please reach out to our Priority Support team.