Katalon Studio DBMS Custom Keywords


#1

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

#2

@Katalon_Store @Katalon_Studio

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 :


#3

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

#4

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') 

#5

@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.


#6

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


#7

It works now, Thanks for your help.


#8

@katalon_store_suppor

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


#9

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.


#10

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,


#11

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.


#12

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.


#13

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


#14

Wow, its working now.

Thanks,


#15

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


#16

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


#17

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.


#18

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


#19

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

Enjoy the plugin!


#20

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?