Using MySQL database for LoadRunner parameter

Recently, a friend wrote a blog where he solved a tricky situation by using parameter file rather than using database for parameter. The scenario was as follows:

"This particular scenario required that a user logged on to the Application can only perform particular searches based on criteria defined and assigned to that particular user. That is,VUSER1 can only search on terms1, terms2 and terms3 whilst VUSER2 can only search on terms4, terms5 and so on."

I have not come across a situation where I had to use database for LR parameter. Therefore, as a challenge(as well as to learn something new) I wrote a simple LR script to solve this problem using database.

NOTE:
1: You will need to add error handling code to the script.
2: Haven't had a chance to check out how much memory this code consumes since I am using mysql_store_result function to store the result into memory.
3: All the applications were running on localhost. You will need to change the database connection details, if you need to connect to remote MySQL server.
4: Also you will need to add all the library files and DLL(see below) on a system that will be executing this script. That is either the vugen or LR agent machines.


Steps:
1: You will first need to download and install MySQL database.
2: Download C driver for MySQL (Connector/C)
3: Create a database, table and add records into the table as shown below in screenshot.

4: In LoadRunner you will need to include mysql.h header file which comes with C driver. You might also need to update path of the following header files in mysql.h.
-mysql_com.h
-mysql_time.h
-mysql_version.h
-typelib.h
-my_list.h
-my_alloc.h

5: You will also need to add a function to load the DLL(libmysql.dll) which allows LR to connect to the MySQL database. Add this file in the vuser_init function.

6: You will need to add all the database connection details. You will need to replace the below values with your database details.
char *MySQLserver = "localhost";
char *MySQLuser = "root";
char *MySQLpassword = "";
char *MySQLdatabase = "loadrunner";
int MySQLport = 3306;

7: Your global.h and vuser_init will look something like this.

vuser_init()
{   
 //libmysql.dll file loaded using lr_load_dll function
 MyRC= lr_load_dll("C:\\Program Files\\MySQL\\MySQL Connector C 6.0.2\\lib\\opt\\libmysql.dll"); 
       

 //initialise mySQL connection handler
 mySQL= mysql_init(NULL);
 
 // Connect to the database
 mysql_real_connect(mySQL,MySQLserver, MySQLuser, MySQLpassword, MySQLdatabase, MySQLport,NULL,0);

 //save SQL statement into variable into sqlQuery
 //This stamentement returns result that matches UserName = {Vuser} parameter
 lr_param_sprintf("sqlQuery","SELECT SearchTerm FROM lrdata WHERE UserName='%s'",lr_eval_string("{Vuser}"));

 //Execute SQL statement
    mysql_query(mySQL, lr_eval_string ("{sqlQuery}"));

 //result of the sql statement is placed into MYSQL_RES result structure
 result = mysql_store_result(mySQL);


 //num_fields = mysql_field_count(mySQL);


 return 0;
}

8: In Action function, add following code.
NOTE: Make sure you have created an LR parameter called "Vuser" that has a text format "Vuser%s" so that a correct vuser name is passed into the sql statement.

//1: initialize connection handler
//2: connect to the database server
//3: Execute SQL statement
//4: Close the connection to the database server 


Action()
{ 
 row=mysql_fetch_row(result);   //retrive next row of the fetched result

 //Incase # of transactions to be executed is more than returned sql result
 //move the result pointer to first row
 // This is similar to "Continue in cyclic manner" for When out of values option in LR    
 if(row==NULL)   
 {
    mysql_data_seek(result,0);
       row=mysql_fetch_row(result);
       lr_output_message("The searched term for [%s] is: [%s]",lr_eval_string ("{Vuser}"), row[0]);
 }  
 else  // print the fetched row 
 {
  lr_output_message("The searched term for [%s] is: [%s]", lr_eval_string ("{Vuser}"),row[0]);
 }

 return 0;
}

9: In your vuser_end function add following code.

vuser_end()
{   
    //free the memory allocated to result structure and close the database connection
 mysql_free_result(result);
 mysql_close(mySQL);

 return 0;
}

10: Running this code for five iterations, we get following result.

If you find any bugs in the code or you have an updated(/improved) version of this code, please leave a comment.