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.

1
2
3
4
5
6
7
8
9
10
11
12
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
// 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.

25 comments

Hello Stuart,
I relay Appreciate your help by posting Lr related blogs. I have one question, I would like to test my company’s’ external URL by clinking to the URL. do you have any idea how is should approach this load test?
Thank you,

Hello Alen,

Thanks for your comment. It sounds like you are looking for performance testing support. Our consultants have strong performance testing capabilities, which are detailed at length on this page: https://www.jds.net.au/services/test-it/performance-testing/

Please feel free to contact us using our contact form if you’re interested in learning more about our services, and we would be happy to assist you.

I keep getting results similar to Stefans (above) a large number (9 figures) could be some pointer adress when I query my database.
Anyone who knows what I get?

Hi,
I am struggling to get resolution on one of the issue in LoadRunner ODBC protocol i.e. “Invalid Cursor State”. Does anyone had seen such type of issue and know how to get out of it ?

While running the script, it’s throwing the error at line “lrd_db_option(Csr875, OT_ODBC_CURSOR_CLOSE_30, 0, 1);”
===========================================================================================

lrd_open_cursor(&Csr875, Con3, 0);
lrd_stmt(Csr875, “UPDATE vec_user SET LOGONTIME = ? WHERE (ID = ?)”, -1, 0 /*Parse only*/, 0 /*None*/, 0);
lrd_assign(&_1_D3766, “16:25:51.000”, 0, 0, 0); //16:25:51 {p_timeFormat}
lrd_bind_placeholder(Csr875, “1”, &_1_D3766,
LRD_BIND_FOR_INPUT_ONLY, 0);
lrd_assign(&_2_D3767, “{ID}”, 0, 0, 0);
lrd_bind_placeholder(Csr875, “2”, &_2_D3767,
LRD_BIND_FOR_INPUT_ONLY, 0);
lrd_exec(Csr875, 0, 0, 0, 0, 0);
lrd_db_option(Csr875, OT_ODBC_CURSOR_CLOSE_30, 0, 1);
lrd_db_option(Csr875, OT_ODBC_CURSOR_UNBOUNDCOLS, 0, 0);
lrd_db_option(Csr875, OT_ODBC_CURSOR_RESETPARAMS, 0, 0);
lrd_close_cursor(&Csr875, 0);

I am using the combination loadrunner and mySQL for a while and it is working perfectly with low number of vusers. However, when i use large number of vusers (>2500) the connections are taking lot of time and are failing.

Could some one help me with a suggestions…

Thanks!

Dear Stuart,

I hav a problem during scripting,

1) In my application i have disabled multiple login simultaneously, know if i have to login to the sceen i need multiple login credentials, so if i create a Virtiual table and then call this usercredentials dynamicaly from the Virtual table by keeping the hostname unique. Can i get a code for the same. Please do the needful

Thanks & Regards
Suhas B.C
Mob:- +91 9008080625

is it only for mysql cant we develop same kind of script for ms sql server .

if yes could gime the sample code ….i have to do poc on ms sql server .

Hi Stuart,

I have implemented this MYSQL stuff suggested by you to pass the data between multiple scripts under load. I was not able to execute the script from load generators, getting an connection error. Whereas my SQL code and script is working fine when I run the script in the controller as localhost.

Please note that I have installed and configured MYSql DB in the controller machine. I am getting connection error when trying to execute the script from other machines.

Regards,
Amith

Stuart Moncrieff
Stuart Moncrieff

Hi Amith,

Perhaps it is a firewall problem. You could check if the MySQL port is open between the load gen and the controller.
e.g. Run this from the command line on the load gen:
telnet my.controller.hostname 3306
If you can’t connect, then you have a firewall problem.

It could also be a MySQL security feature. Your user account may be configured to only be able to connect from localhost.

SELECT USER, host FROM mysql.user;
+-------+-----------+
| USER  | host      |
+-------+-----------+
| amith | localhost |
| root  | 127.0.0.1 |
| root  | ::1       |
| root  | localhost |
+------+-----------+

The ‘amith’@’localhost’ account can be used only when connecting from the localhost. You should update it so to use the ‘%’ wildcard for the host part, so it can be used to connect from any host.

Cheers,
Stuart.

Hi Stuart,

I have updated the %’ wildcard for the host part and I am to connect to MYSQL database from any machine.

Thank you very much for your inputs.

Regards,
Amith

Pallavi Shrivastava

