Class: SimpleOracleJDBC::DBCall

Inherits:
Object
  • Object
show all
Includes:
Binding
Defined in:
lib/simple_oracle_jdbc/db_call.rb

Constant Summary

Constants included from Binding

Binding::RUBY_TO_JDBC_TYPES

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Methods included from Binding

#bind_date, #bind_int, #bind_number, #bind_out_parameter, #bind_raw, #bind_refcursor, #bind_string, #bind_time, #bind_value, #retrieve_date, #retrieve_int, #retrieve_number, #retrieve_raw, #retrieve_refcursor, #retrieve_string, #retrieve_time, #retrieve_value

Methods included from TypeMap

#java_date_as_date, #java_date_as_time, #java_integer_as_integer, #java_number_as_float, #java_string_as_string, #oracle_raw_as_string, #ruby_any_date_as_jdbc_date, #ruby_date_as_jdbc_date, #ruby_number_as_jdbc_number, #ruby_raw_string_as_jdbc_raw, #ruby_time_as_jdbc_timestamp

Constructor Details

#initialize(conn, sql) ⇒ DBCall

Similar to the class method prepare.



47
48
49
50
 
# File 'lib/simple_oracle_jdbc/db_call.rb', line 47

def initialize(conn, sql)
  @connection = conn
  @call = @connection.prepare_call(sql)
end

Instance Attribute Details

#call ⇒ Object (readonly)

Returns the value of attribute call.



14
15
16
 
# File 'lib/simple_oracle_jdbc/db_call.rb', line 14

def call
  @call
end

Class Method Details

.execute(conn, sql, *binds) ⇒ Object

Takes a JDBC database connection, a procedure call and an optional set of binds and returns a SimpleOracleJDBC object after preparing and executing the procedure call. Input bind variables can be passed as simple values. If a value must be passed as null it should be passed in an array with a type:

[String, nil]

TO mark a bind as an out or inout parameter use an array with three elements:

[String, nil, :out]

Examples:

Procedure Call with several binds

call = DBCall.execute(conn, "begin my_proc(:b1, :b2, :b3); end;", "Input 1 value", [String, nil], [Float, nil, :out])


40
41
42
43
44
 
# File 'lib/simple_oracle_jdbc/db_call.rb', line 40

def self.execute(conn, sql, *binds)
  call = new(conn,sql)
  call.execute(*binds)
  call
end

.prepare(conn, sql) ⇒ Object

Takes a JDBC database connection and a procedure call and returns a SimpleOracleJDBC object after preparing the procedure call. The prepared JDBC callable statement is stored in @call.

Examples:

Preparing a procedure call

call = DBCall.prepare(conn, "begin my_proc(:b1, :b2, :b3); end;")


23
24
25
 
# File 'lib/simple_oracle_jdbc/db_call.rb', line 23

def self.prepare(conn, sql)
  call = new(conn, sql)
end

Instance Method Details

#[](i) ⇒ Object

Allows the bound values to be retrieved along with OUT or IN OUT parameters.

The bind variables are indexed from 1.

If a refcursor is returned, it is retrieved as a SimpleOracleJDBC::Sql object. Other values are returned as Ruby classes, such as Date, Time, String, Float etc.



86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
 
# File 'lib/simple_oracle_jdbc/db_call.rb', line 86

def [](i)
  if i < 1
    raise BindIndexOutOfRange, "Bind indexes must be greater or equal to one"
  end
  bind = @binds[i-1]
  if bind.is_a? Array
    # If its an array, it means it was in OUT or INOUT parameter
    if bind[0] == Date
      retrieve_date(@call, i)
    elsif bind[0] == Time
      retrieve_time(@call, i)
    elsif bind[0] == String
      retrieve_string(@call, i)
    elsif bind[0] == Fixnum or bind[0] == Integer
      retrieve_int(@call, i)
    elsif bind[0] == Float
      retrieve_number(@call, i)
    elsif bind[0] == :refcursor
      retrieve_refcursor(@call, i)
    elsif bind[0] == :raw
      retrieve_raw(@call, i)
    elsif bind[0] == SimpleOracleJDBC::OraArray or bind[0] == SimpleOracleJDBC::OraRecord
      bind[1].retrieve_out_value(@connection, @call, i)
    end
  else
    # If its not an array, it was just an IN, so just pull the bind
    # out of the bind array. No need to get it from the DB object.
    bind
  end
end

#close ⇒ Object

Closes the callable statement



118
119
120
121
122
123
124
 
# File 'lib/simple_oracle_jdbc/db_call.rb', line 118

def close
  if @call
    @call.close
    @call = nil
  end
  @bind = nil
end

#execute(*binds) ⇒ Object

Executes the prepared callable statement stored in @call.

The passed list of bind variables are bound to the object before it is executed. Seen the class method Execute for a definition of how to bind nulls and in/out parameters

Examples:

Procedure Call with several binds

call = DBCall.execute(conn, "begin my_proc(:b1, :b2, :b3); end;", "Input 1 value", [String, nil], [Float, nil, :out])


59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
 
# File 'lib/simple_oracle_jdbc/db_call.rb', line 59

def execute(*binds)
  @binds = binds
  @binds.each_with_index do |b,i|
    bind_value(@call, b, i+1)
  end
  begin
    @call.execute
  rescue Java::JavaSql::SQLException => sqle
    if sqle.message =~ /no data found/
      raise SimpleOracleJDBC::NoDataFound, sqle.to_s
    elsif sqle.message =~ /too many rows/
      raise SimpleOracleJDBC::TooManyRows, sqle.to_s
    elsif sqle.message =~ /ORA-2\d+/
      raise SimpleOracleJDBC::ApplicationError, sqle.to_s
    else
      raise
    end
  end
  self
end