Compare values gotten from request with database in Katalon Studio

brithwulf · January 29, 2019, 6:35am

I want to verify if the values from response is not different from database. I make a GET request and it gives me some information from database and I need to verify it somehow that the information is correct. I made a lot of things, here is my code.

@Keyword
    def getResource() {
        SQLHandler_test sql = new SQLHandler_test()
        SQLHandler_test1 sql_cards = new SQLHandler_test1()
        List<GroovyRowResult> res = sql.getSelectResults('SELECT TOP 2 PERSONAL_ID, CLIENT_NO, SMS_MOBILE_PHONE FROM CLIENTS WHERE PERSONAL_ID IS NOT NULL AND SMS_MOBILE_PHONE IS NOT NULL ')
        JsonSlurper slurper = new JsonSlurper()
        for(Map oneRow in res) {
            String personalId = oneRow.get("PERSONAL_ID")
            KeywordUtil.logInfo("Personal ID: " + personalId)
            println("Personal ID: " + personalId)

            String phone = oneRow.get("SMS_MOBILE_PHONE")

            KeywordUtil.logInfo("Phone number: " + phone )
            println("Mobile Phone: " + phone)
            // use variables in the GET request here
            RequestObject get_object = findTestObject('CUSTOMERS_Requests/Customer/CustomerByPersonalID/GET_CustomerByPersonalID')
            get_object.setRestUrl(String.format(get_object.getRestUrl(), personalId, phone))
            ResponseObject get_response = WS.sendRequestAndVerify(get_object)
            int statusCode = get_response.getStatusCode()

            KeywordUtil.logInfo("URL: " + get_object.getRestUrl())

            String getContent = get_response.getResponseBodyContent()
            KeywordUtil.logInfo("Status Code: " + statusCode + " Body Content: " + getContent)
            WS.verifyResponseStatusCode(get_response, 200)
            Map parsedJson = slurper.parseText(getContent)
            String parsed_personalID = parsedJson.get("personalId")
            String parsed_customerID = parsedJson.get("customerId")
            String parsed_mobilePN = parsedJson.get("mobilePhoneNumber")
            String parsed_canUseIB = parsedJson.get("cUIB")
            String client_no = oneRow.get("CLIENT_NO")
//          String parsed_expiryDate =  parsedJson.get("cardExpirationDate")
//          println(parsed_expiryDate)
            println(parsed_personalID)
            if(parsed_personalID == personalId){
                KeywordUtil.logInfo(parsed_personalID + " = " + personalId + " Matched !")
            }
            if(parsed_mobilePN == phone){
                KeywordUtil.logInfo(parsed_mobilePN + " = " + phone + " Matched !")
            }
            if(parsed_customerID == client_no) {
                KeywordUtil.logInfo(parsed_customerID + " = " + client_no + " Matched !")
            }
            List<GroovyRowResult> res_cards = sql_cards.getSelectResults('SELECT CONVERT(date, [EXPIRY_DATE]) "EXPIRY_DATE" FROM card.PCARDS WHERE CARD_CLIENT_NO = ' + client_no)
            for(Map oneRow_cards in res_cards) {
                String expiry_date = oneRow_cards.get("EXPIRY_DATE")
                println(expiry_date)
            }
        }
        return res
    }

It was not a problem with comparing phone, personal_id and customer_id, but I can’t compare card information.

This is how response looks like.

{
  "customerId": 5555,
  "personalId": "1123",
  "mobilePhoneNumber": "2233",
  "cUIB": true,
  "cards": [
    {
      "cardExpirationDate": "2020-05-31",
      "cardNumber": "1"
    },
    {
      "cardExpirationDate": "2012-03-31",
      "cardNumber": "2"
    },
    {
      "cardExpirationDate": "2008-03-31",
      "cardNumber": "33"
    },
    {
      "cardExpirationDate": "2020-09-30",
      "cardNumber": "522"
    },
    {
      "cardExpirationDate": "2018-04-30",
      "cardNumber": "5"
    },
    {
      "cardExpirationDate": "2014-03-31",
      "cardNumber": "23"
    }
  ]
}

I made a code where I can compare only for one request, but I want to make so that it should compare for many requests and cards quantity will be different. If you have some tips, how can I make that, please tell me.

Thanks in advance !

brithwulf · January 29, 2019, 7:58am

I tried to parse cards json and then I selected that information from database and wanted to verify if that json contains some data with this code: parsedJson.cards.contains(oneRow_cards.get(“EXPIRY_DATE”))

Where in parsedJson.cards is stored such an information:

[{cardExpirationDate=2008-03-31, cardNumber=22}, {cardExpirationDate=2014-03-31, cardNumber=32}, {cardExpirationDate=2020-09-30, cardNumber=42}, {cardExpirationDate=2020-05-31, cardNumber=522}, {cardExpirationDate=2018-04-30, cardNumber=123}, {cardExpirationDate=2012-03-31, cardNumber=45}]

and in oneRow_cards.get(“EXPIRY_DATE”) is stored only dates:

