How to Connect to DB in SoapUI with Groovy Scripting

1)import groovy.sql.Sql : Import the SQL package

2)def request = context.expand('${TC_Name#request}')
def holder = new XmlHolder(request)
OrderNum = holder.getNodeValue("//v1:tagname")
log.info OrderNum  : This will hold the request that is being received from the down stream application with the order number

3)getNodeValue() : This will get the tag value of the xpath passed

4) def dbUrl="jdbc:oracle:thin:@tqwertrxx.vxc.abc.com:15xx:tqwetrxx"
def dbUsername="abc"
def dbPassword="abcc#xxxx"
def dbDriver="oracle.jdbc.driver.OracleDriver"
def db = Sql.newInstance(dbUrl , dbUsername , dbPassword , dbDriver)
// Connect to DB

5) def q1 = "select order_id from order where order_name = $OrderNum“
// Writing the query and passing a variable to it, remember to put the dollar sign to make groovy understand that it is a variable

6) result = db.rows(q1) : This will execute the query and return the LIST of data returned from DB in a LIST format

7) result.get(0).get("order_id") // This means, from the 0th col get the value of order_process_status_id
Similarly if the query returns 2 rows then to access the second row we write result.get(1).get("order_id")

8)Once the row from DB are retrieved use them to perform any type of assertion that is required