class TestIbmDb2 < Test::Unit::TestCase

  def test_200
    assert_expect {
      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
         FROM animals
         WHERE id < 4
         ORDER BY name DESC
        DECLARE c3 CURSOR WITH RETURN FOR
         SELECT name, id
         FROM animals
         WHERE weight < 5.0
         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
      
       puts "Fetching fourth result set (should fail)"
       res3 = DB2::next_result stmt
       if res3
        while (row = DB2::fetch_array(res3))
         var_dump(row)
        end
       end
      
       DB2::close conn
      else
        print "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(2) {
  [0]=>
  string(16) "Smarty          "
  [1]=>
  int(2)
}
array(2) {
  [0]=>
  string(16) "Pook            "
  [1]=>
  int(0)
}
array(2) {
  [0]=>
  string(16) "Peaches         "
  [1]=>
  int(1)
}
array(2) {
  [0]=>
  string(16) "Bubbles         "
  [1]=>
  int(3)
}
Fetching third 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) "Pook            "
  [1]=>
  int(0)
}
Fetching fourth result set (should fail)