Description

Multiple types of Test Data Queries (G-Queries) can be added to a Project Version. Users can select Query Before or Query Each to perform a SQL query on the defined database resource. For MongoDB, separate query types are provided. 

In This Article

• When Should a Database Query Be Used? 
• Video Tutorial
• Two Types of Database Queries
• Database Query Configuration Components
• Prerequisites - config.properties File 
• Simple Use Case Example
  • Step 1 - Setup Project
  • Step 2 - Add a G-Query to the Project Version
  • Step 3 - Configure Query Parameters
  • Step 4 - Enter the Query
  • Step 5 - Add Domain Attributes
  • Step 6 - (Optional) Add to a Test Data Case
  • Step 7 - Generate Test Data
  • Data in Queried Database (First 25 Records)
  • Sample Output 

When Should a Database Query Be Used? 

• Any time you want to query real data from one or more database tables and blend it with synthetically generated data. 
  
  
• For MongoDB, please see this article: How to configure a MongoDB Query.

Video Tutorial

The following video provides step-by-step instructions for configuring a database query: 

Two Types of Database Queries

• Query Before - Query columns from one or more database tables as a set of data that is buffered into memory.  
• Query Each - Query one row of columns, from one or more database tables, on each row iteration of test data generation.

Database Query Configuration Components

A Test Data Query configuration has the following primary components: 

1. Parameters - Define the resource path, subdirectory, name, and variables for the Test Data Query.
2. Query - Defines the actual query to be performed on the database.
3. Domain Attributes - Defines the Attributes that will be populated by the real data obtained from the SQL query.

Note: Only the available Parameters are different for these two types of database queries. All other setup steps are the same.

Prerequisites - config.properties File 

A config.properties file must be available on your local computer to connect to the database and perform the query. The properties file contains the database connection information, and its location should match the defined resource path in the Query Parameters (Step 2 below). For more information about setting up this file, click here.

Here are the parameters required in the properties file: 

driver=<JDBCDriverClassName>
user=<userToConnectToDatabase>
password=<passwordToConnectToDatabase>
url=<URL>/<databaseName>
batchCount=1000

Tip: You can create a separate properties file for each database to avoid the additional steps of changing the database information each time the database changes. They should be placed in a sub-directory, which should be included in the query parameters configuration.  

Simple Use Case Example

A tester needs to query a Customer table within a database and blend that data with synthetic data during test data generation. They want to query the following columns within the table: 

• first_name
• last_name

The tester wants to blend the queried data with the following synthetically generated data: 

• ssn
• phone_number

Additionally, they want to generate this data in a single delimited output file using a tab delimiter.  

Step 1 - Setup Project

For this example, the tester will need to complete the following: 

1. Create a Customer Domain with the following Attributes.
   
   
   
2. Set the Domain loopCount.
3. Assign the DelimitedFileReceiver to the Domain.
4. Create a Scenario for the Domain.
   
   
   
5. Configure the Receiver Parameters and Attribute Property Keys.
   
   

Step 2 - Add a G-Query to the Project Version

• Click on Add G-Query in the dashboard. 
  
  
  
  
• Enter the Name and a Description. 
• Select a Type (Query Before or Query Each) and click Save.
  
  

Step 3 - Configure Query Parameters

Test Data Query Parameters define any resource variables to be used during the query. Available parameters vary based on the type of query.

For each query type, items with an asterisk (*) are required. 

• Use the fields and drop-down menus to modify query parameters.
• Click Save once finished.

Query Before

Query Each

Step 4 - Enter the Query

• Click on the field, copy/paste or type the query, and click Save.
  
  

Step 5 - Add Domain Attributes

To finish the configuration, you will need to add the Domain Attributes that data read from columns will be populated into for the Test Data Query. This will need to be done for each queried column of data in the database table.

• Click on Add in the Domain Attributes pane.
  
  
  
  
• Select the Domain and Attribute.
  
• Enter the name of the Column being read by the query.
  
• Enter a Default Value and click Save to finish.
  
  
  
  
• Repeat these steps for each Domain Attribute. In this example, it is three Attributes: id, firstName, and lastName.
  
  
  
• Click on Column Check to ensure everything is correct. 
  
  
  

Step 6 - (Optional) Add to a Test Data Case

Queries can be added to one or more individual test cases. When this is done, the query does not have to be downloaded, just the case and Scenario. For this example, a case will not be used. To learn more, click here. 

Step 7 - Generate Test Data

It is time to generate the test data. The query will be performed during test data generation. The tester will perform the following for this example: 

1. Download the Scenario. 
2. Download the Test Data Query.
3. Copy the Query Command in the Dashboard.
4. Open a Command Prompt or Terminal window.
5. Run the command at the command line. 

Note: Click here to see step-by-step instructions. Please note that downloading is not necessary when using G-Repository.

Data in Queried Database (First 25 Records)

Sample Output 

The first and last name has been included in the generated delimited file along with a synthetically generated social security number and phone number for each record.