SoapUI - using WHERE IN clause and Groovy code to concatenate strings

Recently, I worked on a project that was using SoapUI Pro to test an application. One of the testers had a working test case in SoapUI but wanted it done differently and therefore, approached me for a solution. Following outlines the scenario and a quick groovy code I wrote to address what he really wanted.

Scenario:
He was connecting to a database using SoapUI JDBC step and retrieving more than one result for a SQL query. He was then using FOR loop to iterate through all the values. In the FOR loop, he had another JDBC step which took result value as a parameter and returned an appropriate response (using assertion to validate the response).

What he wanted:
He wanted to get rid of FOR loop and pass all the values returned from the first JDBC step into second JDBC step.

Solution:
The solution I came up with was to concatenate all the values from the first JDBC step using a groovy code and then pass the returned string from the code into a separate JDBC step. Also change the SQL query in the final JDBC step to use WHERE IN clause.

For blogging purpose, I am connecting to MySQL database on my local machine using SoapUI Pro 4.5.2 (trial version).

I have created two tables in MySQL database. First table is a class which contains StudentName and ClassName. Second table is a Subject with StudentName and Subject fields. For code demonstration purpose, I will be querying the database to return me names of all the stundent that are in Class 3. Then I will concatenate the names using groovy code and pass it to a separate JDBC step to get me the StudentName and the Subject they are enrolled in.

NOTE:

• This code does not cater for all the possibilities as it is only for blogging purpose. 
• Also make sure you are adding the MySQL JDBC driver in SoapUI ext folder so you can connect to the database.
• I am sure the code below can be refined further. saving xmlRecCount.toInteger() value to a parameter.

Steps:

1. Add a JDBC Step to the test case. This JDBC step with query the class table and return student names that are in class 3. 

2. Add a DataGen step to the test case. In this step create a parameter with "Type" as "Script". This Script will take response from the first step, concatenate all the student names into one string and pass it to the parameter. 

3. Finally add another JDBC step to the test case. This step will query the Subject table uisng WHERE IN Clause and IN value will be the parameter created in step 2 above.

Code:

def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context)
def holder = groovyUtils.getXmlHolder("GetStudentName#ResponseAsXml")

def concSqlString=""   //initialize parameter string concSqlString
def cnt =1             //initialize counter

//Get the total count of records that have the CLASS.STUDENTNAME
def xmlRecCount=holder["count(//Results[1]/ResultSet[1]/Row/CLASS.STUDENTNAME)"]


//check if only one row is returned
if (xmlRecCount.toInteger()==1)
{
      node = holder.getNodeValue('//Results[1]/ResultSet[1]/Row/CLASS.STUDENTNAME')
     concSqlString=concSqlString +"\""+node+"\""
}
else{
        //for each node, concatenate the node value to concSqlString variable
 for (node in holder['//Results[1]/ResultSet[1]/Row/CLASS.STUDENTNAME'])
 {
 if(cnt<xmlRecCount.toInteger()){
  concSqlString=concSqlString+"\""+node+"\","
  cnt=cnt+1
 }
 else
 concSqlString=concSqlString +"\""+node+"\""
 }
}

//return the concSqlString 
return concSqlString

LoadRunner - Selecting random value using lr_paramarr_random function

In performance testing, it is really important to simulate a realistic user path through an application. For example, randomly select an image link from a gallery or select a share from a share list. In such situations, you can use the LoadRunner lr_paramarr_random function to select a random value from a captured parameter array. Similarly, you can also write a code to do the same.

Before you use the above function, you will need to use web_reg_save_param function to capture all the ordinal values. This can be achieved by passing "ORD=ALL" into the function.

The following code demonstrates the use of lr_paramarr_random function. The code saves link Ids using  web_reg_save_param function and then uses lr_paramarr_random function to randomly select an Id value. The value is passed as a parameter in web_text_link function to navigate to the appropriate webpage.

