cancel
Showing results for 
Search instead for 
Did you mean: 

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

Highlighted
New Contributor

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

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 below for which we would need to extract values i.e. UNIQUEID and ROUTEID using a Groovy script and pass that into my SOAP request as a test case property.


<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>

Regards,

Ajay

5 REPLIES 5
Highlighted
Super Contributor

I like to use the XmlSlurper to parse XML data, here is a standalone example with your data:

 

import groovy.util.XmlSlurper

def xmlData = '''\
<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>'''

def Results = new XmlSlurper().parseText(xmlData)

Results.ResultSet.Row.each(){row ->
	log.info('UNIQUEID = ' + row.UNIQUEID + '; ROUTEID = ' + row.ROUTEID)
}
Highlighted
New Contributor

Thanks for this Radford. A couple of things:

 

1) I need the code to dynamically take its input from a JDBC response as this code will need to run 100 times for our test requirements so I need to parse the JDBC response as XML dynamically. 

2) Once I have extracted that data from the JDBC response, can I populate this data into a single test case property so that I can inject it into a SOAP request?

Regards,

Ajay

Highlighted
Super Contributor

To get the results from the JDBC step I would just use the point and click Get Data functionality. Just right click in the groovy script, select the Get Data item, and select your desired data.

 

As for setting properties via Groovy script. See the following link for examples:

 

https://support.smartbear.com/readyapi/docs/testing/scripts/samples/variable.html

 

But in short, you will see that the test suites, cases and steps all have a setPropertyValue method, so from a Groovy script test step you'll write something like:

 

context.getTestCase().getTestStepByName('StepName').setPropertyValue( 'PropName', 'Value' )
Highlighted
Moderator

Hi everyone,

 

@Radford thank you for sharing your solution and the instructions!

 

@del_exaclee did you manage to accomplish your task? If your question was answered, do you mind accepting Radford's reply as a Solution? This will help other users find the reply easier in the future.

Otherwise, feel free to provide us with updates on the issue.

Thank you in advance,

 

 


Olga Terentieva
SmartBear Assistant Community Manager

Highlighted
New Contributor

Unfortunately Groovy didnt work for us, so we had to code the XML using SQL within in the JDBC request.

Once we had the XML we wanted, we then injected that as a child node within the main SOAP request.

New Here?
Join us and watch the welcome video:
Announcements
TechCorner Leaderboard
Want a fun and easy way to learn ReadyAPI and show off your skills? Try solving weekly TechCorner challenges and get into the Leaderboard!


Challenge Status

Get a full Raw JMS request

Participate!

Compare an expected JSON value and actual response in Events

Participate!

Fetch value/data from JSON response using Groovy Script

See replies!

Get data from Petstore and add it to Excel sheets

Participate!
Top Kudoed Authors