Class: ActiveRecord::ConnectionAdapters::IBM_DB2

Inherits:
IBM_DataServer show all
Defined in:
lib/active_record/connection_adapters/ibm_db_adapter.rb

Overview

class IBM_DataServer

Direct Known Subclasses

IBM_DB2_I5, IBM_DB2_LUW, IBM_DB2_ZOS

Instance Method Summary collapse

Methods inherited from IBM_DataServer

#check_reserved_words, #create_index_after_table, #execute, #limit_not_supported_types, #prepare, #remove_column, #reorg_table, #select, #select_rows, #set_schema, #setup_for_lob_table

Constructor Details

#initialize(adapter, ar3) ⇒ IBM_DB2

Returns a new instance of IBM_DB2.



2184
2185
2186
2187
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2184

def initialize(adapter, ar3)
  super(adapter,ar3)
  @limit = @offset = nil
end

Instance Method Details

#change_column(table_name, column_name, type, options) ⇒ Object 



2243
2244
2245
2246
2247
2248
2249
2250
2251
2252
2253
2254
2255
2256
2257
2258
2259
2260
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2243

def change_column(table_name, column_name, type, options)
  data_type = @adapter.type_to_sql(type, options[:limit], options[:precision], options[:scale])
  begin
    execute "ALTER TABLE #{table_name} ALTER #{column_name} SET DATA TYPE #{data_type}"
  rescue StandardError => exec_err
    if exec_err.message.include?('SQLCODE=-190')
      raise StatementInvalid, 
      "Please consult documentation for compatible data types while changing column datatype. \
The column datatype change to [#{data_type}] is not supported by this data server: #{exec_err}"
    else
      raise "#{exec_err}"
    end
  end
  reorg_table(table_name)
  change_column_null(table_name,column_name,options[:null],nil)
  change_column_default(table_name, column_name, options[:default])
  reorg_table(table_name)
end

#change_column_default(table_name, column_name, default) ⇒ Object

DB2 specific ALTER TABLE statement to add a default clause



2263
2264
2265
2266
2267
2268
2269
2270
2271
2272
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2263

def change_column_default(table_name, column_name, default)
  # SQL statement which alters column's default value
  change_column_sql = "ALTER TABLE #{table_name} ALTER COLUMN #{column_name} \
SET WITH DEFAULT #{@adapter.quote(default)}"

  stmt = execute(change_column_sql)
  reorg_table(table_name)
  ensure
    IBM_DB.free_stmt(stmt) if stmt
end

#change_column_null(table_name, column_name, null, default) ⇒ Object

DB2 specific ALTER TABLE statement to change the nullability of a column



2275
2276
2277
2278
2279
2280
2281
2282
2283
2284
2285
2286
2287
2288
2289
2290
2291
2292
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2275

def change_column_null(table_name, column_name, null, default)
  if !default.nil?
    change_column_default(table_name, column_name, default)
  end 

  if !null.nil? 
    if null
      change_column_sql = "ALTER TABLE #{table_name} ALTER #{column_name} DROP NOT NULL"
    else
      change_column_sql = "ALTER TABLE #{table_name} ALTER #{column_name} SET NOT NULL"
    end
    stmt = execute(change_column_sql)
    reorg_table(table_name)
  end

  ensure
    IBM_DB.free_stmt(stmt) if stmt   
end

#get_datetime_mapping ⇒ Object

This method returns the DB2 SQL type corresponding to the Rails datetime/timestamp type



2296
2297
2298
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2296

def get_datetime_mapping
  return "timestamp"
end

#get_double_mapping ⇒ Object

This method returns the DB2 SQL type corresponding to Rails double type



2307
2308
2309
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2307

def get_double_mapping
  return "double"
end

#get_time_mapping ⇒ Object

This method returns the DB2 SQL type corresponding to the Rails time type



2302
2303
2304
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2302

def get_time_mapping
  return "time"
end

#last_generated_id(stmt) ⇒ Object

Returns the last automatically generated ID. This method is required by the insert method The “stmt” parameter is ignored for DB2 but used for IDS



2200
2201
2202
2203
2204
2205
2206
2207
2208
2209
2210
2211
2212
2213
2214
2215
2216
2217
2218
2219
2220
2221
2222
2223
2224
2225
2226
2227
2228
2229
2230
2231
2232
2233
2234
2235
2236
2237
2238
2239
2240
2241
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2200

