Connecting to a Database in Katalon Studio: A Practical Guide

Connecting to a Database in Katalon Studio: A Practical Guide

26 February 2025 Stephan Petzl Leave a comment Katalon Issues

When working with automated testing frameworks like Katalon Studio, you may need to connect to a database to retrieve or verify data. This guide provides a straightforward method to establish a database connection and perform actions such as retrieving specific information from your database tables.

Setting Up Your Database Connection

To connect to your database in Katalon Studio, follow these steps:

  1. Create a new keyword package, for instance, db_connection.
  2. Within this package, define a class that handles the database connection. Here is an example:
package db_connection

import com.kms.katalon.core.util.KeywordUtil
import groovy.sql.Sql

public class SQLHandler_Con {
    String dbConnString = "jdbc:sqlserver://yourserver;databaseName=yourdb"
    String dbUsername = "yourusername"
    String dbPassword = "yourpassword"
    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()
    }
}

Retrieving Data from the Database

Once the connection is established, you can execute SQL queries to interact with your data. Below is an example of how to retrieve data:

public class database_Selector {

    @Keyword
    def getResource() {
        SQLHandler_Con sql = new SQLHandler_Con()
        List res = sql.getSelectResults('SELECT TOP 10 PERSONAL_ID, PHONE2 FROM CLIENTS WHERE PERSONAL_ID IS NOT NULL AND PHONE2 IS NOT NULL')

        for (Map oneRow in res) {
            String personalId = oneRow.get("PERSONAL_ID")
            KeywordUtil.logInfo("Personal ID: " + personalId)

            String phone = oneRow.get("PHONE2")
            KeywordUtil.logInfo("Phone number: " + phone)

            // Example of sending a request based on retrieved data
            WS.sendRequest(findTestObject('Object Repository/GET_Customer'))
            RequestObject get_object = findTestObject('Object Repository/GET_Customer')
            get_object.setRestUrl(String.format(get_object.getRestUrl(), personalId, phone))
            ResponseObject get_response = WS.sendRequestAndVerify(get_object)
            WS.verifyResponseStatusCode(get_response, 200)
        }
        return res
    }
}

Enhancing Your Testing with Repeato

If you’re looking to streamline your testing process further, consider using Repeato, a no-code test automation tool that supports iOS, Android, and web applications. Repeato’s ability to create, run, and maintain automated tests quickly and efficiently makes it an excellent alternative to traditional tools. It leverages computer vision and AI to enhance automation capabilities.

With Repeato, you can run command line scripts or JavaScript code to automate complex tasks, supporting both data-driven and keyword-driven testing. Its compatibility with text and JSON formats for saving tests and workspace data ensures easy version control. This flexibility can significantly enhance your testing workflow, particularly if you’re transitioning from other tools like Katalon Studio. For more details on how Repeato can revolutionize your testing, visit our documentation.

Check it out!

Like this article? there’s more where that came from!

Repeato

Documentation

Support

Legal

Repeato Reviews