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:
- How to retrieve the database query results?
- 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:
- In the Source Type field, select Database Query to read results from the database query.
- 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.
- 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:
- In the List field, pass the SourceData variable.
- 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.
- 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.
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.
- General information
- Licensing Provar
- Provar trial guide and extensions
- Using Provar
- API testing
- Behavior-driven development
- Creating and importing projects
- Creating test cases
- Custom table mapping
- Debugging tests
- Defining a namespace prefix on a connection
- Defining proxy settings
- Environment management
- Exporting test cases into a PDF
- Exporting test projects
- Override auto-retry for Test Step
- Managing test steps
- Namespace org testing
- Provar desktop
- Provar Test Builder
- Refresh and Recompile
- Reload Org Cache
- Running tests
- Searching Provar with find usages
- Secrets management and encryption
- Setup and teardown test cases
- Tags and Service Level Agreements (SLAs)
- Test cycles
- Test plans
- Testing browser options
- Tooltip testing
- Using the Test Palette
- Test Palette introduction
- Control test steps
- Generate Test Case
- List compare
- Page Object Cleaner
- Read test step
- String test steps
- UI Test Steps
- Using custom APIs
- Callable tests
- Data-driven testing
- Page objects
- Block locator strategies
- Introduction to XPaths
- Creating an XPath
- Label locator strategies
- Maintaining page objects
- Mapping non-Salesforce fields
- Page object operations
- Refresh and reselect field locators in Test Builder
- Using Java method annotations for custom objects
- Applications testing
- Database testing
- Document testing
- Email testing
- Mobile testing
- OrchestraCMS Testing
- Guide in Salesforce CPQ Testing in Provar
- Guide in ServiceMax Testing
- Skuid Testing
- Vlocity API Testing
- Webservices testing
- Introduction to test scheduling
- Apache Ant
- Configuration for Sending Emails via the Provar Command Line Interface
- Continuous integration
- AutoRABIT Salesforce DevOps in Provar Test
- Azure DevOps
- Running a Provar CI Task in Azure DevOps Pipelines
- Configuring the Provar secrets password in Microsoft Azure Pipelines
- Parallel Execution in Microsoft Azure Pipelines Using Multiple build.xml Files
- Parallel Execution in Microsoft Azure Pipelines using Targets
- Parallel execution in Microsoft Azure Pipelines using Test Plans
- Bitbucket Pipelines
- Gearset DevOps CI/CD
- GitHub Actions
- Integrating GitHub Actions CI to Run Provar CI Task
- Remote Trigger in GitHub Actions
- Parameterization using Environment Variables in GitHub Actions
- Parallel Execution in GitHub Actions using Multiple build.xml Files
- Parallel Execution in GitHub Actions using Targets
- Parallel Execution in GitHub Actions using Test Plan
- Parallel Execution in GitHub Actions using Job Matrix
- GitLab Continuous Integration
- Travis CI
- Execution Environment Security Configuration
- Provar Jenkins Plugin
- Parallel Execution
- Running Provar on Linux
- Salesforce DX
- Team foundation server
- Version control
- Salesforce testing
- Adding a Salesforce connection
- Assert Page Error Messages on Add/Edit Product
- Dynamic Forms
- Internationalization support
- List and table testing
- Salesforce Release Updates
- Salesforce Lightning Testing
- Salesforce Lightning Web Component (LWC) locator support
- Salesforce console testing
- Visualforce Testing
- Performance Best Practices
- Testing best practices
- Configurations and permissions
- Error messages
- Administrator has blocked access to client
- macOS Big Sur Upgrade
- Resolving failed to create ChromeDriver error
- Resolving Jenkins license missing error
- Resolving metadata timeout errors
- Test execution fails – Firefox not installed
- Update to Opportunity field validation behaviour
- Licensing, installation and firewalls
- Test Builder and test cases
- Release notes