Action()
{

 //Capture all the Link IDs
 web_reg_save_param("LinkID","LB=id=\"","RB=\" href=\"default.aspx?content=business_","ORD=ALL",LAST);

 lr_start_transaction("VU01_01_Homepage");
 web_browser("website", 
  DESCRIPTION, 
  ACTION, 
  "Navigate={WebsiteUrl}", 
  LAST);

 lr_end_transaction("VU01_01_Homepage",LR_AUTO);

 lr_think_time(10);


 //Save a randomly selected ID to a Parameter
 lr_save_string(lr_paramarr_random("LinkID"),"RandomLink"); 

 //Printout the randomly selected parameter
 lr_output_message("%s",lr_eval_string("{RandomLink}"));

 //Navigate to the appropriate webpage associated to the ID
 lr_start_transaction("VU01_02_RandomLink");

 web_text_link("Links", 
  "Snapshot=t2.inf", 
  DESCRIPTION, 
  "Id={RandomLink}",
  ACTION, 
  "UserAction=Click", 
  LAST);

 lr_end_transaction("VU01_02_RandomLink",LR_AUTO);
 
 web_browser("Sync", 
  "Snapshot=t3.inf", 
  DESCRIPTION, 
  ACTION, 
  "Sync", 
  LAST);

 return 0;
}

Following is a screenshot of replay log, displaying the random id's selected for each iteration. It also displays the values captured using web_reg_save_param function.

SoapUI pro - DB2 Invalid database URL syntax issue

Recently, I was trying to connect to DB2 database using SoapUI pro 4.5.1 and got the following error message "com.ibm.db2.jcc.am.SqlSyntaxErrorException:[jcc]...Invalid database URL syntax: ...ERRORCODE=-4461,SQLSTATE=42815".

On further analysis, I discovered that the default connection string for IBM DB2 Drivers in SoapUI did not have a semicolon at the end of password variable.

Therefore to solve the above issue, you can use any of the following approaches:

1: add the semicolon at the end of the connection string template for DB2. The connection string templates are available under the option soapUI Preferences - JDBC Driver Properties.

2: Create a custom connection string with semicolon in your test case.

3: If you don't want to use either  of the above approaches, third approach is to add semicolon at the end of the Password variable text.

NOTE: To connect to DB2 database, you will require to add db2jcc.jar and db2jcc_licence_cisuz.jar files in the SoapUI ext folder.

Groovy - Saving CouchDB runtime statistics json file to excel file

Last year, I wrote a blog on how to get CouchDB Runtime Statistics and saving it as a json file. However, want I really wanted was to save and open  the file in excel. Therefore, I wrote a Groovy script to read Runtime Statistics data from json file and save all the Key values into an excel file.

Below is the prerequisite to execute the script as well as the script.

NOTE: Their already exists a python script to capture the CouchDB runtime statistics.

Prerequisite:

• Download and Install Groovy Console application.
• Download and copy  jxl.jar file into the Groovy lib folder.

Script:

import groovy.json.*
import jxl.*
import jxl.write.*


Parameter = ["Group","Key","current","max", "mean", "min", "stddev","sum"]; //couchDB worksheet headers

exlfile = "C:\\Harinder\\Groovy\\couchDB.xls"; //excel file path

if   (new File(exlfile).exists())  //check if file exists esle create a new one, write label and close the file
{
    println "File already exists";
}
else
{
    WritableWorkbook workbook1 = Workbook.createWorkbook(new File(exlfile))
    WritableSheet sheet1 = workbook1.createSheet("couchDB", 0);
    //Label label = new Label(column, row, "Text input in Excel");
    for (int iheader=0; iheader<8;iheader++)
    {
        Label label = new Label(iheader, 0, Parameter[iheader]);
        sheet1.addCell(label);
    }
    workbook1.write();
    workbook1.close();
}

def reader =new BufferedReader(new FileReader("C:\\Harinder\\Groovy\\couchDB.json")); //create a json file into a buffer
def jparsedData =new JsonSlurper().parse(reader);


/*open an exisiting excel file, write Key values and close the file*/
Workbook workbook = Workbook.getWorkbook(new File(exlfile)); 
WritableWorkbook copy = Workbook.createWorkbook(new File(exlfile),workbook);

Groups = jparsedData.collect{a,b->a}.reverse(); //Groups=["couchdb","httpd_request_methods"...]

