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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: