Reading 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: 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.
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 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:
- In the List field, pass the SourceData variable.
- 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.
- 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.
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.
- 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
- 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
- List compare
- 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
- 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 issue
- 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
- Version 2.8.0 Winter ’23
- Version 2.7.2
- Version 2.7.0 Summer ’22
- Version 2.6.3
- Version 2.6.0 Spring ’22
- Version 2.5.0 Winter ’22
- Version 2.4.1
- Version 2.4.0 Summer ’21
- Version 2.3.1
- Version 2.3.0 Spring ’21
- Version 2.2.1
- Version 2.2.0 Winter ’21
- Version 2.1.1
- Version 2.1.0 Summer ’20
- Version 2.0.5
- Version 2.0.4
- Version 2.0.3 Spring ’20
- Version 1.9.12
- Version 1.9.11