Aleksey Merzlyakov is an experienced QA Engineer with 5+ years of experience in Web and Software QA testing.

Sergey Horban (pictured) is a QA Engineer with 3+ years of experience in automation and manual testing, bug-tracking and test management.

Learn JMeter in 5 Hours

Start Learning
Slack

Run massively scalable performance tests on web, mobile, and APIs

Sep 24 2017

How to Initialize Database Variables and Assign Them Values in JMeter

A variable is an information storage element (for example, storing numeric values, strings, etc.) whose value can be changed. In order to create a variable, it must be declared (specified a name and the type of data stored) and initialized (assigned a value). Creating a variable and assigning a value to it, is important for writing test cases that use database queries. This is because we need get data from the database and also use the values during the execution of test cases.

 

In our last blog post, we created and asserted a basic data configuration to our MySQL database with Apache JMeter™. Now, we are ready to move on to more advanced scenarios. In this blog post we will learn to initialize variables and assign values to them, in a database. We will do this for one Thread Group.

 

Let’s get started.

 

Suppose that we have an API that as an input parameter (for example, the "address" parameter) that takes the unique value of the client's address and returns data about the payments made by it. The API takes the payment information from the database.

 

Now let’s say we want to execute the following test scenario:

  • Obtaining the client's ID and the unique value of the customer's address (customer_id, address) in the Customer Table and assigning the data to the variable.
  • Getting a list of payments from the Payment Table according to the value of customer_id.
  • Running an API with a variable that stores the unique value of the client's address
  • Comparison of the data that the service returned to what is in the database

 

API requirements:

  • The verification of the existence of a unique client address value is checked in the Customer Table, where address = address_id
  • The unique value of the customer address, in the Customer Table, defines a unique customer number (customer_id)
  • The value of the customer_id in the Payment Table shows the payments made by the customer
  • If the payment exists, the API displays all the data from the Payment Table
  • If there are no payments, the API returns an empty response

 

Customer Table:

 

how to initialize variables in jmeter

 

Payment Table:

 

assign values to variables in jmeter

 

Depending on the architecture that the tester created in JMeter, all four parts of the test case can be placed in one Thread Group, or each part can be placed in a separate Thread Group. This will determine how the client ID is assigned to the variable.

 

In this example we will run the test case in one Thread Group, through multiple JMeter elements. Now let’s build our test scenario in JMeter:

 

1. Add a Thread Group

 

Right Click -> Threads -> Thread Group

 

2. Configure the connection to the database as indicated in the previous article "MySQL Database and JMeter - How to Test Your Connection". 

 

3. In the Thread Group element, add 2 JDBC Request elements.

 

Right Click on Thread Group -> Add ->  Sampler -> JDBC Request

 

  • JDBC Request № 1 - will implement the first part of the test case
  • JDBC Request № 2 - will implement the second part of the test case

 

variables in jmeter

 

4. Add a BeanShell Assertion element to JDBC Request №1

 

Right Click on JDBC Request -> Add -> Assertions -> BeanShell Assertion

 

working with variables in jmeter

 

5. Add the View Results Tree element, which allows you to display the responses received from the database.

 

Right Click on Test Plan -> Add -> Listener -> View Results Tree

 

initializing variables and assigning them values on jmeter

 

6. Now, perform a customer search in the Customer Table through an SQL Query.

 

I used this code in the first JDBC Request:

 

select customer_id, address_id  
from customer 
where address_id is not null
and customer_id is not null
limit 5

 

  • select customer_id, address_id - output fields from the Customer Table only
  • customer_id, address_id - the remaining fields are not in the output.
  • where address_id is not null and customer_id is not null - selects only the lines in which the value for the fields address_id and customer_id is filled
  • limit 5 - displays only the first 5 rows

 

jmeter variables, values

 

7. Here are the results in the View Results Tree listener:

 

jmeter variable results

 

8. Now we will assign the value of the customer_id variable and assign the address_id of the variable in the BeanShell Assertion, to get the results of the specific customer we want.

 

This is the code I used:

 

