Forum Discussion

ssteinhorst's avatar
ssteinhorst
Occasional Contributor
7 years ago

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>
  • ssteinhorst's avatar
    ssteinhorst
    7 years ago

    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" 

     

    • ssteinhorst's avatar
      ssteinhorst
      Occasional Contributor

      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" 

       

      • Olga_T's avatar
        Olga_T
        SmartBear Alumni (Retired)

        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.