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 to 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:

Payment table:

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:

Add a Thread Group (right-click > Threads > Thread Group).

Configure the connection to the database as indicated in the previous article.

In the Thread Group element, add two 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.

Add a BeanShell Assertion element to JDBC Request №1 (right-click on JDBC Request > Add > Assertions > BeanShell Assertion).

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).

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 five rows.

Here are the results in the View Results Tree listener:

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".

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 it will be 1, etc.)

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.

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}"

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

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

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); 
}

We will get the payment information from the Payment Table:

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

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.

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.