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 ?
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”)
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.
new error in the end of the test case
Ok, this is easy If you see my query, I used ‘Count’ as an alias for DB column name. Just change it to match your column name.
I feel so dumb now, whole day working on it I just dont get anything anymore I’m trying to do now so :
But I want to get only Counted number, sorry for so many questions
EDIT: OMG I think i got it I didn’t define column name for Counted items
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.
Oh yes now i see the difference, thank you so much for helping and explanation !!
Welcome! I hope that working with databases is more clear now. Enjoy your automation with Katalon!
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