API GET Request with database

Hello,

I got some problems between database and GET request. I want to make so, that where i make get request, it should take information from database and put it in get request. for example: in my database I have personal_id and phone. GET request need 2 information personal_id and phone and if it is possible to make so that I can take information from db dynamically and set it in get request. I made a SQLHandler for that where i store selected table.
public class database_Selector {

@Keyword
def getResource() {
	SQLHandler_2000 sql = new SQLHandler_2000()
	List<GroovyRowResult> res = sql.getSelectResults('SELECT TOP 10 PERSONAL_ID, PHONE2 FROM CLIENTS WHERE PERSONAL_ID IS NOT NULL AND PHONE2 IS NOT NULL')
	println(res)
}

}

Hello,

for updating GET requests, use the same approach as described here

In this case, you may use foreach loop to send requests for all DB rows. Pseudocode:

List<GroovyRowResult> res = sql.getSelectResults('SELECT TOP 10 PERSONAL_ID, PHONE2 FROM CLIENTS WHERE PERSONAL_ID IS NOT NULL AND PHONE2 IS NOT NULL')

	for(Map oneRow in res) {
		String personalId = oneRow.get("PERSONAL_ID")
        String phone = oneRow.get("PHONE2")
        // use variables in the GET request here
	}
1 Like

What I should return from here to see what is printed ?

**EDIT : **

def database_select = CustomKeywords.'db_connection.database_Selector.getResource'()
println(database_count)

WS.sendRequest(findTestObject('Object Repository/GET_Customer'))
RequestObject get_object = findTestObject('Object Repository/GET_Customer')
get_object.setRestUrl(String.format(get_object.getRestUrl(), oneRow.get("PERSONAL_ID"), start_Pointer, count)) 

I’m doing it wrong…

You are sending your request before you put all variables there. That is the most obvious error I can see.
Then, you should send get_object Object which contains your changes.

Note: Please provide also an error message every time you get some.

The script what you have written for me i use it in keyword package and in test case it can’t see oneRow variable.

def database_select = CustomKeywords.‘db_connection.database_Selector.getResource’()
println(database_select)
String personalId = oneRow.get(“PERSONAL_ID”)
String phone = oneRow.get(“Phone2”)
WS.sendRequest(findTestObject(‘Object Repository/GET_Customer’))
RequestObject get_object = findTestObject(‘Object Repository/GET_Customer’)
get_object.setRestUrl(String.format(get_object.getRestUrl(), personalId, phone))

List<GroovyRowResult> res = sql.getSelectResults('SELECT TOP 10 PERSONAL_ID, PHONE2 FROM CLIENTS WHERE PERSONAL_ID IS NOT NULL AND PHONE2 IS NOT NULL')
	for(Map oneRow in res) {
		String personalId = oneRow.get("PERSONAL_ID")
        KeywordUtil.logInfo("Personal ID: " + personalId)

        String phone = oneRow.get("PHONE2")
        KeywordUtil.logInfo("Phone number: " + phone )
        // use variables in the GET request here
	}

Would be this solution suitable to display variables?

I have one question, I should use this script in my test case or in my package? because I use that in my package and it doesn’t work, when I use that script in test case it doesn’t recognizes sql.getSelectResults

EDIT: I have returned res variable and it printed, but in my package web service keywords is not being recognized. That was a stupid answer from me, just needed to add librarys

I’d create custom keyword for this validation and throw KeywordUtil.markFailed in case of failure within foreach loop.

@Keyword
def getResource() {
	SQLHandler_2000 sql = new SQLHandler_2000()
	List<GroovyRowResult> res = sql.getSelectResults('SELECT TOP 10 PERSONAL_ID, PHONE2 FROM CLIENTS WHERE PERSONAL_ID IS NOT NULL AND PHONE2 IS NOT NULL')
	
	for(Map oneRow in res) {
	String personalId = oneRow.get("PERSONAL_ID")
    KeywordUtil.logInfo("Personal ID: " + personalId)

    String phone = oneRow.get("PHONE2")
	
    KeywordUtil.logInfo("Phone number: " + phone )
	// use variables in the GET request here
	WS.sendRequest(findTestObject('Object Repository/GET_Customer'))
	RequestObject get_object = findTestObject('Object Repository/GET_Customer')
	get_object.setRestUrl(String.format(get_object.getRestUrl(), personalId, phone))
	ResponseObject get_response = WS.sendRequestAndVerify(get_object)
	WS.verifyResponseStatusCode(get_response, 200)
	String getContent = get_response.getResponseBodyContent()
	}
	return res
}

}

My Keyword looks like that, and in test case i define so :
def database_select = CustomKeywords.‘db_connection.database_Selector.getResource’()
println(database_select), but acording to this script how I made that it does only verifications and doesn’t log anything

It definitely logs information. It may be hidden in the log, try to expand all log messages.

20190111-115221

I can see those two log messages from your script.

Why do you send WS.sendRequest(findTestObject('Object Repository/GET_Customer')) ?
You send it, then set parameters to the URL and send again.

So, I should remove that ?

Well, it’s redundant. :slight_smile:

Output is the same, for some reason it returns 422 status code.

What does your test object look like? Do you use String.format with a correct number of arguments?

in personalId is stored : String personalId = oneRow.get(“PERSONAL_ID”)
in phone is stored : String phone = oneRow.get(“PHONE2”)

I use that: get_object.setRestUrl(String.format(get_object.getRestUrl(), personalId, phone))

and url lookls like that : http://testapi/v1/Customer?personalId=%s&mobileNumber=%s

Now, I don’t understand when I tried to run it with selecting top 9 it passed, but when I add tenth it gives me an error, but everything looks green and passed

Add debug message to your loop right before you send a request. HTTP 422 means there is some problem with processing URL parameters.

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

1 Like

Well, sorry the problem was just my unattentive. mobilenumber was somehow clear (even i clearly defined that mobilephone should not be null) and because of that it gave me an error.