def last_generated_id(stmt)
  # Queries the db to obtain the last ID that was automatically generated
  sql = "SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1"
  stmt = IBM_DB.prepare(@adapter.connection, sql)
  if(stmt)
    if(IBM_DB.execute(stmt, nil))
      begin
        # Fetches the only record available (containing the last id)
        IBM_DB.fetch_row(stmt)
        # Retrieves and returns the result of the query with the last id.
        IBM_DB.result(stmt,0)
      rescue StandardError => fetch_error # Handle driver fetch errors
        error_msg = IBM_DB.getErrormsg(stmt, IBM_DB::DB_STMT )
        if error_msg && !error_msg.empty?
          raise "Failed to retrieve last generated id: #{error_msg}"
        else
          error_msg = "An unexpected error occurred during retrieval of last generated id"
          error_msg = error_msg + ": #{fetch_error.message}" if !fetch_error.message.empty?
          raise error_msg
        end
      ensure  # Free resources associated with the statement
        IBM_DB.free_stmt(stmt) if stmt
      end
    else
      error_msg = IBM_DB.getErrormsg(stmt, IBM_DB::DB_STMT )
      IBM_DB.free_stmt(stmt) if stmt
      if error_msg && !error_msg.empty?
        raise "Failed to retrieve last generated id: #{error_msg}"
      else
        error_msg = "An unexpected error occurred during retrieval of last generated id"
        raise error_msg
      end
    end
  else
    error_msg = IBM_DB.getErrormsg(@adapter.connection, IBM_DB::DB_CONN )
    if error_msg && !error_msg.empty?
      raise "Failed to retrieve last generated id due to error: #{error_msg}"
    else
      raise StandardError.new('An unexpected error occurred during retrieval of last generated id')
    end
  end
end

#primary_key_definition(start_id) ⇒ Object 



2193
2194
2195
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2193

def primary_key_definition(start_id)
  return "INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH #{start_id}) PRIMARY KEY"
end

#query_offset_limit(sql, offset, limit) ⇒ Object

# Commenting this code, as offset handling is now part of sql and we need to handle it in select and also

# need not set cursor type during prepare or execute
# Fetches all the results available. IBM_DB.fetch_assoc(stmt) returns
# an hash for each single record.
# The loop stops when there aren't any more valid records to fetch
def select(stmt)
  results = []
  begin
    if (!@offset.nil? && @offset >= 0) || (!@limit.nil? && @limit > 0)
      # We know at this point that there is an offset and/or a limit
      # Check if the cursor type is set correctly
      cursor_type = IBM_DB.get_option stmt, IBM_DB::SQL_ATTR_CURSOR_TYPE, 0
      @offset = 0 if @offset.nil?
      if (cursor_type == IBM_DB::SQL_CURSOR_STATIC)
        index = 0
        # Get @limit rows starting at @offset
        while (index < @limit)
          # We increment the offset by 1 because for DB2 the offset of the initial row is 1 instead of 0
          if single_hash = IBM_DB.fetch_assoc(stmt, @offset + index + 1)
            # Add the record to the +results+ array
            results <<  single_hash
            index = index + 1
          else
            # break from the while loop
            break 
          end
        end
      else # cursor != IBM_DB::SQL_CURSOR_STATIC
        # If the result set contains a LOB, the cursor type will never be SQL_CURSOR_STATIC
        # because DB2 does not allow this. We can't use the offset mechanism because the cursor 
        # is not scrollable. In this case, ignore first @offset rows and return rows starting 
        # at @offset to @offset + @limit
        index = 0
        while (index < @offset + @limit)
          if single_hash = IBM_DB.fetch_assoc(stmt)
            # Add the record to the +results+ array only from row @offset to @offset + @limit
            if (index >= @offset)
              results <<  single_hash
            end
            index = index + 1
          else
            # break from the while loop
            break
          end
        end
      end
    # This is the case where limit is set to zero
    # Simply return an empty +results+
    elsif (!@limit.nil? && @limit == 0)
      results
    # No limits or offsets specified
    else
      while single_hash = IBM_DB.fetch_assoc(stmt)
        # Add the record to the +results+ array
        results <<  single_hash
      end
      return results
    end
  rescue StandardError => fetch_error # Handle driver fetch errors
    error_msg = IBM_DB.getErrormsg(stmt, IBM_DB::DB_STMT )
    if error_msg && !error_msg.empty?
      raise StatementInvalid,"Failed to retrieve data: #{error_msg}"
    else
      error_msg = "An unexpected error occurred during data retrieval"
      error_msg = error_msg + ": #{fetch_error.message}" if !fetch_error.message.empty?
      raise error_msg
    end
  ensure
    # Assign the instance variables to nil. We will not be using them again
    @offset = nil
    @limit = nil
  end
