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.

Read more »

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.

Read more »

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.

Read more »