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 configuring 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: Retrieve the Database Query results.

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

This test step sets the variable’s value, 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 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 query result will be stored and define the variable’s scope. In this example, the query result will be captured in the SourceData variable, as shown in the screenshot above.

Select the appropriate option from the drop-down to define the variable’s scope. In this example, we have selected the scope as a Test. 

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

• Global – The variable’s scope is global and available throughout.
• Folder – The variable’s scope 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 ends, this variable will be deleted.
• Group Step – the variable’s scope is limited to the test steps grouped.
• Local – the variable’s scope is local only and is available only for this test step. 

 Above: View of the scope of the variable.

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

6. In the Iteration Title field, enter var. The var variable will have data from 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 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. This JOBS table has two rows or records  (Item 1 and Item 2 ) and four 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. The task of Parameter Value Source is done, i.e., to convey the query.

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

Step 2: Using the Database Query results.

Now that we have fetched the database query results, we can use this data however 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 applied a ‘For Each ‘loop in the test case. In this loop, we have passed a variable called SourceData, as displayed in the screenshot 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 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. Item 2 is stored in the temp variable when the loop executes for the second time.

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 two column names from the table.

• temp.job_id

• temp.job_title 

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

4. Select the Continue on Failure checkbox if the ‘For Each‘ loop should continue executing after the 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 as the first row. 

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

Above: View Item 1 as a complete row.

In the Test Runner, when we click on Iteration 1, it shows the most recent value,’ 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, ‘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.

As we have these values in a variable in Provar, we can use this 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.