Querying a MySQL database with LoadRunner

Let's imagine that you want to execute arbitary SELECT, INSERT, UPDATE and DELETE queries against a MySQL database from a VuGen script. Obviously it is easiest to use the JDBC libraries from a Java-based script, but most people aren't licensed for any of the Java-based vuser types.

It is much more useful to be able to interact with MySQL from a C-based vuser script, such as the Web (HTTP/HTML) vuser type. JDS has already released code that allows you to use MySQL instead of the Virtual Table Server from a C-based script, but this code will allow you to run any query you like.

Assuming that you have already installed MySQL and created a user, you must then create a database schema and table(s) to use.

CREATE DATABASE `loadrunner` ;

USE `loadrunner`;

CREATE TABLE `test_data` (
`order_id` BIGINT UNSIGNED NOT NULL COMMENT 'Order numbers. Must be unique.',
`status` BOOL NOT NULL DEFAULT '0' COMMENT 'Whether data has been used or not. A value of 0 means FALSE.',
`date_used` DATETIME NULL COMMENT 'Date/time that the data was used.',
UNIQUE (
`order_id`
)
) ENGINE = innodb COMMENT = 'LoadRunner test data';

Now you are free to talk to the database from your VuGen script. Here is the example code:

// The MySQL 5.0 C API documentation is available from:
// http://dev.mysql.com/doc/refman/5.0/en/c-api-functions.html

// Note that this code may have problems with thread safety. 
// It is therefore best to run each vuser as a process rather than as a thread.

