Forum Discussion

nsinghal's avatar
nsinghal
New Contributor
7 months ago
Solved

Compare API response key data against MySQL data using ReadyAPI GUI

Hi, I am exploring ReadyAPI tool for automating our API test cases. The API request returns a response containing multiple record sets. One of the requirement is to compare all the instance values of a field in API response against the data returned by a MYSQL database for the same field.I am not able to get exactly how to compare both sets of data for particular field using ReadyAPI GUI. 
Please guide me with the required steps.
In case scripting is required, please help me with that too.

API response for ref:
<Response xmlns="https://xyz.com/abc">
   <content>
      <e>
         <cusip>003506BT8</cusip>
         <issueId>1714783</issueId>
         <issuerName>XYZ</issuerName>
         <issueDesc>ABC</issueDesc>
         <series>2020</series>
    </e>
      <e>
         <cusip>0035063DF8</cusip>
         <issueId>1714653</issueId>
         <issuerName>XFZ</issuerName>
         <issueDesc>ARC</issueDesc>
         <series>2030</series>
    </e>
    </content>
</Response>

JDBC data:
<Results>
   <ResultSet fetchSize="0">
      <Row rowNumber="1">
         <BOND.CUSIP>003506BT8</BOND.CUSIP>
      </Row>
      <Row rowNumber="2">
         <BOND.CUSIP>0035063DF8</BOND.CUSIP>
      </Row>
</ResultSet>
</Results>

Any help is appreciated.

  • Hi,

    You do need script help for the assertion that Response and JDBC values match.

    As I cannot access your API or DB, I've mocked the responses.

    Here's my test case....

     

    The contents of 'Return XML' looks like...

    // Note : triple quotes allow multi-line strings.
    
    def xmlString = """<Response xmlns="https://xyz.com/abc">
       <content>
          <e>
             <cusip>003506BT8</cusip>
             <issueId>1714783</issueId>
             <issuerName>XYZ</issuerName>
             <issueDesc>ABC</issueDesc>
             <series>2020</series>
        </e>
          <e>
             <cusip>0035063DF8</cusip>
             <issueId>1714653</issueId>
             <issuerName>XFZ</issuerName>
             <issueDesc>ARC</issueDesc>
             <series>2030</series>
        </e>
        </content>
    </Response>"""
    
    return xmlString;

    And here's the "Return JDBC" contents...

    def jdbcString = """<Results>
       <ResultSet fetchSize="0">
          <Row rowNumber="1">
             <BOND.CUSIP>003506BT8</BOND.CUSIP>
          </Row>
          <Row rowNumber="2">
             <BOND.CUSIP>0035063DF8</BOND.CUSIP>
          </Row>
    </ResultSet>
    </Results>""";
    
    return jdbcString;
    

    And finally, the Groovy script that does the compare...

     import groovy.xml.XmlSlurper;  // This is required for turning our mock XML string into real traversable XML.
     
    // Mocking the response and JDBC call.
    def apiResponseAsXmlString = context.expand( '${Return XML - Groovy Script#result#declare namespace ns1=\'https://xyz.com/abc\'; //ns1:Response[1]}' );
    def jdbcResponseAsXMLString = context.expand( '${Return JDBC - Groovy Script#result#//Results[1]}' );
    
    // Let's convert our mock XML Strings into real XML.
    def apiResponseXML = new XmlSlurper().parseText(apiResponseAsXmlString);
    def jdbcResponseXML = new XmlSlurper().parseText(jdbcResponseAsXMLString);
    
    // Let's just check we're playing with XML from both sources.
    log.info(apiResponseXML.name());
    assert apiResponseXML.name() == 'Response';
    
    log.info( jdbcResponseXML.name());
    assert jdbcResponseXML.name() == 'Results';
    
    // Yep, if we've go this far, we're playing with XML.
    
    // Let's now compare CUSIP from both sources...
    // Firstly, let's just log CUSIP
    log.info("API Response - " + apiResponseXML.content.e[0].cusip.text());
    log.info("JDBC Response - " + jdbcResponseXML.ResultSet.Row[0].text());
    
    // Now to compare CUSIP.  Firstly, put the CUSIP value from repsonse and JDBC into VARs.  You don't have but makes it more readable.
    apiCUSIP = apiResponseXML.content.e[0].cusip.text();
    jdbcCUSIP = jdbcResponseXML.ResultSet.Row[0].text();
    
    assert apiCUSIP == jdbcCUSIP;
    
    // If the next line logs, we've successfully asserted!
    log.info ("API and JDBC CUSIP values match!");

    I'm not going to explain the script further, the comments in the script should be helpful enough.  If not, reply with your questions.

