Connect to Oracle SQL database

I tried to use Groovy script from tutorial to connect to Oracle SQL database:

_ def connectDB(String dataFile){_

_ //Load driver class for your specific database type_

_ Class.forName(“oracle.jdbc.driver.OracleDriver”)_

_ String connectionString = “jdbc:sqlite:” + dataFile_

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

_ connection.close()_

_ }_

_ connection = DriverManager.getConnection(connectionString)_

_ return connection_

_ }_

There is sqlite in connection string, but not sure which value should I use there. (I tried jdbc:oracle too.)

Hi i would like to know as well. I tried:

1. String conn = “jdbc:oracle:thin:@” + host + “:” + port + “/” + dbname

2. String conn = “jdbc:oracle:thin:@” + host + “:” + port + “:” + dbname

Both is giving me error: Cannot cast object ‘oracle.jdbc.driver.T4CConnection@45312be2’ with class ‘oracle.jdbc.driver.T4CConnection’ to class ‘com.mysql.jdbc.Connection’

Hafiz said:

with class ‘oracle.jdbc.driver.T4CConnection’ to class ‘com.mysql.jdbc.Connection’

you need to import correct connection class not one used from MySQL

plaidshirtakos said:

I tried to use Groovy script from tutorial to connect to Oracle SQL database:

_ def connectDB(String dataFile){_

_ //Load driver class for your specific database type_

_ Class.forName(“oracle.jdbc.driver.OracleDriver”)_

_ String connectionString = “jdbc:sqlite:” + dataFile_

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

_ connection.close()_

_ }_

_ connection = DriverManager.getConnection(connectionString)_

_ return connection_

_ }_

There is sqlite in connection string, but not sure which value should I use there. (I tried jdbc:oracle too.)

this is reading for you:
https://www.dba-resources.com/oracle/jdbc-connection-strings-for-oracle-thin-driver/
https://docs.oracle.com/cd/E11882_01/appdev.112/e13995/oracle/jdbc/OracleDriver.html

Andrej Podhajský said:

Hafiz said:

with class ‘oracle.jdbc.driver.T4CConnection’ to class ‘com.mysql.jdbc.Connection’

you need to import correct connection class not one used from MySQL

Hi Andrej

I have this declared:

import oracle.jdbc.driver.OracleDriver

Im getting same error

This is how i did it:

1. Create Custom keyword with this imports:

import com.kms.katalon.core.annotation.Keyword

import groovy.sql.Sql

public class executeSQL {

@Keyword

def execSQL(String host, String port, String sid, String username, String password){

String connectionString

connectionString = “jdbc:oracle:thin:@” + host + “:” + port + “:” + sid

def sql = Sql.newInstance(connectionString, username, password)

try {

        sql.eachRow('select sysdate from dual'){ row ->

         println row

   }

} finally {

sql.close()

}

}

}

2: how to call:

CustomKeywords.‘executeSQL.execSQL’(“YOUR_IP”,
“YOUR_PORT”, “YOUR_SID”, “YOUR_USERNAME”,
“YOUR_PASSWORD” )

crokatalontest said:

This is how i did it:

1. Create Custom keyword with this imports:

import com.kms.katalon.core.annotation.Keyword

import groovy.sql.Sql

public class executeSQL {

@Keyword

def execSQL(String host, String port, String sid, String username, String password){

String connectionString

connectionString = “jdbc:oracle:thin:@” + host + “:” + port + “:” + sid

def sql = Sql.newInstance(connectionString, username, password)

try {

        sql.eachRow('select sysdate from dual'){ row ->

         println row

   }

} finally {

sql.close()

}

}

}

2: how to call:

CustomKeywords.‘executeSQL.execSQL’(“YOUR_IP”,
“YOUR_PORT”, “YOUR_SID”, “YOUR_USERNAME”,
“YOUR_PASSWORD” )

Thank you. this works

helolo @hafiz
please mark @crokatalontest response as best

Hi, how you you write the code for SQLServer?

crokatalontest said:

This is how i did it:

1. Create Custom keyword with this imports:

import com.kms.katalon.core.annotation.Keyword

import groovy.sql.Sql

public class executeSQL {

@Keyword

def execSQL(String host, String port, String sid, String username, String password){

String connectionString

connectionString = “jdbc:oracle:thin:@” + host + “:” + port + “:” + sid

def sql = Sql.newInstance(connectionString, username, password)

try {

        sql.eachRow('select sysdate from dual'){ row ->

         println row

   }

} finally {

sql.close()

}

}

}

2: how to call:

CustomKeywords.‘executeSQL.execSQL’(“YOUR_IP”,
“YOUR_PORT”, “YOUR_SID”, “YOUR_USERNAME”,
“YOUR_PASSWORD” )

What is the purpose of this:

{ row ->

This block of code causes errors.

I had this error:
Cannot cast object ‘oracle.jdbc.driver.OracleStatementWrapper@53ed09e8’ with class ‘oracle.jdbc.driver.OracleStatementWrapper’ to class ‘com.mysql.jdbc.Statement’

And this tip solved the problem. Thanks a lot.

Hi,
I am currently working my way into Katalon Free and trying to connect to the Oracle database to then execute an SQL command.
I have created a custom keyword and filled it with the following content. (I’ve sort of adopted the solution here, which probably doesn’t work for me.)

import groovy.sql.Sql

public class resetauthflag {

  @Keyword
  def execSQL(String host, String port, String sid, String username, String password){

  String connectionString

  connectionString = 'jdbc:oracle:thin:@' + host + ':' + port + ':' + sid

  def sql = Sql.newInstance(connectionString, username, password)
  
    {

sql.eachRow('delete from XXXXXX  ka where ka.kunde_id in (select id from kunde k where k.nummer = 123456'){ row ->

        println row

    }
}  

{
sql.close()
}
}
}

• In my test file I have added the keyword

CustomKeywords.'database.resetauthflag.execSQL'('host', 'port', 'sid', 'user', 'password')

when i run the whole thing i get the following error

Caused by: groovy.lang.GroovyRuntimeException: Could not find matching constructor for: groovy.sql.Sql(java.lang.String, java.lang.String, java.lang.String, database.resetauthflag$_execSQL_closure1, database.resetauthflag$_execSQL_closure2)

What am I doing wrong? I am grateful for any support.

How about try:
def sql = Sql.newInstance(connectionString, "${username}", "${password}")

Groovy - Closures (tutorialspoint.com)