Action()
{
  int rc; // return code
  int db_connection; // Declaractions is a bit dodgy. Should really use MYSQL defined in mysql.h
  int query_result; // Declaractions is a bit dodgy. Should really use MYSQL_RES defined in mysql.h
  char** result_row; // Return data as array of strings. Declaractions is a bit dodgy. Should really use MYSQL_ROW defined in mysql.h

  char *server = "localhost";
  char *user = "root";
  char *password = ""; // very naughty to leave default root account with no password :)
  char *database = "loadrunner";
  int port = 3306; // default MySQL port
  int unix_socket = NULL; // leave this as null
  int flags = 0; // no flags

  // You should be able to find the MySQL DLL somewhere in your MySQL install directory.
  rc = lr_load_dll("C:\\LoadRunner\\Lib\\libmysql.dll");
  if (rc != 0) {
    lr_error_message("Could not load libmysql.dll");
    lr_abort();
  }

  // Allocate and initialise a new MySQL object
  db_connection = mysql_init(NULL);
  if (db_connection == NULL) {
    lr_error_message("Insufficient memory");
    lr_abort();
  }

  // Connect to the database
  rc = mysql_real_connect(db_connection, server, user, password, database, port, unix_socket, flags);
  if (rc == NULL) {
    lr_error_message("%s", mysql_error(db_connection));
    mysql_close(db_connection);
    lr_abort();
  }

  // INSERT a row into the database table
  lr_param_sprintf("paramInsertQuery", "INSERT INTO test_data (order_id) VALUE (%d)", time(NULL)); // use current time as order ID for this example
  rc = mysql_query(db_connection, lr_eval_string("{paramInsertQuery}"));
  if (rc != 0) {
    lr_error_message("%s", mysql_error(db_connection));
    mysql_close(db_connection);
    lr_abort();
  }

  // SELECT a single value from the database table, and print the result
  rc = mysql_query(db_connection, "SELECT order_id FROM test_data WHERE status IS FALSE LIMIT 1");
  if (rc != 0) {
    lr_error_message("%s", mysql_error(db_connection));
    mysql_close(db_connection);
    lr_abort();
  }
  query_result = mysql_use_result(db_connection);
  if (query_result == NULL) {
    lr_error_message("%s", mysql_error(db_connection));
    mysql_free_result(query_result);
    mysql_close(db_connection);
    lr_abort();
  }
  result_row = (char **)mysql_fetch_row(query_result); // if the result set had multiple rows, we could keep calling mysql_fetch_row until it returned NULL to get all the rows.
  if (result_row == NULL) {
    lr_error_message("Did not expect the result set to be empty");
    mysql_free_result(query_result);
    mysql_close(db_connection);
    lr_abort();
  }
  lr_save_string(result_row[0], "paramOrderID"); // this parameter will be used when deleting the row.
  lr_output_message("Order ID is: %s", lr_eval_string("{paramOrderID}"));
  mysql_free_result(query_result);

  // SELECT and UPDATE a row in the same step (to avoid concurrency problems if more than 1 vuser is consuming this data).
  // Note that for transactions to work, your MySQL database must use the InnoDB engine.
  rc = mysql_query(db_connection, "BEGIN"); // begin the transaction
  if (rc != 0) {
    lr_error_message("%s", mysql_error(db_connection));
    mysql_close(db_connection);
    lr_abort();
  }
  rc = mysql_query(db_connection, "SELECT order_id FROM test_data WHERE status IS FALSE LIMIT 1 FOR UPDATE"); // note that "FOR UPDATE" locks the record for reading, so other vusers will not get this value.
  if (rc != 0) {
    lr_error_message("%s", mysql_error(db_connection));
    mysql_close(db_connection);
    lr_abort();
  }
  query_result = mysql_use_result(db_connection);
  if (query_result == NULL) {
    lr_error_message("%s", mysql_error(db_connection));
    mysql_free_result(query_result);
    mysql_close(db_connection);
    lr_abort();
  }
  result_row = (char **)mysql_fetch_row(query_result); // if the result set had multiple rows, we could keep calling mysql_fetch_row until it returned NULL to get all the rows.
  if (result_row == NULL) {
    lr_error_message("Did not expect the result set to be empty");
    mysql_free_result(query_result);
    mysql_close(db_connection);
    lr_abort();
  }
  lr_save_string(result_row[0], "paramOrderID"); // this parameter will be used when deleting the row.
  lr_output_message("Order ID is: %s", lr_eval_string("{paramOrderID}"));
  mysql_free_result(query_result);
  lr_param_sprintf("paramUpdateQuery", "UPDATE test_data SET status=TRUE, date_used=NOW() WHERE order_id='%s'", lr_eval_string("{paramOrderID}"));
  rc = mysql_query(db_connection, lr_eval_string("{paramUpdateQuery}")); // UPDATE row to indicate that data has been used
  if (rc != 0) {
    lr_error_message("%s", mysql_error(db_connection));
    mysql_close(db_connection);
    lr_abort();
  }
  rc = mysql_query(db_connection, "COMMIT"); // commit the transaction
  if (rc != 0) {
    lr_error_message("%s", mysql_error(db_connection));
    mysql_close(db_connection);
    lr_abort();
  }

  // SELECT a row from the table (this returns an empty record set if table was empty at the beginning)
  rc = mysql_query(db_connection, "SELECT order_id FROM test_data WHERE status IS FALSE LIMIT 1");
  if (rc != 0) {
    lr_error_message("%s", mysql_error(db_connection));
    mysql_close(db_connection);
    lr_abort();
  }
  query_result = mysql_use_result(db_connection);
  if (query_result == NULL) {
    lr_error_message("%s", mysql_error(db_connection));
    mysql_free_result(query_result);
    mysql_close(db_connection);
    lr_abort();
  }
  result_row = (char **)mysql_fetch_row(query_result); // if the result set had multiple rows, we could keep calling mysql_fetch_row until it returned NULL to get all the rows.
  if (result_row == NULL) {
    lr_output_message("Result set is empty as expected");
    mysql_free_result(query_result);
  } else {
    lr_error_message("Did not expect the result set to contain any rows");
    mysql_free_result(query_result);
    mysql_close(db_connection);
    lr_abort();
  }

  // DELETE a row from the table
  lr_param_sprintf("paramDeleteQuery", "DELETE FROM test_data WHERE order_id = '%s'", lr_eval_string("{paramOrderID}"));
  rc = mysql_query(db_connection, lr_eval_string("{paramDeleteQuery}"));
  if (rc != 0) {
    lr_error_message("%s", mysql_error(db_connection));
    mysql_close(db_connection);
    lr_abort();
  }

  // Free the MySQL object created by mysql_init
  mysql_close(db_connection);

  return 0;
}

I hope you find this useful. If you find any bugs in the code, please leave a comment.