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


#1

Dear All,

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

  1. Download postgresql from https://jdbc.postgresql.org/download.htmlb

  2. Then place postgreSQL driver jar file on class path in Katalon :
    Project --> Setting --> External Libraries --> Click Add --> Click Apply

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

  4. 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  
    
    }  
    

    }

  5. 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’()

  6. Run your test case

importDriver.PNG

testScript.PNG


#2

Since the day I met him, he knew that today

One day, my memory is filled, surrounded by pictures of me surrounded. Struggling forever in a sentence, "now what to do?.. Play Game: basketball games


#3

@Lasma Silalahi
what version i use for postgresql ?


#4

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


#5

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 :slight_smile:


#6

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");
	}
	
}

#7

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?


#8

hi,

what you are doing, insert or update database?


#9

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 :slight_smile:

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


#10

hello,

could you paste your script here and hide sensitive data?


#11

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

#12

hi,

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


#13

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?


#14

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


#15

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!


#16

hello boy,

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


#17

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