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, #limit_not_supported_types, #remove_column, #reorg_table, #set_schema, #setup_for_lob_table

Constructor Details

#initialize(adapter) ⇒ IBM_DB2

Returns a new instance of IBM_DB2.



1839
1840
1841
1842
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 1839

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

Instance Method Details

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



1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
1910
1911
1912
1913
1914
1915
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 1898

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



1918
1919
1920
1921
1922
1923
1924
1925
1926
1927
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 1918

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



1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
1946
1947
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 1930

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

#execute(sql, name = nil) ⇒ Object

Praveen



2134
2135
2136
2137
2138
2139
2140
2141
2142
2143
2144
2145
2146
2147
2148
2149
2150
2151
2152
2153
2154
2155
2156
2157
2158
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2134

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

#get_datetime_mapping ⇒ Object

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



1951
1952
1953
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 1951

def get_datetime_mapping
  return "timestamp"
end

#get_double_mapping ⇒ Object

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



1962
1963
1964
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 1962

def get_double_mapping
  return "double"
end

#get_time_mapping ⇒ Object

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



1957
1958
1959
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 1957

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



1855
1856
1857
1858
1859
1860
1861
1862
1863
1864
1865
1866
1867
1868
1869
1870
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
1881
1882
1883
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 1855

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

#prepare(sql, name = nil) ⇒ Object

Praveen



2107
2108
2109
2110
2111
2112
2113
2114
2115
2116
2117
2118
2119
2120
2121
2122
2123
2124
2125
2126
2127
2128
2129
2130
2131
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2107

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

#primary_key_definition(start_id) ⇒ Object 



1848
1849
1850
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 1848

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 



2160
2161
2162
2163
2164
2165
2166
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2160

def query_offset_limit(sql, offset, limit)
  @limit = limit
  @offset = offset
  if (offset.nil?)
     sql << " FETCH FIRST #{limit} ROWS ONLY"
  end
end

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



2168
2169
2170
2171
2172
2173
2174
2175
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2168

def query_offset_limit!(sql, offset, limit, options)
  @limit = limit
  @offset = offset
  if (offset.nil?)
     sql << " FETCH FIRST #{limit} ROWS ONLY"
  end
  options[:paramArray] = []
end

#rename_column(table_name, column_name, new_column_name) ⇒ Object

Raises:

  • (NotImplementedError) 


1844
1845
1846
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 1844

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

#select(sql, name, stmt, results) ⇒ Object

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



1969
1970
1971
1972
1973
1974
1975
1976
1977
1978
1979
1980
1981
1982
1983
1984
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 1969

def select(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_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
    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

#select_rows(sql, name, stmt, results) ⇒ Object

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



2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079
2080
2081
2082
2083
2084
2085
2086
2087
2088
2089
2090
2091
2092
2093
2094
2095
2096
2097
2098
2099
2100
2101
2102
2103
2104
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2039

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
end

#set_binary_default(value) ⇒ Object

This method generates the default blob value specified for DB2 Dataservers



2179
2180
2181
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2179

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

#set_binary_value ⇒ Object

This method generates the blob value specified for DB2 Dataservers



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

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



2196
2197
2198
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2196

def set_case(value)
  value.upcase
end

#set_text_default(value) ⇒ Object

This method generates the default clob value specified for DB2 Dataservers



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

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