Module: Spider::Model::Storage::Db::Connectors::JDBCOracle

Includes:
JDBC
Defined in:
lib/spiderfw/model/storage/db/connectors/jdbc_oracle.rb

Defined Under Namespace

Modules: ClassMethods

Constant Summary collapse

RUBY_CLASS_TO_SQL_TYPE = 
{
  Fixnum => java.sql.Types::INTEGER,
  Bignum => java.sql.Types::INTEGER,
  Integer => java.sql.Types::INTEGER,
  Float => java.sql.Types::FLOAT,
  BigDecimal => java.sql.Types::NUMERIC,
  String => java.sql.Types::VARCHAR,
  Java::OracleSql::CLOB => Java::oracle.jdbc.OracleTypes::CLOB,
  Java::OracleSql::BLOB => Java::oracle.jdbc.OracleTypes::BLOB,
  Date => java.sql.Types::DATE,
  Time => java.sql.Types::TIMESTAMP,
  DateTime => java.sql.Types::DATE,
  Java::OracleSql::ARRAY => Java::oracle.jdbc.OracleTypes::ARRAY,
  Array => Java::oracle.jdbc.OracleTypes::ARRAY,
  Java::OracleSql::STRUCT => Java::oracle.jdbc.OracleTypes::STRUCT,
  Hash => Java::oracle.jdbc.OracleTypes::STRUCT,
  java.sql.ResultSet => Java::oracle.jdbc.OracleTypes::CURSOR,
}

Constants included from JDBC

Spider::Model::Storage::Db::Connectors::JDBC::DriverManager, Spider::Model::Storage::Db::Connectors::JDBC::Mutex, Spider::Model::Storage::Db::Connectors::JDBC::Statement, Spider::Model::Storage::Db::Connectors::JDBC::Types

Class Method Summary collapse

Instance Method Summary collapse

Methods included from JDBC

driver_class

Class Method Details

.included(klass) ⇒ Object 



8
9
10
 
# File 'lib/spiderfw/model/storage/db/connectors/jdbc_oracle.rb', line 8

def self.included(klass)
    klass.extend(ClassMethods)
end

Instance Method Details

#do_commit ⇒ Object 



84
85
86
87
88
 
# File 'lib/spiderfw/model/storage/db/connectors/jdbc_oracle.rb', line 84

def do_commit
    return release unless transactions_enabled?
    curr[:conn].commit if curr[:conn]
    release
end

#do_describe_table(conn, table) ⇒ Object 



305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
 
# File 'lib/spiderfw/model/storage/db/connectors/jdbc_oracle.rb', line 305

def do_describe_table(conn, table)
    md = get_db_metadata(conn)
    res = md.getColumns(nil, @user.upcase, table, nil)
    columns = []
    while res.next()
        col_name = res.getString("COLUMN_NAME")
        col = {
            :name => col_name,
            :type => res.getString("TYPE_NAME"),
            :length => res.getInt("COLUMN_SIZE"),
            :precision => res.getInt("DECIMAL_DIGITS"),
        }
        col.delete(:length) if (col[:precision])
        columns << col
    end
    columns
end

#do_rollback ⇒ Object 



90
91
92
93
94
 
# File 'lib/spiderfw/model/storage/db/connectors/jdbc_oracle.rb', line 90

def do_rollback
    return release unless transactions_enabled?
    curr[:conn].rollback
    release
end

#do_start_transaction ⇒ Object 



74
75
76
77
 
# File 'lib/spiderfw/model/storage/db/connectors/jdbc_oracle.rb', line 74

def do_start_transaction
    return unless transactions_enabled?
    connection.setAutoCommit(false)
end

#execute(sql, *bind_vars) ⇒ Object 



207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
 
# File 'lib/spiderfw/model/storage/db/connectors/jdbc_oracle.rb', line 207

