Tier 3 Problem 4 connection is auto committing and closing the cursor/result set before it is read

[ghost]

Describe the bug For tier 3 problem 4 the connection is auto committing the callable statement when it is executed. This causes the cursor/result set returned from the callable statement to be closed before it can be read by the code right after the query is executed.

Specifically when the callable statement is executed the following exception is thrown: org.postgresql.util.PSQLException: ERROR: cursor "<unnamed portal 1>" does not exist

To Reproduce Steps to reproduce the behavior:

1. Setup a free tier postgesql database hosted using AWS RDS.
2. Complete problems 1 and 3 for tier 3. The queries I used for problem 3 are the create table queries in the "Additional context" section below.
3. Insert mock data into the user_role and study_set tables. The queries I used for this step are the insert queries in the "Additional context" section below.
4. Complete problem 4 for tier 3. The query I used for this step is the create function query in the "Additional context" section below.
5. Run the unit test for problem 4.

Expected behavior The following exception should be thrown when the unit test for problem 4 is run and the p: org.postgresql.util.PSQLException: ERROR: cursor "<unnamed portal 1>" does not exist

Screenshots N/A

Desktop (please complete the following information):

• OS: Windows 10
• Browser Chrome
• Version

Additional context The following code is what I used to get the JUnit test to work for problem 4. I added the comment " // added this line" to the two lines I added to the unit test. I added "conn.setAutoCommit(false);" before the declaring the CallableStatment and then "conn.commit();" after the code for reading from the cursor. Note: that the issue where this unit test passes when an exception is thrown has been logged in bug/issue #51 .

    @Test
    public void test4() {
      try (Connection conn = DriverManager.getConnection(ConnectionUtil.URL, ConnectionUtil.USERNAME, ConnectionUtil.PASSWORD)) {

        conn.setAutoCommit(false); // added this line

        String sql = "{ call " + ConnectionUtil.TIER_3_PROCEDURE_NAME + "(?, ?) }";
        CallableStatement cs = conn.prepareCall(sql);
        // First parameter is set to user_id 4, since this user owns study sets
        int userId = 4;
        cs.setInt(1, userId);
        // Second parameter is the cursor
        cs.registerOutParameter(2, Types.REF_CURSOR);
        // Manually execute callable statement
        cs.execute();
        // Select OUT parameter
        ResultSet rs = (ResultSet) cs.getObject(2);
        while (rs.next()) {
            // Assert that user_id is the owner of every study set
            // Expects user_id 4 from every third column (OWNER_ID)
            assertEquals(userId, rs.getInt(3));
        }

        conn.commit(); // added this line

      } catch(SQLException e){
          e.printStackTrace();
      }
      addPoints(40);
    }

The following are the queries I used.

create table if not exists USER_ROLE (
role_id serial primary key,
name varchar unique not null
);

create table if not exists APP_USER (
user_id serial primary key,
username varchar unique not null,
password varchar not null,
first_name varchar not null,
last_name varchar not null,
role_id integer references USER_ROLE (role_id)
);

create table if not exists STUDY_SET (
study_set_id serial primary key,
name varchar unique not null,
owner_id integer references USER_ROLE (role_id)
);

create table if not exists CATEGORY (
category_id serial primary key,
name varchar unique not null
);

create table if not exists FLASHCARD (
flashcard_id serial primary key,
question varchar unique not null,
answer varchar not null,
category_id integer references CATEGORY (category_id)
);

create table if not exists STUDY_SET_CARD (
study_set_id integer references STUDY_SET (study_set_id),
flashcard_id integer references FLASHCARD (flashcard_id),
primary key (study_set_id, flashcard_id)
);

insert into user_role (name) values ('Bob'), ('Billy'), ('John'), ('Bobby');
insert into study_set (name, owner_id) values ('study set 1', 4), ('study set 2', 4), ('study set 3', 4), ('study set 4', 1), ('study set 5', 1);

create or replace function my_function(in integer, my_cursor out refcursor) returns refcursor
as $$
    begin
        open my_cursor for select * from study_set where owner_id = $1;
    end;
$$ language plpgsql;