and tried so: if(parsedJson.cards.contains(oneRow_cards.get(“EXPIRY_DATE”))), but it isn’t working.

Mate_Mrse · January 29, 2019, 8:27am

Hi, I haven’t noticed this question here so I posted an answer to your question on SO.

Anyway, you can parse the second level of JSON with

for (def i = 0; i<parsedJson.cards.size(); i++){

    println parsedJson.cards[i].cardExpirationDate
    println parsedJson.cards[i].cardNumber

}

brithwulf · January 29, 2019, 8:35am

Thanks it helped me, but how can i compare now therese datas ? Is in katalon studio some contains statement or something like that? I used your script and now it gives me parsed dates, and also I have used another for statement for selecting dates from database. Now what should I compare ? I’m really confused.

brithwulf · January 29, 2019, 8:39am

This is how my console view looks like:

If there is a way to write if statement like. if parsed json contains selected database data ? Also they are disordered and can’t just compare each index to index I think.

Mate_Mrse · January 29, 2019, 8:43am

You can use

https://docs.katalon.com/katalon-studio/docs/common-verify-match.html#example-

Pass the value from the DB as the first parameter, and the value from parsedJson as the second one.

brithwulf · January 29, 2019, 8:47am

for that I should store pardedJson.cards in some variable yes ? like that:

for(def i = 0; i<parsedJson.cards.size(); i++) {
println("Parsed Expiration Dates : " + parsedJson.cards[i].cardExpirationDate)
return parsedJson.cards[i].cardExpirationDate
}

and then :
List<GroovyRowResult> res_cards = sql_cards.getSelectResults('SELECT CONVERT(date, [EXPIRY_DATE]) "EXPIRY_DATE" FROM card.PCARDS WHERE CARD_CLIENT_NO = ' + client_no)
for(Map oneRow_cards in res_cards) {
String expiry_date = oneRow_cards.get("EXPIRY_DATE")
println("Selected dates from database " + expiry_date)
WS.verifyMatch(expiry_date, exp_date, true)
}

brithwulf · January 29, 2019, 8:53am

WS.verifyMatch(expiry_date, parsedJson.cards[i].cardExpirationDate, true) it gives me an error when I use second value

Mate_Mrse · January 29, 2019, 8:54am

You should store it in the exp_date variable. Also, the third parameter of the verifyMatch denotes weather the string in question is regex or no, so you can set it to “false”.

brithwulf · January 29, 2019, 9:04am

String exp_datee = parsedJson.cards[i].cardExpirationDate so ?

Mate_Mrse · January 29, 2019, 9:08am

Yes. But keep in mind that will set the exp_date for the current iteration/value of “i”. Maybe you could verify match inside the for(def i = 0; i<parsedJson.cards.size(); i++) loop?

brithwulf · January 29, 2019, 9:14am

This loop I use for parsing dates from json

for(def i = 0; i < parsedJson.cards.size(); i++) {
def exp_datee = parsedJson.cards[i].cardExpirationDate
println("Parsed Expiration Dates : " + exp_datee)
}

and then I use second loop for selected dates from database
for(Map oneRow_cards in res_cards) {
String expiry_date = oneRow_cards.get("EXPIRY_DATE")
println("Selected dates from database " + expiry_date)
WS.verifyMatch(expiry_date, exp_datee, false)
}

exp_datee is not recognized and gives me an error.

Really sorry for so many questions.

Mate_Mrse · January 29, 2019, 9:49am

exp_datee is defined only inside the first for loop. That’s why it is not recognized inside the second loop.

You need to find the way to match values from the first loop with those from the second one. I’m not currently free to do that, maybe someone else could help?

brithwulf · January 29, 2019, 10:02am

Anyway, thanks for help with your help I think that I will somehow make what I need.

brithwulf · January 29, 2019, 11:07am

I’ve figured it out how to make it, but maybe my way is too difficult. I’ve made an array where I push each parsed dates. Here is my code:

		def dates = []
		for(def i = 0; i < parsedJson.cards.size(); i++) {
			dates<<parsedJson.cards[i].cardExpirationDate
			println("Parsed Expiration Dates : " + dates)
		}

		for(Map oneRow_cards in res_cards) {
			String expiry_date = oneRow_cards.get("EXPIRY_DATE")
			def ifContains = dates.contains(expiry_date)
			if(ifContains) { 
					println("Matched")
			}
			else {
				println("ERROR ! ")
			}
			
		}

I think that it works good, when you will have a time, can you check my code ?

Topic		Replies	Views
NULL value unable to verify match Katalon Studio katalon-studio , api-testing	3	2861	December 30, 2019
API GET Request with database Katalon Studio katalon-studio , api-testing	19	1579	January 11, 2019
Response body: JSON value is equal or greater than Katalon Studio katalon-studio , api-testing	54	12406	January 3, 2019
Is there a way i can check the value or attribute against a SQL database of object values ? Archive integrations , katalon-studio	2	1335	August 8, 2017
How to get value from query parameter? Katalon Studio api-testing	5	77	September 25, 2024

Compare values gotten from request with database in Katalon Studio

Related topics