How to run multiple MySQL statements via JDBC sampler in JMeter

Question

I am using JDBC sampler in JMeter 2.13.

I have around 100 delete statements in my JMeter sampler like below:

delete from abc where id >= ${Variable_Name};
delete from qwe where id >= ${Variable_Name};
delete from xyz where id >= ${Variable_Name};

Problem is that when I run a single statement in JDBC sampler, it works fine. But when ever I try to run 2 or more than 2 statements from my JDBC sampler. It always throws error.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delete from qwe where id >= 1;

Can someone please mention a workaround it? and how I can overcome this problem.

Answer 1

It seems you cannot execute multiple statements in a single JDBC Request element.

I had a similar situation where I needed to execute some clean up statements on the database before proceeding with the rest of the tests. I was able to achieve this by reading the SQL statements from an external file, using CSV Data Set Config nested in a Loop Controller, in a separate setUp Thread Group.

The elements were placed like this:

And I used the following configurations:

Loop Controller

• Loop Count: Forever

CSV Data Set Config

• Filename: /path/to/multiple-statements.sql
• Variable Name: STMT
• Recycle on EOF: False
• Stop thread on EOF: True

JDBC Request

• Query: ${STMT}

The Loop Controller is set to run forever, as the stop condition is set on the CSV Data Set Config. Each iteration will read one line of the file, set the variable STMT, then JDBC Request will execute the query ${STMT}.

When the end-of-file is reached, the setUp Thread Group will stop and the core test Thread Group will proceed.