NeoLoad – SQL Variable - How to fetch test data from Database |

‘SQL’ is a type of variable in NeoLoad to read the test data from a database through SQL query. This option is very helpful when you have a database along with a full set of test data. Also, you have to make sure that the database can be assessed from the test environment during the script creation and test run. ‘SQL’ type variable requires an additional skill which is SQL writing. If you are not proficient in writing SQL queries then take help from DBA.

Actually, when you run the test script, the SQL variable executes the specified query and request to the database. The outcome of the query (test data) is stored in a temporary CSV file. The temporary CSV file is placed in the project directory SqlTemp subdirectory. Please note that the content of the file is updated each time when the scenario is launched.

The specific use of the ‘SQL’ variable type is to extract the dependent or independent test data from a database like a name, address, ID, account number, order number etc.

The advantage of the ‘SQL’ variable type is that it meets the requirement of the bulk test data which is available in the database. You can simply write the SQL query and fetch the test data.

It is recommended to validate the DB connection and test data count before launching the test to avoid test failure conditions. Also, it is good practice to provide the data count in the query despite of fetching the full rows among millions or billions of table rows.

Inputs of ‘SQL’ type variable

Name: To provide the name of the variable to be used in the script. This is a mandatory input.
Description: To describe the use of the variable. This is an optional input.
Database configuration: To configure the database-related setting:
1. Driver: Select the database from the list as per the system
  1. MySQL (Guided Mode)
  2. Oracle (Guided Mode)
  3. DB2 (Guided Mode)
  4. PostgreSQL (Guided Mode)
  5. Microsoft SQL (Guided Mode)
  6. 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 details 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.
Value change policy: To define the setting when the next row (data) will be picked
1. On each use
2. On each request
3. On each page
4. On each iteration (Default value)
5. For each Virtual User instance
Variable values distribution policy: To define the scope and distribution setting of the variable
1. Scope: The available values are:
  1. Local
  2. Global (Default Value)
  3. Unique
2. Order: The available values are:
  1. Sequential
  1. Random
  2. Any (Default Value)
3. When Out of Values: The available values are:
  1. Cycle values (Default Value)
  2. Stop the test
  3. Return the value “<No Values>”

Example

Consider, that a login page has User Name and Password fields to authenticate a user and give access to the application. Now, to parameterize the User Name and Password fields, we have to use the ‘SQL’ type variable. First of all, we need to select the drive i.e. Database and provide all the necessary details. Now, write a query that fetches the list of user names and passwords from the database (table name is t_credential) and saves it into a temporary CSV file. The username and password will be assessed in the script through the ‘userName’ and ‘passWord’ variables respectively. The ‘Value change policy’ is set to ‘on each iteration’ with Global scope, ‘Any’ order type and cyclic manner when data exhausts. Refer to the below screenshot of how this scenario will be illustrated.