Reading data from Database Query results

Provar supports Data Driven Testing by reading data from Database Query results using the Parameter Value Source. This page describes how to configure Provar to read data from Database Query results.

Broadly, this support article consists of two parts:

1. How to retrieve the database query results?
2. How to use the database query results?

Steps to read the data from Database Query results

Step 1: Retrieving the Database Query results.

Let’s first create a test case. For example, we have created a Parameter.DB testcase. Drag and drop the Set Values test step from the Test Palette into the test case.

This test step sets the value of the variable, which is then used in the test step.

In the Test Step Parameters section, click Add a new parameter Value Source icon .

Above: View of the database query in the test case.

In the Parameter Value Source section:

1. In the Source Type field, select Database Query to read results from the database query.
2. In the Query field, we enter the query from which we want to retrieve the data. For example, we want to read the data from the JOBS table. So, we have written the query as ‘Select * from JOBS‘ and we are selecting all the data from the JOBS table.
3. In the Connection field, select the connection as Postgres. We had already created a Postgres database connection earlier.

Note: You can select the supported database connection to read the query results. For more information on adding a database connection, please see Adding a Database Connection.

4. In the Row Limit field, we enter or set the number of records or rows that we want to display from the query result. In this example, we have set it as 10.

5. In the Variable field, enter the variable name where the result of the query will be stored and define the scope of the variable. In this example, the result of the query will be captured in the SourceData variable as shown in the screenshot above.

To define the scope of the variable, select the appropriate option from the drop-down. In this example, we have selected the scope as Test. 

The options available for the scope of the variable are as given below:

• Global – The scope of the variable is global and it is available throughout.
• Folder – The scope of the variable is limited to the test cases in the specific  folder.
• Test – the scope of the variable is limited to the test case. When the test case will end, this variable will be deleted.
• Group Step – the scope of the variable is limited to the test steps that are grouped.
• Local – the scope of the variable is local only and the variable is available only for this test step. 

 Above: View of scope of the variable.

Note: The result of the query (Select * from JOBS) is captured in the variable SourceData. The scope of this variable is selected as Test, so this variable will be available only for this test case. 

6. In the Iteration Title field, enter var. The var variable will have data of each row at a time. As SourceData can contain multiple records fetched from the table, var is used to iterate through each record.

On execution –

When we execute the test case and the Set Values step executes, we can see it fetches two records Item 1 and Item 2. In the Variables section, if we expand these, we can see Item 1 and Item 2 each have four sub-items. We can see this JOBS table has 2 rows or records  (Item 1 and Item 2 ) and 4 columns (job_id, job_title, max_salary, min_salary).

Above: View of the rows retrieved from the database query.

Now, we have seen how we can fetch the query. Basically, the task of Parameter Value Source is done i.e. to fetch the query.

Above: View of Item 1 and Item 2 in SourceData variable.

Step 2: Using the Database Query results.

Now that we have fetched the database query results; we can use this data in any way we want. We can WRITE this data to a file or READ this data; we have this singular entity of data (as captured in the SourceData variable).

Next, we have applied a ‘For Each‘ loop in the test case. In this loop, we have passed a variable which is SourceData as displayed in the screenshot given below.

Above: View of For Each loop in the test case.

In the Test Step Parameters section:

1. In the List field, pass the SourceData variable.
2. In the From Item field, enter ‘1‘. The execution will start from the first item and it will execute for each item in the list until the last item. 
3. In the Value Name field, we have given ‘temp’. When the loop executes for the first time, the first row is stored in the temp variable; that means Item 1 is stored in the temp variable. When the loop executes for the second time, Item 2 is stored in the temp variable.

Note: The loop doesn’t execute for the third time as we only have Item 1 and Item 2 in the table.

For Iteration 1, we have given two Set Values steps as displayed in the screenshot below. These are basically two column names from the table.

• temp.job_id

• temp.job_title 

Note: In this example, we have selected the above mentioned two column names. We could have selected two other columns max_salary and min_salary also instead of job_id and job_title.

4. Select the Continue on Failure checkbox if ‘For Each‘ loop should continue executing after failure of any iteration within the loop.

Above: View of Iteration 1 and Iteration 2 in the Test Runner.

When for the first time the ‘For Each’ loop is executed for Iteration 1; in the temp variable, Item 1 is stored which is the first row. 

Item 1 is a complete row as displayed in the screenshot given below. So, when we set the value as job_title, the ‘For Each’ loop execution reaches up to the job_title column. So, the Iteration 1 reaches a singular value of job_title.

Above: View of Item 1 as a complete row.

In the Test Runner when we click on Iteration 1, it shows the most recent value which is ‘job_id as 10′ and ‘job_title as TESTTITLE’. We can see this in the SourceData variable.

The temp variable now displays ‘job_id as10′ and ‘job_title as TESTTITLE’.

Above: View of the temp variable for Iteration 1.

When Iteration 2 is executed; in the temp variable, Item 2 is stored, which is the second row.

In the Test Runner if we click on Iteration 2, it shows the most recent value which is ‘job_id as 18’ and ‘job_title as TESTTITLE’. We can see this in the SourceData variable.

The temp variable now displays ‘job_id as 18’ and ‘job_title as TESTTITLE’.

Above: View of the temp variable for Iteration 2.

Now, as we have got these values in a variable in Provar, we can use these job_id and job_title in whichever way we want. We can write these in a UI or we can use these in an Assert test step.