Module: SimpleOracleJDBC::Binding

Included in:
DBCall, Sql
Defined in:
lib/simple_oracle_jdbc/bindings.rb

Constant Summary collapse

RUBY_TO_JDBC_TYPES = 
{
  Date       => OracleTypes::DATE,
  Time       => OracleTypes::TIMESTAMP,
  String     => OracleTypes::VARCHAR,
   #   Clob       => OracleTypes::VARCHAR,
  Fixnum     => OracleTypes::INTEGER,
  Integer    => OracleTypes::INTEGER,
  Bignum     => OracleTypes::NUMERIC,
  Float      => OracleTypes::NUMERIC,
  :refcursor => OracleTypes::CURSOR
}

Instance Method Summary collapse

Instance Method Details

#bind_date(obj, v, i) ⇒ Object 



115
116
117
118
119
120
121
122
123
 
# File 'lib/simple_oracle_jdbc/bindings.rb', line 115

def bind_date(obj, v, i)
  if v
    # %Q is micro seconds since epoch. Divide by 1000 to get milli-sec
    jdbc_date = Java::JavaSql::Date.new(v.strftime("%s").to_f * 1000)
    obj.set_date(i, jdbc_date)
  else
    obj.set_null(i, OracleTypes::DATE)
  end
end

#bind_int(obj, v, i) ⇒ Object 



144
145
146
147
148
149
150
 
# File 'lib/simple_oracle_jdbc/bindings.rb', line 144

def bind_int(obj, v, i)
  if v
    obj.set_int(i, v)
  else
    obj.set_null(i, OracleTypes::INTEGER)
  end
end

#bind_number(obj, v, i) ⇒ Object 



152
153
154
155
156
157
158
159
160
161
162
163
164
 
# File 'lib/simple_oracle_jdbc/bindings.rb', line 152

def bind_number(obj, v, i)
  if v
    # Avoid warning that appeared in JRuby 1.7.3. There are many signatures of
    # Java::OracleSql::NUMBER and it has to pick one. This causes a warning. This
    # technique works around the warning and forces it to the the signiture with a
    # double input - see https://github.com/jruby/jruby/wiki/CallingJavaFromJRuby
    # under the Constructors section.
    construct = Java::OracleSql::NUMBER.java_class.constructor(Java::double)
    obj.set_number(i, construct.new_instance(v))
  else
    obj.set_null(i, OracleTypes::NUMBER)
  end
end

#bind_out_parameter(obj, index, type) ⇒ Object

:nodoc:



110
111
112
113
 
# File 'lib/simple_oracle_jdbc/bindings.rb', line 110

def bind_out_parameter(obj, index, type)
  internal_type = RUBY_TO_JDBC_TYPES[type] || OracleTypes::VARCHAR
  obj.register_out_parameter(index, internal_type)
end

#bind_refcursor(obj, v, i) ⇒ Object 



166
167
168
169
170
 
# File 'lib/simple_oracle_jdbc/bindings.rb', line 166

def bind_refcursor(obj, v, i)
  if v
    raise "not implemented"
  end
end

#bind_string(obj, v, i) ⇒ Object 



136
137
138
139
140
141
142
 
# File 'lib/simple_oracle_jdbc/bindings.rb', line 136

def bind_string(obj, v, i)
  if v
    obj.set_string(i, v)
  else
    obj.set_null(i, OracleTypes::VARCHAR)
  end
end

#bind_time(obj, v, i) ⇒ Object 



125
126
127
128
129
130
131
132
133
134
 
# File 'lib/simple_oracle_jdbc/bindings.rb', line 125

def bind_time(obj, v, i)
  if v
    # Need to use an Oracle TIMESTAMP - dates don't allow a time to be specified
    # for some reason, even though a date in Oracle contains a time.
    jdbc_time = TIMESTAMP.new(Java::JavaSql::Timestamp.new(v.to_f * 1000))
    obj.setTIMESTAMP(i, jdbc_time)
  else
    obj.set_null(i, OracleTypes::TIMESTAMP)
  end
end

#bind_value(obj, v, i) ⇒ Object

Given a JDBC prepared call or prepared statement, a value and a bind index, the value will be bound to JDBC statement.

If value is a single value, ie not an array in is considered an IN parameter.

If value is an array, then it should have either 2 or 3 elements.

  • 2 elements indictes the value is an IN parameter, element 0 indicates the type

of the bind variable, and element 1 is the value, eg:

