Specify which row in the database

API Testing
,
1

All i want to do is specify which row in the db i want to get the value from. At the minute im using the .next() to get the first row in the table which was fine at first but am now at need to test other rows values. Please help me, im having a dumb moment :laughing:

def response = WS.sendRequestAndVerify(findTestObject('GET/AllJopPay'))

CustomKeywords.'db.sql.connectDB'(GlobalVariable.dataBase, GlobalVariable.dataBaseUsername, GlobalVariable.dataBasePW)

def jobPay = CustomKeywords.'db.sql.executeQuery'('SELECT * FROM APIjobpayView ORDER BY JpyID asc')
//Moving the cursor to the first row in the table
jobPay.next()

String record = jobPay.getObject('PerName')

println(record)

def verified = WS.verifyElementPropertyValue(response, 'AllJobPay[0].PersonName', record)
//returns bool
println(verified)
1 Like
2

how about use WHERE in select statement?

3

Yes i could and have done that, would prefer to use something like this

def rowNum = 0
sql.query('SELECT firstname, lastname FROM Author') { resultSet ->
  while (resultSet.next()) {
    def first = resultSet.getString(1)
    def last = resultSet.getString('lastname')
    assert expected[rowNum++] == "$first $last"
  }
}

but not sure on how to implement it. Im not the best at groovy. From what i understand, def rowNum will allow me to specify which row i want to use. How i go about inputting that into my script im not sure. Although maybe just using a WHERE is the best option and im just trying to make things difficult :thinking:

1 Like
4

what you write looks quite viable ā€¦ iā€™m not en expert in groovy (cannot read closures well) but from what i see that could work

def rowNum = 0
resultSet = sql.query('SELECT firstname, lastname FROM Author') 
while (resultSet.next()) {
    def first = resultSet.getString('firstname')
    def last = resultSet.getString('lastname')
    assert expected[rowNum++] == "${first} ${last}"
}
5

Yea i thought so - thanks for clearing it up. Ill have a play about and see what i get :slight_smile:

1 Like
6

I ended up just using the WHERE in my select statement and then turning the locator value into a variable to get multiple rows which works like a dream :slight_smile:

def response = WS.sendRequestAndVerify(findTestObject('GET/Persrec'))

CustomKeywords.'db.sql.connectDB'(GlobalVariable.dataBase, GlobalVariable.dataBaseUsername, GlobalVariable.dataBasePW)

def jobPay = CustomKeywords.'db.sql.executeQuery'('SELECT * FROM Persrec WHERE PerID IN (1,2,3,4,5,6,7,8,9,10)')

def row = -1

while (jobPay.next())
{
	row++
	println(row)
	
	String record = jobPay.getObject('PerName')
	println(record)

	def verified = WS.verifyElementPropertyValue(response, "PersonDetail[${row}].Name", record)
	
	//returns bool
	println(verified)

}
1 Like