Connecting to a MySQL database with LoadRunner

The Virtual Table Server is great for most situations where your virtual users need a common data pool, but the limitations of the API mean that it is a bad fit in some cases.

Say you need to find a row in the data table with a specific value. On a real database you could just do a simple SELECT statement. With VTS, you would have to write code to iterate through every row in the table and check the column value each time.

So sometimes you need to use a real database. Interfacing with a real database is not much harder than interfacing with VTS (as the attached script demonstrates). You will spend more time setting up the database than writing code.

Interfacing with a real database will also allow you talk to the database your application uses (only do read-only operations, and be careful of any additional overhead you might introduce during your load test).

This example users the Java vuser type, and interfaces with a MySQL database. The beauty of JDBC is that to port this code to another database just requires changing the line of code that specifies the database driver (assuming that you have not used database-specific SQL code).

On with the example…

Setting up the database

1. Download and install MySQL.
2. Create a database
   mysql> CREATE DATABASE loadtest;
mysql> SHOW DATABASES;
mysql> USE loadtest;
3. Create a table
   mysql> CREATE TABLE message (time_sent BIGINT UNSIGNED, identifier VARCHAR(127));
mysql> SHOW TABLES;
mysql> DESCRIBE message;

4. Load data into the table
   mysql> INSERT INTO message VALUES ('1178858071111','asdf1234');
mysql> INSERT INTO message VALUES ('1178858071112','asdf1235');
mysql> INSERT INTO message VALUES ('1178858071113','asdf1236');
mysql> INSERT INTO message VALUES ('1178858071114','asdf1237');
mysql> INSERT INTO message VALUES ('1178858071115','asdf1238');
mysql> SELECT * FROM message;
5. Create a user account with access to the database.
mysql> GRANT ALL PRIVILEGES ON loadtest.* TO 'loadrunner'@'%' IDENTIFIED BY 'loadrunner' WITH GRANT OPTION;
Note username/password is loadrunner/loadrunner.
mysql> SELECT * FROM mysql.user where User = 'loadrunner';

Verifying external connectivity to the database

1. Download an SQL query tool to verify that you can successfully connect to the database.
   I recommend that you check out SQLeonardo.
2. Download the JDBC driver for MySQL.
   MySQL now produce their own JDBC driver (Connector/J)
3. Configure the SQL query tool.
   nickyb (the creator of SQLeonardo says)…

   Suppose you have downloaded and unzipped sqleonardo into c:\sqleonardo and the mysql jdbc driver into c:\sqleonardo\mysql

   Run SQLeonardo and into the “metadata explorer” do:
   – choose the menu “actions>new driver…”
   – check “add library (browse filesystem)” and click “next >”
   – select the jar file into c:\sqleonardo\mysql and click “next >”
   – type into the textfield named “name:” => MySQL
   – select into the combobox named “driver:” => com.mysql.jdbc.Driver
   – into “example:” => jdbc:mysql://[host][:port]/[database] – click ok.
   now you have registered the driver!

   Select the item “MySQL” appeared into the tree and:
   – choose the menu “actions>new datasource…”
   – replace jdbc:mysql://[host][:port]/[database] => jdbc:mysql://localhost:3306/
   – put username and password and click ok.
   now you have added your database profile.

   Select the item under “MySQL”…you need now to test the connection!

   So our database connection string would be jdbc:mysql://www.myloadtest.com:3306/loadtest (assuming the database is installed on the default port).

4. Test the connection.
   Try running SELECT * FROM message; in your SQL query tool.

Writing the code

1. Java.sql javadocs are here. There are numerous tutorials on the web, and you have my sample code (text, zipped). What more do you need?