How to access database from SoapUI for test-data generation?

Question

The web-services testing would cover more cases if it is data-driven. So it is better to generate random data (but from the same boundary) for each request.

E.g. in test case 1 we check users id from DB with correct age and valid driver licence, in test case 2 user id with age less then 21, etc.

How to connect to DB in SoapUI (free version)?

Answer 1

First of all you need to download DB driver, e.g. ojdbc7.jar and place it in bin\ext folder of SoapUI dir:

C:\Program Files (x86)\SmartBear\SoapUI-5.3.0\bin\ext

Then you can use groovy script to create connection to DB, query test values and assign it to SoapUI parameter:

import groovy.sql.Sql 
import com.eviware.soapui.support.GroovyUtils

log.info("")
log.info("  init DB connection...")

def scriptServer = "db.db.test.db.db.ru"
def scriptPassword = "password"
def scriptSid = "oraSid"
def scriptUsername = "dbUser"
def scriptDBport = "1111"

com.eviware.soapui.support.GroovyUtils.registerJdbcDriver("oracle.jdbc.driver.OracleDriver")
sql = Sql.newInstance("jdbc:oracle:thin:@${scriptServer }:${scriptDBport}:${scriptSid}", "${scriptUsername}", "${scriptPassword}", "oracle.jdbc.OracleDriver") 

def queryUserId= "" +
"select userId from " +
"  (select userId from " +
"  users_table " +
"  where age between 21 and 80 and driver_licence = 1"
"  order by dbms_random.value) " +
"where rownum = 1"

def res = sql.firstRow(queryUserId)
def user_id = res[0].toString()

testRunner.testCase.setPropertyValue( "user_id" , user_id)

sql.close()

And then use these variables (as SoapUI castom properties) in generation of requests & assertions of responses:

<ServiceProperties>
   <UserId>${#TestCase#user_id}</UserId>
</ServiceProperties>

Answer 2

There is no need for Groovy. You can use a JDBC TestStep for that.

https://www.soapui.org/jdbc/getting-started.html

You can extract value from the, as a xml presented, results at the same way as you can on a xml respons from a webservice.

To do this you can ad a property trasfer step betweeen the jdbc step and the SOAP request. Create a new transfer by clicking the green + sign. In the source select the jdbc step and pick response asxml from the property list. Using xpath you can specify the field you want to get the data from. In the target field select the SOAP request, and pick request form property. Here too you can use xpath te specify the destination of the value.