SQL variable

NeoLoad: SQL variable

In NeoLoad, a type of variable called “SQL” is used to read test data from a database via an SQL query. When you have a database and a complete set of test data, this option is very beneficial. Furthermore, during the

compilation and test execution of the script, make sure that the database can be evaluated from the test environment. Variables of type ‘SQL’ need to know how to write SQL, which is an additional skill. Consult a DBA for help if you don’t have experience writing SQL.

In effect, the SQL variable executes the desired query and database request when the test script is executed. The query results (test data) are saved to a short-running CSV file. The SqlTemp subfolder of the project folder is where the temporary CSV file is stored. Note that each time the scenario is started, the content of the file changes.

The ‘SQL’ variable type is specifically used to retrieve test data dependent or independent of a database, such as a name, address, ID, account number, order number, etc.

The ‘SQL’ variable type has the advantage of meeting the needs of the massive test data available from the database. Just create the necessary SQL code to retrieve the test data.

To avoid test failure scenarios, it is recommended to verify the database connection and test data count before starting the test. Although millions or billions of table rows can be retrieved, it is still a good practice to include the amount of data in the query.

Variable input of type ‘SQL’

1. Name: specify the name of the variable to be used in the script. This entry is required.
2. Description: To explain the function of the variable. This entry is not required.
3. Database configuration: To set the parameter related to the database:
   1. Controller – Depending on your system, choose the database from the list.
      • MySQL (Guided Mode)
      • Oracle (Guided Mode)
      • DB2 (Guided Mode)
      • PostgreSQL (Guided Mode)
      • Microsoft SQL (Guided Mode)
      • Custom (Manual Mode)
   2. Driver class: This input field is available only for Custom driver
   3. URL: This input field is available only for Custom driver
   4. Host: To provide the host detail of the database
   5. Port: To provide the port detail of the database
   6. Database: Name of the database
   7. Login: The user name for the database authentication
   8. Password: The password for the database authentication
   9. Query: Here, you can write the query to fetch the data from the specified database
   10. Test: This is a button to test the database connection and the output of the SQL query.
4. Value change strategy: to specify the time to select the next row (with data),
   1. On each use
   2. On each request
   3. On each page
   4. On each iteration (Default value)
   5. For each Virtual User instance
5. Variable Value Distribution Policy – to specify the scope and distribution settings of the variable.
   1. Scope: The options are as follows:
      • Local
      • Global (Default Value)
      • Unique
   2. Order: The options are as follows:
      • Sequential
      • Random
      • Any (Default Value)
   3. By challenging values: the options are as follows:
      • Cycle values (Default Value)
      • Stop the test
      • Return the value “<No Values>”

Example

To authenticate a user and grant access to the application, a login page contains Username and Password fields. Now we need to use the ‘SQL’ type variable to parameterize the Username and Password fields. First, we must choose the station or database and enter all the relevant information. Now create a query that retrieves the list of usernames and passwords from the t_credential table in the database and stores it in a temporary CSV file. The ‘userName’ and ‘passWord’ variables in the script are used to evaluate the username and password, respectively. ‘Value Change Policy’ is set to ‘every iteration’ with global scope, order type ‘Any’, and cyclical when data runs out. Consider the following