end

# Fetches all the results available. IBM_DB.fetch_array(stmt) returns
# an array for each single record.
# The loop stops when there aren't any more valid records to fetch
def select_rows(sql, name, stmt, results)
  begin
    if (!@offset.nil? && @offset >= 0) || (!@limit.nil? && @limit > 0)
      # We know at this point that there is an offset and/or a limit
      # Check if the cursor type is set correctly
      cursor_type = IBM_DB.get_option stmt, IBM_DB::SQL_ATTR_CURSOR_TYPE, 0
      @offset = 0 if @offset.nil?
      if (cursor_type == IBM_DB::SQL_CURSOR_STATIC)
        index = 0
        # Get @limit rows starting at @offset
        while (index < @limit)
          # We increment the offset by 1 because for DB2 the offset of the initial row is 1 instead of 0
          if single_array = IBM_DB.fetch_array(stmt, @offset + index + 1)
            # Add the array to the +results+ array
            results <<  single_array
            index = index + 1
          else
            # break from the while loop
            break 
          end
        end
      else # cursor != IBM_DB::SQL_CURSOR_STATIC
        # If the result set contains a LOB, the cursor type will never be SQL_CURSOR_STATIC
        # because DB2 does not allow this. We can't use the offset mechanism because the cursor 
        # is not scrollable. In this case, ignore first @offset rows and return rows starting 
        # at @offset to @offset + @limit
        index = 0
        while (index < @offset + @limit)
          if single_array = IBM_DB.fetch_array(stmt)
            # Add the array to the +results+ array only from row @offset to @offset + @limit
            if (index >= @offset)
              results <<  single_array
            end
            index = index + 1
          else
            # break from the while loop
            break
          end
        end
      end
    # This is the case where limit is set to zero
    # Simply return an empty +results+
    elsif (!@limit.nil? && @limit == 0)
      results
    # No limits or offsets specified
    else
      while single_array = IBM_DB.fetch_array(stmt)
        # Add the array to the +results+ array
        results <<  single_array
      end
    end
  rescue StandardError => fetch_error # Handle driver fetch errors
    error_msg = IBM_DB.getErrormsg(stmt, IBM_DB::DB_STMT )
    if error_msg && !error_msg.empty?
      raise StatementInvalid,"Failed to retrieve data: #{error_msg}"
    else
      error_msg = "An unexpected error occurred during data retrieval"
      error_msg = error_msg + ": #{fetch_error.message}" if !fetch_error.message.empty?
      raise error_msg
    end
  ensure
    # Assign the instance variables to nil. We will not be using them again
    @offset = nil
    @limit = nil
  end
  return results
end

# Praveen
def prepare(sql,name = nil)
  # Check if there is a limit and/or an offset
  # If so then make sure and use a static cursor type
  begin
    if (!@offset.nil? && @offset >= 0) || (!@limit.nil? && @limit > 0)
      # Set the cursor type to static so we can later utilize the offset and limit correctly
      if stmt = IBM_DB.prepare(@adapter.connection, sql, 
                {IBM_DB::SQL_ATTR_CURSOR_TYPE => IBM_DB::SQL_CURSOR_STATIC})
        stmt   # Return the statement object
      else
        raise StatementInvalid, IBM_DB.getErrormsg(@adapter.connection, IBM_DB::DB_CONN )
      end
    else
      if stmt = IBM_DB.prepare(@adapter.connection, sql)
        stmt   # Return the statement object
      else
        raise StatementInvalid, IBM_DB.getErrormsg(@adapter.connection, IBM_DB::DB_CONN )
      end
    end
  rescue StandardError => prep_err
    error_msg = "Failed to prepare sql #{sql}"
    error_msg = error_msg + ": #{prep_err.message}" if !prep_err.message.empty?     
    raise error_msg
  end
end

# Praveen
def execute(sql, name = nil)
  # Check if there is a limit and/or an offset
  # If so then make sure and use a static cursor type
  begin
    if (!@offset.nil? && @offset >= 0) || (!@limit.nil? && @limit > 0)
      # Set the cursor type to static so we can later utilize the offset and limit correctly
      if stmt = IBM_DB.exec(@adapter.connection, sql, 
                {IBM_DB::SQL_ATTR_CURSOR_TYPE => IBM_DB::SQL_CURSOR_STATIC})
        stmt   # Return the statement object
      else
        raise StatementInvalid, IBM_DB.getErrormsg(@adapter.connection, IBM_DB::DB_CONN )
      end
    else
      if stmt = IBM_DB.exec(@adapter.connection, sql)
        stmt   # Return the statement object
      else
        raise StatementInvalid, IBM_DB.getErrormsg(@adapter.connection, IBM_DB::DB_CONN )
      end
    end
  rescue StandardError => exec_err
    error_msg = "Failed to execute statement"
    error_msg = error_msg + ": #{exec_err.message}" if !exec_err.message.empty?     
    raise error_msg
  end
