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) ⇒ IBM_DB2

Returns a new instance of IBM_DB2.



2005
2006
2007
2008
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2005

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

Instance Method Details

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



2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079
2080
2081
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2064

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



2084
2085
2086
2087
2088
2089
2090
2091
2092
2093
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2084

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



2096
2097
2098
2099
2100
2101
2102
2103
2104
2105
2106
2107
2108
2109
2110
2111
2112
2113
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2096

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



2117
2118
2119
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2117

def get_datetime_mapping
  return "timestamp"
end

#get_double_mapping ⇒ Object

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



2128
2129
2130
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2128

def get_double_mapping
  return "double"
end

#get_time_mapping ⇒ Object

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



2123
2124
2125
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2123

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



2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2021

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 



2014
2015
2016
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2014

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



2331
2332
2333
2334
2335
2336
2337
2338
2339
2340
2341
2342
2343
2344
2345
2346
2347
2348
2349
2350
2351
2352
2353
2354
2355
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2331

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 



2357
2358
2359
2360
2361
2362
2363
2364
2365
2366
2367
2368
2369
2370
2371
2372
2373
2374
2375
2376
2377
2378
2379
2380
2381
2382
2383
2384
2385
2386
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2357

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) 


2010
2011
2012
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2010

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



2390
2391
2392
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2390

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

#set_binary_value ⇒ Object

This method generates the blob value specified for DB2 Dataservers



2395
2396
2397
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2395

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



2407
2408
2409
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2407

def set_case(value)
  value.upcase
end

#set_text_default(value) ⇒ Object

This method generates the default clob value specified for DB2 Dataservers



2401
2402
2403
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2401

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