Katalon Studio DBMS Custom Keywords

Hi Sudheer_D_J,

Please use below method, it will return list of string (all celldata of specific row)
getSingleRowValue(java.sql.ResultSet rs, int rowIndex)

example:
CustomKeywords.‘com.katalon.plugin.keyword.connection.ResultSetKeywords.getSingleRowValue’(actorData, 1)

The original sourcecode:
"
def getSingleRowValue(java.sql.ResultSet rs, int rowIndex) throws SQLException{

         List<String> listOfValue = new ArrayList<>();

         rs.absolute(rowIndex)

         ResultSetMetaData md = rs.getMetaData();

         int columns = md.getColumnCount(); ;

         for (int i=1; i&lt; columns; i ++) {

         listOfValue.add(md.getColumnName(i) +":" + rs.getString(i))

        }

     return listOfValue

}

Thanks,
HL

Thanks for the support. It Helped for me.

Hi,

The issue has been fixed please re-install the plugin from store again
the new version is 1.0.2

Thanks,
HL

Thanks for the support.

Tested on Version: 6.2.2 Build: 4 and no error in event log.

hi ,everyone.
Does this only support conneting local database?
In my case,I need to make a connection with a database on aliyun,however,it failed.


The error is below.

Mon Aug 19 15:21:44 CST 2019 WARN: Establishing SSL connection without server’s identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn’t set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to ‘false’. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

2019-08-19 15:21:44.778 ERROR k.k.c.m.CustomKeywordDelegatingMetaClass - :x: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Access denied for user ‘b2b’@‘%’ to database ‘b2b’

2019-08-19 15:21:44.785 ERROR c.k.katalon.core.main.TestCaseExecutor - :x: Test Cases/TestForQuery FAILED.

Reason:

org.codehaus.groovy.runtime.InvokerInvocationException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Access denied for user ‘b2b’@‘%’ to database ‘b2b’

at com.katalon.plugin.keyword.connection.DatabaseKeywords.invokeMethod(DatabaseKeywords.groovy)

at com.kms.katalon.core.main.CustomKeywordDelegatingMetaClass.invokeStaticMethod(CustomKeywordDelegatingMetaClass.java:50)

at TestForQuery.run(TestForQuery:14)

at com.kms.katalon.core.main.ScriptEngine.run(ScriptEngine.java:194)

at com.kms.katalon.core.main.ScriptEngine.runScriptAsRawText(ScriptEngine.java:119)

at com.kms.katalon.core.main.TestCaseExecutor.runScript(TestCaseExecutor.java:337)

at com.kms.katalon.core.main.TestCaseExecutor.doExecute(TestCaseExecutor.java:328)

at com.kms.katalon.core.main.TestCaseExecutor.processExecutionPhase(TestCaseExecutor.java:307)

at com.kms.katalon.core.main.TestCaseExecutor.accessMainPhase(TestCaseExecutor.java:299)

at com.kms.katalon.core.main.TestCaseExecutor.execute(TestCaseExecutor.java:233)

at com.kms.katalon.core.main.TestCaseMain.runTestCase(TestCaseMain.java:114)

at com.kms.katalon.core.main.TestCaseMain.runTestCase(TestCaseMain.java:105)

at com.kms.katalon.core.main.TestCaseMain$runTestCase$0.call(Unknown Source)

at TempTestCase1566199298854.run(TempTestCase1566199298854.groovy:21)

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Access denied for user ‘b2b’@‘%’ to database ‘b2b’

at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)

at com.mysql.jdbc.Util.getInstance(Util.java:387)

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:939)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:871)

@katalon_store_suppor @Katalon_Store @katalon3
Dumb question: Will this plugin run AS400 connections and queries?

I saw the items listed under “Version 1.0.0 supported for below DB Type” which refers to MySql, Postgresql, SqlServer and Oracle.

If it is not supported, is there potential of widening support?

Execute keywords includes also the delete command ?

How do you call an Oracle Stored procedure that has out parameters?
package.procedure(output_param1, output_param2, output_param3)

Connection connection = null;

For anyone struggling with this part of the code, the below worked for me.
just add the whole connection string, including keyword where the word null currently is.
Also Make sure to encode password to base64! I completely missed this on the instructions the first time

This plugin was really helpful, so thank you.

Wanted to know if there was any talk of making an export to xlsx or if i could use existing keyword and change to xlsx?

I am new to Katalon. Is there a way to restore the DB after or before updating/creating/deleting tests so that the tests can be run over and over again?

We had a similar issue, when trying to save with an insert statement on MS SQL Server. In our case, we are using a Prepared Statement after grabbing the connection from the DBMS plugin.

    DatabaseKeywords db = new DatabaseKeywords()

	Connection conn = null
	conn = db.getGlobalConnection()

	PreparedStatement ps

	String query = "INSERT INTO person VALUES (?,?) WHERE id = ?";
	
	try {
		ps = conn.prepareStatement(query)
		ps.setString(1, 'name');
        ps.setString(2, 'age');
        ps.setInt(3, 'id');

		ResultSet rs = ps.executeQuery()

We could not get this to insert, but after looking at the source code from rosiecorn’s github project. , we realized that conn.commit() was all we were missing from our statement.

       ResultSet rs = ps.executeQuery()
       conn.commit()

       } catch (Exception e) {
		   log.logInfo e.printStackTrace();
	   }finally{
		   db.closeConnection(conn)
	   }
2 Likes

Can we use this plugin for DB2 connection

@katalon_store_suppor can we connect ClickHouse DB?
If yes can you please help