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.



2714
2715
2716
2717
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2714

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

Instance Method Details

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



2773
2774
2775
2776
2777
2778
2779
2780
2781
2782
2783
2784
2785
2786
2787
2788
2789
2790
2791
2792
2793
2794
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2773

def change_column(table_name, column_name, type, options)
    if !options[:default].nil?
       change_column_default(table_name, column_name, options[:default])
  else
    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
end

#change_column_default(table_name, column_name, default) ⇒ Object

DB2 specific ALTER TABLE statement to add a default clause



2797
2798
2799
2800
2801
2802
2803
2804
2805
2806
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2797

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



2809
2810
2811
2812
2813
2814
2815
2816
2817
2818
2819
2820
2821
2822
2823
2824
2825
2826
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2809

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



2830
2831
2832
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2830

def get_datetime_mapping
  return "timestamp"
end

#get_double_mapping ⇒ Object

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



2841
2842
2843
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2841

def get_double_mapping
  return "double"
end

#get_limit_offset_clauses(limit, offset) ⇒ Object 



2845
2846
2847
2848
2849
2850
2851
2852
2853
2854
2855
2856
2857
2858
2859
2860
2861
2862
2863
2864
2865
2866
2867
2868
2869
2870
2871
2872
2873
2874
2875
2876
2877
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2845

def get_limit_offset_clauses(limit, offset)
  retHash = {"endSegment"=> "", "startSegment" => ""}
  if(offset.nil? && limit.nil?)
    return retHash
  end

		
  if (offset.nil?)
     retHash["endSegment"] = " FETCH FIRST #{limit} ROWS ONLY"
     return retHash
  end

  #if(limit.nil?)
		if(limit.nil?)
    #retHash["startSegment"] = "SELECT O.* FROM (SELECT I.*, ROW_NUMBER() OVER () sys_row_num FROM ( SELECT "
    retHash["startSegment"] = "SELECT O.* FROM (SELECT I.*, ROW_NUMBER() OVER () sys_row_num FROM (  "
    retHash["endSegment"] = " ) AS I) AS O WHERE sys_row_num > #{offset}"
    return retHash
  end

  # Defines what will be the last record
  last_record = offset.to_i + limit.to_i
  #retHash["startSegment"] = "SELECT O.* FROM (SELECT I.*, ROW_NUMBER() OVER () sys_row_num FROM ( SELECT "
  retHash["startSegment"] = "SELECT O.* FROM (SELECT I.*, ROW_NUMBER() OVER () sys_row_num FROM (  "
  
		if last_record < offset+1 		
			retHash["endSegment"] = " ) AS I) AS O WHERE sys_row_num BETWEEN #{last_record} AND #{offset+1}"
		else
			retHash["endSegment"] = " ) AS I) AS O WHERE sys_row_num BETWEEN #{offset+1} AND #{last_record}"
		end
				
  return retHash
end

#get_time_mapping ⇒ Object

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



2836
2837
2838
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2836

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



2730
2731
2732
2733
2734
2735
2736
2737
2738
2739
2740
2741
2742
2743
2744
2745
2746
2747
2748
2749
2750
2751
2752
2753
2754
2755
2756
2757
2758
2759
2760
2761
2762
2763
2764
2765
2766
2767
2768
2769
2770
2771
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2730

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 



2723
2724
2725
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2723

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

#query_offset_limit(sql, offset, limit) ⇒ Object 



2879
2880
2881
2882
2883
2884
2885
2886
2887
2888
2889
2890
2891
2892
2893
2894
2895
2896
2897
2898
2899
2900
2901
2902
2903
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2879

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 



2905
2906
2907
2908
2909
2910
2911
2912
2913
2914
2915
2916
2917
2918
2919
2920
2921
2922
2923
2924
2925
2926
2927
2928
2929
2930
2931
2932
2933
2934
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2905

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) 


2719
2720
2721
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2719

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



2938
2939
2940
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2938

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

#set_binary_value ⇒ Object

This method generates the blob value specified for DB2 Dataservers



2943
2944
2945
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2943

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



2955
2956
2957
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2955

def set_case(value)
  value.upcase
end

#set_text_default(value) ⇒ Object

This method generates the default clob value specified for DB2 Dataservers



2949
2950
2951
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2949

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