Using the built in data files functionality in Katalon Studio, I can create a data file connecting to my DB and then return the results as a variable with the below:
//get data
def data = findTestData('Data Files/DB Data')
// Get specific data (column, row) as a variable
String record = data.getValue(3, 4)
//print my variable
println record
Hello, could you please explain a little further? I am trying to achieve the same thing but am unsure how to implement the Data files you have mentioned:
CustomKeywords.'customMethods.DatabaseMethods.connectDB'('serverAddress', 'TableName', '1433', 'username', 'password')
String queryResult = CustomKeywords.'customMethods.DatabaseMethods.executeQuery'('SELECT STATUSDESCRIPTION FROM dbo.RASPOTSTATUS WHERE SPOTSTATUSID = 15')
CustomKeywords.'customMethods.DatabaseMethods.closeDatabaseConnection'()
println(queryResult)
Hello, could you please explain a little further? I am trying to achieve the same thing but am unsure how to implement the Data files you have mentioned:
Thank you for your reply Anthony. That way does work for me and in certain scenarios, is the right solution.
However, in my current scenario, I want to just be able to access the result as a single variable for comparison. Is there a way to do this without having to create a Data file? I would like to just be able to do this within the scripting view of Katalon
This is a high level description of the test I am trying to do:
1. Login to application front end
2. Create an ‘order’
3. Get a unique ID/Order number
4. Query the database to check the order has been created correctly with unique id
5. Make changes to the order in the application front end
6. Query the database again to make sure updates have been committed correctly
So something like
CustomKeywords.'customMethods.DatabaseMethods.connectDB'('serverAddress', 'TableName', '1433', 'username', 'password')
String queryResult = CustomKeywords.'customMethods.DatabaseMethods.executeQuery'('SELECT STATUSDESCRIPTION FROM dbo.RASPOTSTATUS WHERE SPOTSTATUSID = 15')
CustomKeywords.'customMethods.DatabaseMethods.closeDatabaseConnection'()String expectedResult = "Update"
//Then compare queryResult against expectedResult and pass/fail the test based on this
Thank you for your reply Anthony. That way does work for me and in certain scenarios, is the right solution.
However, in my current scenario, I want to just be able to access the result as a single variable for comparison. Is there a way to do this without having to create a Data file? I would like to just be able to do this within the scripting view of Katalon
This is a high level description of the test I am trying to do:
1. Login to application front end
2. Create an ‘order’
3. Get a unique ID/Order number
4. Query the database to check the order has been created correctly with unique id
5. Make changes to the order in the application front end
6. Query the database again to make sure updates have been committed correctly
String queryResult = CustomKeywords.‘customMethods.DatabaseMethods.executeQuery’(‘SELECT STATUSDESCRIPTION FROM dbo.RASPOTSTATUS WHERE SPOTSTATUSID = 15’)
CustomKeywords.‘customMethods.DatabaseMethods.closeDatabaseConnection’()String expectedResult = “Update”
//Then compare queryResult against expectedResult and pass/fail the test based on this
Does that make sense?
Yes, that makes sense and is simular to what I was trying to acheive when I first posted.
But I did not know how to deal with the results, leading me to use the data file as a quick and simple work around.
A Resultset is a table of data representing the results we get back from the query. A cursor is maintained inside the ResultSet but is not always set to the current row (i.e. the row with our data)
So your code would look something like this. NOTE that you need to keep the connection open whilst you manipulate the ResultSet otherwise it has nothing to manipulate and will return an error:
//Connect to Database
CustomKeywords.'customMethods.DatabaseMethods.connectDB'('IPAddress', 'tableName', '1433', 'username', 'password')
//Execute our query
ResultSet queryResult = CustomKeywords.'customMethods.DatabaseMethods.executeQuery'('SELECT STATUSDESCRIPTION FROM dbo.RASPOTSTATUS WHERE SPOTSTATUSID = 15')
//Move the cursor in the ResultSet table to the first row
queryResult.next()
//Save the result as a string
String valueFromDatabase = queryResult.getString(1)
//Print our result from the database as a string to the console
println(valueFromDatabase)
//Close the connection
CustomKeywords.'customMethods.DatabaseMethods.closeDatabaseConnection'()
A Resultset is a table of data representing the results we get back from the query. A cursor is maintained inside the ResultSet but is not always set to the current row (i.e. the row with our data)
So your code would look something like this. NOTE that you need to keep the connection open whilst you manipulate the ResultSet otherwise it has nothing to manipulate and will return an error:
//Connect to Database
CustomKeywords.‘customMethods.DatabaseMethods.connectDB’(‘IPAddress’, ‘tableName’, ‘1433’, ‘username’, ‘password’)
//Execute our query
ResultSet queryResult = CustomKeywords.‘customMethods.DatabaseMethods.executeQuery’(‘SELECT STATUSDESCRIPTION FROM dbo.RASPOTSTATUS WHERE SPOTSTATUSID = 15’)
//Move the cursor in the ResultSet table to the first row
queryResult.next()
//Save the result as a string
String valueFromDatabase = queryResult.getString(1)
//Print our result from the database as a string to the console
println(valueFromDatabase)
//Close the connection
CustomKeywords.‘customMethods.DatabaseMethods.closeDatabaseConnection’()
Take a closer look at this post to avoid this cumbersome handling of cursors (or pointers) for ResultSet objects. When using the groovy.sql.Sql class, you only need one Keyword method to create the desired SQL instance. Then their methods can be used directly to apply all required SQL statements and process their result sets as maps or lists.