How can I connect and execute query postgresql in katalon project?

Lasma_Silalahi · October 30, 2018, 4:04am

Dear All,

if you want to conncet your postgresql database and execute your query, you can follow below step.

Download postgresql from https://jdbc.postgresql.org/download.htmlb
Then place postgreSQL driver jar file on class path in Katalon :
Project → Setting → External Libraries → Click Add → Click Apply
Create new custom keyword to create db connection. This (.groovy) class can be used for WebUI and Web Service testing. In this case class name is : postgressConn.groovy

Copy this code to postgressConn.groovy : import static com.kms.katalon.core.checkpoint.CheckpointFactory.findCheckpoint
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.annotation.Keyword
import com.kms.katalon.core.checkpoint.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
import com.kms.katalon.core.testcase.TestCase
import com.kms.katalon.core.testdata.TestData
import com.kms.katalon.core.testobject.TestObject
import com.kms.katalon.core.webservice.keyword.WSBuiltInKeywords as WS
import com.kms.katalon.core.webui.keyword.WebUiBuiltInKeywords as WebUI

import internal.GlobalVariable
import java.sql.ResultSet
import org.postgresql.Driver
import java.sql.DriverManager
import java.sql.SQLException
import java.sql.Statement
import org.postgresql.jdbc.PgConnection
import org.postgresql.jdbc.PgResultSet
import org.postgresql.jdbc.PgStatement
import java.sql.*

public class postgressConn {
private static PgConnection connection = null;
@Keyword

def connectDB(String url, String dbname, String port, String username, String password) {  

    //Load driver class for your specific database type  

    String conn = "jdbc:postgresql://" + url + ":" + port + "/" + dbname  

    if (connection != null && !connection.isClosed()) {  

        connection.close()  

    }  

    connection = DriverManager.getConnection(conn, username, password)  

    return connection  

}  

/**  
 \* execute a SQL query on database  
 \* @param queryString SQL query string  
 \* @return a reference to returned data collection, an instance of java.sql.ResultSet  
 */  

@Keyword  

def executeQuery(String queryString) {  

    Statement stm = connection.createStatement()  

    ResultSet resultSet = stm.executeQuery(queryString)  

    ResultSetMetaData metadata = resultSet.getMetaData()  
    int columnCount = metadata.getColumnCount()  
    List<List<String>> rowList = new LinkedList<List<String>>()  

    while (resultSet.next()) {  
        List<String> row = new LinkedList<>()  

        for(int i = 1; i <=columnCount; i++) {  
            Object value = resultSet.getObject(i)  
            row.add(value)  
        }  

        rowList.add(row)  
    }  

    for(List<String> row: rowList) {  
        for(String data: row) {  
            System.out.print(data + " ")  
        }  
        System.out.println()  
    }  

    return rowList  

}  

//Closing the connection  

@Keyword  

def closeDatabaseConnection() {  

    if (connection != null && !connection.isClosed()) {  

        connection.close()  

    }  

    connection = null  

}

}

Then call custom keyword in your test script : import static com.kms.katalon.core.checkpoint.CheckpointFactory.findCheckpoint
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 java.sql.ResultSetMetaData

import org.postgresql.jdbc.PgResultSet

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

responseCode = WS.sendRequest(findTestObject(‘DDSI-13 Search/DDSI-13_UA01_TC001’))
String url=‘localhost’
String dbname=‘mydatabase’
String port=‘5432’
String username=‘password’
String password=‘password’
CustomKeywords.‘com.test.postgressConn.connectDB’(url, dbname, port, username, password)

String selectData=‘SELECT * FROM user limit 1’

List<List> rowList = CustomKeywords.‘com.test.postgressConn.executeQuery’(selectData)
String code=rowList.get(0).get(0)
String name=rowList.get(0).get(7)
String value=rowList.get(0).get(8)

WS.verifyResponseStatusCode(responseCode, 200)

WS.verifyElementPropertyValue(responseCode, ‘data.result[0].code’, code)

WS.verifyElementPropertyValue(responseCode, ‘data.result[0].name’, name)

WS.verifyElementPropertyValue(responseCode, ‘data.result[0].value’, value)

