Description

The QueryBeforeLoopV2Gen Generator allows querying a column within a table using an SQL select statement BEFORE the 1st iteration of test data generation.


Restrictions

  • This Generator will not run if any of the following keywords are found in the query (update, delete, set or truncate).


Parameters

  • resourcePath - Defines the path, on a user's local machine, where the resource file for the JDBC configuration properties file exists.
  • resourceSubDir - Defines an optional subdirectory under the resourcePath where the resource file for the JDBC configuration properties file exists.
  • resourceName - Defines the name of the configuration properties file that contains the database connection information.
  • setLoop - Gives the ability to override the loop count at the Scenario level to only produce the number of rows that are retrieved by the query.
  • waitAmount - Defines the number of iterations the generator should wait before moving to the next value in the list.
  • queryOnce - Defines if the before loop query is only queried once (Default = True).  If the Domain is a child Domain, then each time the parent traverses the child, the query will be executed unless the value equals False.
  • random - Randomizes the list of queried values when True and not when False (Default = False).
  • seed - Defines an optional seed for randomization. Using the seed will ensure that the same random data is generated each time data is generated as long as the values retrieved from the query are the same.
  • grVar1 - The first of five possible variables to use in the SQL select statement.
  • grVar2 - The second of five possible variables to use in the SQL select statement.
  • grVar3 - The third of five possible variables to use in the SQL select statement.
  • grVar4 - The fourth of five possible variables to use in the SQL select statement.
  • grVar5 - The fifth of five possible variables to use in the SQL select statement.
  • query - Defines the SQL select statement to run.
  • list - Defines a list of values that will be used for preview purposes only.  Three values are all that is necessary for preview data.


Example Story

Actors

  • Tester - The QA Tester that wants to generate the test data.
  • User - The object of data needed to be synthetically generated.

Key Components

  • employee table - A database table that contains user information.
  • externalId - A value, safe for public access, that uniquely identifies a User in the Employee table.

Plot

  • A Tester wants to retrieve a User's actual age, from the employee production database table, while merging the age value with other synthetically generated values like the user's first name, last name, address, city, state, and zip code.

  • In order for the Tester to attain a User's age from the Employee Table, the Tester must first retrieve the first 1000 User externalIds from the Employee Table using the QueryBeforeLoopV2Gen Generator.

  • Then, on each iteration, as data is synthetically generated for each User, the Tester will use the QueryEachLoopV2Gen Generator to retrieve the User's age from the employee table for the given externalId.

  • The Tester must also define a configuration properties file on their local computer. This file will contain the JDBC connection information to an SQL database. 
    • The Tester will define their base directory, that stores configuration files on their local computer, in the global resource named, #{resource.jdbc.directory}.

    • The Tester will create a subdirectory called, employee, under the base directory.

    • Within the employee subdirectory, the Tester will create a file called, config.properties, that will contain the JDBC connection information to the database.

           

             Note:  To learn more about the steps above, please see Lesson 6: Setting Up Your JDBC Resource.


JDBC config.properties File

In the following example, the config.properties file contains the properties for connecting to a MySQL database called: acme.

#
# JDBC database connection
#
driver=com.mysql.jdbc.Driver
user=root
password=admin
url=jdbc:mysql://localhost:3306/acme
batchCount=10000


Employee Domain

This is a preview of the User Domain data on the GenRocket web platform.


  • Within the GenRocket Web Platform, the Tester will create a new Project.

  • Within the Project,  the Tester will need to create a Domain called, employee.

  • The Domain will contain the following Attributes with each associated to the following Generators:
    • id  - RangeGen
    • externalId - QueryBeforeLoopV2Gen
    • age - QueryEachLoopV2Gen
    • lastName - NameGen
    • firstName - NameGen
    • address - AddressGen
    • city - USStateCapitalGen
    • state - USStateAbbrevGen
    • zipCode - USStateCapitalZipCodeGen


Attribute externalId - QueryBeforeLoopV2Gen

The following provides a visual example of how the Generator configuration will appear for the externalId Attribute and is based on the given usage example.



Note: Please remember that the above image is based on the given example and certain parameters may need to be different depending on your needs.


Attribute age - QueryEachLoopV2Gen

The following provides a visual example of how the Generator configuration will appear for the age Attribute and is based on the given usage example. 


The age Attribute uses the QueryEachLoopV2Gen Generator and the image below shows the parameter configuration necessary for this example.  For more information about this Generator, please see How Do I Use the QueryEachLoopV2Gen Generator



Note: Please remember that the above image is based on the given example and certain parameters may need to be different depending on your needs.