From YiPs Wiki (i powered)

XMLSERVICE: Tony Ibm Db Gem Samples

Goto Main Page
Goto Documents
Goto Ruby Page
Goto Tony Work Ruby Page

ibm_db gem is being tested with IBM i before being returned to the usual git repository.

ibm_db gem connection

I use a database.yml file for both Ruby and Rails testing as follows…

require 'active_record'
# choose config.yml and environment
if !ENV['TEST_YAML']
  ENV['TEST_YAML'] = "../database.yml"
end
if !ENV['TEST_ENV']
  ENV['TEST_ENV'] = "development"
end
# connect based on config.yml
ActiveRecord::Base.establish_connection(
  :adapter => 'ibm_db'
)

/path/database.yml

To skip encrypted passwords simply comment out pwd_yaml: and use password:

master: &master
  adapter: ibm_db
  database: "*LOCAL"
  username: DB2
  pwd_yaml: ../password.yml
  #password: MYPWD
development:
  schema: DB2
  <<: *master
test:
  schema: DB2
  <<: *master
production:
  ibm_i_naming: system
  ibm_i_libl: BOB QTEMP
  ibm_i_curlib: BOB
  schema: BOB
  <<: *master

/path/password.yml (encrypted password)

• xmlservice gem (interchangeable with ibm_db)
  • pwd_key = ActiveXMLService::Base.generate_key()
  • pwd_enc = ActiveXMLService::Base.generate_password(password,pwd_key)
• ibm_db gem (interchangeable with xmlservice)
  • pwd_key = ActiveRecord::Base.ibm_db_generate_key()
  • pwd_enc = ActiveRecord::Base.ibm_db_generate_password(password,pwd_key)

DB2: 
  pwd_enc: "YiYNfodSh5MGZVX7TXktEPSrnVlrAPjoyzzn48SdC/k=%0A"
FRED:
  pwd_enc: xSwNlLR8nZ7ar5C3Q+aStSR4B7AhiX/jMsgsVFcflnE=%0A
SALLY:
  pwd_enc: 5+legQzEhgPZ01fwBtZrdljVEtvYfcPGOlPORTnx3Dw=%0A

/path/key.yml

pwd_key: "dHKTuwxppje7zyl0cMRRQA==%0ADwtA7"

ibm_db gem samples

A few samples to start your journey …

table (ibm_db driver)

require "./sample_000_authorization"
@conn = ActiveRecord::Base.connection.connection

sql = 'DROP TABLE ANIMALS'
puts "IBM_DB::exec(#{sql})"
stmt = IBM_DB::exec(@conn,sql)
if !stmt
  puts IBM_DB::getErrorstate(@conn,IBM_DB::DB_CONN)
  puts IBM_DB::getErrormsg(@conn,IBM_DB::DB_CONN)
end

sql = 'CREATE TABLE ANIMALS (ID INTEGER, BREED VARCHAR(32), NAME CHAR(16), WEIGHT DECIMAL(7,2))'
puts "IBM_DB::exec(#{sql})"
stmt = IBM_DB::exec(@conn,sql)
if !stmt
  puts IBM_DB::getErrorstate(@conn,IBM_DB::DB_CONN)
  puts IBM_DB::getErrormsg(@conn,IBM_DB::DB_CONN)
end

sql = 'INSERT INTO ANIMALS (ID, BREED, NAME, WEIGHT) VALUES (?, ?, ?, ?)'
puts "IBM_DB::prepare(#{sql})"
stmt = IBM_DB::prepare(@conn,sql)
if !stmt
  puts IBM_DB::getErrorstate(@conn,IBM_DB::DB_CONN)
  puts IBM_DB::getErrormsg(@conn,IBM_DB::DB_CONN)
end
$animals = 
[
  [0, 'cat',        'Pook',         3.2],
  [1, 'dog',        'Peaches',      12.3],
  [2, 'horse',      'Smarty',       350.0],
  [3, 'gold fish',  'Bubbles',      0.1],
  [4, 'budgerigar', 'Gizmo',        0.2],
  [5, 'goat',       'Rickety Ride', 9.7],
  [6, 'llama',      'Sweater',      150]
]
$animals.each { |a|
  puts "IBM_DB::execute(#{a})"
  ret = IBM_DB::execute(stmt,a)
  if !ret
    puts IBM_DB::getErrorstate(stmt,IBM_DB::DB_STMT)
    puts IBM_DB::getErrormsg(stmt,IBM_DB::DB_STMT)
  end
}

