Get SQL query results as variables


#1

Hello,

I am using the following code below to query a MS MSQL Server, and do not know how to deal with the results correctly…

CustomKeywords.'com.db.sql.connectDB'(server, port, dbname, username, password)

result = CustomKeywords.'com.db.sql.executeQuery'(query)

CustomKeywords.'com.db.sql.closeDatabaseConnection'()

The above is working (as in performing the query) using the example keywords from here, but how do I get the result of the query in a useable form.

If after running the above I do:

println result

Then all I get in the console is this:

SQLServerResultSet:1

How do I see/get at my results? I want to assign them to variables for comparison against others.

Thank you

Anthony


#2

Found a solution to get my results as variables.

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

I can then do what ever I need with the variable :smiley:


#3

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)

#4

Justin Harper said:

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 Justin, to generate my data file I followed the steps here:
https://docs.katalon.com/display/KD/Manage+Test+Data doing a select * from table name query.

Let me know if that is not enough


#5

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
Does that make sense?

#6

Justin Harper said:

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


  
  

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.

Anyway here: https://dzone.com/articles/how-to-perform-database-testing-using-katalon-stud is what I used as an example to script my SQL connection and query, hopefully this will help you.

If you can figure out how to deal with query results as variables please share, it would be useful.


#7

Got it:)

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)

We ned to move the cursor first, then call the getString method to get our string. Probably not the best explanation but these sites do it better:
https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html
https://docs.oracle.com/javase/tutorial/jdbc/basics/retrieving.html#retrieve_rs
http://websitenotebook.blogspot.com/2010/06/resultset-has-no-current-row.html (This one really drove it home for me)

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'()

#8

Justin Harper said:

Got it:)

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)

We ned to move the cursor first, then call the getString method to get our string. Probably not the best explanation but these sites do it better:
https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html
https://docs.oracle.com/javase/tutorial/jdbc/basics/retrieving.html#retrieve_rs
http://websitenotebook.blogspot.com/2010/06/resultset-has-no-current-row.html (This one really drove it home for me)

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’()


  
  
  
  
  
  
  
  
  

  

That is great, thanks for sharing what you found!


#9

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.