Ask a Question

Verify multiple rows and then data drive

SOLVED
endorium
Frequent Contributor

Verify multiple rows and then data drive

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.

 

Ready.PNGexcel.PNG

45 REPLIES 45

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.



If my answer helped please click on the 'Accept as Solution' button.

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.




---

Click the Accept as Solution button if my answer has helped, and remember to give kudos where appropriate too!
sanj
Super Contributor

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
Frequent Contributor

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 🙂

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.



If my answer helped please click on the 'Accept as Solution' button.
endorium
Frequent Contributor

Thanks.

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

endorium
Frequent Contributor

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
Frequent Contributor

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;

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:

 

2018-07-03_12h15_07.png

 

2. Imported the dummy excel you provided:

 

2018-07-03_12h16_26.png

 

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

 

2018-07-03_12h17_28.png

 

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

 

2018-07-03_12h18_21.png

 

I will come back with more info later today.



If my answer helped please click on the 'Accept as Solution' button.
Lucian
Community Hero

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()
	}
}


If my answer helped please click on the 'Accept as Solution' button.
cancel
Showing results for 
Search instead for 
Did you mean: