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,
  :raw       => OracleTypes::RAW
}

Instance Method Summary collapse

Instance Method Details

#bind_date(obj, v, i) ⇒ Object 



121
122
123
124
125
126
127
128
129
 
# File 'lib/simple_oracle_jdbc/bindings.rb', line 121

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 



150
151
152
153
154
155
156
 
# File 'lib/simple_oracle_jdbc/bindings.rb', line 150

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 



158
159
160
161
162
163
164
165
166
167
168
169
170
 
# File 'lib/simple_oracle_jdbc/bindings.rb', line 158

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:



116
117
118
119
 
# File 'lib/simple_oracle_jdbc/bindings.rb', line 116

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_raw(obj, v, i) ⇒ Object 



178
179
180
181
182
183
184
185
 
# File 'lib/simple_oracle_jdbc/bindings.rb', line 178

def bind_raw(obj, v, i)
  if v
    raw = Java::OracleSql::RAW.new(v)
    obj.set_raw(i, raw)
  else
    obj.set_null(i, OracleTypes::RAW)
  end
end

#bind_refcursor(obj, v, i) ⇒ Object 



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

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

#bind_string(obj, v, i) ⇒ Object 



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

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 



131
132
133
134
135
136
137
138
139
140
 
# File 'lib/simple_oracle_jdbc/bindings.rb', line 131

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,
  :raw       => OracleTypes::RAW
}

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.



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
86
87
88
 
# File 'lib/simple_oracle_jdbc/bindings.rb', line 58

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)
  elsif type == :raw
    bind_raw(obj, value, i)
  else
    raise UnknownBindType, type.to_s
  end
end

#retrieve_date(obj, i) ⇒ Object 



188
189
190
191
192
193
194
195
 
# File 'lib/simple_oracle_jdbc/bindings.rb', line 188

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 



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

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

#retrieve_number(obj, i) ⇒ Object 



219
220
221
222
223
224
225
226
 
# File 'lib/simple_oracle_jdbc/bindings.rb', line 219

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

#retrieve_raw(obj, i) ⇒ Object 



235
236
237
238
239
240
241
242
 
# File 'lib/simple_oracle_jdbc/bindings.rb', line 235

def retrieve_raw(obj, i)
  v = obj.get_raw(i)
  if v
    v.string_value
  else
    nil
  end
end

#retrieve_refcursor(obj, i) ⇒ Object 



228
229
230
231
232
233
 
# File 'lib/simple_oracle_jdbc/bindings.rb', line 228

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

#retrieve_string(obj, i) ⇒ Object 



206
207
208
 
# File 'lib/simple_oracle_jdbc/bindings.rb', line 206

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

#retrieve_time(obj, i) ⇒ Object 



197
198
199
200
201
202
203
204
 
# File 'lib/simple_oracle_jdbc/bindings.rb', line 197

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.



96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
 
# File 'lib/simple_oracle_jdbc/bindings.rb', line 96

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)
  when 'RAW'
    retrieve_raw(obj, i)
  else
    raise UnknownSQLType, obj.get_meta_data.get_column_type_name(i)
  end
end