Querying a MySQL database with LoadRunner

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';

// 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;
}