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

Advertisements

3 Responses to “Getting soapUI SQL server connections to work using jTDS and Groovy”

  1. Reusing a database connection in soapUI « Technical Testing Says:

    […] Getting soapUI SQL server connections to work using jTDS and Groovy […]

  2. Stephen Says:

    what jar file do you need to put in the /bin/ext folder. I download the zip file, but I don’t see the jar file. can you give me the fill name?

  3. Stephen Says:

    has anyone ever configured jtds to work with SOAP UI (free). I’m struggling…any help would be appreciated


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: