cancel
Showing results for 
Search instead for 
Did you mean: 

Checking unordered result sets from JDBC query

SOLVED
Highlighted
Occasional Contributor

Checking unordered result sets from JDBC query

Hi all, I was looking for some advice on how to handle unsorted JDBC results. The below is an example of some query results, simplified a little to keep it short. We have rows that are grouped by the DOCID column but they can come in any order other than that. Is there a way to check that a row exists in the result set easily? 

 

For example I need to be able to check that there is a row that has UNITS of 4000, AMOUNT of 12800 and DOCID of 3131459 but that row can appear in any order in the result set so I'm drawing a blank on a way to do this. Any advice is appreciated.

 

 

 

<Results>
   <ResultSet fetchSize="0">
      <Row rowNumber="1">
         <UNITS>4000</UNITS>
         <AMOUNT>12800</AMOUNT>
         <DOCID>3131459</DOCID>
      </Row>
      <Row rowNumber="2">
         <UNITS>6000</UNITS>
         <AMOUNT>1160800</AMOUNT>
         <DOCID>3131459</DOCID>
      </Row>
      <Row rowNumber="3">
         <UNITS>-3100</UNITS>
         <AMOUNT>899000</AMOUNT>
         <DOCID>3131460</DOCID>
      </Row>
      <Row rowNumber="4">
         <UNITS>300</UNITS>
         <AMOUNT>-22500</AMOUNT>
         <DOCID>3131460</DOCID>
      </Row>
   </ResultSet>
</Results>
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Occasional Contributor

Re: Checking unordered result sets from JDBC query

I ended up writing a script for this. Here it is current watchers and future googlers. Note that this solution ignored the Row element in both the results and the expected values since the rowNumber value isn't relevant. I hope this helps!

 

def allslurped = new XmlSlurper().parseText("""<Results>
<ResultSet fetchSize="0">
<Row rowNumber="1">
<UNITS>4000</UNITS>
<AMOUNT>12800</AMOUNT>
<DOCID>3131459</DOCID>
</Row>
<Row rowNumber="2">
<UNITS>6000</UNITS>
<AMOUNT>1160800</AMOUNT>
<DOCID>3131459</DOCID>
</Row>
<Row rowNumber="3">
<UNITS>-3100</UNITS>
<AMOUNT>899000</AMOUNT>
<DOCID>3131460</DOCID>
</Row>
<Row rowNumber="4">
<UNITS>300</UNITS>
<AMOUNT>-22500</AMOUNT>
<DOCID>3131460</DOCID>
</Row>
</ResultSet>
</Results>""")


def expected_row01 = """<Row rowNumber="1">
<UNITS>4000</UNITS>
<AMOUNT>12800</AMOUNT>
<DOCID>3131459</DOCID>
</Row>"""

def expected_row02 = """<Row rowNumber="2">
<UNITS>-3100</UNITS>
<AMOUNT>899000</AMOUNT>
<DOCID>3131460</DOCID>
</Row>"""
def expected_row03 = """<Row rowNumber="3">
<UNITS>6000</UNITS>
<AMOUNT>1160800</AMOUNT>
<DOCID>3131459</DOCID>
</Row>"""
def expected_row04 = """<Row rowNumber="4">
<UNITS>300</UNITS>
<AMOUNT>-22500</AMOUNT>
<DOCID>3131460</DOCID>
</Row>"""

def expected_rows = [new XmlSlurper().parseText(expected_row01),
new XmlSlurper().parseText(expected_row02),
new XmlSlurper().parseText(expected_row03),
new XmlSlurper().parseText(expected_row04)]

for(item in allslurped.ResultSet.Row) {
assert expected_rows.contains(item) : "Database has unexpected data: " + item
expected_rows.remove(item)

}
assert expected_rows.size() == 0 : "Expected row(s) not found: " + expected_rows
assert allslurped.ResultSet.Row.size() == 4 : "Database results are too long, expected 4" 

 

View solution in original post

3 REPLIES 3
Highlighted
New Member

Re: Checking unordered result sets from JDBC query

I have this problem too

Highlighted
Occasional Contributor

Re: Checking unordered result sets from JDBC query

I ended up writing a script for this. Here it is current watchers and future googlers. Note that this solution ignored the Row element in both the results and the expected values since the rowNumber value isn't relevant. I hope this helps!

 

def allslurped = new XmlSlurper().parseText("""<Results>
<ResultSet fetchSize="0">
<Row rowNumber="1">
<UNITS>4000</UNITS>
<AMOUNT>12800</AMOUNT>
<DOCID>3131459</DOCID>
</Row>
<Row rowNumber="2">
<UNITS>6000</UNITS>
<AMOUNT>1160800</AMOUNT>
<DOCID>3131459</DOCID>
</Row>
<Row rowNumber="3">
<UNITS>-3100</UNITS>
<AMOUNT>899000</AMOUNT>
<DOCID>3131460</DOCID>
</Row>
<Row rowNumber="4">
<UNITS>300</UNITS>
<AMOUNT>-22500</AMOUNT>
<DOCID>3131460</DOCID>
</Row>
</ResultSet>
</Results>""")


def expected_row01 = """<Row rowNumber="1">
<UNITS>4000</UNITS>
<AMOUNT>12800</AMOUNT>
<DOCID>3131459</DOCID>
</Row>"""

def expected_row02 = """<Row rowNumber="2">
<UNITS>-3100</UNITS>
<AMOUNT>899000</AMOUNT>
<DOCID>3131460</DOCID>
</Row>"""
def expected_row03 = """<Row rowNumber="3">
<UNITS>6000</UNITS>
<AMOUNT>1160800</AMOUNT>
<DOCID>3131459</DOCID>
</Row>"""
def expected_row04 = """<Row rowNumber="4">
<UNITS>300</UNITS>
<AMOUNT>-22500</AMOUNT>
<DOCID>3131460</DOCID>
</Row>"""

def expected_rows = [new XmlSlurper().parseText(expected_row01),
new XmlSlurper().parseText(expected_row02),
new XmlSlurper().parseText(expected_row03),
new XmlSlurper().parseText(expected_row04)]

for(item in allslurped.ResultSet.Row) {
assert expected_rows.contains(item) : "Database has unexpected data: " + item
expected_rows.remove(item)

}
assert expected_rows.size() == 0 : "Expected row(s) not found: " + expected_rows
assert allslurped.ResultSet.Row.size() == 4 : "Database results are too long, expected 4" 

 

View solution in original post

Highlighted
Moderator

Re: Checking unordered result sets from JDBC query

Hi all,

 

@ssteinhorst thank you so much for sharing your solution with the Community!

You can now mark the post where you provided the script as a Solution so that it could be easily found by other users in the future. Thanks in advance.

 

 


Olga Terentieva
SmartBear Assistant Community Manager

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

Compare an expected JSON value and actual response in Events

Participate!

Fetch value/data from JSON response using Groovy Script

See replies!

Filtering data retrieved from a DataSource

See replies!

Get data from Petstore and add it to Excel sheets

Participate!
Top Kudoed Authors