def execute(sql, *bind_vars)
    begin
        if (bind_vars && bind_vars.length > 0)
            debug_vars = bind_vars.map{|var| var = var.to_s; var && var.length > 50 ? var[0..50]+"...(#{var.length-50} chars more)" : var}
        end
        curr[:last_executed] = [sql, bind_vars]
        if (Spider.conf.get('storage.db.replace_debug_vars'))
            debug("oci8 #{connection} executing: "+sql.gsub(/:(\d+)/){
                i = $1.to_i
                v = bind_vars[i-1]
                dv = debug_vars[i-1]
                v.is_a?(String) ? "'#{dv}'" : dv
            })
        else
            debug_vars_str = debug_vars ? debug_vars.join(', ') : ''
            debug("oci8 #{connection} executing:\n#{sql}\n[#{debug_vars_str}]")
        end
        query = curr[:last_query]
        stmt = connection.prepareStatement(sql)
        return unless stmt
        bind_vars.each_index do |i|
            set_bind_variable(stmt, i+1, bind_vars[i])
        end
        res = nil
        if stmt.execute() # false means this is an update query
            res = stmt.getResultSet()
        end
        if (res)
            result = []
            metadata = res.getMetaData
            column_count = metadata.getColumnCount
            column_names = []
            column_types = []
            1.upto(column_count) do |i|
                column_names[i] = metadata.getColumnName(i)
                column_types[i] = metadata.getColumnTypeName(i).to_sym
            end
            
            while res.next()
                h = {}
                1.upto(column_count) do |i|
                    h[column_names[i]] = value_from_resultset(res, i, column_types[i])
                end
                if block_given?
                    yield h
                else
                    result << h
                end
            end
            res.close
        end
        if (res)
            unless block_given?
                result.extend(StorageResult)
                curr[:last_result] = result
                return result
            end
        else
            return res
        end
        stmt.close
    rescue => exc
        stmt.cancel if stmt
        # curr[:conn].break if curr[:conn]
        rollback! if in_transaction?
        #curr[:conn].logoff
        release
        raise
    ensure
        stmt.close if stmt
        release if curr[:conn] && !in_transaction?
    end
end

#execute_statement(stmt, *bind_vars) ⇒ Object 



287
288
289
290
291
292
 
# File 'lib/spiderfw/model/storage/db/connectors/jdbc_oracle.rb', line 287

def execute_statement(stmt, *bind_vars)
    bind_vars.each_index do |i|
        set_bind_variable(stmt, i+1, bind_vars[i])
    end
    stmt.execute()
end

#get_db_metadata(conn) ⇒ Object 



324
325
326
 
# File 'lib/spiderfw/model/storage/db/connectors/jdbc_oracle.rb', line 324

def get_db_metadata(conn)
    @db_metadata ||= conn.getMetaData()
end

#get_table_metadata(conn, table) ⇒ Object 



328
329
330
 
# File 'lib/spiderfw/model/storage/db/connectors/jdbc_oracle.rb', line 328

def get_table_metadata(conn, table)
    get_db_metadata(conn).getTables(nil, @user.upcase, table, nil)
end

#in_transaction? ⇒ Boolean

Returns:

  • (Boolean) 


79
80
81
82
 
# File 'lib/spiderfw/model/storage/db/connectors/jdbc_oracle.rb', line 79

def in_transaction?
    return false unless transactions_enabled?
    return curr[:conn] && !curr[:conn].getAutoCommit()
end

#java_bigdecimal(value) ⇒ Object 



340
341
342
 
# File 'lib/spiderfw/model/storage/db/connectors/jdbc_oracle.rb', line 340

def java_bigdecimal(value)
    value && java.math.BigDecimal.new(value.to_s)
end

#java_date(value) ⇒ Object 



332
333
334
 
# File 'lib/spiderfw/model/storage/db/connectors/jdbc_oracle.rb', line 332

def java_date(value)
    value && Java::oracle.sql.DATE.new(value.strftime("%Y-%m-%d %H:%M:%S"))
end

#java_timestamp(value) ⇒ Object 



336
337
338
 
# File 'lib/spiderfw/model/storage/db/connectors/jdbc_oracle.rb', line 336

def java_timestamp(value)
    value && Java::java.sql.Timestamp.new(value.year-1900, value.month-1, value.day, value.hour, value.min, value.sec, value.usec * 1000)
end

#prepare(sql) ⇒ Object 



282
283
284
285
 
# File 'lib/spiderfw/model/storage/db/connectors/jdbc_oracle.rb', line 282

def prepare(sql)
    debug("oci8 preparing: #{sql}")
    return connection.prepareStatement(sql)
end

#prepare_value(type, value) ⇒ Object 



124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
 
# File 'lib/spiderfw/model/storage/db/connectors/jdbc_oracle.rb', line 124

