Oracle DB connection

Hi,

I cannot help with OS credentials.

I use Oracle Jars too for connecting to Oracle db within Ready API.  I use the ojdbc6.jar file from Oracle's website.  It's probably old, but it works.

I also use the Environments tab to create db connections for the right db for the 'current' environment.

I sometimes use the ODBC test step, but my preference is to connect via Groovy using the details stored in Environments.

This is a bit complicated, so one step at a time.

I use a lot of Groovy scripts that are stored outside of ReadyAPI.  I do this as maintaining a script that is within dozens of different test steps is a maintenance nightmare.  Instead, in my test I simple instantiate an instance of a Groovy class and call the required methods.  As the actual class is stored outside of ReadyAPI, I only have to update the code in one place.

Have a look on the SmartBear site about the scripts folder....

Moving on, here is the Groovy class I use to establish a connection to a db configured in Environments.  Mine is called jdbcConnections.groovy

package groovyScripts.yourOrgNamespace

import groovy.sql.Sql

class jdbcConnections
{
	jdbcConnections()
	{
		// Empty constructor.  Unused.
	}	
	
	def getJdbcConnection(conDetails, log)
	{
		// This method uses the passed conDetails to tease out the conn string.
		log.info(conDetails.getConfig());

		def connString = conDetails.getConnectionString();
		def userName;
		def password;
		def url;
		
		if (connString.contains('PASS_VALUE')){

			url = 'jdbc:oracle:thin:' + connString.substring(connString.indexOf('@'),connString.size());
			userName = connString.replace('jdbc:oracle:thin:', '');
			userName = userName.substring(0,userName.indexOf('/'));
			password = conDetails.getPassword();
			
		} else {
			url = 'jdbc:oracle:thin:' + connString.substring(connString.indexOf('@'),connString.size());
			userName = connString.replace('jdbc:oracle:thin:', '');
			userName = userName.substring(0,userName.indexOf('/'));
			password = connString.substring(connString.indexOf('/') + 1,connString.indexOf('@'));
		
		}
		
		log.info('		Connecting to database ' + conDetails.getName() + '.  Using account ' + userName + '.  Using Password ' + password + ' at URL ' + url );
	
		return Sql.newInstance(url, userName, password, conDetails.getDriver());

	}
	
}

Below is a basic example of usage....

// This line get the connection details for the named db that is in the 'current' environment.
def dbConnDetails =  messageExchange.modelItem.testStep.testCase.testSuite.project.activeEnvironment.databaseConnectionContainer.getResourceByName("NAME_OF_DB_IN_ENVIRONMENTS");

// Instantiate an object of our class.
def dbJdbcObj = new groovyScripts.yourOrgNamespace.jdbcConnections();

// Using the conn details we obtained above, we pass it in to our get connection method.
// The log is the ReadyAPI log which is accessible from any Groovy script within ReadyAPI.
// I always pass this in so I can make logging calls even in scripts outside of Ready API.
dbConn = dbJdbcObj .getJdbcConnection(dbConnDetails , log);

// Let's query our db...
sqlString = "select * from customer_table where id = 1";
sqlRecord = dbConn.rows(sqlString);

surname = sqlRecord[0]["SURNAME"];  // The bit in quotes is the column of interest.
forename = sqlRecord[0]["FORENAME"];

// Never forget to clean up....
log.info("Closing database connections");
        
dbConn.close();

Hi Richie,

oracle jar - ojdbc8.jar
Oracle is on version 18
Java is on 1.8.0_231
I have the ojdbc8.jar in the bin\ext folder path
and the ready api preference have the oracle thin configured

I have to use os authentication(intergrated security) to connect so i haven`t tried the jdbc connection step.
I would like to know how to connect in jdbc step with os authentication
and also want groovy script with os authentication as i would need to call stored proc from oracle with TVP(table values params)

And i want to connect using servicename