JesseN JesseN - 2 months ago 16
Groovy Question

How do I pass a collection/ list of results into a SQL IN Statement in Groovy?

Using Groovy in SoapUI/ ReadyAPI, I am trying to pass the results of one query into another query's IN statement to be able to run an assertion against the results. However, I am not getting the results I want.

Here is what I have now. I am getting the groupNumber from a JDBC query and passing it to the first query to get all associate contracts. I am saving those results to a collection.

I then take the results of that query and turn it into inStatement. I need to have the results wrapped in single quotes, separated by commas.

After that is where things go downhill for me. Currently, I have the results of my query saving to a collection, maybe this is not the best way? If I try to pass $inStatement in the query, I get an empty collection. If I try to pass the code I am using to create inStatement, I get compilation errors for unexpected tokens (this varies depending on how I try to pass the code, wrapped in double quotes, single quotes, etc. but always get a compilation error).

This is my code:

def groupBillPolicies = []
sql.eachRow("SELECT column1 as column1 FROM table1 WHERE groupnumber = $groupNumber") {row ->
groupBillPolicies.add(row.column1)
}

String inStatement = groupBillPolicies.collect{"'$it'"}.join(",")

def sqlDataObjects = []
sql.eachRow("select trim(column2) as column2 from table2 where contract in ($inStatement) order by date desc fetch first 1 rows only") {row ->
sqlDataObjects.add(row.column2)
assert 'Validation Text' == row.column2
}


I have log.info after the inStatement to see what would be passed and it looked correct to me. Each value was wrapped in single quotes and separated by a comma, but again, passing that into my second query returns an empty collection.

I have also tried the code below instead of creating a collection of results in the first query.

def groupBillPolicies = sql.rows("SELECT column1 as column1 FROM table1 WHERE groupnumber = $groupNumber")


This is returning results in a format that I can not use.

[[Column:Value], [Column:Value]]


I was able to remove the "Column:" from the string using replaceAll, but I cannot remove the brackets around each value.

How would I go about correctly passing in a collection into an IN statement?

Thanks in advance for any assistance.

Rao Rao
Answer

How about this?

def response = [["Column1":"Value1"], ["Column2":"Value2"]]
response.collect{ item ->
   item.collect {
         println "key : ${it.key}, value : ${it.value}"
   }
}

I believe you can execute your sql query inside item.collect