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.
Tech tips from JDS

Browser Console
Read More

Glide Variables
Read More

Understanding Database Indexes in ServiceNow
Read More

Fast-track ServiceNow upgrades with Automated Testing Framework (ATF)
Read More

Read More

Splunk .conf18
Read More

ServiceNow Catalog Client Scripts: G_Form Clear Values
Read More

Is DevPerfOps a thing?
Read More

The benefits of performance testing with LoadRunner
Read More

Monitoring Atlassian Suite with AppDynamics
Read More

5 quick tips for customising your SAP data in Splunk
Read More

How to maintain versatility throughout your SAP lifecycle
Read More

How to revitalise your performance testing in SAP
Read More

Reserve and import data through Micro Focus ALM
Read More

How to effectively manage your CMDB in ServiceNow
Read More

ServiceNow and single sign-on
Read More

How to customise the ServiceNow Service Portal
Read More

Integrating a hand-signed signature to an Incident Form in ServiceNow
Read More

Integrating OMi (Operations Manager i) with ServiceNow
Read More

Implementing an electronic signature in ALM
Read More

Service portal simplicity
Read More

Learning from real-world cloud security crises
Read More

Static Variables and Pointers in ServiceNow
Read More

Citrix and web client engagement on an Enterprise system
Read More

Understanding outbound web services in ServiceNow
Read More

How to solve SSL 3 recording issues in HPE VuGen
Read More

How to record Angular JS Single Page Applications (SPA)
Read More

Calculating Pacing for Performance Tests
Read More

Vugen and GitHub Integration
Read More

What’s new in LoadRunner 12.53
Read More

Filtered Reference Fields in ServiceNow
Read More

ServiceNow performance testing tips
Read More

Monitor Dell Foglight Topology Churn with Splunk
Read More

Straight-Through Processing with ServiceNow
Read More

Splunk: Using Regex to Simplify Your Data
Read More

ServiceNow Choice List Dependencies
Read More

Tips for replaying RDP VuGen scripts in BSM or LoadRunner
Read More

Incorporating iSPI metric reports into MyBSM dashboard pages
Read More

Using SV contexts to simulate stored data
Read More

What’s new in LoadRunner 12.02
Read More

Recycle Bin for Quality Center
Read More

LoadRunner Correlation with web_reg_save_param_regexp
Read More

LoadRunner 11.52
Read More

QC for Testers – Quiz
Read More

Agile Performance Tuning with HP Diagnostics
Read More

What’s new in HP Service Virtualization 2.30
Read More

Understanding LoadRunner Virtual User Days (VUDs)
Read More

Problems recording HTTPS with VuGen
Read More

Improving the management and efficiency of QTP execution
Read More

Performance testing Oracle WebCenter with LoadRunner
Read More

Generating custom reports with Quality Center OTA using Python
Read More

Asynchronous Communication: Scripting For Cognos
Read More

How to fix common VuGen recording problems
Read More

Monitoring Active Directory accounts with HP BAC
Read More

URL Attachments in Quality Center
Read More

What’s new in LoadRunner 11.00?
Read More

Restore old License Usage stats after upgrading Quality Center
Read More

Changing LoadRunner/VuGen log options at runtime
Read More

Restricting large attachments in Quality Center
Read More

Retrieving Quality Center user login statistics
Read More

A comparison of open source load testing tools
...
Read More

Worst practices in performance testing
Read More

LoadRunner Sales Questions
Read More

LoadRunner Analysis: Hints and tips
Read More

LoadRunner in Windows 7
HP Loadrunner 11 is now available. This new version now natively supports Windows 7 and Windows Server 2008. I ...
Read More

Using the QuickTest Professional “commuter” license
Read More

Installing HP Diagnostics
Read More

Understanding LoadRunner licensing
Read More

VuGen scripting for YouTube video
Read More

Creating a Web + MMS vuser
Read More

Why you should use backwards dates
Read More

How to get the host’s IP address from within VuGen
Read More

VuGen scripting for BMC Remedy Action Request System 7.1
Read More

Unique usernames for BPM scripts
Read More

Mapping drives for LoadRunner Windows monitoring
Read More

VuGen feature requests
Read More

LoadRunner script completion checklist
Read More

Querying Quality Center user roles
Read More

Querying the Quality Center Database
Read More

HPSU 2009 Presentation – Performance Testing Web 2.0
Read More

Scaling HP Diagnostics
Read More

Global variables aren’t really global in LoadRunner
Read More

Client-side certificates for VuGen
Read More

Detect malicious HTML/JavaScript payloads with WebInspect (e.g. ASPROX, Gumblar, Income Iframe)
Read More

VuGen code snippets
Read More

Integrating QTP with Terminal Emulators
Read More

Why you must add try/catch blocks to Java-based BPM scripts
Read More

Querying a MySQL database with LoadRunner
Read More

ANZTB 2009 Presentation: Performance Testing Web 2.0
Read More

How to make QTP “analog mode” steps more reliable
Read More

Testing multiple browsers in a Standardized Operating Environment (SOE)
Read More

DNS-based load balancing for virtual users
Read More

What’s new in LoadRunner 9.50?
Read More

Calculating the difference between two dates or timestamps
Read More

The “is it done yet” loop
Read More

Think time that cannot be ignored
Read More

Understanding aggregate variance within LoadRunner analysis
Read More

Load balancing vusers without a load balancer
Read More

Harvesting file names with VuGen
Read More

Parameterising Unix/Posix timestamps in VuGen
Read More

HP Software trial license periods
Read More

How to handle HTTP POSTs with a changing number of name-value pairs
Read More

VuGen string comparison behaviour
Read More

Persistent data in VuGen with MySQL
Read More

How to write a Performance Test Plan
Read More

Unable to add virtual machine
To get ...
Read More

LoadRunner scripting languages
Read More

WDiff replacement for VuGen
Read More

Testing web services with a standard Web Vuser
Read More

Why your BPM scripts should use Download Filters
Read More

Querying your web server logs
Read More

Importing IIS Logs into SQL Server
Read More

QTP “Uninstall was not completed” problem
Read More

VuGen correlation for SAP Web Dynpro
Read More

How to save $500 on your HP software license
Read More

Testing and monitoring acronyms
Read More

Solving VuGen script generation errors
Read More

An introduction to SiteScope EMS Topology
Read More

Using the BAC JMX Console
Read More