sql = 'select * from ANIMALS'
puts "IBM_DB::exec(#{sql})"
stmt = IBM_DB::exec(@conn,sql)
rpt = ""
rpt << " |%20s" % 'breed'
rpt << " |%20s" % 'name'
rpt << " |%20s" % 'weight'
puts rpt
rpt = ""
rpt << " |%20s" % '--------------------'
rpt << " |%20s" % '--------------------'
rpt << " |%20s" % '--------------------'
puts rpt
while row = IBM_DB::fetch_assoc(stmt)
  rpt = ""
  rpt << " |%20s" % row['breed']
  rpt << " |%20s" % row['name']
  rpt << " |%20s" % row['weight'].to_f.to_s
  puts rpt
end

stored procedure (ibm_db driver)

require "./sample_000_authorization"
@conn = ActiveRecord::Base.connection.connection

sql = 'DROP PROCEDURE MATCH_ANIMAL'
puts "IBM_DB::exec(#{sql})"
stmt = IBM_DB::exec(@conn,sql)
if !stmt
  puts IBM_DB::getErrorstate(@conn,IBM_DB::DB_CONN)
  puts IBM_DB::getErrormsg(@conn,IBM_DB::DB_CONN)
end

sql = "CREATE PROCEDURE match_animal(IN first_name VARCHAR(128), INOUT second_name VARCHAR(128), OUT animal_weight DOUBLE) 
DYNAMIC RESULT SETS 1
LANGUAGE SQL 
BEGIN 

DECLARE match_name INT DEFAULT 0; 
DECLARE c1 CURSOR FOR SELECT COUNT(*) FROM animals WHERE name IN (second_name); 
DECLARE c2 CURSOR FOR SELECT SUM(weight) FROM animals WHERE name in (first_name, second_name); 
DECLARE c3 CURSOR WITH RETURN FOR SELECT name, breed, weight FROM animals WHERE name BETWEEN first_name AND second_name ORDER BY name; 

OPEN c1; 
FETCH c1 INTO match_name; 
IF (match_name > 0) THEN SET second_name = 'TRUE'; 
END IF; 
CLOSE c1;

OPEN c2; 
FETCH c2 INTO animal_weight; 
CLOSE c2; 

OPEN c3; 

END"
puts "IBM_DB::exec(#{sql})"
stmt = IBM_DB::exec(@conn,sql)
if !stmt
  puts IBM_DB::getErrorstate(@conn,IBM_DB::DB_CONN)
  puts IBM_DB::getErrormsg(@conn,IBM_DB::DB_CONN)
end

name1 = 'Peaches'
name2 = 'Rickety Ride'
weight = 22.22
puts "Input:"
puts "first_name = #{name1}"
puts "second_name = #{name2}"
puts "animal_weight = #{weight}"

sql = 'CALL MATCH_ANIMAL(?, ?, ?)'
puts "IBM_DB::prepare(#{sql})"
stmt = IBM_DB::prepare(@conn,sql)
if !stmt
  puts IBM_DB::getErrorstate(@conn,IBM_DB::DB_CONN)
  puts IBM_DB::getErrormsg(@conn,IBM_DB::DB_CONN)
end

ret = IBM_DB::bind_param(stmt, 1, "name1", IBM_DB::SQL_PARAM_INPUT)
if !ret
  puts IBM_DB::getErrorstate(stmt,IBM_DB::DB_STMT)
  puts IBM_DB::getErrormsg(stmt,IBM_DB::DB_STMT)
end
ret = IBM_DB::bind_param(stmt, 2, "name2", IBM_DB::SQL_PARAM_INPUT_OUTPUT)
if !ret
  puts IBM_DB::getErrorstate(stmt,IBM_DB::DB_STMT)
  puts IBM_DB::getErrormsg(stmt,IBM_DB::DB_STMT)
end
ret = IBM_DB::bind_param(stmt, 3, "weight", IBM_DB::SQL_PARAM_OUTPUT)
if !ret
  puts IBM_DB::getErrorstate(stmt,IBM_DB::DB_STMT)
  puts IBM_DB::getErrormsg(stmt,IBM_DB::DB_STMT)
end

ret = IBM_DB::execute(stmt)
if !ret
  puts IBM_DB::getErrorstate(stmt,IBM_DB::DB_STMT)
  puts IBM_DB::getErrormsg(stmt,IBM_DB::DB_STMT)
end

puts "Output:"
puts "first_name = #{name1}"
puts "second_name = #{name2}"
puts "animal_weight = #{weight}"
rpt = ""
rpt << " |%20s" % 'breed'
rpt << " |%20s" % 'name'
rpt << " |%20s" % 'weight'
puts rpt
rpt = ""
rpt << " |%20s" % '--------------------'
rpt << " |%20s" % '--------------------'
rpt << " |%20s" % '--------------------'
puts rpt
while row = IBM_DB::fetch_assoc(stmt)
  rpt = ""
  rpt << " |%20s" % row['breed']
  rpt << " |%20s" % row['name']
  rpt << " |%20s" % row['weight'].to_f.to_s
  puts rpt
end

update (ibm_db driver)

require "./sample_000_authorization"
@conn = ActiveRecord::Base.connection.connection

# autocommit off 
opts = {IBM_DB::SQL_ATTR_AUTOCOMMIT => IBM_DB::SQL_AUTOCOMMIT_OFF}
IBM_DB::set_option(@conn,opts,1)

# start count
sql = 'select count(id) from ANIMALS where id = 6'
puts "IBM_DB::exec(#{sql})"
stmt = IBM_DB::exec(@conn,sql)
if !stmt
  puts IBM_DB::getErrorstate(@conn,IBM_DB::DB_CONN)
  puts IBM_DB::getErrormsg(@conn,IBM_DB::DB_CONN)
end
row = IBM_DB::fetch_array(stmt)
puts "start count #{row[0]}"

# update animal(s) matching criteria
sql = 'UPDATE animals SET id = 6 where id < 6'
puts "IBM_DB::exec(#{sql})"
stmt = IBM_DB::exec(@conn,sql)
if !stmt
  puts IBM_DB::getErrorstate(@conn,IBM_DB::DB_CONN)
  puts IBM_DB::getErrormsg(@conn,IBM_DB::DB_CONN)
end
nbr = IBM_DB::num_rows(stmt)
puts "update count #{nbr}"

# end count
sql = 'select count(id) from ANIMALS where id = 6'
puts "IBM_DB::exec(#{sql})"
stmt = IBM_DB::exec(@conn,sql)
if !stmt
  puts IBM_DB::getErrorstate(@conn,IBM_DB::DB_CONN)
  puts IBM_DB::getErrormsg(@conn,IBM_DB::DB_CONN)
end
row = IBM_DB::fetch_array(stmt)
puts "select count #{row[0]}"

# rollback to restore original data
puts "IBM_DB::rollback"
IBM_DB::rollback(@conn)

# end count
sql = 'select count(id) from ANIMALS where id = 6'
puts "IBM_DB::exec(#{sql})"
stmt = IBM_DB::exec(@conn,sql)
if !stmt
  puts IBM_DB::getErrorstate(@conn,IBM_DB::DB_CONN)
  puts IBM_DB::getErrormsg(@conn,IBM_DB::DB_CONN)
end
row = IBM_DB::fetch_array(stmt)
puts "rollback count #{row[0]}"

delete (ibm_db driver)

require "./sample_000_authorization"
@conn = ActiveRecord::Base.connection.connection

# autocommit off 
opts = {IBM_DB::SQL_ATTR_AUTOCOMMIT => IBM_DB::SQL_AUTOCOMMIT_OFF}
IBM_DB::set_option(@conn,opts,1)

# start count
sql = 'select count(id) from ANIMALS where id < 6'
puts "IBM_DB::exec(#{sql})"
stmt = IBM_DB::exec(@conn,sql)
if !stmt
  puts IBM_DB::getErrorstate(@conn,IBM_DB::DB_CONN)
  puts IBM_DB::getErrormsg(@conn,IBM_DB::DB_CONN)
end
row = IBM_DB::fetch_array(stmt)
puts "start count #{row[0]}"

# update animal(s) matching criteria
sql = 'DELETE FROM animals WHERE id < 6'
puts "IBM_DB::exec(#{sql})"
stmt = IBM_DB::exec(@conn,sql)
if !stmt
  puts IBM_DB::getErrorstate(@conn,IBM_DB::DB_CONN)
  puts IBM_DB::getErrormsg(@conn,IBM_DB::DB_CONN)
end
nbr = IBM_DB::num_rows(stmt)
puts "delete count #{nbr}"

# end count
sql = 'select count(id) from ANIMALS where id < 6'
puts "IBM_DB::exec(#{sql})"
stmt = IBM_DB::exec(@conn,sql)
if !stmt
  puts IBM_DB::getErrorstate(@conn,IBM_DB::DB_CONN)
  puts IBM_DB::getErrormsg(@conn,IBM_DB::DB_CONN)
end
row = IBM_DB::fetch_array(stmt)
puts "select count #{row[0]}"

# rollback to restore original data
puts "IBM_DB::rollback"
IBM_DB::rollback(@conn)

