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.



2338
2339
2340
2341
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2338

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

Instance Method Details

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



2397
2398
2399
2400
2401
2402
2403
2404
2405
2406
2407
2408
2409
2410
2411
2412
2413
2414
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2397

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



2417
2418
2419
2420
2421
2422
2423
2424
2425
2426
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2417

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



2429
2430
2431
2432
2433
2434
2435
2436
2437
2438
2439
2440
2441
2442
2443
2444
2445
2446
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2429

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



2450
2451
2452
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2450

def get_datetime_mapping
  return "timestamp"
end

#get_double_mapping ⇒ Object

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



2461
2462
2463
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2461

def get_double_mapping
  return "double"
end

#get_limit_offset_clauses(limit, offset) ⇒ Object 



2465
2466
2467
2468
2469
2470
2471
2472
2473
2474
2475
2476
2477
2478
2479
2480
2481
2482
2483
2484
2485
2486
2487
2488
2489
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2465

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?)
    #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 (  "
  retHash["endSegment"] = " ) AS I) AS O WHERE sys_row_num BETWEEN #{offset+1} AND #{last_record}"
  return retHash
end

#get_time_mapping ⇒ Object

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



2456
2457
2458
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2456

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



2354
2355
2356
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
2387
2388
2389
2390
2391
2392
2393
2394
2395
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2354

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 



2347
2348
2349
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2347

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 



2491
2492
2493
2494
2495
2496
2497
2498
2499
2500
2501
2502
2503
2504
2505
2506
2507
2508
2509
2510
2511
2512
2513
2514
2515
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2491

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 



2517
2518
2519
2520
2521
2522
2523
2524
2525
2526
2527
2528
2529
2530
2531
2532
2533
2534
2535
2536
2537
2538
2539
2540
2541
2542
2543
2544
2545
2546
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2517

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) 


2343
2344
2345
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2343

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



2550
2551
2552
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2550

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

#set_binary_value ⇒ Object

This method generates the blob value specified for DB2 Dataservers



2555
2556
2557
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2555

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



2567
2568
2569
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2567

def set_case(value)
  value.upcase
end

#set_text_default(value) ⇒ Object

This method generates the default clob value specified for DB2 Dataservers



2561
2562
2563
 
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 2561

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