CustomKeywords.‘com.test.postgressConn.closeDatabaseConnection’()
Run your test case

importDriver.PNG

testScript.PNG

ninja · October 30, 2018, 9:15am

@Lasma Silalahi
what version i use for postgresql ?

Lasma_Silalahi · October 30, 2018, 9:19am

@1392-ninja I’m using postgresql-42.2.5. It’s displayed as well in my screenshoot anyway.

ninja · October 30, 2018, 9:21am

Lasma Silalahi said:

@1392-ninja I’m using postgresql-42.2.5. It’s displayed as well in my screenshoot anyway.

Thank you lasma.
i’ll try

Timo_Kuisma · March 22, 2019, 3:54pm

hello.

Testcase
CustomKeywords.'postgresUtil.postgresHandler.connectToPostgres'()

//CustomKeywords.'postgresUtil.postgresHandler.createTable'()

CustomKeywords.'postgresUtil.postgresHandler.insertInto'()

CustomKeywords.'postgresUtil.postgresHandler.selectData'()

CustomKeywords.'postgresUtil.postgresHandler.updateData'()

Keyword
package postgresUtil

import com.kms.katalon.core.annotation.Keyword
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;

public class postgresHandler {
	
	Connection c = null;
	Statement stmt = null;
	
	@Keyword
	public void connectToPostgres(){
		
		
		try {
		   Class.forName("org.postgresql.Driver");
		   c = DriverManager
			  .getConnection("jdbc:postgresql://localhost:5432/testdb","postgres", "postgres");
		} catch (Exception e) {
		   e.printStackTrace();
		   System.err.println(e.getClass().getName()+": "+e.getMessage());
		   System.exit(0);
		}
		System.out.println("Opened database successfully");
	}
	
	@Keyword
	public void createTable(){
		
		try {
			Class.forName("org.postgresql.Driver");
			c = DriverManager
			   .getConnection("jdbc:postgresql://localhost:5432/testdb", "postgres", "postgres");
			System.out.println("Opened database successfully");
   
			stmt = c.createStatement();
			String sql = "CREATE TABLE COMPANY " +
			   "(ID INT PRIMARY KEY     NOT NULL," +
			   " NAME           TEXT    NOT NULL, " +
			   " AGE            INT     NOT NULL, " +
			   " ADDRESS        CHAR(50), " +
			   " SALARY         REAL)";
			stmt.executeUpdate(sql);
			stmt.close();
			c.close();
		 } catch ( Exception e ) {
			System.err.println( e.getClass().getName()+": "+ e.getMessage() );
			System.exit(0);
		 }
		 System.out.println("Table created successfully");
	}
	
	@Keyword
	public void insertInto(){
		
		try {
			Class.forName("org.postgresql.Driver");
			c = DriverManager
			   .getConnection("jdbc:postgresql://localhost:5432/testdb","postgres", "postgres");
			c.setAutoCommit(false);
			System.out.println("Opened database successfully");
   
			stmt = c.createStatement();
			String sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 );";
			stmt.executeUpdate(sql);
   
			sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Allen', 25, 'Texas', 15000.00 );";
			stmt.executeUpdate(sql);
   
			sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );";
			stmt.executeUpdate(sql);
   
			sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
			stmt.executeUpdate(sql);
   
