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
  }
)