JRuby Oracle RAC 11g R2 SCAN Client Demo

Previously I created a JDBC client against an 11g R2 RAC client using SCAN as shown here. That example simply ensures we are load balancing between the RAC nodes in the cluster because from the JDBC URL we would not even know we are connecting to a RAC cluster.

jdbc:oracle:thin:@apctcsol1.au.oracle.com:1521/pas_srv

In the example below we use some JRuby code to do the exact same thing. We are using the 11.2.0.2 Oracle JDBC Driver and an 11.2.0.2 RAC cluster as well.

JRuby Code

require 'java'  
require 'C:/jdev/jdcbdrivers/11.2/11202/ojdbc6.jar'  
  
java_import java.sql.Statement
java_import java.sql.Connection
java_import java.sql.SQLException
java_import java.sql.DatabaseMetaData

java_import 'oracle.jdbc.OracleDriver'
java_import 'oracle.jdbc.pool.OracleDataSource'

INSTANCE_SQL = <<EOF
select sys_context('userenv', 'instance_name'),
sys_context('userenv', 'server_host'), 
sys_context('userenv', 'service_name')
from dual
EOF

class MyOracleDataSource   
  def initialize(user, passwd, url)  
    @user, @passwd, @url = user, passwd, url
      
    @ods = OracleDataSource.new
    @ods.set_user user
    @ods.set_password passwd
    @ods.set_url url
  end  
   
  # add getters and setters for all attrributes we wish to expose  
  attr_reader :user, :passwd, :url

  def get_connection()
    @ods.get_connection
  end
  
  def create_statement()
    @connection.create_statement
  end
  
  def to_s  
    "OracleDataSource  [user=#{@user}, passwd=#{@passwd}, " +  
    "url=#{@url}]"  
  end  

  def self.create(user, passwd, url)
    ods = new(user, passwd, url)
  rescue
    puts "\n** Error occured **\n"  
    puts "Failed executing SCAN Load Blance test from JRuby ", $!, "\n"
  end
end


user = "scott"
passwd = "tiger"
url = "jdbc:oracle:thin:@apctcsol1.au.oracle.com:1521/pas_srv"

print "Run at #{Time.now} using JRuby #{RUBY_VERSION}\n\n"  

mods = MyOracleDataSource.create(user, passwd, url)

# empty array
conns = []

# obtain 5 connections
for i in 1..5 
  conns[i] = mods.get_connection
end

# determine instance details
for i in 1..5
  if (i == 1)
    meta = conns[i].get_meta_data
    puts "=============\nDatabase Product Name is ... #{meta.getDatabaseProductName()}"  
    puts "Database Product Version is  #{meta.getDatabaseProductVersion()}"  
    puts "=============\nJDBC Driver Name is ........ #{meta.getDriverName()}" 
    puts "JDBC Driver Version is ..... #{meta.getDriverVersion()}"
    puts "JDBC Driver Major Version is #{meta.getDriverMajorVersion()}"  
    puts "JDBC Driver Minor Version is #{meta.getDriverMinorVersion()}" 
    puts "============="    
  end
  
  stmt = conns[i].create_statement
  rset = stmt.execute_query INSTANCE_SQL
  rset.next
  puts "Connection #{i} : instance [#{rset.get_string 1}], " + 
       "host[#{rset.get_string 2}], service[#{rset.get_string 3}] "
  
  rset.close
  stmt.close
end

#close the 5 connections
for i in 1..5
  conns[i].close
end

print "\nEnded at #{Time.now}"

Output

C:\jdev\scripting\demos\jruby\jdbc\scan-load-balance-test>jruby scan-lbt.rb
Run at Mon Jan 31 14:28:11 +1100 2011 using JRuby 1.8.7


=============
Database Product Name is ... Oracle
Database Product Version is  Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
=============
JDBC Driver Name is ........ Oracle JDBC driver
JDBC Driver Version is ..... 11.2.0.2.0
JDBC Driver Major Version is 11
JDBC Driver Minor Version is 2
=============
Connection 1 : instance [A12], host[auw2k4], service[pas_srv]
Connection 2 : instance [A11], host[auw2k3], service[pas_srv]
Connection 3 : instance [A12], host[auw2k4], service[pas_srv]
Connection 4 : instance [A11], host[auw2k3], service[pas_srv]
Connection 5 : instance [A12], host[auw2k4], service[pas_srv]


Ended at Mon Jan 31 14:28:12 +1100 2011