			stmt.close();
			c.commit();
			c.close();
		 } catch (Exception e) {
			System.err.println( e.getClass().getName()+": "+ e.getMessage() );
			System.exit(0);
		 }
		 System.out.println("Records created successfully");
	}
	
	@Keyword
	public void selectData(){
		
		try {
			Class.forName("org.postgresql.Driver");
			c = DriverManager
			   .getConnection("jdbc:postgresql://localhost:5432/testdb","postgres", "postgres");
			c.setAutoCommit(false);
			System.out.println("Opened database successfully");
   
			stmt = c.createStatement();
			ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
			while ( rs.next() ) {
			   int id = rs.getInt("id");
			   String  name = rs.getString("name");
			   int age  = rs.getInt("age");
			   String  address = rs.getString("address");
			   float salary = rs.getFloat("salary");
			   System.out.println( "ID = " + id );
			   System.out.println( "NAME = " + name );
			   System.out.println( "AGE = " + age );
			   System.out.println( "ADDRESS = " + address );
			   System.out.println( "SALARY = " + salary );
			   System.out.println();
			}
			rs.close();
			stmt.close();
			c.close();
		 } catch ( Exception e ) {
			System.err.println( e.getClass().getName()+": "+ e.getMessage() );
			System.exit(0);
		 }
		 System.out.println("Operation done successfully");
	}
	
	@Keyword
	public void updateData(){
		
		try {
			Class.forName("org.postgresql.Driver");
			c = DriverManager
			   .getConnection("jdbc:postgresql://localhost:5432/testdb","postgres", "postgres");
			c.setAutoCommit(false);
			System.out.println("Opened database successfully");
   
			stmt = c.createStatement();
			String sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1;";
			stmt.executeUpdate(sql);
			c.commit();
   
			ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
			while ( rs.next() ) {
			   int id = rs.getInt("id");
			   String  name = rs.getString("name");
			   int age  = rs.getInt("age");
			   String  address = rs.getString("address");
			   float salary = rs.getFloat("salary");
			   System.out.println( "ID = " + id );
			   System.out.println( "NAME = " + name );
			   System.out.println( "AGE = " + age );
			   System.out.println( "ADDRESS = " + address );
			   System.out.println( "SALARY = " + salary );
			   System.out.println();
			}
			rs.close();
			stmt.close();
			c.close();
		 } catch ( Exception e ) {
			System.err.println( e.getClass().getName()+": "+ e.getMessage() );
			System.exit(0);
		 }
		 System.out.println("Operation done successfully");
	}
	
	@Keyword
	public void deleleRowData(){
		
		try {
			Class.forName("org.postgresql.Driver");
			c = DriverManager
			   .getConnection("jdbc:postgresql://localhost:5432/testdb","postgres", "postgres");
			c.setAutoCommit(false);
			System.out.println("Opened database successfully");
   
			stmt = c.createStatement();
			String sql = "DELETE from COMPANY where ID = 2;";
			stmt.executeUpdate(sql);
			c.commit();
   
			ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
			while ( rs.next() ) {
			   int id = rs.getInt("id");
			   String  name = rs.getString("name");
			   int age  = rs.getInt("age");
			   String  address = rs.getString("address");
			   float salary = rs.getFloat("salary");
			   System.out.println( "ID = " + id );
			   System.out.println( "NAME = " + name );
			   System.out.println( "AGE = " + age );
			   System.out.println( "ADDRESS = " + address );
			   System.out.println( "SALARY = " + salary );
			   System.out.println();
			}
			rs.close();
			stmt.close();
			c.close();
		 } catch ( Exception e ) {
			System.err.println( e.getClass().getName()+": "+ e.getMessage() );
			System.exit(0);
		 }
		 System.out.println("Operation done successfully");
	}
	
}

jmorris · June 4, 2019, 6:50pm

I’ve got a similar keyword like this:

`@Keyword


def executeQuery(String queryString)  {


	Statement stm = connection.createStatement()

	ResultSet rs = stm.executeQuery(queryString)

	return rs

}`

I invoke it in the test step by CustomKeywords.'com.mycompany.usersettings.StoredProc.executeQuery'('qa.UnitTest_UserTwoFactorAuthentication_Get')

but am getting an error because I need to pass a parameter email. Here is the error returned:

org.codehaus.groovy.runtime.InvokerInvocationException: com.microsoft.sqlserver.jdbc.SQLServerException: Procedure or function 'UnitTest_UserTwoFactorAuthentication_Get' expects parameter '@userEmailAddress', which was not supplied. at co

how can I pass parameter user@emailaddress.test into this?

Timo_Kuisma · June 5, 2019, 1:30pm

hi,

what you are doing, insert or update database?

jmorris · June 5, 2019, 3:05pm

this particular stored proc is just a select - I have another use case for another stored proc which is an update, but one thing at a time

This stored proc just retrieves information that I insert into the ui as part of a different step.

Timo_Kuisma · June 5, 2019, 6:55pm

hello,

could you paste your script here and hide sensitive data?

jmorris · June 6, 2019, 4:37pm

Hi Timo,

