Description

The QueryReGenerateOnHitGen allows regenerating the value if the referenced value exists in the database table. The main idea behind this Generator is to generate unique values that do not exist in the database table.

This generator allows querying a column within a database table, using an SQL select statement, on EACH iteration of test Generation and validates whether the referenced value exists or not. If the value exists, then Generator calls the Generator of referenced value to regenerate the next value, which is again validated for its existence.

Select statements, at this level, should only retrieve one row in the result set.  Even if the result set retrieves more than one row, the Generator will only return the first row. The value must be either 1 or true if the value exists in the table and 0 or false if the value doesn't exist.

Restrictions

• The 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.
• grRef - Defines the reference to the Generator within the same Attribute that needs to be checked into the database table. It must be a reference to Linked Generator otherwise Generator will throw an error.
• grVar1 - Defines a first of five possible variables to use in the SQL select statement.
• grVar2 - Defines a second of five possible variables to use in the SQL select statement.
• grVar3 - Defines a third of five possible variables to use in the SQL select statement.
• grVar4 - Defines a fourth of five possible variables to use in the SQL select statement.
• grVar5 - Defines a fifth of five possible variables to use in the SQL select statement.
• maxTryCount - Defines a numeric value that tells how many times this Generator will query to check if a value exists or not. For example, if GenRocket could not generate a unique value after trying 100 times, then GenRocket will throw an error.
• 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 are necessary for preview data.

Example Story

Actors

• Tester -  The QA Tester that wants to generate the Unique SSN that does not exist in the table User.
• user - The object of data needed to be synthetically generated.
  
  

Key Components

• user table - A database table that contains SSN along with other user information.

Plot

• A Tester wants to generate the SSN that does not exist in the User table along with other synthetically generated values like the user's first name, last name, address, city, state, and zip code.
  
  
• Then, on each iteration, as data is synthetically generated for each user, the Tester will use the QueryReGenerateOnHitGen Generator to check if the generated SSN exists in the table or not.
  
  
• 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, which stores configuration files on their local computer, in the global resource named, #{resource.jdbc.directory}.
    
    
  • The Tester will create a subdirectory called, user, under the base directory
    
    
  • With 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

Attribute ssn - QueryReGenerateOnHitGen

The following provides a visual example of how the Generator configuration will appear when the necessary parameters have been configured for the given 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.