require 'ibm_db2'
require 'stringio'
require 'test/unit'

class TestIbmDb2 < Test::Unit::TestCase

  def test_201
    assert_equal expected, capture {
      procedure = <<<HERE
       CREATE PROCEDURE multiResults ()
       RESULT SETS 3
       LANGUAGE SQL
       BEGIN
        DECLARE c1 CURSOR WITH RETURN FOR
         SELECT name, id
         FROM animals
         ORDER BY name
      
        DECLARE c2 CURSOR WITH RETURN FOR
         SELECT name, id, breed, weight
         FROM animals
         ORDER BY name DESC
      
        DECLARE c3 CURSOR WITH RETURN FOR
         SELECT name
         FROM animals
         ORDER BY name
      
        OPEN c1
        OPEN c2
        OPEN c3
       END
      HERE
      
      conn = DB2::connect(database, user, password)
      
      if conn
       @DB2::exec(conn, 'DROP PROCEDURE multiResults()')
       DB2::exec(conn, procedure)
       stmt = DB2::exec(conn, 'CALL multiResults()')
      
       puts "Fetching first result set\n"
       while (row = DB2::fetch_{stmt})
        var_dump(row)
       end
      
       puts "Fetching second result set\n"
       res = DB2::next_result(stmt)
       if res
        while (row = DB2::fetch_{res})
         var_dump(row)
        end
       end
      
       puts "Fetching third result set\n"
       res2 = DB2::next_result(stmt)
       if res2
        while (row = DB2::fetch_{res2})
         var_dump(row)
        end
       end
      
       DB2::close(conn)
      else
        puts"Connection failed."
      end
    }
  end

end

__END__
Fetching first result set
array(2) {
  [0]=>
  string(16) "Bubbles         "
  [1]=>
  int(3)
}
array(2) {
  [0]=>
  string(16) "Gizmo           "
  [1]=>
  int(4)
}
array(2) {
  [0]=>
  string(16) "Peaches         "
  [1]=>
  int(1)
}
array(2) {
  [0]=>
  string(16) "Pook            "
  [1]=>
  int(0)
}
array(2) {
  [0]=>
  string(16) "Rickety Ride    "
  [1]=>
  int(5)
}
array(2) {
  [0]=>
  string(16) "Smarty          "
  [1]=>
  int(2)
}
array(2) {
  [0]=>
  string(16) "Sweater         "
  [1]=>
  int(6)
}
Fetching second result set
array(4) {
  [0]=>
  string(16) "Sweater         "
  [1]=>
  int(6)
  [2]=>
  string(5) "llama"
  [3]=>
  string(6) "150.00"
}
array(4) {
  [0]=>
  string(16) "Smarty          "
  [1]=>
  int(2)
  [2]=>
  string(5) "horse"
  [3]=>
  string(6) "350.00"
}
array(4) {
  [0]=>
  string(16) "Rickety Ride    "
  [1]=>
  int(5)
  [2]=>
  string(4) "goat"
  [3]=>
  string(4) "9.70"
}
array(4) {
  [0]=>
  string(16) "Pook            "
  [1]=>
  int(0)
  [2]=>
  string(3) "cat"
  [3]=>
  string(4) "3.20"
}
array(4) {
  [0]=>
  string(16) "Peaches         "
  [1]=>
  int(1)
  [2]=>
  string(3) "dog"
  [3]=>
  string(5) "12.30"
}
array(4) {
  [0]=>
  string(16) "Gizmo           "
  [1]=>
  int(4)
  [2]=>
  string(10) "budgerigar"
  [3]=>
  string(4) "0.20"
}
array(4) {
  [0]=>
  string(16) "Bubbles         "
  [1]=>
  int(3)
  [2]=>
  string(9) "gold fish"
  [3]=>
  string(4) "0.10"
}
Fetching third result set
array(1) {
  [0]=>
  string(16) "Bubbles         "
}
array(1) {
  [0]=>
  string(16) "Gizmo           "
}
array(1) {
  [0]=>
  string(16) "Peaches         "
}
array(1) {
  [0]=>
  string(16) "Pook            "
}
array(1) {
  [0]=>
  string(16) "Rickety Ride    "
}
array(1) {
  [0]=>
  string(16) "Smarty          "
}
array(1) {
  [0]=>
  string(16) "Sweater         "
}