Dmitri Tikhanski is a Contributing Writer to the BlazeMeter blog.

Learn JMeter in 5 Hours

Start Learning
Slack

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

Oct 19 2015

Debugging JDBC Sampler Results in JMeter

When it comes to database load testing, in the majority of cases it’s enough just to execute a request or series of requests and measure the execution time. However, there may be some cases when you will need to access the query result, such as for building the following query, or when you use data from a database for load test parametrization instead of the CSV Data Set Config. The post will look at how to conduct such database load testing, using JDBC Sampler results.

 

NOTE: This article assumes that you already are familiar with The Real Secret to Building a Database Test Plan With JMeter, and it is not a problem for you to download an appropriate JDBC driver for the database, put it into a JMeter classpath, and configure database connection details and credentials. This guide is more advanced and it focuses on working with your SQL query results.

 

The result of an SQL query is presented in a table so the usual correlation with the Regular Expression Extractor won’t be that easy. Let’s examine what an JDBC query results look like and how they can be accessed in a JMeter test.

 

All SQL queries in this article are made against Chinook Database version 1.4, which is a sample database representing an online music and media store. If you want to replicate them, download and import that sample database to your server.

 

Let’s start with a very simple JMeter Test script:

 

 

Where:

 

  • JDBC Connection Configuration specifies the database URL, credentials, etc.
  • JDBC Request is the request itself with input and output options
  • Debug Sampler and View Results Tree listener are for responses and variables visualization

 

Now we need to fetch some data from the database. Three artist/album combinations should be enough. This is how the relevant tables look like in the database:

 

 

Here is how it appears in the database client utility:

 

 

The same query executed via the JDBC Request sampler produces the same content, but the layout is not as good for interpreting the results and is very tricky to parse with regular expressions.

 

 

The JDBC Request sampler provides two options to access the result set values:

 

  1. Variable Names
  2. Result Variable Name

 

Variable Names are presented in a list of either a single variable or comma-separated variables. If specified for SELECT statements, it holds the values from the relevant result set columns. If being used for a CALLABLE statement (invoking a stored procedure), it holds the values per OUT parameters.

 

The Result Variable Name stores the result set as an ArrayList, and the values are accessible via Beanshell scripting.

 

So let’s put “artist, album” into the Variable Names and “result” into the Result Variable Name:

 

 

As you can see below, it is possible to observe the variable values via the Debug Sampler:

 

 

So given the above result set table, it’s easy to identify the mapping between the result set values and the relevant JMeter variables:

 

 

You can either access the  values directly, such as:

 

  • ${artist_1} - for “AC/DC”
  • ${album_1} - for “Let There Be Rock”
  • and so on...

 

Or use the ForEach Controller to iterate the results:

 

 

 

Regarding working with the Result Variable Name, as mentioned previously, it is an ArrayList of HashMaps, one map per result set row. The map entries count depends on the column values, the map entry name will be the same as the result set column name, and the map entry value will be the value itself.

 

  • result (ArrayList)
    • HashMap (1st row)
      • Map Entry  1
        • Key: Album
        • Value: Let There Be Rock
      • Map Entry 2
        • Key: Artist
        • Value: AC/DC
    • HashMap (2nd row)
      • Map Entry 1
        • Key: Album
        • Value: A Copland Celebration, Vol. I
      • etc.
         

 

An example of iterating the result set using Beanshell PostProcessor:

 

 

Now you should know enough to conduct an end-to-end database load test or use JDBC test elements for test parametrization.

 

As always, if you have any questions or feedback, leave it in the comments section below.

 

Experienced JMeter users, do you want more? You'll want to view the on-demand webcast, How to Create Advanced Load Testing Scenarios with JMeter
 

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.