How can i conncet and execute query postgresql in katalon?

Hi all,

Any examples for database postgresql?

at the doc below, i just find mysql and oracle

Regards,
Joko T. Susilo Widodo

hmmm…
just by searching forum, you can find answer here:

Hi, @Andrej Podhajský

Thanks, it’s solved

but, i can’t show the query result to console log

is any way to show the query result?

Thanks

Screen Shot 2018-09-02 at 16.08.53.png

what you get by execute executeQuerry is result set - it’s like pointer set to point BEFORE beginning of lines with result
so to get responses you need cycle to go thru results
(next if written from head, so errors will occur) change rs.getXXX to type of column no. in brakets

def rs = CustomKeywords.'pgsqlConnection.DBCon.executeQuery'("SELECT * FROM borrowers")
while (rs.next()) {
    println(rs.getString(1));
}

Joko T. Susilo Widodo said:

Hi, @Andrej Podhajský

Thanks, it’s solved

but, i can’t show the query result to console log

is any way to show the query result?

Thanks

Can you share PostgreSQL db connection establishment samples?

Can any body share PostgreSQL DB connection as keyword ?

it’s there in my response marked as BA

Andrej Podhajský said:

it’s there in my response marked as BA

@Keyword

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

//Load driver class for your specific database type

String conn = "jdbc:postgresql://" +GlobalVariable.server + ":" +GlobalVariable.port + "/" +GlobalVariable.dbname

//String url = GlobalVariable.server + “:” + GlobalVariable.port + “;databaseName=” +GlobalVariable.dbname+ “;user=” + GlobalVariable.username + “;password=” + GlobalVariable.password

//Class.forName("org.sqlite.JDBC")

//String connectionString = "jdbc:sqlite:" + dataFile

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

connection.close()

}

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

return connection

}

and am calling it in test case like below

Connection connectdb=CustomKeywords.‘appuserdetails.getverificationcode.dbconnection’(GlobalVariable.server,GlobalVariable.port,GlobalVariable.dbname,GlobalVariable.username,GlobalVariable.password)

Statement st=connectdb.createStatement()

st.executeQuery(‘Select verificationkey from appuser where id=Select MAX(id) from appuser)’)

but stuck as like below snap

Can you provide solution what is the problem here?


please post screenshot to forum … always

image.png

Andrej Podhajský said:


please post screenshot to forum … always

Here is the screen shot below

Screenshot_1.jpg

there is no error in that
did you try that select in db console? how long it took there?

Andrej Podhajský said:

there is no error in that
did you try that select in db console? how long it took there?

I have changed the query like below and it start working
(’(Select verificationkey from appuser where id=(Select MAX(id) from appuser))’)

cool

I face the same problem


my keyword is

public class TestPostgres {

private static PgConnection connection = null;
//private static Connection connection = null;

@Keyword
def connectDB(String queryString ) {


	String conn = ''
	String username = ''
	String password = ''
	connection = DriverManager.getConnection(conn, username, password)
	return connection

	Statement stm = connection.createStatement()
	String 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
	}

}