Database Variables

Applies to LoadComplete 4.97, last modified on May 20, 2019

You use database variables to retrieve values (logins, passwords, product IDs and so on) from databases and insert them into scenarios. Database variables are similar to list variables, but they are linked to a database field, not to a static list. Since the values are stored apart from your test project, there is no need to modify the project when you need to add or remove the values.

Each database variable also defines the order in which different virtual users access values from the list. For example, you can configure the variable so that each virtual user uses only one specific value from the record set. Or each virtual user could access the record set sequentially and take the next value every time it simulates a request to the server.

You can use database variables to specify query string parameters of GET requests, as well as the form fields sent via POST requests, for example:

GET /find/?first=@FirstName&last=@LastName HTTP/1.1

We recommend that you select a variable whose data type matches the parameter data type. If LoadComplete fails to convert the variable value to the parameter data type, the test server may raise an error.

Depending on the value access type specified for the list, different virtual users will be able to play back the parameterized request using different parameter values, for example:

GET /find/?first=John&last=Smith
GET /find/?first=Susan&last=McLaren
GET /find/?first=Charles&last=Dodgeson
and so on

This way, you will get more realistic behavior than that when using the same recorded value for all virtual users.

For more information about using variables in requests, see About Variables.

Tip: To retrieve values from Excel and CVS files, use Excel and CSV variables.

Parameters

You create and modify database variables using a wizard that assists you with filling in and verifying variable parameters. The wizard includes the following pages:

Specify Connection Settings and Data Source Page

On this page, you can specify the following parameters:

Name

The variable name, which will be used to refer to this variable in request parameters. The name can include only letters (A..Z, a..z), digits (0..9) or underscores ( _ ) and must be unique within the project.

Connection

The connection string that will be used to connect the variable to the database. You can type the connection string directly into the edit box, or click the ellipsis button to open the standard Data Link Properties dialog where you can visually specify the needed connection settings.

For a quick reference on connection strings for various database providers, see http://connectionstrings.com.

Note: The database connection string may contain computer-specific values. You need to keep this in mind if you are planning to move your test project to another computer.

For example, connection strings for Microsoft Access databases (.mdb) include the database file name. If you move the project to another computer that does not have the same database at exactly the same location, the connection string will point to a non-existent file and the database variable will not work.

To avoid the problem, use computer-independent connection strings. For example, in case of an Access database you could put it to a shared network folder (say, \\MyServer\SharedFolder\Database.mdb) and use the network file path in the connection string. If you move the project to another computer in your network, your database variable will remain valid because the database will be accessible from that computer as well.

Login and Password

The user name and password that will be used to connect to the database. In the test project files on the disk, they are stored in the encrypted form.

Select the data source type

Allows you to specify the source of the values to be fetched by the variable:

  • Table - The variable will retrieve values from a database table.

  • View - The variable will retrieve values from a view or query defined in the database.

  • Custom query - The variable will retrieve values by using a custom SQL query.

Enter SQL Query Page

This page is available only if you have selected the Custom query data source type on the Specify Connection Settings and Data Source page. On this page, you can specify the code of the SQL SELECT query that selects the needed field. For example:

SELECT ProductID FROM Products

Note: Since a database variable is associated with a single field of the result set, it is recommended that you use SELECT Field_Name rather than SELECT * to form the result set. This can improve the query performance as a smaller result set will be returned.

You can enter the SELECT query manually, paste it from the clipboard or click Design and use the SQL Designer to visually build the target query. You can also use the SQL Designer to test your query.

Select Database Object Page

On this page, you can select the table, view or query in the specified data source from which the variable will retrieve values:

Object

The table, view or query in the specified data source from which the variable will retrieve values. You can either type the name of the table, view or query in the Object box, or select it from the drop-down list.

Note: If LoadComplete fails to retrieve information on the fields that the specified table, view or query contains, it displays an error description returned by the database engine.

In some cases, the cause of the error is that the table or query name does not match the rules adopted by the database engine. For instance, the names of Microsoft SQL Server or Microsoft Access tables and queries may contain spaces and this is different from the rules adopted by classic SQL. So, you may need to change the name in order for LoadComplete to be able to retrieve data from the table or query. For SQL Server or Access tables and queries, enclose the table (or query) name in brackets, for example, use [My Query Name] instead of My Query Name.

Specify Column and Selection Type Page

On this page, you can associate the variable with a specific field in a record set and select a value access rule for the variable:

Column

The name of the record set field the variable will retrieve values from. You can either enter the field name manually, or select it from the drop-down list.

Value Access Type

Specifies the order in which the variable will return values from the record set:

  • Item index equals virtual user index.

  • Item index equals virtual user index within a station.

  • Next value on each use.

  • Next value on each use within a station.

  • Next value on each use by a virtual user.

  • Random.

For complete information on these settings, see Value Access Types.

Preview Data Page

On this page, you can view the values that are stored in the table or record set field you selected as a data source. When you run the load test, the variable will return individual values from this field according to the value selection rules you specified in the wizard earlier.

Remarks

A variable fetches all needed values from the specified database upon the test start:

  • External changes made to the  database during the test run will not affect the variable values.

  • If you simulate virtual users on remote stations or on cloud computers, you will not have to configure access to the database from them. Only the computer, from which you control the test run, must have access to the database.

See Also

Using External Data Sources
Excel Variables
CSV Variables
List Variables
Incremental Variables

Highlight search results