Description
A Test Data Query performs an SQL query on the defined database resource. This query can be performed in one of two ways for a database: Query Before and Query Each.
When should you use a Database Query?
- Any time you want to query real data from a database and blend it with synthetically generated data.
How do you configure Database Queries?
- Create a Database Test Data Query (Query Before or Query Each)
- Configure the Query Parameters
- Enter the Database Query
- Add Domains and Attributes for the real data to populate
2 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:
- Parameters - Define the resource path, subdirectory, name, and variables for the Test Data Query.
- Query - Defines the actual query to be performed on the database.
- Domain Attributes - Defines the Attributes that will be populated by the real data obtained from the SQL query.
Sample Database Query
In This Article
- Prerequisites: Database Properties File
- Step 1: Configure Test Data Query Parameters
- Step 2: Enter the Query
- Step 3: Add Domain Attributes to the Test Data Query
Note: Only the available Parameters are different for these two types of database queries. All other set up steps are the same.
Prerequisites: Database Connection 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. 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. When doing so, you will need to place these files in their own sub-directory and include the sub-directory within the configuration.
Step 1: Configure Test Data Query Parameters
Test Data Query Parameters define any resources variables to be used during the query. Available parameters vary based on the type of query.
Query Before
Items with an asterisk (*) are required to perform this type of Test Data Query.
- resourcePath* - Defines the path where the resource file for JDBC connection properties exists.
- resourceSubDir - Defines an optional subdirectory under the resourcePath where the resource file for JDBC connection properties exists.
- resourceName* - Defines the name of resource that contains the database connection information on a user's local machine.
- bufferSize* - Defines how many rows to keep in memory at a time. Setting the number of rows to a very high number may cause an OutOfMemoryException to occur if the computer does not have enough available memory. The default buffer size is 1000 rows.
- setLoop* - setLoop provides the ability to OVERRIDE the loopCount, at the Scenario level, and to the number of rows that are queried. The default value is False.
- queryOnce* - When the Domain that defines the query has a Parent Domain, it becomes a Child Domain of the Parent Domain. This flag determines if the Child Domain's query should be executed each time the Child Domain is called from the Parent Domain or if the query should only be executed once, the first time the Parent Domain calls the Child Domain. The default is set True to query once.
- grVar1 - The first of five possible variables that can be used within the query.
- grVar2 - The second of five possible variables that can be used within the query.
- grVar3 - The third of five possible variables that can be used within the query.
- grVar4 - The fourth of five possible variables that can be used within the query.
- grVar5 - The fifth of five possible variables that can be used within the query.
Note: Remember to click Save after making changes to the parameter configuration.
Query Each
Items with an asterisk (*) are required to perform this type of Test Data Query.
- resourcePath* - Defines the path where the resource file for JDBC connection properties exists.
- resourceSubDir - Defines an optional subdirectory under the resourcePath where the resource file for JDBC connection properties exists.
- resourceName* - Defines the name of resource that contains the database connection information on a user's local machine.
- grVar1 - The first of five possible variables that can be used within the query.
- grVar2 - The second of five possible variables that can be used within the query.
- grVar3 - The third of five possible variables that can be used within the query.
- grVar4 - The fourth of five possible variables that can be used within the query.
- grVar5 - The fifth of five possible variables that can be used within the query.
Note: Remember to click Save after making changes to the parameter configuration.
Step 2: Enter the Query
After the appropriate parameters have been configured, you will need to enter the actual query for the database or CSV file.
- Click on the field and copy/paste or type the query.
- Click Save to save the change to your Test Data Query configuration.
Step 3: Add Domain Attributes to the Test Data Query
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 below the Domain/Attribute/Column table.
- Select the Domain and Attribute from the drop-down menus.
- Enter the name of the Column being read by the query.
- Click Save to finish.
- The new Domain Attribute will appear in the table as shown below:
Note: Options in the Action Column can be used to edit or delete a Domain Attribute from the Test Data Query configuration.