Class: ActiveRecord::ConnectionAdapters::IBM_DB2
- Inherits:
-
IBM_DataServer
- Object
- IBM_DataServer
- ActiveRecord::ConnectionAdapters::IBM_DB2
- Defined in:
- lib/active_record/connection_adapters/ibm_db_adapter.rb
Overview
class IBM_DataServer
Direct Known Subclasses
Instance Method Summary collapse
- #change_column(table_name, column_name, type, options) ⇒ Object
-
#change_column_default(table_name, column_name, default) ⇒ Object
DB2 specific ALTER TABLE statement to add a default clause.
-
#change_column_null(table_name, column_name, null, default) ⇒ Object
DB2 specific ALTER TABLE statement to change the nullability of a column.
-
#get_datetime_mapping ⇒ Object
This method returns the DB2 SQL type corresponding to the Rails datetime/timestamp type.
-
#get_double_mapping ⇒ Object
This method returns the DB2 SQL type corresponding to Rails double type.
-
#get_time_mapping ⇒ Object
This method returns the DB2 SQL type corresponding to the Rails time type.
-
#initialize(adapter) ⇒ IBM_DB2
constructor
A new instance of IBM_DB2.
-
#last_generated_id(stmt) ⇒ Object
Returns the last automatically generated ID.
- #primary_key_definition(start_id) ⇒ Object
-
#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.
- #query_offset_limit!(sql, offset, limit, options) ⇒ Object
- #rename_column(table_name, column_name, new_column_name) ⇒ Object
-
#set_binary_default(value) ⇒ Object
This method generates the default blob value specified for DB2 Dataservers.
-
#set_binary_value ⇒ Object
This method generates the blob value specified for DB2 Dataservers.
-
#set_case(value) ⇒ Object
For DB2 Dataservers , the arguments to the meta-data functions need to be in upper-case.
-
#set_text_default(value) ⇒ Object
This method generates the default clob value specified for DB2 Dataservers.
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.
2027 2028 2029 2030 |
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2027 def initialize(adapter) super(adapter) @limit = @offset = nil end |
Instance Method Details
#change_column(table_name, column_name, type, options) ⇒ Object
2086 2087 2088 2089 2090 2091 2092 2093 2094 2095 2096 2097 2098 2099 2100 2101 2102 2103 |
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2086 def change_column(table_name, column_name, type, ) data_type = @adapter.type_to_sql(type, [:limit], [:precision], [:scale]) begin execute "ALTER TABLE #{table_name} ALTER #{column_name} SET DATA TYPE #{data_type}" rescue StandardError => exec_err if exec_err..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,[:null],nil) change_column_default(table_name, column_name, [: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
2106 2107 2108 2109 2110 2111 2112 2113 2114 2115 |
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2106 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
2118 2119 2120 2121 2122 2123 2124 2125 2126 2127 2128 2129 2130 2131 2132 2133 2134 2135 |
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2118 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
2139 2140 2141 |
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2139 def get_datetime_mapping return "timestamp" end |
#get_double_mapping ⇒ Object
This method returns the DB2 SQL type corresponding to Rails double type
2150 2151 2152 |
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2150 def get_double_mapping return "double" end |
#get_time_mapping ⇒ Object
This method returns the DB2 SQL type corresponding to the Rails time type
2145 2146 2147 |
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2145 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
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 |
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2043 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.}" if !fetch_error..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
2036 2037 2038 |
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2036 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.}" if !fetch_error..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.}" if !fetch_error..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.}" if !prep_err..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.}" if !exec_err..empty?
raise error_msg
end
end
2353 2354 2355 2356 2357 2358 2359 2360 2361 2362 2363 2364 2365 2366 2367 2368 2369 2370 2371 2372 2373 2374 2375 2376 2377 |
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2353 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
2379 2380 2381 2382 2383 2384 2385 2386 2387 2388 2389 2390 2391 2392 2393 2394 2395 2396 2397 2398 2399 2400 2401 2402 2403 2404 2405 2406 2407 2408 |
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2379 def query_offset_limit!(sql, offset, limit, ) if(offset.nil? && limit.nil?) [:paramArray] = [] return sql end if (offset.nil?) [: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 > ?" [: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 ?" [:paramArray] = [offset+1, last_record] end |
#rename_column(table_name, column_name, new_column_name) ⇒ Object
2032 2033 2034 |
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2032 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
2412 2413 2414 |
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2412 def set_binary_default(value) "BLOB('#{value}')" end |
#set_binary_value ⇒ Object
This method generates the blob value specified for DB2 Dataservers
2417 2418 2419 |
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2417 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
2429 2430 2431 |
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2429 def set_case(value) value.upcase end |
#set_text_default(value) ⇒ Object
This method generates the default clob value specified for DB2 Dataservers
2423 2424 2425 |
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2423 def set_text_default(value) "'#{value}'" end |