Showing posts with label soapui tip. Show all posts

Read data from excel in soapUI like a database

Treating Excel as a database is one of the most popular method used in automation testing. Web services testing is also not much different than that. There might be instances that we might need to treat an excel sheet as a database and read data from this excel sheet in soapUI for data validation or input data or for configuration purposes. In this example, instead of relying on groovy, we will be using a bit of java code to connect to an excel sheet and read data from this excel sheet. In order to connect to an excel as a database, we need a driver. We will be using "sun.jdbc.odbc.JdbcOdbcDriver" in our example to connect to an excel fom soapUI. Lets say we have a file named "soapUI-Excel-Example .xls" and it has a work sheet named "ExcelSheetName". Now, create a coulmn named "Name" in the sheet and enter some data in the column. Its time for us to look into the code now. Lets see the below example on how to connect to excel from soapUI.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

def soapUI_Excel = "C:\\soapUI-Excel\\soapUI-Excel-Example.xls"
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
soapUIDataConnection = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=" + soapUI_Excel + ";ReadOnly=0", "", "");
Statement stmt = ((Connection) soapUIDataConnection).createStatement();
recordSet = stmt.executeQuery("Select * from [ExcelSheetName\$] where Name like 'soapUI%'");

while (recordSet.next())
{

log.info(recordSet.getString("Name"))

}

The above example will select all the Names like "soapUI" and print it to soapUI log.

soapUI Composite Project

In this soapUI tutorial we will explore a nice feature in soapUI called "Composite Project". This is a property in soapUI project which takes boolean values true or false. Click on soapUI project and you will see the project properties tab in soapUI and there you can see the option "Composite Project" and by default the value will be false.
Now lets discuss what this exactly means to us. First create a project on a work space and save the project. Make sure you have atleast one test suite and one test case in the project. Go back to the directory where you saved the project and see how many files you have. You will see a work space XML file and also a project XML file. In a way this is good as the number of files as less, it is easy to maintain. But imagine if multiple people are working on web services automation using soapUI and everyone is working different test cases. In this case, it will be hard to maintain a single file and merge it always. Here comes the use of soapUI "Composite Project" property. Create another project and change the "Composite Project" property to "true". Now create a test suite and create a test case inside that and save the project. Go back to the folder where the project is saved and check for the files. You will see a seperate folder for project with soapUI project file and settings files along with a folder for test suite and the interface folders. Now navigate to the soapUI test suite folder, you will see settings file, element order file, and the soapUI test case file. In this case each test case will be mainted in different soapUI XML files and each test suite will be a different folder. This way multiple people can work different test cases at the same time on the same project. This is more useful if you are working on a large web service automation project with hundreds of operations are present. Version control is also more easier with this approach.

Groovy Script Library in soapUI

Do you know in soapUI we can bundle all the frequently used scripts/ functions into a script library and use it where ever we want without duplicating the code? Lets take a look into this topic. Lets assume we need to use the below block of groovy script in many places like soapUI groovy test steps, soapUI assertions etc.

def soapUI_String = "soapUI Tutorial";
/* Do some processing on the soapUI_String variable here */
log.info(soapUI_String);
We can make all this into a function and call it when required. Doesn't that sound good? To do this, first we need to create a class and make the above tasks into a member function. Lets create a Groovy class now.

package soapUIScripts
class Scripts
{
def log;
Scripts(log)
{
this.log = log;
}
def soapUI_groovy_function()
{
def soapUI_String = "soapUI Tutorial";
/* Do some processing on the soapUI_String variable here */
log.info(soapUI_String);
}
}

Create a folder with the name of the package (in this case "soapUIScripts") and place the above code into a file and save inside the folder with name "Scripts.groovy"

We have created a groovy class and now we need to include the above class into soapUI for us to use it. In soapUI Pro, go to "soapUI Preferences -> soapUI Pro -> Script Library" mention the path of the parent folder (name of the folder which has the folder "soapUIScripts"). We can't have just the functions inside the groovy file, it should always be attached to a class. soapUI is all set to use your custom code now. Navigate to any soapUI test case and create a groovy script test step and use the below code inside that step to call the above created function.

def myscript = new soapUIScripts.Scripts(log);
myscript.soapUI_groovy_function();

If you are using any soapUI built in objects in your groovy class/ script, make sure you are first passing that as a constructor. The same way we passed the "log" object to our class in the above example.

If you are not using soapUI Pro you will need to compile your scripts into a standard java library (ie .jar file) and add that file to the classpath by putting it in the bin\ext folder to use the functions.

Capture soapUI Logs to File

In this Groovy Script example, lets see how can we capture the soapUI logs and save it into a file. At the bottom of soapUI, you might have noticed different tabs named soapUI log, http log, jetty log, error log, wsrm log, memory log and script log. Each tab will have the logs based on log category. For example, if you are invoking a Web Service request/ response, you will be able to see the web service request and response with the http headers under http tab. Same way if you have groovy script to on your soapUI test steps and if those groovy scripts are printing any dynamic values while test execution, you might also see that in the tab "script log". Lets see the below groovy script to capture the logs and save it into a text file.

//The below groovy script step is to capture the soapUI log area.

// "soapUI log" can be replaced with http log, jetty log, script log, error log etc based on the need.

def logArea = com.eviware.soapui.SoapUI.logMonitor.getLogArea( "soapUI log" );

//Below two lines of groovy script is to get the project directory, we will be saving the soapUI log contents on a file in that directory.

def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context)

def projectPath = groovyUtils.projectPath

def logFile = new File(projectPath + "\\soapUI-Logs-to-file.txt")

//This is the first line in the soapUI log file.

logFile.write("soapUI Logs In a file.\r\n")

if( logArea !=null )

{

def model = logArea.model

if( model.size > 0 )

for( c in 0..(model.size-1) )

logFile.append(model.getElementAt( c ))

}

List testSteps using Groovy Script in soapUI

Let us see how can we get a list of all the test steps available in a particular testCase in soapUI. This is a simple script and might be useful if you need more control on the testSteps individually.

See the example below.

// This will get the list of all testSteps in the testCase
def testSteps = context.testCase.getTestStepList()
testSteps.each{
// This block will loop through the test steps and print the test step name to the log
log.info(it.name)
}

Capture the list of Assertions using groovy script

While doing automation testing of web services using groovy and soapUI, we might need to get the list of assertions and its status of a test request. By using groovy this can be achieved easily in soapUI. See the below example.

def obj = context.testCase.getTestStepByName("GetCustomer");
def assertions = obj.getAssertionList()
assertions.each{
log.info(it.name + ' --> ' + it.status)
}

The above code is creating an object for the test request named "GetCustomer" in the first line. In second line it is storing all the assertions into a variable name "assertions". The assertions.each block iterates through the list of all assertions attached to the test request "GetCustomer" and the log statement prints the name of the assertions along with it status.

Below line of code will give the total number of assertions attached to a test request.

def obj = context.testCase.getTestStepByName("GetCustomer");

log.info(obj.getAssertionCount())