3

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.

brithwulf
  • 538
  • 10
  • 35
  • Can't you just change the SELECT ....? – Mate Mrše Jan 11 '19 at 07:29
  • SELECT 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, I use that, but in my script are problems. – brithwulf Jan 11 '19 at 07:48
  • It gives me an error at this line int countFromDb = res.get(0).get('calls') , I just want to get selected table, but can't perform that – brithwulf Jan 11 '19 at 07:49
  • what is the full error message? – Tony Bui Jan 11 '19 at 07:49
  • 01-11-2019 11:47:54 AM database_count = db_connection.database_Selector.getResource() Elapsed time: 4.032s db_connection.database_Selector.getResource:14 db_connection.database_Selector.invokeMethod:0 database_count = db_connection.database_Selector.getResource() FAILED. – brithwulf Jan 11 '19 at 07:51
  • Well I made half of my job :D now in res variable I have whole information what I've selected. Is there any way to parse it, I mean that, I have 3 different rows Personal_id, Phone2 and SMS_MOBILE_Phone, I need to store them in some variables and somehow use that to perform API action with GET – brithwulf Jan 11 '19 at 08:05

2 Answers2

4

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
    }
brithwulf
  • 538
  • 10
  • 35
  • @britwulf This is a great reference. As a newb to Katalon, I'm looking for something which pulls all the concepts together. For example, they mention keywords, creating a package, etc. However their tutorials are somewhat spatial and lacking an example from beginning to end. Would you have a repo, tutorial video, or other which ties this all together? – webcrew Apr 16 '19 at 17:48
  • @webcrew Unfortunately detailed tutorial videos or some documentations for these cases does not exist or I have not found yet. But I've got a lot of help and advices on stackoverflow and also on official forum of katalon studio. Katalon Studio is based on a groovy language so, I read documentations about groovy and I'm trying to create some new things. – brithwulf Apr 17 '19 at 11:34
3

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-----')
Ren
  • 31
  • 2