class OrangeLib::OracleHandler

Attributes

connection[R]

Public Class Methods

new(user, password, host, port, service_name) click to toggle source

Initialize a connection to cassandra @param [String] user @param [String] password @param [String] host @param [String] service_name @example

db = Orange::OracleHandler.new('audit_repo', 'audit_repo123', 'localhost', '1521', 'orclpmd')
# File lib/orange_lib/oracle_handler.rb, line 18
def initialize(user, password, host, port, service_name)
  oradriver = OracleDriver.new
  DriverManager.registerDriver oradriver
  @user = user
  @password = password
  @host = host
  @port = port
  @service_name = service_name

  begin
    @connection = DriverManager.get_connection("jdbc:oracle:thin:@#{@host}:#{@port.to_i}/#{@service_name}",
                                               @user, @password)
    @connection.auto_commit = false
  rescue Exception => ex
    raise "Connection failed! :X url=jdbc:oracle:thin:@#{host}:#{port}/#{service_name}\nException: #{ex}"
    return
  end
end

Public Instance Methods

execute(sql_string) click to toggle source

Execute a sql statement. @param [String] sql_string the sql statement you would like to execute @return [Array] @example

db = Orange::OracleHandler.new('audit_repo', 'audit_repo123', '192.168.40.50','orclpmd.Orangeonline.com')
result = db.execute("SELECT MAX(ID) FROM AUDIT_REQUEST WHERE HMS_ID='piid_1438591533'")
result.each do |row|
  puts row
end
# File lib/orange_lib/oracle_handler.rb, line 46
def execute(sql_string)
  error_msg = nil
  open_connection if @connection.is_closed
  begin
    stmt = @connection.prepare_statement(sql_string)
    row_set = stmt.execute_query
    meta_data = row_set.get_meta_data
    column_count = meta_data.get_column_count
    columns = {}
    for index in 1..column_count do
      label = meta_data.get_column_label(index)
      columns[label] = {}
      columns[label][:type] = meta_data.get_column_type_name(index).to_s.downcase
      columns[label][:precision] = meta_data.get_precision(index)
      columns[label][:scale] = meta_data.get_scale(index)
    end

    materias = []
    while row_set.next
      hash_materia = {}
      columns.each do |column_name, column_type|
        if column_type[:type] == 'blob'
          value = row_set.get_blob(column_name.to_s)
        else
          value = row_set.get_string(column_name.to_s)
        end
        hash_materia[column_name] = convert_data_type(column_type, value)
      end
      materias << hash_materia
    end
    materias
  rescue Exception => ex
    error_msg =  "Oracle query execution failed.\nException: #{ex}"
  ensure
    row_set.close unless row_set.nil?
    stmt.close unless stmt.nil?
    @connection.close unless (@connection.is_closed || @connection.nil?)
    raise error_msg unless error_msg.nil?
  end
end
execute_update(sql_string) click to toggle source

Execute a sql statement for updating. @param [String] sql_string the sql statement you would like to execute @return [Int] number of rows which are affected @example

db = HMS::OracleHandler.new('audit_repo', 'audit_repo123', '192.168.40.50','orclpmd.hmsonline.com')
result = db.execute_update("UPDATE user set username = 'user1' where id = 1")
puts result
# File lib/orange_lib/oracle_handler.rb, line 95
def execute_update(sql_string)
  error_msg = nil
  open_connection if @connection.is_closed
  begin
    stmt = @connection.prepare_statement(sql_string)
    number_of_rows = stmt.execute_update
    @connection.commit
    number_of_rows
  rescue Exception => ex
    @connection.rollback
    error_msg = "Oracle query execution failed.\nException: #{ex}"
  ensure
    stmt.close unless stmt.nil?
    @connection.close unless (@connection.is_closed || @connection.nil?)
    raise error_msg unless error_msg.nil?
  end
end

Private Instance Methods

convert_data_type(orcl_data={}, value) click to toggle source
# File lib/orange_lib/oracle_handler.rb, line 114
def convert_data_type(orcl_data={}, value)
  if value.nil?
    nil
  else
    case orcl_data[:type]
      when 'number' then
        if orcl_data[:scale] > 0
          value.to_f
        else
          value.to_i
        end
      when 'date' then Date.parse(value)
      when 'timestamp' then
        time_string = value.split('.').map(&:strip)
        time_string = "#{time_string[0]} #{time_string[1]}:#{time_string[2]}:#{time_string[3]} #{time_string[4]}"
        Time.parse(time_string)
      else value
    end
  end
end
open_connection() click to toggle source
# File lib/orange_lib/oracle_handler.rb, line 135
def open_connection
  oradriver = OracleDriver.new
  DriverManager.registerDriver oradriver
  begin
    @connection = DriverManager.get_connection("jdbc:oracle:thin:@#{@host}:#{@port.to_i}/#{@service_name}",
                                               @user, @password)
    @connection.auto_commit = false
  rescue Exception => ex
    raise "Connection failed! :X url=jdbc:oracle:thin:@#{host}:#{port}/#{service_name}\nException: #{ex}"
    return
  end
end