Reusing a database connection in soapUI

Creation of a database connection is a rather resource heavy task. Depending on the way your soapUI test cases are implemented there might potentially be a large number of different calls to an underlying database. When running test cases or test suites the connection shall preferably only be created once and then be reused whenever needed. Though on the other hand when debugging test cases a single test step might need to be executed in its own context without an existing database connection. Below I’ll highlight a short example that will combine the two and which will make it rather smooth to reuse and create a database connection when needed.

Solution

A reusable database connection in;

• test cases
• test suites
• test steps

The example below assumes that the code for setting up a database connection is already in place. If not setting up the database connection using SQL server with jtds can be found here.

How

The trick is really to use the soapUI context as a placeholder for the database connection object. Whenever a connection is established the connection shall be stored in the context so that it does not matter if a full test suite or a single test case is executed, the context variable will hold the DB connection. For execution of single test steps the connection needs to be created every time, sorry no workaround here.

Modularization (sort of)

Since this example references to the free version of soapUI, modularization is simply re-execution of a test step and a way of avoiding duplicate code lines. Assume a test case like the one below.

SoapUITestCase
  \- SetupDatabaseConnection
  \- TestRequest (SOAP)
  \- RunADatabaseQueryTestStep

The test step SetupDatabaseConnection is must preferably be present in all test cases (it is possible to include test cases in test cases) where a database connection shall be shared, it can if wanted even be disabled. The modularization in effect simply invokes the SetupDatabaseConnection test step from the query test step when needed i.e. when no connection exists.

Setup database connection test step

/* Groovy */
if (context.sqlconn == null) {
  // Create the database connection. This wont be needed in the following cases
  // 1) The running test suite context alread has a sql connection created and stored
  // 2) The running test case context alread has a sql connection created and stored
  ...
  def sql = Sql.newInstance("jdbc:jtds:sqlserver://"+dbIp+":"+dbPort"+"/"
  +dbName,dbUserName,dbUserPassword, "net.sourceforge.jtds.jdbc.Driver")
  context.setProperty("sqlconn", sql)
}

Running a database query test step

/* Groovy */
if (context.sqlconn == null) {
  // Recreate a database connection here since the
  // context is only alive for a test case execution
  // not for executing single test steps.
  testRunner.runTestStepByName("SetupDatabaseConnection")
}
if (context.sqlconn != null) {
  def res = sql.eachRow("select * from Users", {
    userId = it.UserId
  })
}

So by storing away the database connection in the current context and by using a simple switch the same connection can be resused all over and at the same time be created only a needed basis.

Getting soapUI SQL server connections to work using jTDS and Groovy

So many suggestions, a few halfworking approaches and finally I found the line of Groovy code I was missing in a totally different context. I was struggling to get the Microsoft provided jdbc drivers to work getting “ClassNotFound”, incompatible JRE versions and could not find driver for error messages. There where an number of suggestions on the web, using jTDS was one of them that seemed simple enough and it worked.

Anyway this is how I got it to work in the end, setting up an SQL connection using jTDS, calling the DB and interating the response.

• soapUI 3.6.1
• jTDS 1.2.5
• SQL server 2008

Setup

• download the jTDS JDBC driver (jTDS), unzip and put the jarfile in the
  <soap UI installation folder>/bin/ext folder.
• Initially I also put the ntlmauth.dll (matching the system) in the <soap UI installation folder>/bin folder. It showed later that this was not really needed.

Before I added the most important line off all, where the actual driver is registered with soapUI there was a lot of fiddling with the conncetion string. Do not do any mistakes there, the format is important and must absolutely follow the format defined for jTDS.

jdbc:jtds:<server_type>://<server>[:<port>][/<database>][;<property>=<value>[;...]]

The database is stated as optional but if not defining it there was an SQL exception thrown stating that the looked for table was not found and probably indicating that the default database was not the one I wanted to use.

My approach in Groovy below:

//
// * Register driver
// * Set up the connection
// * Run a query
//
import groovy.sql.Sql
com.eviware.soapui.support.GroovyUtils.registerJdbcDriver( "net.sourceforge.jtds.jdbc.Driver" )

def sql = Sql.newInstance('jdbc:jtds:sqlserver://<IP/HOSTNAME>:1433/<DATABASENAME>', "<USERNAME>", "<PASSWORD>", "net.sourceforge.jtds.jdbc.Driver")

def res = sql.eachRow("select * from <TABLE> where <COND>, {
    // Do the stuff
  }
)