Forum Discussion

Gkm's avatar
Gkm
Contributor
8 years ago
Solved

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 error message,

Script Error.png

Can anyone please help me figure out what is wrong with this script.

I will be very appreciating if there is any alternate way to do this.

 

Thanks,

Geeta

21 Replies

    • Gkm's avatar
      Gkm
      Contributor

      Thanks for reply nmrao, I have updated your code as per my case. 

      @groovy.transform.Canonical
      import groovy.json.JsonSlurper
      class Model {
          def caseID
          def name
          def description
          def createdOn
        
          def buildJdbcData(row) {
              row.with {
                  caseID = CaseID
                  name = Name
                  description = Description
                  createdOn = CreatedOn     
              }
          }
      
          def buildJsonData(Root){
              caseID = CaseID
              name = Name
              description = Description
              createdOn = CreatedOn
          }
      }
      
      def jdbcResponse = context.expand('${JDBC Request#Response}')
      def restResponse = context.expand('${Cases#Response}')
      
      //Parsing the jdbc and build the jdbc model object list
      def results = new JsonSlurper().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 json = new JsonSlurper().parseText(restResponse)
      def jsonDataObjects = []
      json.Root.each { Root ->
          jsonDataObjects.add(new Model().buildJsonData(Root))
      }
      
      if (jdbcDataObjects.size() != jsonDataObjects.size()) {
        System.err.println("Jdbc resultset size is : ${jdbcDataObjects.size()} and Json result size is : ${jsonDataObjects.size()}")
      }
      assert jdbcDataObjects == jsonDataObjects, "Comparison of Jdbc and Json data is failed"

      But I'm getting an error, Text must not be null

       

      Thanks,

      Geeta

       

       

      • nmrao's avatar
        nmrao
        Champion Level 3
        Jdbc response is Xml, so please use XmlSlurper to parse.
    • Gkm's avatar
      Gkm
      Contributor

      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

        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 

  • shriram's avatar
    shriram
    New Contributor

    Gkm did you found the solution with regards to how to do it?

     

    I'm after the similar kind of testing and struggling to find an answer.

     

    I have established jdbc connection.

     

    Now i need to verify the input xml against the database fields.

     

    the xml elements and DB column names are different.

     

    already i mapped the xml elements in a property, now i need to map the db fields against these elements and need to assert the db values are same as the xml input fields.

     

    Any help would be much appreciated.

     

    • nmrao's avatar
      nmrao
      Champion Level 3
      shriram, I would suggest you to create new question and provide the details of your case along with sample data.