CS 430/630 Homework 5 JDBC with transactions, PL/SQL

Homework 5 due Sunday midnight April 30 in your cs630/hw5 directory.

If you have trouble with your Oracle account being locked up (esp. when using transactions), use "ps" on topcat to see if you have sessions you can kill (kill -9 pid), and/or reboot your development machine to kill off clients from there.

1. For FindPilots1.java, process the flights table in departs order, and find a pilot (employee) for each, and an airplane, such that the pilot is certified for the aircraft, and the aircraft has cruisingrange above the distance of the flight, and neither the pilot (eid) nor the airplane (aid) has yet been assigned to a flight. Find the lowest-salary such pilot by using order by and then using the first row returned, as in homework 4. Again we are assuming any employee and any aircraft can be at the origin airport without delay. If your algorithm finds a pilot, insert a new row in assigned_flights, with flno, eid, and aid.  If no pilot is found, insert a row in delayed_flights, with just flno. As in homework 4, do as much computation as possible in the database. Note that you need to modify the query you used in homework 4 to use the assigned_flights table to eliminate pilots and aircraft already assigned. Use the main method to scan the flights table (or use a method for this), and provide a method assignFlight(Connection connection, int flno, int distance) to handle the work for a certain flight.

Create file createairdb.sql to create empty assigned_flights and delayed_flights tables, and dropairdb.sql to drop them. You can test this by executing dropairdb.sql, createairdb.sql, FindPilots1, then displaying tables assigned_flights and delayed_flights. For final runs, use the script developed in problem 3.

2. Once you have FindPilots1.java working, turn each separate flight determination (i.e., execution of assignFlight) into a transaction for FindPilots2.java. Put the commit after the call to assignFlight has returned successfully, so that the commit and rollback are visible in the same function's code. It needs to be clear that every transaction finishes with commit or rollback. Recall that it is important to keep each transaction short, so this is a good plan. Although we would like to use SERIALIZABLE isolation here, our server can't handle it under load, so just use default isolation (READ COMMITTED). Note that some databases might close the ResultSet from the table scan in main at the first COMMIT in assignFlight, but Oracle does not, so we are allowed to next() in main after committing an individual transaction in assignFlight. If interested, try it out on mysql to see if it also allows cursors to work through commits.

PL/SQL

Don't save data in varrays or nested tables (these are PL/SQL memory structures described in Murach, Chap. 13). The point here is to use the database as the data manager. Since PL/SQL is Oracle-specific, we are not trying to support mysql here.

3. [4/24: fixed table name assigned_flights] Write a Sqlplus script report.sql that shows "select * from assigned_flights order by flno" and "select * from delayed_flights" and then a unified report (using PL/SQL) that lists the flno, distance, and then aid, eid for flights that have assigned pilots, but only the string "(delayed)" for delayed flights, all in order of departure time,  like this:

Flight  Distance        Aid    Eid
7789           319         7        574489457
...
     7          7489        (delayed)

Because the display format depends on the case, you need use a PL/SQL cursor over the query to provide this output.

4. Convert your FindPilots1.java to use PL/SQL for the code of AssignFlight(), by calling the stored procedure assign_pilot as shown in the provided sample code in /data/htdocs/cs630/hw5files/FindPilotsSP0.java. Stub code for the needed stored procedure is provided in hw5files/createSP0.sql. Name the Java program FindPilotsSP1.java and the PL/SQL stored procedure createSP1.sql. We don't actually need the ability to return a value from PL/SQL to Java for this program, but the sample shows you how to do it.

To debug assign_pilot, call it directly from SQLPlus as shown below, where you can see the output from dbms_output.put_line(). The output shows after the stored procedure completes.
SQL> set serveroutput on;
SQL> var eid number;  -- define bind variable eid
SQL> call assign_pilot(10, 1000, :eid);
hi from SP assign_pilot, param_flno = 10

Call completed.
SQL> print eid;    -- display returned value (should be 18 from supplied sample)

       EID
----------
        18

This sequence will work with the provided stub procedure, but no rows will be inserted into assigned_flights or delayed_flights. Your createSP1.sql will do the insertions properly as well chose the right eid.

As noted in createSP0.sql, our plan is to let the PL/SQL code throw up to the Java code in cases of error. This follows our general pattern of letting the lower-level database code throw up to the higher-level code, which is better prepared to know what to do on a failure.

5. Turn the code of assignFlight of FindPilotsSP1.java into a transaction, for FindPilotsSP2.java. It will use the same stored procedure as FindPilotsSP1. We could do the transaction in PL/SQL, but the fact that exceptions are being thrown back to Java code means we should do it in the Java caller, so all cases are covered in one code. It is very important to do a rollback if there is an exception, for otherwise transactions can linger in the system and lock up your account.

Files to deliver in cs630/hw5:

README: explanations to grader, if any.

report.sql--from problem 3
FindPilots1.java--prompt for Oracle username and password, host, port, sid as in JdbcCheckup
createairdb.sql, dropairdb.sql
FindPilots1.txt--typescript of run of dropairdb, createairdb, compile and run FindPilots1, then report.sql
createSP1.sql--create stored procedure for FindPilotsSP1.java and FindPilotsSP2.java
FindPilotsSP1.java--prompt for Oracle username and password, host, port, sid 
FindPilots1SP.txt: typescript of a run: execute dropairdb.sql, then createairdb.sql, then createSP1.sql, compile and run FindPilots1SP, then report.sql.
FindPilot2SP.java--prompt for Oracle username and password, host, port, sid. 
FindPilot2SP.txt: execute dropairdb.sql, and so on as for FindPilotsSP1.