Forum Discussion

sreedv1986's avatar
sreedv1986
New Contributor
3 years ago

Oracle DB connection

Hi I have to connect to the Oracle DB using Oracle Thin.

I have added the jar to the ext folder

I want to connect both using JDBC step and Groovy

And i want to connect using OS Authentication .

 

can you please share a sample code

  • richie's avatar
    richie
    Community Hero
    Hey sreedv1986,

    Are you saying you want to configure a connection using groovy as well as using the jdbc gui config?

    Can you confirm that please? Reason i ask, if youve already got a connection setup in your jdbc preferences then you dont need to configure the connection in groovy to use it...

    Also. If you want a db connection have you checked what version of db youre using to ensure you have the correct jdbc drivers and likewise ensured youve got the correct version of java to run the jdbc drivers?

    Also, as with the other oracle post on the board at the moment, youll need to confirm whether youre connecting to a db using service name or sid as they require different connectiom strings.

    Cheers,

    Rich
    • ChrisAdams's avatar
      ChrisAdams
      Champion Level 3

      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();

       

       

       

       

      • sreedv1986's avatar
        sreedv1986
        New Contributor

        Hi Chris,

         

        Really appreciate your detailed explanation.

        In my case I don't have username/password

        I need to figure out a way to build connection string.

        Right now I could connect directly in Oracle database using OS Authentication.

        I don't know how to connect without using username and password in readyapi 

        Can you please share the template of the connection string. So that I will reach out to DB's to provide the required details.

    • sreedv1986's avatar
      sreedv1986
      New Contributor

      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