[String, "Some_value"]

  • 3 elements indicates the value is an OUT or an IN OUT parameter (useful only when using

stored procedures), eg:

[String, "Some_value", :out]
[:refcursor, nil, :out]

When binding values, Ruby types are mapped to Java / JDBC types based on the type of the passed in Ruby object. The mapping is as follows:

RUBY_TO_JDBC_TYPES = {
  Date       => OracleTypes::DATE,
  Time       => OracleTypes::TIMESTAMP,
  String     => OracleTypes::VARCHAR,
  Fixnum     => OracleTypes::INTEGER,
  Integer    => OracleTypes::INTEGER,
  Bignum     => OracleTypes::NUMERIC,
  Float      => OracleTypes::NUMERIC,
  :refcursor => OracleTypes::CURSOR
}

Note that to bind a ref_cursor, there is no natural Ruby class, so it can only be bound using the array form for values.

Also note that in this version, it is not possible to bind a ref_cursor into a procedure - it can only be retrieved.



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
 
# File 'lib/simple_oracle_jdbc/bindings.rb', line 56

def bind_value(obj, v, i)
  type  = v.class
  value = v
  if v.is_a? Array
    # class is being overriden from the input
    type = v[0]
    value = v[1]

    if v.length == 3
      bind_out_parameter(obj, i, type)
    end
  end

  if type == Date
    bind_date(obj, value, i)
  elsif type == Time
    bind_time(obj, value, i)
  elsif type == String
    bind_string(obj, value, i)
  elsif type == Fixnum or type == Integer
    bind_int(obj, value, i)
  elsif type == Float
    bind_number(obj, value, i)
  elsif type == :refcursor
    bind_refcursor(obj, value, i)
  else
    raise UnknownBindType, type.to_s
  end
end

#retrieve_date(obj, i) ⇒ Object 



172
173
174
175
176
177
178
179
 
# File 'lib/simple_oracle_jdbc/bindings.rb', line 172

def retrieve_date(obj, i)
  jdate = obj.get_date(i)
  if jdate
    Date.new(jdate.get_year+1900, jdate.get_month+1, jdate.get_date)
  else
    nil
  end
end

#retrieve_int(obj, i) ⇒ Object 



194
195
196
197
198
199
200
201
 
# File 'lib/simple_oracle_jdbc/bindings.rb', line 194

def retrieve_int(obj, i)
  v = obj.get_int(i)
  if obj.was_null
    nil
  else
    v
  end
end

#retrieve_number(obj, i) ⇒ Object 



203
204
205
206
207
208
209
210
 
# File 'lib/simple_oracle_jdbc/bindings.rb', line 203

def retrieve_number(obj, i)
  v = obj.get_number(i)
  if v
    v.double_value
  else
    nil
  end
end

#retrieve_refcursor(obj, i) ⇒ Object 



212
213
214
215
216
217
 
# File 'lib/simple_oracle_jdbc/bindings.rb', line 212

def retrieve_refcursor(obj, i)
  rset = obj.get_object(i)
  results = Sql.new
  results.result_set = rset
  results
end

#retrieve_string(obj, i) ⇒ Object 



190
191
192
 
# File 'lib/simple_oracle_jdbc/bindings.rb', line 190

def retrieve_string(obj, i)
  obj.get_string(i)
end

#retrieve_time(obj, i) ⇒ Object 



181
182
183
184
185
186
187
188
 
# File 'lib/simple_oracle_jdbc/bindings.rb', line 181

def retrieve_time(obj, i)
  jdate = obj.get_timestamp(i)
  if jdate
    Time.at(jdate.get_time.to_f / 1000)
  else
    nil
  end
end

#retrieve_value(obj, i) ⇒ Object

Given a open JDBC result set and a column index, the value is retrieved and mapped into a Ruby type.

The columns are indexed from 1 in the array.

If the retrieved value is null, nil is returned.



92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
 
# File 'lib/simple_oracle_jdbc/bindings.rb', line 92

def retrieve_value(obj, i)
  case obj.get_meta_data.get_column_type_name(i)
  when 'NUMBER'
    retrieve_number(obj, i)
  when 'INTEGER'
    retrieve_int(obj, i)
  when 'DATE'
    retrieve_time(obj, i)
  when 'TIMESTAMP'
    retrieve_time(obj, i)
  when 'CHAR', 'VARCHAR2', 'CLOB'
    retrieve_string(obj, i)
  else
    raise UnknownSQLType, obj.get_meta_data.get_column_type_name(i)
  end
end