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, #columns, #create_database, #create_index_after_table, #drop_database, #execute, #indexes, #limit_not_supported_types, #prepare, #remove_column, #reorg_table, #select, #select_rows, #set_schema, #setup_for_lob_table, #task_charset, #task_collation, #task_create, #task_drop, #task_purge, #task_structure_dump, #task_structure_load

Constructor Details

#initialize(adapter, ar3) ⇒ IBM_DB2

Returns a new instance of IBM_DB2.



2588
2589
2590
2591
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2588

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

Instance Method Details

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



2647
2648
2649
2650
2651
2652
2653
2654
2655
2656
2657
2658
2659
2660
2661
2662
2663
2664
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2647

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



2667
2668
2669
2670
2671
2672
2673
2674
2675
2676
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2667

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



2679
2680
2681
2682
2683
2684
2685
2686
2687
2688
2689
2690
2691
2692
2693
2694
2695
2696
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2679

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



2700
2701
2702
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2700

def get_datetime_mapping
  return "timestamp"
end

#get_double_mapping ⇒ Object

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



2711
2712
2713
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2711

def get_double_mapping
  return "double"
end

#get_time_mapping ⇒ Object

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



2706
2707
2708
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2706

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



2604
2605
2606
2607
2608
2609
2610
2611
2612
2613
2614
2615
2616
2617
2618
2619
2620
2621
2622
2623
2624
2625
2626
2627
2628
2629
2630
2631
2632
2633
2634
2635
2636
2637
2638
2639
2640
2641
2642
2643
2644
2645
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2604

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 



2597
2598
2599
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2597

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



2914
2915
2916
2917
2918
2919
2920
2921
2922
2923
2924
2925
2926
2927
2928
2929
2930
2931
2932
2933
2934
2935
2936
2937
2938
2939
2940
2941
2942
2943
2944
2945
2946
2947
2948
2949
2950
2951
2952
2953
2954
2955
2956
2957
2958
2959
2960
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2914

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 self.instance_of? IBM_DB2_I5
    # T022: ORDER BY must be at the end of the SQL wrapper, with aliases, if any
    order_by = nil
    if sql =~ /( ORDER BY .*)$/
      order_by = $1
      sql = sql.gsub(order_by, '')
      order_by = order_by.gsub(/(\w*)\./, 'O.\1.')
      # replace ORDER BY column names with alias
      alias_names = Hash.new
      order_by.scan(/[UPPER|LOWER]*O\.([.A-Za-z0-9_]*)/).each do |u|
        if sql =~ /[UPPER|LOWER]?\(?\s*#{u[0]}\)?\s*AS\s(\w*)/
          alias_names[u[0]] = "#{$1}"
        end
      end
      alias_names.each {|col, aliaz| order_by = order_by.gsub(col, aliaz) }
      # no need for table name qualifier 
      order_by.gsub!(/O\.\w*\./, 'O.')
    end
  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}"
  sql << order_by if order_by && self.instance_of?(IBM_DB2_I5)
  sql
end

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



2962
2963
2964
2965
2966
2967
2968
2969
2970
2971
2972
2973
2974
2975
2976
2977
2978
2979
2980
2981
2982
2983
2984
2985
2986
2987
2988
2989
2990
2991
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2962

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) 


2593
2594
2595
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2593

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



2995
2996
2997
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2995

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

#set_binary_value ⇒ Object

This method generates the blob value specified for DB2 Dataservers



3000
3001
3002
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 3000

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



3012
3013
3014
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 3012

def set_case(value)
  value.upcase
end

#set_text_default(value) ⇒ Object

This method generates the default clob value specified for DB2 Dataservers



3006
3007
3008
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 3006

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