SoapUI - using WHERE IN clause and Groovy code to concatenate strings

Recently, I worked on a project that was using SoapUI Pro to test an application. One of the testers had a working test case in SoapUI but wanted it done differently and therefore, approached me for a solution. Following outlines the scenario and a quick groovy code I wrote to address what he really wanted.

Scenario:
He was connecting to a database using SoapUI JDBC step and retrieving more than one result for a SQL query. He was then using FOR loop to iterate through all the values. In the FOR loop, he had another JDBC step which took result value as a parameter and returned an appropriate response (using assertion to validate the response).

What he wanted:
He wanted to get rid of FOR loop and pass all the values returned from the first JDBC step into second JDBC step.

Solution:
The solution I came up with was to concatenate all the values from the first JDBC step using a groovy code and then pass the returned string from the code into a separate JDBC step. Also change the SQL query in the final JDBC step to use WHERE IN clause.

For blogging purpose, I am connecting to MySQL database on my local machine using SoapUI Pro 4.5.2 (trial version).

I have created two tables in MySQL database. First table is a class which contains StudentName and ClassName. Second table is a Subject with StudentName and Subject fields. For code demonstration purpose, I will be querying the database to return me names of all the stundent that are in Class 3. Then I will concatenate the names using groovy code and pass it to a separate JDBC step to get me the StudentName and the Subject they are enrolled in.

NOTE:

• This code does not cater for all the possibilities as it is only for blogging purpose. 
• Also make sure you are adding the MySQL JDBC driver in SoapUI ext folder so you can connect to the database.
• I am sure the code below can be refined further. saving xmlRecCount.toInteger() value to a parameter.

Steps:

1. Add a JDBC Step to the test case. This JDBC step with query the class table and return student names that are in class 3. 

2. Add a DataGen step to the test case. In this step create a parameter with "Type" as "Script". This Script will take response from the first step, concatenate all the student names into one string and pass it to the parameter. 

3. Finally add another JDBC step to the test case. This step will query the Subject table uisng WHERE IN Clause and IN value will be the parameter created in step 2 above.

Code:

def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context)
def holder = groovyUtils.getXmlHolder("GetStudentName#ResponseAsXml")

def concSqlString=""   //initialize parameter string concSqlString
def cnt =1             //initialize counter

//Get the total count of records that have the CLASS.STUDENTNAME
def xmlRecCount=holder["count(//Results[1]/ResultSet[1]/Row/CLASS.STUDENTNAME)"]


//check if only one row is returned
if (xmlRecCount.toInteger()==1)
{
      node = holder.getNodeValue('//Results[1]/ResultSet[1]/Row/CLASS.STUDENTNAME')
     concSqlString=concSqlString +"\""+node+"\""
}
else{
        //for each node, concatenate the node value to concSqlString variable
 for (node in holder['//Results[1]/ResultSet[1]/Row/CLASS.STUDENTNAME'])
 {
 if(cnt<xmlRecCount.toInteger()){
  concSqlString=concSqlString+"\""+node+"\","
  cnt=cnt+1
 }
 else
 concSqlString=concSqlString +"\""+node+"\""
 }
}

//return the concSqlString 
return concSqlString