I think you’re asking for the script to the stored proc, since my test cases are posted above:

(
    @userEmailAddress NVARCHAR(254)
)
AS
BEGIN


        SELECT *
          FROM dbo.MyTestUserList
         WHERE Email = @userEmailAddress

END
GO

Timo_Kuisma · June 6, 2019, 5:35pm

hi,

not this postgres script, i asked some of your katalon script how to data is read from the postgres database

jmorris · June 6, 2019, 8:02pm

I’ve got a similar keyword like this:

`@Keyword


def executeQuery(String queryString)  {


	Statement stm = connection.createStatement()

	ResultSet rs = stm.executeQuery(queryString)

	return rs

}`

I invoke it in the test step by CustomKeywords.'com.mycompany.usersettings.StoredProc.executeQuery'('qa.UnitTest_UserTwoFactorAuthentication_Get')

but am getting an error because I need to pass a parameter email . Here is the error returned:

org.codehaus.groovy.runtime.InvokerInvocationException: com.microsoft.sqlserver.jdbc.SQLServerException: Procedure or function 'UnitTest_UserTwoFactorAuthentication_Get' expects parameter '@userEmailAddress', which was not supplied. at co

how can I pass parameter user@emailaddress.test into this?

Timo_Kuisma · June 7, 2019, 6:26pm

hi,
this select will fetch only user Colin data

CustomKeywords.‘postgresUtil.postgresHandler.selectDataByUsername’(“Colin”)

@Keyword
public void selectDataByUsername(String userName){
	
	String query = "SELECT * FROM COMPANY WHERE name = '"+userName+"';"
	
	try {
		Class.forName("org.postgresql.Driver");
		c = DriverManager
				.getConnection("jdbc:postgresql://"+host+":5432/"+db,user,pass);
		c.setAutoCommit(false);
		System.out.println("Opened database successfully");

		pstmt = c.prepareStatement(query);
		ResultSet rs= pstmt.executeQuery();
		while ( rs.next() ) {
			int id = rs.getInt("id");
			String  name = rs.getString("name");
			int age  = rs.getInt("age");
			String  address = rs.getString("address");
			float salary = rs.getFloat("salary");
			String  ssn = rs.getString("ssn");
			System.out.println( "ID = " + id );
			System.out.println( "NAME = " + name );
			System.out.println( "AGE = " + age );
			System.out.println( "ADDRESS = " + address );
			System.out.println( "SALARY = " + salary );
			System.out.println( "SSN = " + ssn );
			System.out.println();
		}
		rs.close();
		pstmt.close();
		c.close();
	} catch ( Exception e ) {
		System.err.println( e.getClass().getName()+": "+ e.getMessage() );
		System.exit(0);
	}
	System.out.println("Operation done successfully");
}

ID = 26
NAME = Colin
AGE = 58
ADDRESS = East Americaland
SALARY = 57573.49
SSN = 080619-866U

jmorris · June 11, 2019, 2:27pm

I looked at this yesterday, not sure it can help do the thing I need. The stored proc I want to invoke doesn’t return data, it just changes the state of a user in the db - one possible work around is embedding specific user’s in the stored proc, but that’s not what I want to do - just having trouble finding a way to pass the username into the stored procedure. I’ll dig in some today. Thank you for your help!

Timo_Kuisma · June 11, 2019, 2:32pm

hello boy,

do you mean that you will need to update some data in db?

jmorris · June 13, 2019, 7:18pm

fwiw, I found a solution. I posted that answer here: Using Stored Procedure in Keyword

Topic		Replies	Views
How can I connect my katalon project to a postgreSQL database? Archive integrations , katalon-studio	2	1772	October 15, 2018
java.sql.SQLException: No suitable driver found for jdbc:postgresql://localhost:5433 Archive integrations , katalon-studio	2	14112	August 8, 2022
Postgres driver...how does Katalon find the suitable one? Katalon Studio integrations , katalon-studio	2	1550	December 28, 2018
Help With Database Connection Archive katalon-studio , web-testing	1	971	August 8, 2022
How would I execute this Java code from Katalon Katalon Studio web-testing	3	536	November 11, 2022

How can I connect and execute query postgresql in katalon project?

Related Topics