Parse jdbc response in Groovy script (until max row number value)

Question

I am using a JDBC request in ReadyAPI 2.5.0 to retrieve data from our database. What I need to do is loop through all of the rows that are returned and pick out specific values for use in my SOAP request later on.

How do I use a Groovy script to take a JDBC response and loop through until all rows have been read? Please note the JDBC response will return a dynamic set of results each time so its important to loop until all rows have been processed.

I have included a sample JDBC response for which we would need to extract values i.e. UNIQUEID and ROUTEID using a Groovy script and pass that into my SOAP request.

<Results>
   <ResultSet fetchSize="128">
      <Row rowNumber="1">
         <UNIQUEID>80382049</UNIQUEID>
         <SOURCESYSTEM>HitsSC</SOURCESYSTEM>
         <ROUTEID>39812</ROUTEID>
         <SHIFTDATE>2018-12-07 00:00:00.0</SHIFTDATE>
      </Row>
      <Row rowNumber="2">
         <UNIQUEID>80382096</UNIQUEID>
         <SOURCESYSTEM>NTExchange</SOURCESYSTEM>
         <ROUTEID>39812</ROUTEID>
         <SHIFTDATE>2018-12-07 00:00:00.0</SHIFTDATE>
      </Row>
      <Row rowNumber="3">
         <UNIQUEID>80382097</UNIQUEID>
         <SOURCESYSTEM>NTExchange</SOURCESYSTEM>
         <ROUTEID>39812</ROUTEID>
         <SHIFTDATE>2018-12-07 00:00:00.0</SHIFTDATE>
      </Row>
      <Row rowNumber="4">
         <UNIQUEID>80382098</UNIQUEID>
         <SOURCESYSTEM>NTExchange</SOURCESYSTEM>
         <ROUTEID>39812</ROUTEID>
         <SHIFTDATE>2018-12-07 00:00:00.0</SHIFTDATE>
      </Row>
   </ResultSet>
</Results>

I have a number of questions. Is the _only_ thing you need from this query the set of distinct IDs and route IDs? Do you want to create a "multimap" from route ID to unique ID? Either `Sql#eachRow()` or `Sql#rows()` would serve (see http://docs.groovy-lang.org/latest/html/api/groovy/sql/Sql.html#rows-groovy.lang.GString-). What purpose do the additional columns serve? — AbuNassar, Dec 11 '18 at 21:22
Additionally, why doesn't the query `LIMIT` the number of rows? This cannot actually be done with `Sql#eachRow()`, because it's not a loop. — AbuNassar, Dec 11 '18 at 21:42

score 0 · Answer 1 · answered Dec 12 '18 at 14:04

You didn't specify the criteria, so I made this that loops through all rows, and picks the rows where SOURCESYSTEM = "NTExchange".

You will need to fetch the JDBC XML response into the xmlResponse, and then add the script after that.

Once you have made your adjustments, you may want to remove the log.info lines.

After the script has run, you will have a HashMap with UNIQUEID - ROUTEID pairs.

def jdbcResult =  new XmlSlurper().parseText(xmlResponse)
def result = new java.util.HashMap<String,String>()
for (def row : jdbcResult.ResultSet.Row) {
    log.info "Looking at row entry with unique id : " + row.UNIQUEID
    if (row.SOURCESYSTEM.equals("NTExchange")) {
        result.put(row.UNIQUEID, row.ROUTEID)
        log.info "Entry added"
    }
    else {
        log.info "Entry skipped"
    }
}

Parse jdbc response in Groovy script (until max row number value)

1 Answers1