end



2510
2511
2512
2513
2514
2515
2516
2517
2518
2519
2520
2521
2522
2523
2524
2525
2526
2527
2528
2529
2530
2531
2532
2533
2534
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2510

def query_offset_limit(sql, offset, limit)
  if(offset.nil? && limit.nil?)
    return sql
  end

  if (offset.nil?)
     return sql << " FETCH FIRST #{limit} ROWS ONLY"
  end

  if(limit.nil?)
    sql.sub!(/SELECT/i,"SELECT O.* FROM (SELECT I.*, ROW_NUMBER() OVER () sys_row_num FROM (SELECT")
    return sql << ") AS I) AS O WHERE sys_row_num > #{offset}"
  end

  # Defines what will be the last record
  last_record = offset + limit
  # Transforms the SELECT query in order to retrieve/fetch only
  # a number of records after the specified offset.
  # 'select' or 'SELECT' is replaced with the partial query below that adds the sys_row_num column
  # to select with the condition of this column being between offset+1 and the offset+limit
  sql.sub!(/SELECT/i,"SELECT O.* FROM (SELECT I.*, ROW_NUMBER() OVER () sys_row_num FROM (SELECT")
  # The final part of the query is appended to include a WHERE...BETWEEN...AND condition,
  # and retrieve only a LIMIT number of records starting from the OFFSET+1
  sql << ") AS I) AS O WHERE sys_row_num BETWEEN #{offset+1} AND #{last_record}"
end

#query_offset_limit!(sql, offset, limit, options) ⇒ Object 



2536
2537
2538
2539
2540
2541
2542
2543
2544
2545
2546
2547
2548
2549
2550
2551
2552
2553
2554
2555
2556
2557
2558
2559
2560
2561
2562
2563
2564
2565
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2536

def query_offset_limit!(sql, offset, limit, options)
  if(offset.nil? && limit.nil?)
    options[:paramArray] = []
    return sql
  end

  if (offset.nil?)
     options[:paramArray] = []
     return sql << " FETCH FIRST #{limit} ROWS ONLY"
  end
    
  if(limit.nil?)
    sql.sub!(/SELECT/i,"SELECT O.* FROM (SELECT I.*, ROW_NUMBER() OVER () sys_row_num FROM (SELECT")
    sql << ") AS I) AS O WHERE sys_row_num > ?"
    options[:paramArray] = [offset]
    return 
  end

  # Defines what will be the last record
  last_record = offset + limit
  # Transforms the SELECT query in order to retrieve/fetch only
  # a number of records after the specified offset.
  # 'select' or 'SELECT' is replaced with the partial query below that adds the sys_row_num column
  # to select with the condition of this column being between offset+1 and the offset+limit
  sql.sub!(/SELECT/i,"SELECT O.* FROM (SELECT I.*, ROW_NUMBER() OVER () sys_row_num FROM (SELECT")
  # The final part of the query is appended to include a WHERE...BETWEEN...AND condition,
  # and retrieve only a LIMIT number of records starting from the OFFSET+1
  sql << ") AS I) AS O WHERE sys_row_num BETWEEN ? AND ?"
  options[:paramArray] = [offset+1, last_record]
end

#rename_column(table_name, column_name, new_column_name) ⇒ Object

Raises:

  • (NotImplementedError) 


2189
2190
2191
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2189

def rename_column(table_name, column_name, new_column_name)
  raise NotImplementedError, "rename_column is not implemented yet in the IBM_DB Adapter"
end

#set_binary_default(value) ⇒ Object

This method generates the default blob value specified for DB2 Dataservers



2569
2570
2571
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2569

def set_binary_default(value)
  "BLOB('#{value}')"
end

#set_binary_value ⇒ Object

This method generates the blob value specified for DB2 Dataservers



2574
2575
2576
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2574

def set_binary_value
  "BLOB('?')"       
end

#set_case(value) ⇒ Object

For DB2 Dataservers , the arguments to the meta-data functions need to be in upper-case



2586
2587
2588
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2586

def set_case(value)
  value.upcase
end

#set_text_default(value) ⇒ Object

This method generates the default clob value specified for DB2 Dataservers



2580
2581
2582
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2580

def set_text_default(value)
  "'#{value}'"
end