3 Replies

  • ChrisAdams's avatar
    ChrisAdams
    Champion Level 3

    Hi,

    You do need script help for the assertion that Response and JDBC values match.

    As I cannot access your API or DB, I've mocked the responses.

    Here's my test case....

     

    The contents of 'Return XML' looks like...

    // Note : triple quotes allow multi-line strings.
    
    def xmlString = """<Response xmlns="https://xyz.com/abc">
       <content>
          <e>
             <cusip>003506BT8</cusip>
             <issueId>1714783</issueId>
             <issuerName>XYZ</issuerName>
             <issueDesc>ABC</issueDesc>
             <series>2020</series>
        </e>
          <e>
             <cusip>0035063DF8</cusip>
             <issueId>1714653</issueId>
             <issuerName>XFZ</issuerName>
             <issueDesc>ARC</issueDesc>
             <series>2030</series>
        </e>
        </content>
    </Response>"""
    
    return xmlString;

    And here's the "Return JDBC" contents...

    def jdbcString = """<Results>
       <ResultSet fetchSize="0">
          <Row rowNumber="1">
             <BOND.CUSIP>003506BT8</BOND.CUSIP>
          </Row>
          <Row rowNumber="2">
             <BOND.CUSIP>0035063DF8</BOND.CUSIP>
          </Row>
    </ResultSet>
    </Results>""";
    
    return jdbcString;
    

    And finally, the Groovy script that does the compare...

     import groovy.xml.XmlSlurper;  // This is required for turning our mock XML string into real traversable XML.
     
    // Mocking the response and JDBC call.
    def apiResponseAsXmlString = context.expand( '${Return XML - Groovy Script#result#declare namespace ns1=\'https://xyz.com/abc\'; //ns1:Response[1]}' );
    def jdbcResponseAsXMLString = context.expand( '${Return JDBC - Groovy Script#result#//Results[1]}' );
    
    // Let's convert our mock XML Strings into real XML.
    def apiResponseXML = new XmlSlurper().parseText(apiResponseAsXmlString);
    def jdbcResponseXML = new XmlSlurper().parseText(jdbcResponseAsXMLString);
    
    // Let's just check we're playing with XML from both sources.
    log.info(apiResponseXML.name());
    assert apiResponseXML.name() == 'Response';
    
    log.info( jdbcResponseXML.name());
    assert jdbcResponseXML.name() == 'Results';
    
    // Yep, if we've go this far, we're playing with XML.
    
    // Let's now compare CUSIP from both sources...
    // Firstly, let's just log CUSIP
    log.info("API Response - " + apiResponseXML.content.e[0].cusip.text());
    log.info("JDBC Response - " + jdbcResponseXML.ResultSet.Row[0].text());
    
    // Now to compare CUSIP.  Firstly, put the CUSIP value from repsonse and JDBC into VARs.  You don't have but makes it more readable.
    apiCUSIP = apiResponseXML.content.e[0].cusip.text();
    jdbcCUSIP = jdbcResponseXML.ResultSet.Row[0].text();
    
    assert apiCUSIP == jdbcCUSIP;
    
    // If the next line logs, we've successfully asserted!
    log.info ("API and JDBC CUSIP values match!");

    I'm not going to explain the script further, the comments in the script should be helpful enough.  If not, reply with your questions.

    • richie's avatar
      richie
      Community Hero

      Hey ChrisAdams 

      Great answer!  I would give you some Kudos for this fella - but since the UI changed, I can't find a Kudos button to give you the cred you deserve!

      Niceone!

      rich