Compare Number from test case and from Database

Web Testing
2

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”)