Class: OrangeLib::OracleHandler

Inherits:
Object
  • Object
show all
Defined in:
lib/orange_lib/oracle_handler.rb

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(user, password, host, port, service_name) ⇒ OracleHandler

Initialize a connection to cassandra

Examples:

db = Orange::OracleHandler.new('audit_repo', 'audit_repo123', 'localhost', '1521', 'orclpmd')

Parameters:

  • user (String)
  • password (String)
  • host (String)
  • service_name (String) 


18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
 
# 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

Instance Attribute Details

#connection ⇒ Object (readonly)

Returns the value of attribute connection.



9
10
11
 
# File 'lib/orange_lib/oracle_handler.rb', line 9

def connection
  @connection
end

Instance Method Details

#execute(sql_string) ⇒ Array

Execute a sql statement.

Examples:

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

Parameters:

  • sql_string (String) —

    the sql statement you would like to execute

Returns:

  • (Array) 


46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
 
# 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) ⇒ Int

Execute a sql statement for updating.

Examples:

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

Parameters:

  • sql_string (String) —

    the sql statement you would like to execute

Returns:

  • (Int) —

    number of rows which are affected



95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
 
# 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