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

class TestIbmDb2 < Test::Unit::TestCase

  def test_200
    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
         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()')
       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
      
       puts "Fetching fourth result set (should fail)\n"
       res3 = DB2::next_result(stmt)
       if res3
        while (row = DB2::fetch_{res3})
         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(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)