Persistent data in VuGen with MySQL

One of the main drawbacks with VuGen is the inability to easily store data for later retrieval, and the lack of interscript communication. I had this exact problem on a recent assignment, the problem was that the business process involved a batch process that needed to run before the rest of the business process could continue. The business scenario looked like this:

• Create new application data. ** PRODUCER **
• Wait for batch or manual process to process the new application.
  (this batch process may create a delay of several hours, and is not part of the load testing scope).
• Perform transactions on the application (using lookup data from step 1). ** CONSUMER **

To achieve this business scenario, I created two distinct scripts, a producer script to handle step 1 and a consumer script to handle step 3. I now needed the ability to pass data from script 1 to script 2 in a persistent manner so that a buffer existed which allowed sufficient delay for the batch process to execute.

For this to work, I wrote a Loadrunner add-in library in Visual C that exposed a FIFO (First In, First Out) queue to VuGen using a MySql database. I used a queue structure so that the producer can add items to the queue, and the consumer can pick them up off the queue.

Setting Up Your MySql Environment

Download and configure the MySql database, I used MySql Server 5.0 so I recommend that particular version. Here are a couple of tips for configuring the MySql environment:

• I recommend installing the mysql database on the Loadrunner Controller machine. Not a requirement, but it is a nice central place for the database to be installed, certainly MySql doesn't interfere with LoadRunner in any way.
• Make sure that you enable networking in the my.ini file this is done by commenting out the skip-networking line.
• Ensure that the max-connections setting is set to a value a bit higher than the number of VUsers you plan to run.

Next create a new database and table structure for the VuGen data, to make this easy copy and paste the following into a mysql command line: mysql -uroot -p

create database vugen;

use vugen;

create table runtime_data
  ( name varchar(50) not null,
    value varchar(4000) not null,
    flag int default 0 not null,
    timestamp datetime not null);

alter table runtime_data
    add primary key (name, flag, timestamp);

exit

That's it. You can test the everything is set up correctly by running the following windows command:
mysql -uroot -p -Dvugen -e "select name,value,flag,timestamp from runtime_data limit 0"

Setting up VuGen (and the Load Generators).

This part is easy, the only prequiste is that you have installed VuGen and the Load Generators into their default installation location (C:\Program Files\HP\LoadRunner).

Simply download and run the following setup file which contains the JDS MySql Library.
https://www.jds.net.au/files/JDSMySqlLibrary.msi

Using the JDS MySql Library.

Now this where the real magic happens.

Before progressing any further you must set a property in Runtime Settings to run the VUser as a process. Unfortunately, the JDS MySql library is not thread-safe yet (unfortunately, there is a tradeoff between thread-safety and ease of use...the later won).

To expose the new functions into MySql you must add the following function into the vuser_init section of your script.

lr_load_dll("JDSMySqlLibrary.dll");

Now you're ready to use the MySql functionality. As mentioned at the top of this post, this library exposes queue functionality, rather than the ability to run specific SQL commands. So the SQL code itself is hidden away in the MySql library code itself, and doesn't need to be factored into your script. Easy eh!

The JDS MySql Library Function Reference.

Life Cycle Overview.

There are four functions within the JDS MySql Library, these functions form part of a lifecycle. How you integrate this lifecycle into your script requirements.

The life cycle is:

• Connect to the MySql Database

  int jds_mysql_connect(char *host_name, char *user_name, char *password, char *db_name, int port_num)

• Insert new data into the queue.

  int jds_mysql_insert(char *name, char *value, int status)

• Retrieve the data, and update the status.

  char* jds_mysql_retrieve(char *name, int old_status, int new_status)

• Retrieve the data, and update the status.

  char* jds_mysql_retrieve(char *name, int old_status, int new_status)

• Disconnect from the Database.

  int jds_mysql_disconnect()

You will notice two particular things here, firstly, the data is inserted with a status. And that we retrieve the data twice. This allows a piece of data to transistion from state to state (or status to status), following the business process.

For example, A customer creates a new airline booking (Status 0), the airline confirms the booking (Status 1), the customer checks in at the airport (Status 2), the customer boards the plane (Status 3), the customer arrives at his/her destination (Status 4). Each one of these states can be handled by a different script at different times.

jds_mysql_connect

Connects to the MySql database using the credentials specified.

int jds_mysql_connect(char *host_name, char *user_name, char *password, char *db_name, int port_num)

Returns 0 on success, otherwise error.

host_name - The network host name of the MySql Server. Note that while, localhost, will might work fine for VuGen it won't necessarily work for your load generators.
user_name - The user name of the MySql user. Normally "root".
password - The password you configured for the MySql user.
db_name - If you used the above script to configure your MySql environment, then this should be "vugen".
port_num - The MySql server port number. Normally 3306.

Example:

jds_mysql_connect("nicks_pc", "root", "mypass", "vugen", 3306);

I recommend enclosing the connect and disconnect statements immediately before and after the function statements, rather then adding them to the vuser_init and vuser_end sections.

jds_mysql_disconnect

Disconnects from the MySql database.

int jds_mysql_disconnect()

Returns 0 on success, otherwise error.

I recommend enclosing the connect and disconnect statements immediately before and after the function statements, rather then adding them to the vuser_init and vuser_end sections.

jds_mysql_insert

Inserts a new name/value pair into the queue.

int jds_mysql_insert(char *name, char *value, int status)

Returns 0 on Success, otherwise error.

name - The name from the name/value pair.
value - The value from the name/value pair.
status - The initial status for this name/value pair.

Example:

jds_mysql_insert("bookingId", "3443364", 0)

jds_mysql_retrieve

Retrieve the next name/value pair from the queue, and set the status.

char* jds_mysql_retrieve(char *name, int old_status, int new_status)

Returns a string with the value, or NULL is no value is found.

name - The name from the name/value pair.
old_status - The status to retrieve.
new_status - The new status to set.

Example:

jds_mysql_retrieve("BookingId", 0, 1);

Note, that you must set the status. If you use the same status value from old_status and new_status this has the effect of "peeking" at the queue.

Foot Note

When I get a chance I'll make a full example for this library. But in the meantime enjoy, and feel free to add any comments or ask any questions. I hope to develop this library further over time.