I have tried to run the same line of code through LoadRunner but the execution is getting stop at line number 45 of the above set of code. And the error message is “Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘{paramInsertQuery}’ at line 1”.
And the value of variable “rc” will change to 1.

Please suggest me the solution for the same.

Hi Stuart

I have been using the MYSQL solution for years but because of some organizational policies I cannot use MYSQL. I know it sound wiired :).

I am looking for some other solution db2 , oracle and ms access are the available option. Only drawback is I have to rewrite the functions written for MYSQL.

I would appreciate your inputs on this. If I have to choose another db , which one you would suggest.

Stuart Moncrieff
Stuart Moncrieff

Hi Deshraj,

You can always have a look at HP’s Virtual Table Server, which is distributed free with LoadRunner 11.52. It is designed for load testing, and the functions are easy to call from a C-based VuGen script.

Cheers,
Stuart.

Hi Stuart Moncrieff

I would like to extend our sincere THANKS to You for saving me from this problem.
Its working fine.

Thanks& Regards,
Shankar

Please help me.
I am following your instructions .
I need to insert a dynamic value from load runner to MySQl database.
First I recorded script and then I caputure dynamic values by writing corelation.
Where I am facing the problem is inserting CAPTURED dynamic value

For insertion :

lr_param_sprintf("paramInsertQuery", "INSERT INTO lr_defect (defect_id) VALUE ({SDID})", time(NULL));
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();
}

The dynamic value stored in SDID.

Please help me from this problem

Thanks& Regards,
Shankar

Stuart Moncrieff

Hi Shankar,

To insert the SSID parameter value into the “defect_id” field in the “lr_defect” table, you should change your code to this:

lr_param_sprintf("paramInsertQuery", "INSERT INTO lr_defect (defect_id) VALUE (%s)", lr_eval_string("{SDID}"));

Cheers,
Stuart.

Good blog. Does anybody know of a dll to query SQL Server?

[…] You don’t have to use VTS, you can use mySQL as well as this blog post […]

Hi Guys,

How can I distribute 1000 Unique Usernames and Passwords file to each Vuser Group?

If a Script uses Unique file Parametrization, running more than One Vuser Group with that script in the same scenario may cause unexpected scenario results.

Lets take the below Scenario,
Vusers: 1000
LG(S): 5
No Of Vusers per LG is: 200 Vusers(i.e. Group1 :200 Vusers , Group2: 200 Vusers … Group 5: 200 Vusers)
Scenario: Login
Method: Unique & Once
File: Unique Usernames & Password file (1000 Vusers)
Iteration: 1

MY question is how can I distribute 1000 Unique Usernames and Passwords file to each Vuser Group?

What I mean Group1 should read first 200 username and passwords and Group 2 should read from 201 to 400 so…on.

How do I make sure each Group is taking unique values?

Could someone please advise me on the above!

Thanks,
Raj

Stuart Moncrieff
Stuart Moncrieff

The unique file parameter type only maintains uniqueness within a group.

The easiest way to ensure that 5 groups of 200 vusers select unique usernames from a list of 1000 is to split the file into 5 equal chunks, and save your script 5 times with different usernames in each file.

Hello again

I have what I think is a good question: do you know if it’s possible to execute transactions taken from a database? For example, taking from the DB a string containing “web_submit_data(…);check();” and EXECUTING it like it’s a piece of code in LR.
Is there some kind of parser function available?

Thanks

Stuart Moncrieff
Stuart Moncrieff

Sorry Stefan, there is no equivalent of eval() in C.

Thanks for the tip
Now I’ve gotten into more issues :) for example, I keep getting a large value (like 252452235) inserted in the variable, when I update a cell and try to store its value.

Must be some pointer address or smth, but I see that mysql_use_result and mysql_fetch_row return ints
I have int in those certain cells I want to get values from

And I didn’t understand why you cast the type to char**. I know it’s defined like that in MYSQL_ROW, but it would’ve been great if I could just use those types instead. And I can’t because the compiler keeps asking for headers it doesn’t find (and which I don’t find either) like mysql.h – after fixing that, mysql.h asks for types.h and so on

Is there a way around this? Thanks!

I forgot to mention in my last post that I came across your other post “vugen polling loop” and was going to use that method. However, I will have a margin of error (i.e think time in loop as well as waiting on the system to return a status “complete” while I am refreshing.

This is a very good idea. I am working on a project and would like to query a DB for the amount of time it takes to process documents. In order to use this code would I have to install MYSQL on each load generator? The max number of vusers for this test is 50. Thank you.

Leave a Reply