Class: Oci8Simple::Client

Inherits:
Object
  • Object
show all
Defined in:
lib/oci8_simple/client.rb

Overview

Run SQL and PL/SQL statements against an Oracle schema.

Logging

All logging is done to ~/.oci8_simple/oci8_simple.log

Examples

  • Initialize a client against the development schema

    client = Oci8Simple::Client.new

  • Run a simple select query against development schema

    client.run('select id, name from foos') => [[2, "lol"], [3, "hey"], ...]

  • Run a simple select query against stage schema

    Oci8Simple:Client.new("stage").run('select id, name from foos') => [[2, "lol"], [3, "hey"], ...]

  • Update something

    client.run <<-SQL
  UPDATE foos SET bar='baz' WHERE id=1233
SQL

  • Run some DDL

    client.run <<-SQL
  CREATE TABLE foos (
     ID NUMBER(38) NOT NULL
  )
SQL

  • Run some PL/SQL

    client.run <<-SQL
  DECLARE
    a NUMBER;
    b NUMBER;
  BEGIN
    SELECT e,f INTO a,b FROM T1 WHERE e>1;
    INSERT INTO T1 VALUES(b,a);
  END;
SQL

Constant Summary collapse

USER_DIR = 
File.join(ENV["HOME"], ".oci8_simple")
CONFIG = 
Config.new
LOG_FILE = 
File.join(USER_DIR, "oci8_simple.log")

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(env = nil) ⇒ Client

  • env is the environment heading in your database.yml file



45
46
47
 
# File 'lib/oci8_simple/client.rb', line 45

def initialize(env=nil)
  self.env = env || "development"
end

Instance Attribute Details

#env ⇒ Object

Returns the value of attribute env.



42
43
44
 
# File 'lib/oci8_simple/client.rb', line 42

def env
  @env
end

#log_file ⇒ Object

Returns the value of attribute log_file.



42
43
44
 
# File 'lib/oci8_simple/client.rb', line 42

def log_file
  @log_file
end

Instance Method Details

#config ⇒ Object 



99
100
101
 
# File 'lib/oci8_simple/client.rb', line 99

def config
  @config ||= CONFIG[  env]
end

#conn ⇒ Object

Create and return raw Oci8 connection



104
105
106
 
# File 'lib/oci8_simple/client.rb', line 104

def conn
  @conn ||= new_connection
end

#fetch_arrays(sql, &block) ⇒ Object 



83
84
85
86
87
 
# File 'lib/oci8_simple/client.rb', line 83

def fetch_arrays(sql, &block)
  conn.exec(sql) do |r|
    yield r.map{|col| record_to_string(col)}
  end
end

#fetch_hashes(sql, &block) ⇒ Object 



74
75
76
77
78
79
80
81
 
# File 'lib/oci8_simple/client.rb', line 74

def fetch_hashes(sql, &block)
  cursor = conn.exec(sql)
  col_names = cursor.get_col_names.map{|s| s.downcase.to_sym }
  while(r = cursor.fetch) do
    yield Hash[*col_names.zip(r.map {|col| record_to_string(col)}).flatten]
  end
  cursor.close
end

#record_to_string(record) ⇒ Object 



89
90
91
92
93
94
95
96
97
 
# File 'lib/oci8_simple/client.rb', line 89

def record_to_string(record)
  if record.class == BigDecimal
    record.to_f
  elsif record.class == OCI8::CLOB
    record.read
  else
    record.to_s
  end
end

#run(sql, options = {}) ⇒ Object

sql - a query options: 

:hash => true|false - default is false - return an array of hashes (with column names) 
         instead of an array or arrays



59
60
61
62
63
64
65
66
67
68
69
70
71
72
 
# File 'lib/oci8_simple/client.rb', line 59

def run(sql, options={})
  log(sql)
  result = []
  if(options[:hash])
    fetch_hashes(sql) do |r|
      result << r
    end
  else
    fetch_arrays(sql) do |r|
      result << r
    end
  end
  result
end