Katalon Studio DBMS Custom Keywords

What Katalon version are you using? Try to use the latest one

1 Like

I was on 6.0.5. Upgrading to 6.1.0 did the thing. Thanks!!

1 Like

Hi,
Since I’m doing a same ‘select’-query on the same database in different test cases, I tought it would be efficient to write a keyword as follows

@Keyword
def getVariables(String tableName){
myConnection = CustomKeywords.‘com.katalon.plugin.keyword.connection.DatabaseKeywords.createConnection’(myDatabaseInfo)
variable = CustomKeywords.‘com.katalon.plugin.keyword.connection.DatabaseKeywords.executeQuery’(myConnection ,
'select Value from ’ + tableName)
CustomKeywords.‘com.katalon.plugin.keyword.connection.DatabaseKeywords.closeConnection’(myConnection )
return variable
}

This code is working in a test case script, but I can’t get it working in a keyword script and call it in a test case.

I tried several things (import CustomKeywords as CustomKeywords, new class()-statements, …) which I found on the Katalon forum, but without success.
Is it anyway possible to do that?

What exactly failure step ? Create connection or execute the query?

Could you show me error information?

Hi,

In the image you can see the console result where I first execute the code in a test case (where it is working) and then call the keyword (which you can see in the image too) that has the same code, but is not working.

I added to the keyword file already

  • import java.sql.Connection as Connection
  • import com.katalon.plugin.keyword.connection.DBType as DBType
  • import CustomKeywords as CustomKeywords
  • import com.kms.katalon.core.configuration.RunConfiguration as RunConfiguration

I think the problem is more a ‘use keyword in other keyword’ problem, but I don’t know how to solve it.(eventhough I checked on https://docs.katalon.com/katalon-studio/tutorials/how-to-use-custom-keyword-in-groovy-class.html)

Hi my dear friend,

Sorry for late response.

Please try below code

import java.sql.Connection
import java.sql.ResultSet

import com.katalon.plugin.keyword.connection.DBType
import com.katalon.plugin.keyword.connection.DatabaseKeywords
import com.katalon.plugin.keyword.connection.ResultSetKeywords
import com.kms.katalon.core.annotation.Keyword

@Keyword
def getVariables(String tableName){
DatabaseKeywords db = new DatabaseKeywords();
ResultSetKeywords rs = new ResultSetKeywords();

	Connection myConnection = db.createConnection(myDatabaseInfo)

	ResultSet data = db.executeQuery(myConnection ,'select Value from ’ + tableName)
	
	String variable = rs.getSingleCellValue(data, 1, 2)
	
	db.closeConnection(myConnection)
	
	return variable

	//return variable
}

You can custom at line “variable” as what you want

Important note:

If you one to return a ResultSet and use in the test script, please make sure to move “close connection” into another keyword, because ResultSet is not contained data, it is an object maintains a cursor only.

P/S: Please give a review on plugin if you really have time

Thanks,
Supporter

Thanks a lot, I got it working fine now.

PS : was planned and is done :slight_smile: :+1:

Hi i am facing an issue

i gave encoded base 64 password still it is not establishing an connection

Test Cases/DBtest FAILED.
Reason:
org.codehaus.groovy.runtime.InvokerInvocationException: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user ’

at com.katalon.plugin.keyword.connection.DatabaseKeywords.invokeMethod(DatabaseKeywords.groovy)
at com.kms.katalon.core.main.CustomKeywordDelegatingMetaClass.invokeStaticMethod(CustomKeywordDelegatingMetaClass.java:49)
at DBtest.run(DBtest:32)
at com.kms.katalon.core.main.ScriptEngine.run(ScriptEngine.java:194)
at com.kms.katalon.core.main.ScriptEngine.runScriptAsRawText(ScriptEngine.java:119)
at com.kms.katalon.core.main.TestCaseExecutor.runScript(TestCaseExecutor.java:336)
at com.kms.katalon.core.main.TestCaseExecutor.doExecute(TestCaseExecutor.java:327)
at com.kms.katalon.core.main.TestCaseExecutor.processExecutionPhase(TestCaseExecutor.java:306)
at com.kms.katalon.core.main.TestCaseExecutor.accessMainPhase(TestCaseExecutor.java:298)
at com.kms.katalon.core.main.TestCaseExecutor.execute(TestCaseExecutor.java:232)
at com.kms.katalon.core.main.TestCaseMain.runTestCase(TestCaseMain.java:114)
at com.kms.katalon.core.main.TestCaseMain.runTestCase(TestCaseMain.java:105)
at com.kms.katalon.core.main.TestCaseMain$runTestCase$0.call(Unknown Source)
at TempTestCase1557834077728.run(TempTestCase1557834077728.groovy:21)

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user ’ psp '. ClientConnectionId:43459f45-dc49-4d70-b0a9-90e3242c05b4
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:251)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:81)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:3077)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:2360)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$100(SQLServerConnection.java:43)
at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:2346)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:6276)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1793)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1404)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1068)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:904)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:451)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1014)
at java_sql_DriverManager$getConnection.call(Unknown Source)
at com.katalon.plugin.keyword.connection.DatabaseKeywords.createConnection(DatabaseKeywords.groovy:44)
at com.katalon.plugin.keyword.connection.DatabaseKeywords.invokeMethod(DatabaseKeywords.groovy)
at com.kms.katalon.core.main.CustomKeywordDelegatingMetaClass.invokeStaticMethod(CustomKeywordDelegatingMetaClass.java:49)
at Script1557830194123.run(Script1557830194123.groovy:32)
… 11 more

