Forum Discussion

Gkm's avatar
Gkm
Contributor
9 years ago

how to verify json response with Database SQL Server

I want to verify my JSON response with my Database. I have created a script but getting an error.  Here is my JSON Response,   Here is my Data Base response,   My groovy script with ...
Gkm's avatar
Gkm
Contributor
9 years ago

nmrao Your code works for me , groovy is new to me that's why understanding issue but finally :)

 

@groovy.transform.Canonical
class Model {
    def caseid
    def name

    def buildJdbcData(row) {
        row.with {
            caseid = CASEID
            name = NAME            
        }
    }

    def buildJsonData(root){
    	   root.with {
                caseid = CaseID
                name = Name
    	}
   }
}

def jdbcResponse = context.expand('${cases JDBC#ResponseAsXml}')

def jsonResponse = context.expand('${cases#ResponseAsXml}')

//Parsing the jdbc and build the jdbc model object list
def results = new XmlSlurper().parseText(jdbcResponse)
def jdbcDataObjects = []
results.ResultSet.Row.each { row ->
    jdbcDataObjects.add(new Model().buildJdbcData(row))
}

//Parsing the json and build the json model object list
def Response = new XmlSlurper().parseText(jsonResponse)
def jsonDataObjects = []
Response.Root.e.each { root ->
    jsonDataObjects.add(new Model().buildJsonData(root))
}

if (jdbcDataObjects.size() != jsonDataObjects.size()) {
    log.info ("Jdbc resultset size is : ${jdbcDataObjects.size()} and Json result size is : ${jsonDataObjects.size()}")
}
assert jdbcDataObjects == jsonDataObjects, "Comparison of Jdbc and Json data is failed"
  • Gkm's avatar
    Gkm
    Contributor
    9 years ago

    nmraoI have tried but script is passing every time if i give wrong caseid still, but give error if provide wrong name.

    Kindly highlight which step need to be correct 

    • nmrao's avatar
      nmrao
      Champion Level 3
      9 years ago
      Gkm, probably, if the data is available(in text format both jdbc response and json, not image) that would be helpful.
      • Gkm's avatar
        Gkm
        Contributor
        9 years ago
        @groovy.transform.Canonical
class Model {   
    def caseid
    def name

    def buildJdbcData(row) {
        row.with {
            caseid = CASEID
            name = NAME
        }
    }

    def buildJsonData(root){
    	   root.with {
             caseid = CaseID 
             name = Name              
    	}
   }
}

def jdbcResponse = '''<Results>
    <ResultSet fetchSize="128">
        <Row rowNumber="1">
            <CASEID>3</CASEID>
            <NAME>Crime123</NAME>
        </Row>
        <Row rowNumber="2">
            <CASEID>1</CASEID>
            <NAME>robbery</NAME>
        </Row>
    </ResultSet>
</Results>'''

def jsonResponse ='''<Response xmlns="http://local/Cases/ListAll">
   <Entity>Cases Grid</Entity>
   <EntityID/>
   <OperType>0</OperType>
   <Root>
      <e>
         <CaseID>3</CaseID>
         <Name>Crime123</Name>
      </e>
      <e>
         <CaseID>4</CaseID>
         <Name>robbery</Name>
      </e>
   </Root>
   <TotalCount>2</TotalCount>
</Response> '''

//Parsing the jdbc and build the jdbc model object list
def results = new XmlSlurper().parseText(jdbcResponse)
def jdbcDataObjects = []
results.ResultSet.Row.each { row ->
    jdbcDataObjects.add(new Model().buildJdbcData(row))
}

//Parsing the json and build the json model object list
def Response = new XmlSlurper().parseText(jsonResponse)
def jsonDataObjects = []
Response.Root.e.each { root ->
    jsonDataObjects.add(new Model().buildJsonData(root))
}

if (jdbcDataObjects.size() != jsonDataObjects.size()) {
    log.info ("Jdbc resultset size is : ${jdbcDataObjects.size()} and Json result size is : ${jsonDataObjects.size()}")
}
assert jdbcDataObjects == jsonDataObjects, "Comparison of Jdbc and Json data is failed"

        Case Id are different it should fail for row 2