Verify multiple rows and then data drive

[endorium]

First off I am new to Soup/ReadyAPI. 

I have an API that returns row information for a grid.

I have assertions for the count of rows and the first row. I am also verifying the data against an excel sheet which is all working fine.

The problems comes when I add more than one row of data in the excel sheet as i want to validate multiple rows. I cannot get this to work. Ideally I want to validate 3 rows on each run looking at the same excel sheet.

Any ideas how I do this? Eventually I want to datadrive this as well.

Screenshots of assertions and data returned plus excel sheet.

[Lucian]

It should be quite simple to do it with groovy. The following can be the steps:

1. HTTP Request

2. DataSource

3. GroovyScript

4. DataSourceLoopEnd

The magic should happen in step 3 where you would need to:

- extract the elements in the response

- compare them with the current iteration elements.

Just write to me in private if you want and I'll guide you. We can skype if you want.

[msiadak]

I can write up a groovy script, but I need an idea of what your response looks like, as well as the excel spreadsheet. If you can attach sample / sanitized files that we could use to work on this, that would be much appreciated and very helpful.

[sanj]

I do not think googling or stackoverflow will help you here.

Once a response and excel spread is given it should be easy to come up with a solution

[endorium]

Thanks again for help with this. Attached is a sample spreadsheet and the json response I can get.

So using the ID column in the excel sheet as the unique identifier. It should then find the corresponding ID in the JSON response and see if the rows data match.

It should then cycle to the next row and do the same.

Please forgive any more questions as you may be able to help but I really want to understand anything you come back with 🙂

[Lucian]

I don't have my work laptop at home now but this is very simple to do. The setup would look something like:

1. Datasource

2. HTTP Request

3. Groovy

4. DataSourceEndLoop

In the groovy the pseudocode can look something like:

1. Read the current Datasource row id

2. Get the json node where the id is the same as in the previous step

3. Just compare the Datasource#value with JsonSubNode#value

And that should be all. If nobody will do this for you I will try to do it on Monday. You can also try to do this of course.

[endorium]

Thanks.

I will give it a go today and see how far I get.......

[endorium]

I have been trying on the weekend and not been successful.

As I said I am very new to this. I seem to find bits of information that might be useful but end up getting no where.

Any help would be really appreciated and if need be I can understand it by reverse engineering it (and asking questions)

[endorium]

Below is as far as i have gotten. This would compare the entire response though and I only need to verify certain rows based off the ID in the first column.

import com.eviware.soapui.*;
import java.lang.*;
import java.util.*;
import java.io.*;
import java.net.*;
import groovy.lang.*;
import groovy.util.*;
import groovy.json.JsonSlurper;
import java.io.FileInputStream;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

//Create an array to hold the cell values
def cellValues = [];
//Get the JSON in a format to easily access
def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context);
def ResponseMessage = testRunner.testCase.testSteps["TestsGrid"].getPropertyValue("response");
def jsonResult = new JsonSlurper().parseText(ResponseMessage);


//Get the number of recommended expressions nodes
def rspnsNumValue = jsonResult.value.size();
//Get the file from the location specified in the test case custom properties
def fs = new FileInputStream("C://Users//snejad//Desktop//Tester.xls")
def wb = new HSSFWorkbook(fs)


if(wb != null && !wb.equals(""))
{
    HSSFSheet sheet = wb.getSheet("Sheet1");
    HSSFRow row;
    HSSFCell cell;

    def totalNumRows;
    def numRows;
    //Get the total number of rows with data on the sheet
    totalNumRows = sheet.getPhysicalNumberOfRows();
    //log.info " The total number of rows are " + totalNumRows;
    //Get the number of rows without the header row
    numRows = totalNumRows - 1;
    //log.info " The number of rows to use are " + rows;

    def cols = 0;

    cols = sheet.getRow(0).getPhysicalNumberOfCells();
    //log.info " The total number of columns are " + cols;

    if(numRows == rspnsNumValue)
    {
        for(int i = 1; i < totalNumRows; i++)
        {
            row = sheet.getRow(i);
            //log.info " Row # " + i;

            for(int j = 0; j < cols; j++)
            {
                //log.info "  Cell # " + j;
                cell = row.getCell(j);
                //log.info "  The value in the cell is " + cell;

                cellValues.push(cell);
            }

        }

        log.info jsonResult.product["value"]["imageUrl"].every{it in [cellValues]}

    }

    else
    {
        assert false, "The number of nodes does not match the number of rows.";
    }
}
else
{
    assert false, "The file name is missing.";
}

return;

[Lucian]

Hi,

With some delay today I will start to solve your problem... Firstly I had to do some preparations namely:

1. Created the test layout:

2. Imported the dummy excel you provided:

3. Opened the SoapUI OS and created a mock service with the JSON response you provided:

Now whenever I do a request I get the same response as you provided:

I will come back with more info later today.

[Lucian]

Hereby is the groovy script:

import groovy.json.JsonSlurper

// Get the id from the datasource
def id = context.expand('${DataSource#ID}') as Integer

// Get the response from the GetResponse step
def response = context.expand('${GetResponse#Response}')

// Parse the response with JsonSlurper
def jsonSlurper = new JsonSlurper()
def jsonObject = jsonSlurper.parseText( response )

// Get the element where the ID is the same as in the datasource
for (int i = 0; i < jsonObject.value.size(); i++) {
	if (jsonObject.value[i].Id == id) {
		// Start comparison
		assert jsonObject.value[i].TestID.equals( context.expand('${DataSource#TestId}'))
		assert jsonObject.value[i].TestName.equals( context.expand('${DataSource#TestName}'))
		assert jsonObject.value[i].Assays.equals( context.expand('${DataSource#Assay}'))
		assert jsonObject.value[i].Analyses.equals( context.expand('${DataSource#Analyses}'))
		assert jsonObject.value[i].Cassettes.equals( context.expand('${DataSource#Cassettes}'))
		assert jsonObject.value[i].Enabled == context.expand('${DataSource#Enabled}').toBoolean()
		assert jsonObject.value[i].UserDefined == context.expand('${DataSource#UserDefined}').toBoolean()
	}
}