Katalon Studio v5.8 - It's time for the all-new API testing experience
Anthony
Katalon Ambassador
05/16/2018
edited May 16

Get SQL query results as variables

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

Upvote
Quote

Comments

  • Justin Harper
    Katalon Ambassador
    06/28/2018
    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'()




    Best Answer
    Upvote
    Quote
  • Anthony
    Katalon Ambassador
    05/18/2018
    edited May 18
    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 :D

    Upvote
    Quote
  • Justin Harper
    Katalon Ambassador
    06/13/2018
    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)

    Upvote
    Quote
  • Anthony
    Katalon Ambassador
    06/13/2018
    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
    Upvote
    Quote
  • Justin Harper
    Katalon Ambassador
    06/21/2018
    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?
    Upvote
    Quote
  • Anthony
    Katalon Ambassador
    06/23/2018
    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.
    Upvote
    Quote
  • Anthony
    Katalon Ambassador
    06/28/2018
    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!
    Upvote
    Quote
Sign In or Register to comment.
Not the answer you're looking for? Consider subscribing to Business support.