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?

 

Published On: May 14, 2007Tags: ,

5 Comments

  1. Stuart Moncrieff May 15, 2007 at 10:14 am - Reply

    For those with C-based LoadRunner scripts…

    * http://www.ucl.ac.uk/is/mysql/c/
    * http://dev.mysql.com/doc/refman/5.0/en/c.html

  2. VK December 30, 2008 at 8:24 am - Reply

    “and you have my sample code (text, zipped). What more do you need?”

    – Where can I download the sample code ?

  3. Guruprem March 18, 2009 at 11:23 pm - Reply

    Hi Stuart,

    I have seen the contents and comments posted in ur site

    I have just started learning Load Runner… So can u please guide me with some notes that can help me to learn Load Runner…

    Thanks
    Guruprem

  4. Stuart Moncrieff March 29, 2009 at 1:58 pm - Reply

    Example code for a C-based vuser is available from: http://www.jds.net.au/tech-tips/query-mysql-with-loadrunner/

    Example code for a Java-based vuser is available from: http://90kts.com/blog/2008/using-jdbc-with-loadrunner-java-vusers/

  5. ankit September 23, 2015 at 11:45 pm - Reply

    Hi,
    I am using LR 11.52 and want to establish a connection and run some simple query to a MS SQL developer version 4.1.1.19
    Manually i am able to create a dummy table,update record in a table and drop tables but unable to figure out how to replicate it through VUGen.
    While creating a connection manually, i use the below information:
    server,username,password,database and port
    Can you please help me out?

Leave A Comment