Connecting to database and getting some info from db in Katalon Studio

Question

I'm trying to connect to my database and get from there some info in Katalon Studio, but for some reason it doesn't work.

I've made an db connection keyword for connection and then I use it in my script.

package db_connection

import com.kms.katalon.core.util.KeywordUtil

import groovy.sql.Sql

public class SQLHandler_Con {
    String dbConnString = "jdbc:sqlserver://testapdb\\TEST;databaseName=2007"
    String dbUsername = "testuser"
    String dbPassword = "Password!"
    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()
    }
}

This is my SQLHandler and other script looks like that

public class database_Selector {

    @Keyword
    def getResource() {
        SQLHandler_Con sql = new SQLHandler_Con()
        List<GroovyRowResult> res = sql.getSelectResults('SELECT TOP 100 PERSONAL_ID, PHONE2, SMS_MOBILE_PHONE FROM CLIENTS WHERE PERSONAL_ID IS NOT NULL AND PHONE2 IS NOT NULL AND SMS_MOBILE_PHONE IS NOT NULL ')
        int countFromDb = res.get(0).get('clients')
        println("No. of rows in Database is(are):" + res.get(0))
        return countFromDb
    }
}

And when I try to run this

def database_count = CustomKeywords.'db_connection.databaseSelector.getResource'()
println(database_count)

it gives me an error: database_count = db_connection.database_Selector.getResource() FAILED.

EDIT: I've edited my code and now it works, but if you have an ideas how can i get table from database, can you tell me ? My script was written only for getting count.

SELECT COUNT(*) as Clients FROM CLIENTS WHERE PERSONAL_ID IS NULL AND PHONE2 IS NOT NULL AND SMS_MOBILE_PHONE IS NULL

But I want to perform different action.

Answer 1

found an answer for my question and I decided to post it here, if its possible.

 @Keyword
    def getResource() {
        SQLHandler_2000 sql = new SQLHandler_2000()
        List<GroovyRowResult> 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 )


        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)
        String getContent = get_response.getResponseBodyContent()
        }
        return res
    }

Answer 2

Refresh a DataFile -Katalon Internal DB, Use Dataset, Do Assert Against Some Data

Hi! Wanted to add on to this thread to help the newbs. I am still a semi newb so I know how it feels. The example below is after you have established a database connection. Now what? This is geared for Katalon users that have a license (perhaps can use if you don't have a license. Not sure. This test assumes you have a global connection going and a datafile made to pull in items you want to do asserts against. Why am I adding this answer? Because it took me ages to find an answer on how to refresh katalons internal datafiles on the fly

Below I am, mid test execution telling my internal database\database query to refresh. I call this after I have ordered something from website. I look to take a LogNo from our website UI and compare it with it's companion database entry on sql server.

Some of these imports come standard with katalon, others are from plugins or jars

import com.kms.katalon.core.testcase.TestCase as TestCase 
import com.kms.katalon.core.testdata.TestData as TestData
import com.kms.katalon.core.testobject.TestObject as TestObject
import com.kms.katalon.core.testdata.DBData as DBData
import com.katalon.plugin.keyword.connection.DBType as DBType

//Fetch and Refresh Data. i.e. Katalon DataFile

DBData dbTestData1 = findTestData('internal database name')
dbTestData1.fetchedData = dbTestData1.fetchData()

//Create Method to compare UI against Database. I am also concatenating two items 
together to create unique strings to do asserts on. 
The items are LogNo (transaction number essentially and a message field in database. 
For the front end UI I am grabbing the LogNo on the payment confirmation page 
and using a hard coded string to concatenate with

//Defining UI String and Concatenate. Do not need to add .trim. 
My LogNo has a leading space

String UILOG = WebUI.getText(findTestObject('Object repository/Page_MyWebsite 
Confirmation/td_LogNo')).trim() + 'Transaction Processed Successfully'

//Defining Database String and Using Fetched Data
// example ('LogNo', 1) is the column name and the row number from my 
   dataset\datafile

String dbObjects1 = dbTestData1.getObjectValue('LogNo', 1) + 
dbTestData1.getObjectValue('Message', 1)

dbTestData1.getObjectValue('LogNo', 1)
dbTestData1.getObjectValue('Message', 1)

//This is optional. To see your strings you will do an assert on. 
Also to give a tiny bit of structure in the console

println('-----Comparison Strings-----')
println(dbObjects1)
println(UILOG)
println('------Comparison Strings End-----')

println('-----Assert Statements-----')
//Basically comparing LogNo-Transaction Completed Successfully (hardcoded)
with Database LogNo and Message field 
(Should say Transaction Completed Successfully)

assert dbObjects1 == UILOG : 'Passed'

println('-----Assert Statements End-----')