Compare Number from test case and from Database

Hello, guys. I’m new in Katalon Studio and trying to make some different things. I just want to know if it is possible to somehow compare result number from test case with database number ? or i should check it manually ?
image
image

Hello,

of course it is possible. First, you have to set up a database connection, run a query, get count(*) and compare the result. See my sample code:

public class SQLHandler {
	String dbConnString = "jdbc:sqlserver://my.sqlserver.com:1433;databaseName=dummy"
	String dbUsername = "admin"
	String dbPassword = "passwd"
	String dbDriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"

	Sql sql = Sql.newInstance(dbConnString,dbUsername,dbPassword,dbDriver)

	public List getSelectResults(String query) {
		KeywordUtil.logInfo("Executing the query: " + query)
		List results = sql.rows(query)
			if(results.size() == 0) {
				KeywordUtil.markFailedAndStop("No rows returned from SQL Server")
			}
		return results
	}

	public void closeConn() {
		sql.close()
	}
}

The usage in test case:

SQLHandler sql = new SQLHandler()
List res = sql.getSelectResults("select count(*) as Count from dummy.table1")

int countFromDb  = res.get(0).get("Count")

Hope this helps.

Edit: Maybe it deserves a little bit of explanation. List res contains a list of results, one for each row. This row is a map of columnName:value.
So to get a count of records in a table:

  • get a first result row —> res.get(0).get(“Count”)
  • get a value from column ‘Count’ —> res.get(0).get(“Count”)
1 Like

Sorry but from where should i run the new query ? where should i add that first code ?

Create a new custom keyword for the SQL handling code.
The second code snippet would take place in a test case.

As soon as you have int countFromDb, you can compare it with expected number.

int expectedCount = 10
if(countFromDb != expectedCount) {
    KeywordUtil.markFailed("The count from DB does not match the expected count.")
}

Sorry for so many questions. I got 2 errors in the query, my database name is testdb\sql2016 and when i’m trying to write that it shows an error

and when I remove that \ , then appears another error

Try to use double backslash instead of a single backslash. It is a special character and it must be escaped.

And press Ctrl + Shift + O to add required imports.

1 Like

image

image
new error in the end of the test case :smiley:

Ok, this is easy :smiley: If you see my query, I used ‘Count’ as an alias for DB column name. Just change it to match your column name. :wink:

1 Like

I feel so dumb now, whole day working on it I just dont get anything anymore :smile: I’m trying to do now so :

But I want to get only Counted number, sorry for so many questions :frowning:

EDIT: OMG I think i got it :roll_eyes: I didn’t define column name for Counted items :thinking:

Exactly.

The problem with COUNT(*) in SQL is that the result doesn’t have default column name. So the resultmap doesn’t have a key for the count value. You have to use alias every time to get correct count results.

See the difference between aliased vs. not aliased query.

1 Like

Oh yes now i see the difference, thank you so much for helping and explanation !! :heart_eyes::heart_eyes:

Welcome! I hope that working with databases is more clear now. :slight_smile: Enjoy your automation with Katalon!

3 Likes

I have added code in separate class:
public static Connection connection = null;
def static connectDB(String url, String dbname, String port, String username, String password){

	//Load driver class for your specific database type
	String conn = "jdbc:oracle:thin:@//*****/##"
	if(connection != null && !connection.isClosed()){
		connection.close()
	}
	connection = DriverManager.getConnection(conn, username, password)
	return connection
}
//Executing the constructed Query and Saving results in resultset
def static executeQuery(String queryString) {
	Statement stm = connection.createStatement()
	ResultSet rs = stm.executeQuery(queryString)
	return rs
	
}

and then calling in my testcase:

def GZAUMAP_form(){

	def sql =cusN.connectDB("***", "###", "###", "###", "####")
	cusN.executeQuery("select * from general.gobumap")
	

def res =	cusN.executeQuery('select * from general.gobumap')

My testcase is getting passed, but i want to print my query result as well?? how can i do so