Reusable SQL Connection in soapUI

No matter how modern the systems get, we always often have some level of interaction with databases whether for reporting purposes or for recording transactions. In typical web services testing, one would imagine testing xml response to a given xml payload. What if, you need to connect to a database and say, verify, if a particular record was inserted by firing a select query? OR if, you need to create a log of your testing right inside the database? If not you may need to fire a select query after each SOAP Request, right in the Groovy Assertion Step of that request.

Now, quick and dirty way would be to create a SQL connection each time you need a connection and close it once you are done with it. Creating and closing a resource connection is very expensive. Hence, this could prove very costly when you are testing thousands of SOAP Requests. Imagine for each request you fire if there is a connection created whether in Groovy Assertion step or otherwise! I shall try to go over a simple mechanism to create a common reusable connection right at the beginning of your soapUI TestCase and then recycle it once you don’t need it.

The example I am trying to illustrate shall be useful when you have a single soapUI test case that, say, is iterating over number of rows from a datasource, carrying out some number of test steps and at each test step, before the step or after that step you need to connect to the database and carry out some operation.

To begin for any RDBMS you need four things: Driver, URL, Username and Password.
1. You can store these as TestCase properties, say, “dbUrl”, “dbDriverClassName”, “dbUser”, “dbPassword”
2. Each soapUI test case has two scripts: Setup Script and TearDown Script. We shall make use of these two scripts in order to illustrate our example. Inside Setup Script we shall obtain handle to our common connection as shown below:

//In a Setup Script
import groovy.sql.Sql

//try to create connection to database, if available. load this connection on context
//if not, log error and continue
//In order for this to work, you need to have jdbc driver jar file in $SOAPUI_HOME/bin/ext folder
def url = context.expand( '${#TestCase#dbUrl}' )
def driver = context.expand( '${#TestCase#dbDriverClassName}' )
def user = context.expand( '${#TestCase#dbUser}' )
def password = context.expand( '${#TestCase#dbPassword}' )

if ( (null != url) && (null != driver) && (null != user) && (null != password) )
{
  try {
    connection = Sql.newInstance(url, user, password, driver)
    context.setProperty("dbConn", connection)
  } catch (Exception e) {
    log.error "Could not establish connection to the database."
  }
}

3. Now wherever we need access to this connection in any of our test steps: whether in Groovy Assertion or in Groovy Script, we simply check for existence of this property on the soapUI context, for example, say following Groovy Assertion verifies if a customer account was created in the database right after SOAP Request CreateCustomer.

  //In a Groovy Assertion Step of a SOAP Request
  def MSG_CUSTOMER_NOT_FOUND = "Customer not found!"
  // Obtain customer number from response of a SOAP Request that creates a customer
  def customerNumber = <... groovy code retrieving customer number from response here ...> 

  //Check if connection to database is available
  if (context.dbConn)
  {
    //connection to the database
    def sql = context.dbConn

    row = sql.firstRow("select count(*) as numOfRecords from customers where customer_number = ? ", [customerNumber])

    //Verify that customer record exists in Customer Table in the database
    assert ( 1 == row.numOfRecords ):MSG_CUSTOMER_NOT_FOUND
  }

4. Finally, in TearDown Script, we close the connection:

//In a TearDown Script
//Close db connection
if (context.dbConn)
{
  context.dbConn.close()
  log.info "Closed Database Connection."
}

Ain’t that simple and reusable? Of course, you can extend this idea to create a connection pool or any other advanced set of objects, map of objects that you can hold onto soapUI context and use whenever needed. However, do not forget to clear up these resources though. That is what the TearDown Script is for!

~srs