class TestIbmDb2 < Test::Unit::TestCase

  def test_201
    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, 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__
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         "
}