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