Hi all, I am struggling with setting up this plug in.
I have downloaded the plug and got the keywords, but I’m a bit lost as to how to get started.
I tried looking through the details on https://github.com/rosiecorn/katalon-dbms-plugin-usage, but I can’t find the /katalon-dbms-keywords/Sql Scripts to work through example project.

Does anyone have any guidance on how to get up and running?

Hi arvindram,

Could you please share your test scripts, I will spend time to support you

Thanks,

Hi James,

I commit SqlScripts, please clone again and run demo test cases for more understanding
Do not hesitate to contact team if you come across issue

Thanks,

Sorry for the delay

Here is the screenshot plz go through it

import java.sql.Connection as Connection
import java.sql.ResultSet as ResultSet
import com.katalon.plugin.keyword.connection.DBType as DBType
import com.kms.katalon.core.configuration.RunConfiguration as RunConfiguration
//println(CustomKeywords.‘com.katalon.plugin.keyword.connection.ResultSetKeywords.isEmptyResult’(actorData))
//CustomKeywords.‘com.katalon.plugin.keyword.connection.ResultSetKeywords.exportToCSV’(actorData, RunConfiguration.getProjectDir() +
//‘/OutPut Files/sqlserver_actor.csv’)
//println(CustomKeywords.‘com.katalon.plugin.keyword.connection.ResultSetKeywords.getRowCount’(actorData))
//println(CustomKeywords.‘com.katalon.plugin.keyword.connection.ResultSetKeywords.getColumnCount’(actorData))
//println(CustomKeywords.‘com.katalon.plugin.keyword.connection.ResultSetKeywords.getSingleCellValue’(actorData, 2, 2))
//println(CustomKeywords.‘com.katalon.plugin.keyword.connection.ResultSetKeywords.getSingleCellValue’(actorData, 2, ‘ActorName’))
//println(CustomKeywords.‘com.katalon.plugin.keyword.connection.ResultSetKeywords.getListCellValue’(actorData, 3))
//println(CustomKeywords.‘com.katalon.plugin.keyword.connection.ResultSetKeywords.getListCellValue’(actorData, ‘ActorDOB’))
import static com.kms.katalon.core.testcase.TestCaseFactory.findTestCase
import static com.kms.katalon.core.testdata.TestDataFactory.findTestData
import static com.kms.katalon.core.testobject.ObjectRepository.findTestObject
import com.kms.katalon.core.checkpoint.Checkpoint as Checkpoint
import com.kms.katalon.core.cucumber.keyword.CucumberBuiltinKeywords as CucumberKW
import com.kms.katalon.core.mobile.keyword.MobileBuiltInKeywords as Mobile
import com.kms.katalon.core.model.FailureHandling as FailureHandling
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.webservice.keyword.WSBuiltInKeywords as WS
import com.kms.katalon.core.webui.keyword.WebUiBuiltInKeywords as WebUI
import internal.GlobalVariable as GlobalVariable

Connection connection = null

ResultSet Creativereview

connection = CustomKeywords.‘com.katalon.plugin.keyword.connection.DatabaseKeywords.createConnection’(DBType.sqlserver,
‘’)

Creativereview = CustomKeywords.‘com.katalon.plugin.keyword.connection.DatabaseKeywords.executeQuery’(connection, ‘’)

CustomKeywords.‘com.katalon.plugin.keyword.connection.DatabaseKeywords.closeConnection’(Creativereview)

Hi arvindram,

Your test scripts looks okay. And It should work correctly if you enter correct password
Your password is having blank spaces. Maybe it cause the issue.

Thanks,

Thanks for the reply,
I had managed to set up the global connection though and use your examples to get an idea of how the keywords work.
I am now wanting to use the data from an SQl table to populate an email address field on a website
I tried exporting the table to a CSV, but I can’t then select from that field. I then tried using the Create DataFile example, but I am struggling with ho it is set out.
Do you have any advise on how to do these kind of actions?
Thanks,

Hi James,

Assume that you made connections successfully

connection = CustomKeywords.‘com.katalon.plugin.keyword.connection.Database.createConnection’(DBType.mysql, ‘localhost’, ‘3306’, ‘Movies’, ‘katalon’, ‘a2F0YWxvbg==’)

Then, try to make a query call like below

 actorData = CustomKeywords.'com.katalon.plugin.keyword.connection.Database.executeQuery'(connection, "SELECT * FROM `tblActor`")

Then, you could read data from acctorData variable by using ResultSet, bases on you demand, you would like to use this data to populate EmailAddress field on the website. It is easy to handle it by using resultset

Example:

First reading the cell value and assign into one variable
 emailadress = CustomKeywords.'com.katalon.plugin.keyword.connection.ResultSet.getSingleCellValue'(actorData, 2, 'ActorName')

Then you can use this variable to populate into email addess web element easily

To get list of cell data from db consider to use another method like: ```
getListCellValue

Thanks,
Support team

Hi, when can we expect the custom keywords for MongoDB?

Thanks for the reply.
I am all sorted.
My issue was that I was trying to make things too complicated for myself.
I was able to use the default database function in Katalon to create the data file and then make my selection there.

Hi:

I get this error in Event log in version 6.2.1 build 2 when open any project and custom keyword “com.katalon.plugin.keyword.connection.DatabaseKeywords.ResultSetKeywords” isn’t available in custom keywords list.

Mon Jul 15 09:41:45 CDT 2019
Could not load class: com.katalon.plugin.keyword.connection.DatabaseKeywords.ResultSetKeywords of custom plugin keyword: katalon-dbms-keywords.jar
java.lang.ClassNotFoundException: com.katalon.plugin.keyword.connection.DatabaseKeywords.ResultSetKeywords
at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
at groovy.lang.GroovyClassLoader.loadClass(GroovyClassLoader.java:677)
at groovy.lang.GroovyClassLoader.loadClass(GroovyClassLoader.java:787)
at groovy.lang.GroovyClassLoader.loadClass(GroovyClassLoader.java:775)
at com.kms.katalon.custom.parser.CustomKeywordParser.parsePluginKeywords(CustomKeywordParser.java:114)
at com.kms.katalon.controller.KeywordController.parseCustomKeywordInStore(KeywordController.java:152)
at com.kms.katalon.controller.KeywordController.parseAllCustomKeywords(KeywordController.java:139)
at com.kms.katalon.plugin.service.PluginService.refreshProjectClasspath(PluginService.java:222)
at com.kms.katalon.plugin.service.PluginService.reloadPlugins(PluginService.java:168)
at com.kms.katalon.composer.handlers.ReloadPluginsHandler$1.run(ReloadPluginsHandler.java:63)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:55)

how to fetch all the values in a particular Row from a result set by passing the row index??

Hi,

Could you please down grade the version (6.1.1 - build 1)? - It work smoothly on this version
I am investigating why the issue happen on latest Katalon release and will get back with you.

Thanks,
HL