def prepare_value(type, value)
    return Oracle::OracleNilValue.new(Spider::Model.ruby_type(type)) if (value == nil)
    case type.name
    when 'Spider::DataTypes::Decimal', 'BigDecimal'
        java_bigdecimal(value)
    when 'Date', 'DateTime'
        java_date(value)
    when 'Time'
        java_timestamp(value)
    when 'Spider::DataTypes::Text'
        if value
            clob = Java::OracleSql::CLOB.createTemporary(connection, false, Java::OracleSql::CLOB::DURATION_SESSION)
            clob.setString(1, value)
            clob
        else
            Java::OracleSql::CLOB.getEmptyCLOB
        end
    when 'Spider::DataTypes::Binary'
        if value
            blob = Java::OracleSql::BLOB.createTemporary(connection, false, Java::OracleSql::BLOB::DURATION_SESSION)
            blob.setBytes(1, value.to_java_bytes)
            blob
        else
            Java::OracleSql::BLOB.getEmptyBLOB
        end
    else
        super(type, value)
    end
end

#release ⇒ Object 



64
65
66
67
68
69
70
71
72
 
# File 'lib/spiderfw/model/storage/db/connectors/jdbc_oracle.rb', line 64

def release
    begin
        curr[:conn].setAutoCommit(true) if curr[:conn]
        super
    rescue
        self.class.remove_connection(curr[:conn], @connection_params)
        curr[:conn] = nil
    end
end

#set_bind_variable(stmt, i, val) ⇒ Object 



155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
 
# File 'lib/spiderfw/model/storage/db/connectors/jdbc_oracle.rb', line 155

def set_bind_variable(stmt, i, val)
    method = nil
    if val.is_a?(Oracle::OracleNilValue)
        type = RUBY_CLASS_TO_SQL_TYPE[val.type] || java.sql.Types::VARCHAR
        return stmt.setNull(i, type)
    else
        method = case val.class.name
        when 'Fixnum', 'Float'
            :setInt
        when 'Java::JavaMath::BigDecimal'
            :setBigDecimal
        when 'String'
            :setString
        when 'Java::OracleSql::CLOB'
            :setClob
        when 'Java::OracleSql::BLOB'
            :setBlob
        when 'Java::OracleSql::DATE'
            :setDATE
        when 'Java::OracleSql::Timestamp'
            :setTimestamp
        end
    end
    raise "Can't find how to bind variable #{val}" unless method
    stmt.send(method, i, val)
end

#table_exists?(table) ⇒ Boolean

Returns:

  • (Boolean) 


295
296
297
298
299
300
301
302
303
 
# File 'lib/spiderfw/model/storage/db/connectors/jdbc_oracle.rb', line 295

def table_exists?(table)
    connection do |c|
        res = get_table_metadata(c, table)
        while res.next()
            return true
        end
        return false
    end
end

#value_from_resultset(res, i, type) ⇒ Object 



182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
 
# File 'lib/spiderfw/model/storage/db/connectors/jdbc_oracle.rb', line 182

def value_from_resultset(res, i, type)
    method = case type
    when :INTEGER, :SMALLINT, :TINYING, :BIGINTEGER, :NUMBER, :NUMERIC
        :getInt
    when :LONG
        :getLong
    when :FLOAT, :REAL
        :getFloat
    when :DECIMAL
        :getBigDecimal
    when :VARCHAR, :VARCHAR2, :LONGVARCHAR, :NCHAR, :CHAR
        :getString
    when :CLOB
        :getClob
    when :DATE, :TIME
        :getDATE
    when :TIMESTAMP
        :getTimestamp
    else
        raise "Don't know how to convert Oracle value of type #{type}"
    end
    res.send(method, i)
end

#value_to_mapper(type, value) ⇒ Object 



96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
 
# File 'lib/spiderfw/model/storage/db/connectors/jdbc_oracle.rb', line 96

def value_to_mapper(type, value)
    return value if value.nil?
    case type.name
    when 'Time', 'Date', 'DateTime'
        return nil unless value
        d = value.dateValue
        t = value.timeValue
        value = Time.local(d.year + 1900, d.month + 1, d.date, t.hours, t.minutes, t.seconds)
        return value.to_datetime if type == DateTime
        return value.to_date
    when 'Spider::DataTypes::Text'
        if value.isEmptyLob
            value = nil
        else
            value = value.getSubString(1, value.length)
        end
    when 'Spider::DataTypes::Binary'
        if value.isEmptyLob
            nil
        else
            String.from_java_bytes(value.getBytes(1, value.length))
        end
    when 'Spider::DataTypes::Decimal', 'BigDecimal'
        value = value.to_s
    end
    return super(type, value)
end