Saturday, September 10, 2011

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.

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.


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.

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.
 //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 

 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    
       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.
    //free the memory allocated to result structure and close the database connection

 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.

Saturday, September 3, 2011

Property Transfer in soapUI

Property transfer function (/TestStep) provides an option to transfer properties between TestSteps and their TestCase, TestsSuite and Project. For example, Property transfer TestStep could be used to pass sessonID value extracted from a response of one request into subsequent request.

Alright let’s get started on how to transfer a property.

Assumption: soapUI application is installed on your machine.
For blogging purpose, I am using free soapUI 4.0 incase you do not have Pro soapUI.

Step 1:
First we will need a wsdl. For demonstration purpose, I am using Metric Weight Unit Convertor.

Step 2: Start the application and then navigate(File ->New soapUI project) to New soapUI project window.

Step 3: Enter a project name in Project Name text box. Lets call it "WeightConvertor". Also enter the wsdl URL from step 1 into Initial WSDL/WADL textbox. See the screenshot below:

Step 4: click OK button. If the application is able to successfully access the WSDL then a new project called "WeightConverter" is added under "Projects" list. You will also see the Bindings. If you expand those bindings, you will see Operations and SOAP requests.

Step 5: Right click "WeightConverter" project and Click "New TestSuite" option. Enter a TestSuite name (i.e. Metric TestSuite) in the textbox and click OK button. A TestSuite is created under the WeightConverter project.

Step 6: Right click the TestSuite and select "New TestCase" option. Enter a TestCase name(i.e. WeightTestCase) in the textbox and click OK button. A TestCase is created under the TestSuite. A TestCase window is displayed.

Step 7: Add two steps to this TestCase. One step called as "GramToKilogramStep" and other one called "KilogramToGramStep". Make sure you follow all the steps required to create these steps. Finally you will have something like this as shown in the screenshot

Step 8: Double click "GramToKilogramStep" and enter following values in place of question mark.
MetricWeightValue = 3000
fromMetricWeightUnit = gram
toMetricWieghtUnit= kilogram
Once done click on the Green Run button. If successful, you should see "3" in the response. See the screenshot below.

Step 9: Double click "KilogramToGramStep" and enter following values in place of question mark.
fromMetricWeightUnit = kilogram
toMetricWieghtUnit= gram
NOTE: Leave MetricWeightValue as it is. We want to pass the value received in step 8 above using Transfer Property TestStep into this variable. If you try to run this step, you will get"Server was unable to read request" error response.

Step 10: Right click "Test Steps" and add a "Property Transfer" step. The step will be added at the bottom of the test step list. A Property Transfer window is displayed.

Note: You want this step to be between the two steps so that the value captured in GramToKilogramStep is passed to KilogramToGramStep. Therefore, hightlight the step and move it between the two steps.

Step 11: Enter a name for Transfer by clicking the "Plus" sign in the Property Transfer window.

Step 12:
In Source dropdown, select "GramToKilogramStep" and Property as "Response"
and in Target dropdown, select "KilogramToGramStep" and Property as "Request". In the source text box, we will need to declare a namespace and enter the XPath of the property that we want to transfer. See the screenshot under step 13.

Step 13: Once done, run the test step and in transfer log you will notice that pResponse has a value 3 now. See the screenshot below.

Step 14: Open up the "KilogramToGramStep" test step. You will now notice that "MetricWieghtValue" has automatically been assigned value 3. Run the step and you will see the correct response from the server with Result as 3000.

Well this sum up how to use Property Transfer TestStep to transfer a value from a response to a request.