# end count
sql = 'select count(id) from ANIMALS where id < 6'
puts "IBM_DB::exec(#{sql})"
stmt = IBM_DB::exec(@conn,sql)
if !stmt
  puts IBM_DB::getErrorstate(@conn,IBM_DB::DB_CONN)
  puts IBM_DB::getErrormsg(@conn,IBM_DB::DB_CONN)
end
row = IBM_DB::fetch_array(stmt)
puts "rollback count #{row[0]}"

Rails 4 (ibm_db adapter)

require 'active_record'

class AnimalActiveRecord < ActiveRecord::Base
  self.table_name = "ANIMALS"
end


  def test_0010_select_fetch_all
    # rails 3 syntax:
    # animals = AnimalActiveRecord.find(:all, :order => 'ID')

    # rails 4 syntax:
    all = Array.new
    AnimalActiveRecord.order('id').each { |row|
      all << [ 
               row.id, 
               row.breed, 
               row.name, 
               row.weight 
             ]
    }
  end

  def test_0020_select_fetch_below_10
    # rails 3 syntax:
    # animals = AnimalActiveRecord.find(:all, :order => 'ID', :conditions => "weight < 10.0")

    # rails 4 syntax:
    all = Array.new
    AnimalActiveRecord.order('id').where("weight < 10.0").each { |row|
      all << [ 
               row.id, 
               row.breed, 
               row.name, 
               row.weight 
             ]
    }
  end

  def test_0030_select_fetch_above_300
    # rails 3 syntax:
    # animals = AnimalActiveRecord.find(:all, :order => 'ID', :conditions => "weight > 300.0")

    # rails 4 syntax:
    all = Array.new
    AnimalActiveRecord.order('id').where("weight > 300.0").each { |row|
      all << [ 
               row.id, 
               row.breed, 
               row.name, 
               row.weight 
             ]
    }
  end

  def test_0040_select_fetch_equal_0
    # rails 3 syntax:
    # animals = AnimalActiveRecord.find(:all, :order => 'ID', :conditions => "id = 0")

    # rails 4 syntax:
    all = Array.new
    AnimalActiveRecord.order('id').where("id = 0").each { |row|
      all << [ 
               row.id, 
               row.breed, 
               row.name, 
               row.weight 
             ]
    }
  end

  def test_0110_select_fetch_id_name
    # rails 3 syntax:
    # animals = AnimalActiveRecord.find(:all, :order => 'ID', :select => "ID, NAME")

    # rails 4 syntax:
    all = Array.new
    AnimalActiveRecord.order('id').select("id, name").each { |row|
      all << [ 
               row.id, 
               row.name 
             ]
    }
  end

  def test_0120_select_fetch_id_breed_limit_2
    # rails 3 syntax:
    # animals = AnimalActiveRecord.find(:all, :order => 'ID', :select => "ID, BREED", :limit => 2)

    # rails 4 syntax:
    all = Array.new
    AnimalActiveRecord.order('id').select("id, breed").take(2).each { |row|
      all << [ 
               row.id, 
               row.breed 
             ]
    }
  end

  def test_0130_select_fetch_id_set_below_10
    # rails 3 syntax:
    # animals = AnimalActiveRecord.find(@@row_weight_below_10)

    # rails 4 syntax:
    all = Array.new
    AnimalActiveRecord.order('id').find(@@row_weight_below_10).each { |row|
      all << [ 
               row.id, 
               row.breed, 
               row.name, 
               row.weight 
             ]
    }
  end

  def test_0140_select_fetch_id_set_above_300
    # rails 3 syntax:
    # animals = AnimalActiveRecord.find(@@row_weight_above_300)

    # rails 4 syntax:
    all = Array.new
    AnimalActiveRecord.order('id').find(@@row_weight_above_300).each { |row|
      all << [ 
               row.id, 
               row.breed, 
               row.name, 
               row.weight 
             ]
    }
  end

  def test_0150_select_fetch_id_set_equal_0
    # rails 3 syntax:
    # animals = AnimalActiveRecord.find(@@row_id_equal_0, :select => "NAME, WEIGHT")

    # rails 4 syntax:
    all = Array.new
    AnimalActiveRecord.order('id').select("name, weight").find(@@row_id_equal_0).each { |row|
      all << [ 
               row.name, 
               row.weight 
             ]
    }
  end

  def test_0180_select_fetch_id_breed_offset_3_limit_2
    # rails 3 syntax:
    # animals = AnimalActiveRecord.find(:all, :order => 'ID', :select => "ID, BREED", :offset => 3, :limit => 2)

    # rails 4 syntax:
    all = Array.new
    AnimalActiveRecord.order('id').select("id, breed").offset(3).take(2).each { |row|
      all << [ 
               row.id, 
               row.breed 
             ]
    }
  end