Ask a Question

Validate Data from DB2 in groovy script

New Contributor

Validate Data from DB2 in groovy script

I have 2 test cases, for one of them I need to validate in DB2, for the other one in SQL.


The scenario: I have a search service and when sending a customer number I get all the credit cards connected to that customer. For this I need to validate in DB2. If I send a number for a customer who only has paypal accounts, I need to validate in SQL. For the second one I'm using simple SELECT query to retrieve records from one table. Here is the code that I use to validate:


//----------Script Functions----------
//XMLify Response
def XMLifyResponse(String step) {
def resp = context.expand('${' + step + '#ResponseAsXml}')
def xml = new XmlSlurper().parseText(resp)"String response for step " + step + ": " + resp)"XMLified content for step " + step + ": " + xml)
return xml

//Jsonify Response
def JsonifyResponse(String step) {
def resp = context.expand('${' + step + '#Response}').toString()
def json = new groovy.json.JsonSlurper().parseText(resp)"String response for step " + step + ": " + resp)"Jsonified content for step " + step + ": " + json)
return json

//----------Main Script----------
//Parse Responses
def xmlSQL = XMLifyResponse('TestData_SQL')

def jsonEndpoint = JsonifyResponse('List Request')

//Put into organized lists
def sqlList =xmlSQL.'**'.findAll{ == 'Row'}"SQL LIST: "+sqlList)


def endpointList = jsonEndpoint.creditCards


//Validate Sizes

assert sqlList.size() == endpointList.size()

assert endpointList.size() <= 500, "Endpoint returned more than 500 results. "

//Account Validation Endpoint - SQL
endpointList.each {
//Account Number
def creditCardNumberHashCode = it.creditCardNumberHashCode.toString() creditCardNumberHashCode
def sqlcreditCardNumberHashCode = sqlList.find{it.PAYMENT_METHOD_ID.toString() == creditCardNumberHashCode} sqlcreditCardNumberHashCode

def statusCode = it.statusCode.toString() statusCode

assert it.creditCardNumberHashCode == sqlcreditCardNumberHashCode.PAYMENT_METHOD_ID.toString(), "SQL and endpoint account numbers do not match"
assert it.statusCode == sqlcreditCardNumberHashCode.PAYMENT_METHOD_STATUS_TYPE_CD.toString()

}"Validation Complete")


This line: def sqlcreditCardNumberHashCode = sqlList.find{it.PAYMENT_METHOD_ID.toString() == creditCardNumberHashCode} --- is supposed to create another list for that row where the account numbers match

The problem arises when I need to validate in DB2 where the query retrieves following XML:

<Row rowNumber="1">
<Row rowNumber="2">

---So when I use that same line of code to create a list from a row based on a specific credit card number from Json response: 

def db2creditCardNumberHashCode = db2List.find{it.CUST_CREDT_CARD.CREDITCARD.toString() == creditCardNumberHashCode} 

I get null object. I am Joining 3 tables and when querying db2 i receive an XML that gives the table and field (CUST_CREDT_CARD.CREDITCARD) and that seems to be the problem. When validating SQL jdbc response it works fine because it just retrieves me the field name (PAYMENT_METHOD_ID). I tried with using just CREDITCARD, I tried replacing the . (dot) with different things, nothing helped. Any suggestions will be greatly appreciated!

Showing results for 
Search instead for 
Did you mean: