class TestIbmDb2 < Test::Unit::TestCase

  def test_146
    assert_expect {
      conn = DB2::connect database, user, password
      if conn
        
      # Drop the stored procedure, in case it exists
      drop = 'DROP PROCEDURE match_animal'
      result = DB2::exec(conn, drop) rescue nil
      
      # Create the stored procedure
      result = DB2::exec(conn, "
      CREATE PROCEDURE match_animal(IN first_name VARCHAR(128), INOUT second_name VARCHAR(128), OUT animal_weight DOUBLE)
      DYNAMIC RESULT SETS 1
      MODIFIES SQL DATA
      LANGUAGE SQL
      NO EXTERNAL ACTION
      BEGIN
       DECLARE match_name INT DEFAULT 0
       DECLARE c1 CURSOR FOR
        SELECT COUNT(*) FROM animals
        WHERE name IN (second_name)
      
       DECLARE c2 CURSOR FOR
        SELECT SUM(weight) FROM animals
        WHERE name in (first_name, second_name)
       
       DECLARE c3 CURSOR WITH RETURN FOR 
        SELECT name, breed, weight FROM animals
        WHERE name BETWEEN first_name AND second_name
        ORDER BY name
      
       OPEN c1
       FETCH c1 INTO match_name
       IF (match_name > 0)
        THEN SET second_name = 'TRUE'
       END IF
       CLOSE c1
      
       OPEN c2
       FETCH c2 INTO animal_weight
       CLOSE c2
      
       OPEN c3
      END
      ")
      
      result = null
      
        sql = 'CALL match_animal(?, ?, ?)'
        stmt = DB2::prepare conn, sql
        name = "Peaches"
        second_name = "Rickety Ride"
        weight = 0
        DB2::bind_param stmt, 1, "name", DB2::PARAM_IN
        DB2::bind_param stmt, 2, "second_name", DB2::PARAM_INOUT
        DB2::bind_param stmt, 3, "weight", DB2::PARAM_OUT
        puts "Values of bound parameters _before_ CALL:"
        print "  1: #{name} 2: #{second_name} 3: #{weight}\n\n"
        if DB2::execute(stmt)
            puts "Values of bound parameters _after_ CALL:"
            print "  1: #{name} 2: #{second_name} 3: #{weight}\n\n"
            puts "Results:"
            while (row = DB2::fetch_array(stmt))
                puts "  #{trim(row[0])}, #{trim(row[1])}, #{row[2]}"
            end
        end
      end
    }
  end

end

__END__
Values of bound parameters _before_ CALL:
  1: Peaches 2: Rickety Ride 3: 0

Values of bound parameters _after_ CALL:
  1: Peaches 2: TRUE 3: 12

Results:
  Peaches, dog, 12.30
  Pook, cat, 3.20
  Rickety Ride, goat, 9.70
  Smarty, horse, 350.00
  Sweater, llama, 150.00