Hello All,
I need to test REST API- JSON Response with DB(SQL Server) Response
How can I make it dynamically so that I can validate the JSON Response and DB Response
Currently I'm validating by taking property value and comparing them
Do we have any scripts it validates the entire response with DB response
Any help is appreciated. Thanks!
An example comparing selected columns from DB query
Add a script assertion in the JDBC request step to set the property values
import com.eviware.soapui.support.XmlHolder holder = new XmlHolder( messageExchange.getResponseContent() )
context.testCase.setPropertyValue( "Array IDs", holder["//Row/ID"].toString() ) context.testCase.setPropertyValue( "Array Names", holder["//Row/NAME"].toString() )
Then use JSONPath Match assertions
(in this case I converted ID to lower case to match the response)
JSONPath Expression $..Id
Expected Result ${="${#TestCase#Array IDs}".toLowerCase()}
JSONPath Expression $..Name
Expected Result ${#TestCase#Array Names}
Can you guys please help me on this. How to compare multiple JSON Elements with DB elements in one script and capture the result in Excel sheet
as
JSON ELEMENTELEMENT DATA BASE ELEMENT VALUE RESULT
123 123 PASS
1234 456 FAIL
I'm able to compare (Json Response Element vs DB Response) only one element at a time with the below script and capturing the results in excel sheet as above for one element. Attached is the sample screen snippet of my test steps and end result excel sheet
I'm creating a property as Result with Blank
context.testCase.setPropertyValue("Result","")
Groovy Script
import groovy.xml.*
import groovy.json.*
def response = context.expand( '${REST Request#Response#$[\'STMT_BK_ADDR_LINE\'][\'STMT_ADDR_MONETARY_ID\']}' )
log.info response
def responseAsXml = context.expand( '${JDBC Request#ResponseAsXml#//Results[1]/ResultSet[1]/Row[1]/STMT_ADDR_MONETARY_ID[1]}' )
log.info responseAsXml
context.testCase.setPropertyValue("DBResponse",responseAsXml)
context.testCase.setPropertyValue("JsonResponse",response)
if(response.equals(responseAsXml))
{
context.testCase.setPropertyValue("Result","pass")
}
else
{
context.testCase.setPropertyValue("Result","fail")
}
It is difficult to be precise without example JDBC or REST responses.
Is the JDBC result set expected to contain only 1 row for each Statement ID? My example above was comparing a list with multiple rows.
How many columns do you need to compare, should differences for some columns be ignored if the value is not static?
Are the DB column names different to the JSON response?
One option is to create a testcase property for each column to be tested and setPropertyValue for each in the groovy script using different JSON and XPath expressions as needed.
In Excel results you probably need another column to record the element name.
DB Column names are same as JSON Response elements
I need to compare the DB response column values with Json Response Column values dynamically based on Statement ID
Here is the sample JSON Structure(Like this have different array objects and array elements). Below is the sample array object
"STMT_BK_ADDR_LINE": {
"STMTID": 10,
"BK_ADDR1": "VALUE"
"BK_ADDR2": "VALUE",
"BK_ADDR3": "VALUE",
"CRET_TMSP": "VALUE",
"CRET_PGM_ID": "VALUE",
"CRET_USER_ID": "VALUE"
}
What is the best method for validating the response with DB result dynamically
Currently I have created one property element for each JSON element and DB element (I.,e STMT_ID(for JSON) and STMT_ID for DB-as per my earlier post)
How can I refresh the property value if I use the for loop for validating all data elements
Thanks!!
Assuming that you want to validate all data from the first row of the JDBC response, you could use a script like this to loop through each of the children
def xml = new XmlSlurper().parseText(context.expand( '${JDBC Request#ResponseAsXml}' ))
xml.ResultSet.Row[0].children().each {
Name = it.name();
DBResponse = it.text();
JsonResponse = context.expand( '${REST Request#Response#$..' + Name + '}' )
Result = (DBResponse == JsonResponse) ? "pass" : "fail"
//either write each result to Excel or set property values
//context.testCase.setPropertyValue("DBResponse" + Name,DBResponse)
//context.testCase.setPropertyValue("JsonResponse" + Name,JsonResponse)
//context.testCase.setPropertyValue("Result" + Name,Result)
}