WritableSheet sheet = copy.getSheet(0);
int groupCount=1;
int rowCount=1;
for (int gCount=0;gCount<Groups.size();gCount++)  //iterate through the Groups
 {   
    tGroups=Groups[gCount]; //assign Groups[gCount] value to a temporary variable tGroups
    sheet.addCell(new Label(0,groupCount,  tGroups)) //save tGroups into the sheet
    Keys = jparsedData."$tGroups".collect{a,b->a}.reverse();  //collect all the Keys associated to Group[gGroup]
    for (int kCount=0;kCount<Keys.size();kCount++) // iterate through all the Keys and save their min,max,count,mean,stddev,sum values into the sheet
     {
       tKeys=Keys[kCount];
       rowCount=kCount+groupCount; 
       sheet.addCell(new Label(1,rowCount,  tKeys));
       sheet.addCell(new Label(2,rowCount,  jparsedData."$tGroups"."$tKeys".current.toString()));
       sheet.addCell(new Label(3,rowCount,  jparsedData."$tGroups"."$tKeys".max.toString()));
       sheet.addCell(new Label(4,rowCount,  jparsedData."$tGroups"."$tKeys".mean.toString()));
       sheet.addCell(new Label(5,rowCount,  jparsedData."$tGroups"."$tKeys".min.toString()));
       sheet.addCell(new Label(6,rowCount,  jparsedData."$tGroups"."$tKeys".stddev.toString()));
       sheet.addCell(new Label(7,rowCount,  jparsedData."$tGroups"."$tKeys".sum.toString()));
     }
     groupCount=rowCount;
 }

copy.write()
copy.close()

Script steps:

1. Check couchDB excel file exists. If it does not, create it and add all the necessary headers to a worksheet and close the file.
2. Read and parse the json file.
3. Open the couchDB file for writing.
4. Navigate through the parsed json data and save all the Group names.
5. Navigate through each group name in step 4 and save all the Keys associated to it.
6. For each Keys saved in step 5, navigate through it and save all the associated values into excel file.
7. Once done, close the worksheet.

Result:
 

SOASTA CloudTest Lite VM - Switching to graphics mode

Today, I downloaded CloudTest Lite VM from SOASTA website so I could have a look. After starting it up with VMWare Player, I got the following screen.

To switch to graphic mode, which is what we want, you need to hit

CTRL+ALT+F7

After hitting the keys, you will see the Welcome CloudTest Lite Screen.

How to compare two heap dumps and view result using jhat

The jhat is a Java Heap Analysis Tool that comes as a part of the JDK. This tool can be found in the JDK bin directory. For more information on jhat refer to Java SE documentation.

Following is the step to compare two heap files using jhat:

jhat -baseline baseline.hprof newbaseline.hprof

If successful, jhat will start an http server on default 7000 port. To view the result, navigate to

http://localhost:7000/ 

Classes
Some of the classes you might see
http://localhost:7000/allClassesWithPlatform/
http://localhost:7000/showRoots/
http://localhost:7000/showInstanceCounts/includePlatform/
http://localhost:7000/showInstanceCounts/
http://localhost:7000/histo/
http://localhost:7000/finalizerSummary/
http://localhost:7000/oql/

Generating Websphere verboseGC Graph in LoadRunner Analysis tool

Few weeks ago, I was working for a client and I wanted to analyze Websphere Application server verboseGC logs. I could have used tools such as IBM PMAT but what I really wanted was to merge verboseGC graph with response time graph in Load Runner and this required a lot of manual work. Therefore, I modified existing Silkperformer vbscript to Websphere verboseGC vbscript.

For the blogging purpose, this script saves only handful of verboseGC attributes into CSV file. You can then import the csv file as an external monitor in Load Runner Analysis tool.

NOTE: You can modify this script to suit your requirements. Also make sure everything is contained within verbosegc tag in your verboseGC log file. See the example below.

Please read the following blog on how to generate the CSV file.

Websphere verboseGC Vbscript

Option Explicit
 
Dim xmlDoc
Dim af, gc, timestamp, gcIntervalms, DateTimeArray, afDate, afTime, afIntervalms,minimum,requestedBytes,Totalms,gcTotalms,gcTotal,Time
Dim reportFile, outputFile
Dim myFSO, fileHandle
 

'Murray Wardle code
Set xmlDoc = CreateObject("Microsoft.XMLDOM")
 
Const ForReading = 1, ForWriting = 2, ForAppending = 8
 
If Wscript.Arguments.Count = 0 Then
    msgbox "Please specify the overview Report file to process"
Else
 
 ' Get report file name & set output filename
    reportFile = Wscript.Arguments(0)
 outputFile = Left(reportFile, Len(reportFile)-3) + "csv"
 
 xmlDoc.async = false
 xmlDoc.SetProperty "SelectionLanguage", "XPath"
 xmlDoc.SetProperty "ServerHTTPRequest", True
 xmlDoc.validateOnParse = False
 xmlDoc.resolveExternals = False
 
 'load overview report
 xmlDoc.load(reportFile)
 xmlDoc.setProperty "SelectionLanguage", "XPath"
 
 'open csv file to dump results into
 Set myFSO = CreateObject("Scripting.FileSystemObject")
 Set fileHandle = myFSO.OpenTextFile(outputFile, ForWriting, True)

'Modified code for verboseGC
fileHandle.WriteLine("date,time,afIntervalms,requestedBytes,gcIntervalms,gcTotalms,Totalms")
 
For Each af In xmlDoc.SelectNodes("//af")
  timestamp = af.getAttribute("timestamp")'get af timestamp attribute value
  DateTimeArray=Split(timestamp," ",-1,1) 'split the date time into array
  afDate=FormatDateTime(DateTimeArray(1)+"/"+DateTimeArray(0)+"/"++DateTimeArray(3),vbShortDate) 'format into date
  afTime=FormatDateTime(DateTimeArray(2),vbLongTime) 'format into time
 
  afIntervalms=af.getAttribute("intervalms") 'get af intervalms attribute value
 
  For Each minimum In af.SelectNodes("./minimum") 'get minimum requested bytes value
    requestedBytes = minimum.getAttribute("requested_bytes")
  Next
  For Each gc In af.SelectNodes("./gc") 'get gc intervalms value
    gcIntervalms = gc.getAttribute("intervalms")
  Next
  For Each gcTotal In af.SelectNodes("./gc/time") 'get total gc time value
    gcTotalms = gcTotal.getAttribute("totalms")
 Next
 For Each Time In af.SelectNodes("./time")
    Totalms = Time.getAttribute("totalms") 'get total time value
 Next   
 fileHandle.WriteLine(afDate+","+afTime+","+afIntervalms+","+ requestedBytes+","+ gcIntervalms+","+gcTotalms+","+Totalms)
Next
fileHandle.Close
end if

Websphere Application verboseGC log Example

CSV Output



VerboseGC graph in LoadRunner

Failed to initialize dtrace message

Recently, I wanted to learn DTrace programming, so I installed OpenSolaris 10 virtual machine on my laptop. After installation, I tried to execute a simple dtrace command but got the following message:

"dtrace: failed to initialize dtrace: DTrace require additional previleges"

Solution:
By default, when you install OpenSolaris 10, your primary profile is set as 'Primary Administrator' and role as 'root' but you are logged in as a normal user without root privileges. Therefore, to solve my issue I had to 'su' as 'root' and run DTrace commands.

You can check what role and profile you are assigned after installation by running following command:    cat /etc/user_attr

NOTE: If you don't want to log in as a root each time you want to run the DTrace command, you can read the following article on how to give DTrace privilege to a normal user.

Macro to delete multiple comments in MS Word 2003

There are times when a reviewed Test plan doc(or someother MS Word document) comes back with multiple comments that need addressing. Once these have been addressed, there is no option to delete all comments in the MS Word like "Accept All Changes in Document" option. You can delete each comment after you have address it (manually) or use the following vbs macro to delete all the comments at once.

Sub RemoveComments()

Dim commCount As Integer

Dim oDoc As Document

Set oDoc = ActiveDocument

For commCount = 1 To oDoc.Comments.Count

   oDoc.Comments(1).Delete

Next

End Sub 

Alternatively you can use this macro.

Sub RemoveComments()

Dim oDoc As Document

Set oDoc = ActiveDocument

oDoc.DeleteAllComments
End Sub 

NOTE: In MS Word 2007 and later, there is an option to delete all comments at once.

SQL vs NoSQL

Interesting talk on SQL vs NoSQL by Ken Ashcraft and Alfred Fuller.

Following scorecard taken from the talk.