Run testexecute with UI tests in a Windows server

Here's an example on mapping values from a data source into a response message. This example reads in an Excel file, then uses a request parameter as a key value to find the values of a row of data in the Excel spreadsheet. 

There's another example on the SoapUI documentation that discusses how to do this with a database connection.  You can navigate here for that example.

This script is broken down into three sections. 
The first section reads in the request parameter that we'll use as our key. Request parameters can be accessed with the mockRequest.getRequest().getQueryString() methods. I take the output of this method and tie it to a string. You'll want to do some parsing on the string to isolate the exact value you want to use. 

The second section then opens a workbook in Excel and looks for the row of data based on our key (the "q" property). The POI libraries make it fairly easy to work with Excel workbooks, and there's lots of resources online that can explain how to use the libraries better than I can. All we're doing here is opening the workbook and scanning each row to see if we find a row of data that matches our key. 

The third section triggers if we find a row of data that matches our key. I use a switch statement here to map the values of the row to a property. The main thing to note is that we use the  requestContext.value format to assign the values to a virtual service property. 

Here's the script I use: 

 

//Read in excel libraries
import org.apache.poi.ss.usermodel.*
import org.apache.poi.xssf.usermodel.XSSFWorkbook

//================================================================//
//Part 1. Read in request parameters. 
//================================================================//

// Get the query string of the request, isolate the "q" parameter
// In this example, the request string looks like: "q=CityName&apikey=abc1234"
def inputString = mockRequest.getRequest().getQueryString()
def inputString2 = mockRequest.getRequest()
log.info inputString2
def q = null
//split query string
def keyValuePairs = inputString.split("&")
keyValuePairs.each { pair ->
    def key = pair.split("=")[0]
    def value = pair.split("=")[1]
    if (key == "q") {
        q = value
    }
}

//================================================================//
//Part 2. Open Workbook. Scan for row of data 
//================================================================//

// Specify the path to your Excel file
def filePath = "<path to your file here>"
// Specify the value to search in the first column
def searchValue = q

// Load the Excel file
def file = new File(filePath)
def workbook = new XSSFWorkbook(file)

// Assuming the data is in the first sheet, you can change the sheet index if needed
def sheet = workbook.getSheetAt(0)

// Find the row with the matching search value
def matchedRow = null

for (Row row : sheet) {
    def cellValue = row.getCell(0)?.toString()
    if (cellValue == searchValue) {
        matchedRow = row
        break
    }
}

//================================================================//
//Part 3. Map cells of the row to response variables. Close workbook.
//================================================================//
if (matchedRow != null) {
    for (Cell cell : matchedRow) {
        int columnIndex = cell.getColumnIndex()
        switch (columnIndex) {
            case 0:
                requestContext.cityCell = cell.toString()
                break
            case 1:
                requestContext.countryCell = cell.toString()
                break
            case 2:
               requestContext.abbrCell = cell.toString()
                break
            default:
                break
        }
}
}else {
    //Do Something 
}

// Close the workbook
workbook.close()

 

The response then looks like this: 

 

{
  "Response": "This is a dynamic response",
  "Location":"${cityCell}",
  "Country" : "${countryCell}",
  "Country Code" : "${abbrCell}"
}

 

My example uses a spreadsheet which provides responses for the 50 most populated cities. As long as we hit a city on this list we'll return a response such as this:

 

The dataset that I'm using in this example looks like this:

 

Below is a scripted example on taking the content of a payload in a POST request and routing the JSON nodes into a virtual response message. To read in the values of the request body I use the mockRequest.getRequestContent() method. This will return a string value that may include some extra content along with the request payload. You'll likely want to trim the extra content out so that the payload is extracted from the original string. Once we have the payload isolated, we can create a JSON slurper to easily extract the content of the JSON nodes. The nodes are then tied to a property value that I reference in the response message using the requestContext.value format, where value is the name of the property that we'll reference in the response.

//Add slurper to isolate json. This makes it easier to parse the nodes in the response
import groovy.json.JsonSlurper

//Save the response content to a string
//This will result in some overhead
def logText =  mockRequest.getRequestContent()


// Find the index of the first '{' character
def startIndex = logText.indexOf('{')
// Extract the substring starting from the '{' character
def result = startIndex != -1 ? logText.substring(startIndex) : logText

//pipe the result into a json slurper for easy parsing
def jsonSlurper = new JsonSlurper()
def JsonBody = jsonSlurper.parseText(result)

//We can map the individual nodes from the Json object that we created previously
 requestContext.id = JsonBody.id
 requestContext.categoryID = JsonBody.category.id
 requestContext.categoryName = JsonBody.category.name
 requestContext.name = JsonBody.name
 requestContext.status = JsonBody.status

 
The response will then look like this: 

{
    "Message": "Successfully read in payload",
    "Values": {
        "ID": "${id}",
        "Category ID": "${categoryID}",
        "Category Name": "${categoryName}",
        "Name": "${name}",
        "Status": "${status}"
    }
}

The result should look like this: