Katalon Studio DBMS Custom Keywords

system · March 18, 2019, 10:46am

This is a companion discussion topic for the original entry at https://store.katalon.com/product/24#qa-content

manpreet.mukkar · March 22, 2019, 8:57pm

I am trying to use the Katalon Studio DBMS Custom Keywords but I am not able to Understand what should be the Connection value here :

katalon_store_suppor · March 23, 2019, 3:22pm

Hi Manpreet,

To get Global Connection from (Project --> Setting–> Database)

Please include those script ( but make sure your project has DBMS plugin from Store)

Connection globalConnection = null;
 ResultSet actorData;

 globalConnection = CustomKeywords.'connection.Database.getGlobalConnection'()
 actorData = CustomKeywords.'connection.Database.executeQuery'(globalConnection, "SELECT * FROM tblActor WITH(NOLOCK)")
 CustomKeywords.'connection.Database.closeConnection'(globalConnection)

For your custom connection ( All password should be encoded with Base64 “https://www.base64encode.org/”)

Connection connection = null;
 ResultSet actorData;
 
 connection = CustomKeywords.'connection.Database.createConnection'(DBType.mysql, 'localhost', '3306', 'Movies', 'katalon', 'a2F0YWxvbg==')
 actorData = CustomKeywords.'connection.Database.executeQuery'(connection, "SELECT * FROM `tblActor`")
 CustomKeywords.'connection.Database.closeConnection'(connection)

DBType values: 
- mysql
- sqlserver
- postgresql
- oracle

katalon_store_suppor · March 23, 2019, 3:22pm

Example for GlobalConnection ( global connection is using SQL SERVER)

 Connection globalConnection = null;
 ResultSet actorData;

 globalConnection = CustomKeywords.'connection.Database.getGlobalConnection'()
 actorData = CustomKeywords.'connection.Database.executeQuery'(globalConnection, "SELECT * FROM tblActor WITH(NOLOCK)")
 CustomKeywords.'connection.Database.closeConnection'(globalConnection)

Example for InternalConnection

DBType values: 
- mysql
- sqlserver
- postgresql
- oracle

All Password should be encoded, you can use base64encode online to get encoded text

 Connection connection = null;
 ResultSet actorData;
 
 connection = CustomKeywords.'connection.Database.createConnection'(DBType.mysql, 'localhost', '3306', 'Movies', 'katalon', 'a2F0YWxvbg==')
 actorData = CustomKeywords.'connection.Database.executeQuery'(connection, "SELECT * FROM `tblActor`")
 CustomKeywords.'connection.Database.closeConnection'(globalConnection)

Examples for ResultSet keywords

'Example: check result set is empty'
 println CustomKeywords.'connection.ResultSet.isEmptyResult'(actorData)
 
'Example: Export a result set to csv file'
 CustomKeywords.'connection.ResultSet.exportToCSV'(actorData, RunConfiguration.getProjectDir() +"/OutPut Files/actor.csv")

'Example: Get total of rows from a result set'
 println CustomKeywords.'connection.ResultSet.getRowCount'(actorData)
 
'Example: Get total of coulmns from a result set'
 println CustomKeywords.'connection.ResultSet.getColumnCount'(actorData) 
 
'Example: Get single cell value using row and column index'
 println CustomKeywords.'connection.ResultSet.getSingleCellValue'(actorData, 2, 2)

'Example: Get single cell value using row and column index'
 println CustomKeywords.'connection.ResultSet.getSingleCellValue'(actorData, 2, 'ActorName')
 
'Example: Get list of cell value using row and column index'
 println CustomKeywords.'connection.ResultSet.getListCellValue'(actorData, 3)
 
'Example: Get list of cell value using row and column label'
 println  CustomKeywords.'connection.ResultSet.getListCellValue'(actorData, 'ActorDOB')

manpreet.mukkar · March 25, 2019, 7:45pm

@katalon_store_suppor I was able to connect to the database using Global Connection Settings and the DBMS keyword but it is returning a value which is is different from what I was expecting.

============ Script I have ============

Connection globalConnection = null

ResultSet Last_Name

globalConnection = CustomKeywords.‘com.katalon.plugin.keyword.connection.DatabaseKeywords.getGlobalConnection’()

Last_Name = CustomKeywords.‘com.katalon.plugin.keyword.connection.DatabaseKeywords.executeQuery’(globalConnection, ‘Select LastName from FirstNameLastName LIMIT 1’)

println ('The Last Name is: '+Last_Name)

===========

When I run this script I get below thing in console log :

The Last Name is: com.mysql.jdbc.JDBC42UpdatableResultSet@3381b4fc
2019-03-25 12:32:37.194 INFO c.k.katalon.core.main.TestCaseExecutor - END Test Cases/WIP/Test

=======

Sorry, I am new to Test Automation and this might be a dumb question.

I think I have to use ResultSet keywords to translate the output but was not able to figure out how to do that.

katalon_store_suppor · March 26, 2019, 12:02am

Hi,

The executeQuery will return a ResultSet, if you want to get information from resultset, please use below

Last_Name = CustomKeywords.‘com.katalon.plugin.keyword.connection.DatabaseKeywords.executeQuery’(globalConnection, ‘Select LastName from FirstNameLastName LIMIT 1’)

'Example: Get single cell value using row and column index'
 println 'The Last Name is '  + CustomKeywords.'connection.ResultSet.getSingleCellValue'(Last_Name, 1, 1)

'Example: Get single cell value using row and column label'
 println CustomKeywords.'connection.ResultSet.getSingleCellValue'(Last_Name, 1, 'LastName')

'Example: Get list of cell value using  column label'
 println  CustomKeywords.'connection.ResultSet.getListCellValue'(Last_Name, 'LastName')

It is open sources that I have developed - there are many demo test cases for you, you can follow it

manpreet.mukkar · March 26, 2019, 10:08pm

It works now, Thanks for your help.

duyluong · March 28, 2019, 3:21am

@katalon_store_suppor

It would be great if you or publisher post the details of using at Overview section on Store

manpreet.mukkar · March 29, 2019, 5:06pm

I am able to run Insert Query using the database keyword but it is not adding anything to the datatbase, Katalon says the Query has passed. Basically what I am trying to save into database is a variable value. Below is my script :

===============

saveemail = CustomKeywords.‘com.katalon.plugin.keyword.connection.DatabaseKeywords.getGlobalConnection’()

insertquery = WebUI.concatenate((([‘Insert into useraccounts_dev_ca values (’’, Field_Email_Address_Field, ‘’, ‘test123!’);’]) as String[]))

CustomKeywords.‘com.katalon.plugin.keyword.connection.DatabaseKeywords.executeUpdate’(saveemail, insertquery)

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

====================

I tried to see the examples in the repo attached by you but the difference in my requirement is that I want to save the value Variable “Field_Email_Address_Field” in the database.

katalon_store_suppor · April 1, 2019, 4:24am

Hi manpreet,

your insert query is not correct syntax. Please try below statement

insertquery = WebUI.concatenate ([“INSERT INTO useraccounts_dev_ca VALUES (’’,”,"’" +Field_Email_Address_Field + “’”,",’’,‘test123!’)"] as String[])

println insertquery

Important note when you try to concate data

For String parameter please make sure to have syntax like this “’” + string_param + “’”
For Integer parameter: “” + int_param + “”

Let me know if it can resolve your problem

Thanks,

manpreet.mukkar · April 1, 2019, 7:52pm

Hello,

I am still not able to successfully insert into table.

The forum editor seems to be changing the Characters, So I was not able to use the exact same solution you mentioned as the Katalon editor is showing error. Based on your solution I came up with this :

Upon executing the script the log viewer gives below statement which makes me wonder that ’ at beginning and end of insert statement is causing the statement to not run on the database but for Katalon it is a valid statement and it is marking the step as passed

Sorry for bugging you again and again but this is a very important step in my script as the saved email address will be used in many other test scenarios.

manpreet.mukkar · April 1, 2019, 8:09pm

Another update, It seems there is some issue with the Connection: When I run Insert query from Katalon I am getting this error

But I can run the insert query successfully using a SQL client on the same database. The database is hosted on AWS.

katalon_store_suppor · April 2, 2019, 1:02am

Hi manpreet,

try to inject this statement to turn off read-only connection

saveemail = CustomKeywords.‘com.katalon.plugin.keyword.connection.DatabaseKeywords.getGlobalConnection’()

saveemail.setReadOnly(false);

your insert query here

and try to execute again and let me know the result
…

manpreet.mukkar · April 2, 2019, 4:50pm

Wow, its working now.

Thanks,

kaushalkumarsingh · April 11, 2019, 4:56pm

Hi, Is there a plan to create Custom Keywords for MongoDB as well?

katalon_store_suppor · April 12, 2019, 8:44am

Yes, I will consider it - maybe it will be included in new version

mehal.patel · April 12, 2019, 3:16pm

I am new to using plugins… I downloaded plugins from Katalon Store. I don’t see this plugins in Katalon. I also downloaded zip version of this project from Git but not sure the step I need to take in order to use it Katalon. I read the readme file as well but being new here I am not able to get the setup done. Please help with some instruction how to start using it.

mehal.patel · April 12, 2019, 6:46pm

Nevermind… I got it installed and I can see the plugins now

katalon_store_suppor · April 14, 2019, 6:38am

Great! for any concern just post the question in the forum, team will follow and help you on issue

Enjoy the plugin!

nausikaa.geeraert · April 15, 2019, 8:53am

I didn’t manage to install either.
I did the reload plugin, I got the zip from github, copied files into folders (to use through plugin-get jar.file), … copied the example in a test case, it blocked on ‘import com.katalon.plugin.keyword.connection.DBType as DBType’

Can someone help?

Topic		Replies	Views
DBMS custom keyword plugin issue Katalon Studio plugins , katalon-studio	2	718	January 16, 2020
Error with custom keyword - connection to MSSQL DB Katalon Studio katalon-studio , web-testing	3	873	April 28, 2020
How to use plugin method inside Keywords Katalon Studio katalon-studio , web-testing	5	705	October 23, 2020
Display database result query Archive katalon-studio	6	809	August 8, 2022
Get SQL query results as variables Katalon Studio integrations , katalon-studio	12	12232	August 19, 2020

Katalon Studio DBMS Custom Keywords

Example for GlobalConnection ( global connection is using SQL SERVER)

Example for InternalConnection

Examples for ResultSet keywords

Related Topics