class TestIbmDb2 < Test::Unit::TestCase

  def test_201
    assert_expect do
      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()') rescue nil
       DB2::exec conn, procedure
       stmt = DB2::exec conn, 'CALL multiResults()'
      
       puts "Fetching first result set"
       while (row = DB2::fetch_array(stmt))
        var_dump(row)
       end
      
       puts "Fetching second result set"
       res = DB2::next_result stmt
       if res
        while (row = DB2::fetch_array(res))
         var_dump(row)
        end
       end
      
       puts "Fetching third result set"
       res2 = DB2::next_result stmt
       if res2
        while (row = DB2::fetch_array(res2))
         var_dump(row)
        end
       end
      
       DB2::close conn
      else
        print "Connection failed."
      end
    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         "
}