if(ResponseCode.equals("200")) {

	if(vars.getObject("customer").size() != 0) {

		String IDClient = vars.getObject("customer").get(0).get("customer_id") + "";
		vars.put("IDClient",IDClient);

		String address = vars.getObject("customer").get(0).get("address_id") + "";
		vars.put("address",address);

		log.info(" Unique value of customer address:  " + address);
		log.info(" ID client:  " + IDClient);		
	
	} else {
	
		FailureMessage = "!!!!!!!!!!!! The response is empty !!!!!!!!!!!!!";
		Failure = true;	
		prev.setStopThread(true); 
	}	
	
} else {
	
	FailureMessage = "!!!!!!!!!!!! No connection to the database !!!!!!!!!!!!!";
	Failure = true;	
	prev.setStopThread(true); 
}

 

Let's take a closer look at this line of code:


String IDClient = vars.getObject("customer").get(0).get("customer_id") + ""

 

  • When the database sends a response, all the data received in the image below is written to the variable "customer".

 

variables, jmeter, values

 

  • The method vars.getObject ("customer") gets the value of the variable "customer", which is the same structure as in the image
  • The get (0) method gets the first string from the value of the variable (in our case this is a string where customer_id = 1 and address_id = 5). The method get (int a) as the value of the parameter a takes the sequence number of the line starting with "0" (That is, the serial number for the first line will be "0", for the second "1", etc.)

 

initializing variables in jmeter

 

  • The get ("customer_id") method from the string received by the get (0) method gets the value for the address_id field. In our case, this value is equal to "5". The get (String name) method, as the value of the parameter name, takes the name of the column from which you want to get the value

 

JMeter variables, how to execute

 

  • The value obtained by the get ("customer_id") method is assigned to the String IDClient variable. Thus, the value of the IDClient variable will be 5. Similar operations are performed to assign the value of the variable address.

 

Note that the put() method from the JMeterVariables class is used to create a variable available in any JMeter element within the same Thread Group.

 

jmeter variables open source load testing

 

9. Now we will configure the script so we get payment information from the Table Payment through the IDClient variable.

 

This is the code I used in the second JDBC Request:

 

select *
from payment 
where customer_id = "${IDClient}"

 

load testing variables jmeter

 

10. The IDClient variable is converted to a value at the time the request is submitted to the Payment Table.

 

assigning values to variables on jmeter

 

11. View the results of receiving all IDClient = customer_id payment from the Payment Table.

 

performance testing, variables, jmeter

 

Let's see what happens if you do not use the put() method after assigning the value of the variable IDClient.

 

This is the example code:

 

Example code: 

if(ResponseCode.equals("200")) {

	if(vars.getObject("customer").size() != 0) {

		String IDClient = vars.getObject("customer").get(0).get("customer_id") + "";
		

		String address = vars.getObject("customer").get(0).get("address_id") + "";
		vars.put("address",address);

		log.info(" Unique value of customer address:  " + address);
		log.info(" ID client:  " + IDClient);		
	
	} else {
	
		FailureMessage = "!!!!!!!!!!!! The response is empty !!!!!!!!!!!!!";
		Failure = true;	
		prev.setStopThread(true); 
	}	
	
} else {
	
	FailureMessage = "!!!!!!!!!!!! No connection to the database !!!!!!!!!!!!!";
	Failure = true;	
	prev.setStopThread(true); 
}

 

open source, variables, jmeter, value assigning

 

We will get the payment information from the Payment Table:

 

select *
from payment 
where customer_id = "${IDClient}"

 

jmeter. variables

 

As seen in the image above, the IDClient variable was not converted to a value, because the put() method, which allows the variable to be used in other JMeter elements, was not used. Without the  put () method, the variable can be used only within the element we are using (in this case this is BeanShell Assertion).

 

By passing the IDClient variable to the log.info() method, it is converted to a value.

 

How to Initialize Database Variables and Assign Them Values in JMeter

 

If you plan to use a variable in one Thread Group, but in different JMeter elements, then you need to use the vars.put(String key, String value) method.

  • String key - the name of the variable to be used
  • String value - the value that will be used for the variable

 

To finish the creation of the test case in JMeter, all that remains is to send the address variable to the API request and compare the results.

 

That’s it! You now know how to initialize database variables and assign them values, when working with one thread group. Next time, we will show how to do this for multiple thread groups.

 

You can learn more about variables and other JMeter test configurations for free through our JMeter academy.

 

Click here to subscribe to our newsletter.

 

To check out BlazeMeter, which enhances JMeter, request a demo or just put your URL or JMX file in the box below, and your test will start in minutes.

Interested in writing for our Blog?Send us a pitch!

Your email is required to complete the test. If you proceed, your test will be aborted.