Forum Discussion

cstott1's avatar
cstott1
Occasional Contributor
9 years ago
Solved

XPATH assertion for an expression containing another value, field length, field type and is not NULL

Hi,

 

I have used a JDBC driver to run a SQL query. The below is the result

 

<Results>
    <ResultSet fetchSize="128">
        <Row rowNumber="1">
            <PIECEID>1820480</PIECEID>
            <CONSIGNMENTID>1166645</CONSIGNMENTID>
            <TRACKINGNUMBER>907598985733</TRACKINGNUMBER>
            <BARCODE>001390759898573373</BARCODE>
            <ISBILLABLE>1</ISBILLABLE>
            <ISCANCELLED>0</ISCANCELLED>
        </Row>
    </ResultSet>
</Results>

 

I am trying to setup 4 assertions for the below but am struggling with what to put in the expected result.

 

1) <BARCODE> contains the <TRACKINGNUMBER>

2) <ISCANCELLED> is not NULL

3) <PIECEID> is 7 digits long

4)  <ISBILLABLE> is numerical

 

Also being very new to  XPATH where is the best place to learn it for the purposes of expected results in SOAPUI

  • nmrao's avatar
    nmrao
    9 years ago

    Below groovy script does what you needed.

     

    It is taken one positive Row and a negative Row for the sake of test evaluation.

     

    /**
    * this class holds the all 4 assertions result along with its rowNumber
    **/
    class AssertionResult {
      def rowNumber
      def failures
    }
    
    /**
    * below method will validates each row and returns AssertionResult object
    **/
    def validateRow(row) {
       def number = row.@rowNumber
       def barCode = row.BARCODE.text()
       def trackingNumber = row.TRACKINGNUMBER.text()
       def tempResult = new AssertionResult(rowNumber: number, failures: null)
       StringBuffer tempFailure = new StringBuffer()
       if (!barCode.contains(trackingNumber)) {
          tempFailure.append("Bar code does not contain Tracking number\n")
       }
       if (!row.ISCANCELLED.text()) {
          tempFailure.append("Is cancelled empty\n")
       }
       if (!(row.PIECEID ==~ "\\d{7}")){
          tempFailure.append("PieceId is not of 7 digits long\n")
       }
       def billable
       try { 
           billable = row.ISBILLABLE.text() as Long
           if (!(billable instanceof java.lang.Long)) {
               tempFailure.append("Is billable is not a number\n")
           }
       } catch (NumberFormatException e) {
          tempFailure.append("Is billable is not a number\n")
       }   
       if (tempFailure) {
          tempResult.failures = tempFailure.toString()
       }
       tempResult
    }
    
    
    /**
    * below is the sample result set
    * you may directly assign the response from jdbc test step here later
    **/
    def jdbcResponse = '''
    <Results>
        <ResultSet fetchSize="128">
            <Row rowNumber="1">
                <PIECEID>1820480</PIECEID>
                <CONSIGNMENTID>1166645</CONSIGNMENTID>
                <TRACKINGNUMBER>907598985733</TRACKINGNUMBER>
                <BARCODE>001390759898573373</BARCODE>
                <ISBILLABLE>1</ISBILLABLE>
                <ISCANCELLED>0</ISCANCELLED>
            </Row>
            <Row rowNumber="2">
                <PIECEID>18204811</PIECEID>
                <CONSIGNMENTID>1166646</CONSIGNMENTID>
                <TRACKINGNUMBER>907598985734</TRACKINGNUMBER>
                <BARCODE>001390759898573375</BARCODE>
                <ISBILLABLE>a</ISBILLABLE>
                <ISCANCELLED/>
            </Row>
        </ResultSet>
    </Results>'''
    def results = new XmlSlurper().parseText(jdbcResponse)
    def assertionResults = []
    
    /**
    * looping thru each Row and validating the row data and 
    * collecting result into a list
    **/
    results.ResultSet.Row.each {
       assertionResults.add(validateRow(it))
    }
    
    /**
    * check the validation result and log errors if any
    * and then finally show if the resultset data is passed the 
    * mentioned 4 conditions or not
    **/
    def finalResult = true
    assertionResults.each { res ->
      if (res.failures) {
        finalResult = false
        log.error "Row number ${res.rowNumber} has assertion errors:\n ${res.failures}"
      }
    }
    
    assert finalResult, "The data verification on ResultSet has failures as logged above"
    ā€‹

    Note: The reason for using if conditions in validateRow() method becuase, it will stop the assertions on first failure and rest of the verification is not done. 

     

    You may see error something like shown below

    JdbcVerification

     

8 Replies

  • cstott1's avatar
    cstott1
    Occasional Contributor

    Hi,

     

    I have used a JDBC driver to run a SQL query. The below is the result

     

    <Results>
        <ResultSet fetchSize="128">
            <Row rowNumber="1">
                <PIECEID>1820480</PIECEID>
                <CONSIGNMENTID>1166645</CONSIGNMENTID>
                <TRACKINGNUMBER>907598985733</TRACKINGNUMBER>
                <BARCODE>001390759898573373</BARCODE>
                <ISBILLABLE>1</ISBILLABLE>
                <ISCANCELLED>0</ISCANCELLED>
            </Row>
        </ResultSet>
    </Results>

     

    I am trying to setup 4 assertions for the below but am struggling with what to put in the expected result.

     

    1) <BARCODE> contains the <TRACKINGNUMBER>

    2) <ISCANCELLED> is not NULL

    3) <PIECEID> is 7 digits long

    4)  <ISBILLABLE> is numerical

     

    Also being very new to  XPATH where is the best place to learn it for the purposes of expected results in SOAPUI? Is XQUERY what i should learn for this?

    • nmrao's avatar
      nmrao
      Champion Level 3
      I believe script assertion suites better fit in this case.
      • cstott1's avatar
        cstott1
        Occasional Contributor

        Many thanks Rao, i will check that out. To get me started what scripts would you put for those 4 examples?

  • nmrao's avatar
    nmrao
    Champion Level 3

    By the way I like the way you explained the question clearly.
    May be that demanded me to attempt validating a jdbc resultset for first time and looping it in groovy script. Thank you for the question.

    • cstott1's avatar
      cstott1
      Occasional Contributor

      Perfect, many thanks for your help

    • cstott1's avatar
      cstott1
      Occasional Contributor

       Hi Rao, I have tried to write this for one field in the way below:

       

      def pieceid = context.expand( '${OneDX#ResponseAsXml#//Results[1]/ResultSet[1]/Row[1]/PIECEID[1]}' )

      def TrackingNumber = context.expand( '${OneDX#ResponseAsXml#//Results[1]/ResultSet[1]/Row[1]/TRACKINGNUMBER[1]}' )

       

      assert {!TrackingNumber.contains(Pieceid)}

       

      The tracking number is 907598985733 and Pieceid is 1820480....therefore the Pieceid is not in the tracking number. However when I run the script it passes. do you know what i'm doing wrong