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

Constructor Details

This class inherits a constructor from ActiveRecord::ConnectionAdapters::IBM_DataServer

Instance Method Details

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



1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 1576

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



1596
1597
1598
1599
1600
1601
1602
1603
1604
1605
1606
1607
1608
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 1596

def change_column_default(table_name, column_name, default)
  # SQL statement which alters column's default value
  if default.nil?
    change_column_sql = "ALTER TABLE #{table_name} ALTER #{column_name} DROP DEFAULT"   
  else
    change_column_sql = "ALTER TABLE #{table_name} ALTER COLUMN #{column_name} \
SET WITH DEFAULT #{@adapter.quote(default)}"
  end
  stmt = execute(change_column_sql)
  reorg_table(table_name)
  ensure
    IBM_DB.free_result 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



1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 1611

def change_column_null(table_name, column_name, null, default)
  if !default.nil?
    change_column_default(table_name, column_name, default)
  end 
  #reorg_table(table_name)
  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     
  end
  stmt = execute(change_column_sql)
  reorg_table(table_name)
  ensure
    IBM_DB.free_result stmt if stmt   
end

#execute(sql, name = nil) ⇒ Object 



1784
1785
1786
1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 1784

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
  if (!@offset.nil? && @offset >= 0) || (!@limit.nil? && @limit > 0)
    begin
      # 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.stmt_errormsg
      end
    rescue StandardError
      error_msg = IBM_DB.conn_errormsg
      if error_msg && !error_msg.empty?
        raise "Failed to execute statement due to communication error: #{error_msg}"
      else
        raise
      end
    end
  else
    begin
      if stmt = IBM_DB.exec(@adapter.connection, sql)
        stmt   # Return the statement object
      else
        raise StatementInvalid, IBM_DB.stmt_errormsg
      end
    rescue StandardError
      error_msg = IBM_DB.conn_errormsg
      if error_msg && !error_msg.empty?
        raise "Failed to execute statement due to communication error: #{error_msg}"
      else
        raise
      end
    end
  end
end

#get_datetime_mapping ⇒ Object

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



1631
1632
1633
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 1631

def get_datetime_mapping
  return "timestamp"
end

#get_double_mapping ⇒ Object

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



1642
1643
1644
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 1642

def get_double_mapping
  return "double"
end

#get_time_mapping ⇒ Object

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



1637
1638
1639
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 1637

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



1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 1545

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"
  if stmt = IBM_DB.exec(@adapter.connection, sql)
    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  # Handle driver fetch errors
      error_msg = IBM_DB.conn_errormsg 
      error_msg = IBM_DB.stmt_errormsg if error_msg.empty?
      if error_msg && !error_msg.empty?
        raise "Failed to retrieve last generated id: #{error_msg}"
      else
        raise "An unexpected error occurred during retrieval of last generated id"
      end
    ensure  # Free resources associated with the statement
      IBM_DB.free_result(stmt) if stmt
    end
  else
    error_msg = IBM_DB.conn_errormsg 
    error_msg = IBM_DB.stmt_errormsg if error_msg.empty?
    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 



1538
1539
1540
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 1538

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 



1822
1823
1824
1825
1826
1827
1828
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 1822

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

#rename_column(table_name, column_name, new_column_name) ⇒ Object

Raises:

  • (NotImplementedError) 


1534
1535
1536
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 1534

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



1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 1649

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  # Handle driver fetch errors
    error_msg = IBM_DB.conn_errormsg 
    error_msg = IBM_DB.stmt_errormsg if error_msg.empty?
    if error_msg && !error_msg.empty?
      raise StatementInvalid,"Failed to retrieve data: #{error_msg}"
    else
      raise "An unexpected error occurred during data retrieval"
    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



1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
1782
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 1718

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  # Handle driver fetch errors
    error_msg = IBM_DB.conn_errormsg 
    error_msg = IBM_DB.stmt_errormsg if error_msg.empty?
    if error_msg && !error_msg.empty?
      raise StatementInvalid,"Failed to retrieve data: #{error_msg}"
    else
      raise "An unexpected error occurred during data retrieval"
    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



1832
1833
1834
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 1832

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

#set_binary_value ⇒ Object

This method generates the blob value specified for DB2 Dataservers



1837
1838
1839
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 1837

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



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

def set_case(value)
  value.upcase
end

#set_text_default(value) ⇒ Object

This method generates the default clob value specified for DB2 Dataservers



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

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