Sunday, 30 August 2009

Getting soapUI Test Cases to check Databases

I have done some investigating of how to connect to a Database and query to ensure that the data from the SOAP call in soapUI has made it all the way to the DB.

Firstly ensure you have downloaded the relevant JDBC driver jar file and placed it in $SOAPUI_HOME/bin/ext and then restarted soapUI as it only seems to grab the file on restart.

Below is a simple Groovy which Have put in as a test step which will go to the database and check if the you want is there. It will need some additions like the addition of a where clause I would guess. If the count or data for that matter is right it will assert true or false as applicable and show up in results.

import groovy.sql.Sql
def ERROR_MSG = "The count is not 1"
sql = Sql.newInstance("jdbc:mysql://localhost:3306/demo", "demo", "password", "com.mysql.jdbc.Driver")
row = sql.firstRow("SELECT count(*) count FROM foo")
log.info("Count: ${row.count}")
assert (1 == row.count):ERROR_MSG
sql.close()

Also remember before running these test ensure that the database is truncated or your SQL takes into account only to look the results that you want it to look at not ones from earlier runs. If you are testing automatically with Maven you could get Maven to do the cleaning up and prep work.

1 comments:

omatzura said...

Hi Dave,

thanks for your nice posts on soapUI! In this script you might need to surround the assert with a try / finally to be sure the sql.close() is called, otherwise the script will terminate when the assert fails, ie

try
{
assert ...
}
finally
{
sql.close()
}

cheers!

/Ole
eviware.com