The Professor-Student Mixed Form Application
This application lets the user browse and update information about graduate students who report to a specific professor. The program is structured in a master/detail fashion, with the professor being the master entry, and the students the detail entries. The application uses two forms--one to contain general professor information and another for detailed student information.
The objects used in this application are shown in the following table:
The program uses the "masterfrm" as the general-level master entry, in which data can only be retrieved and browsed, and the studentfrm as the detailed screen, in which specific student information can be updated. The user can enter a name in the pname field and then select the Students menu operation. The operation fills the Studenttbl table field with detailed information of the students reporting to the named professor. This is done by the studentcsr database cursor in the Load_Students procedure. The program assumes that each professor is associated with exactly one department.
The user can then browse the table field (in read mode), which displays only the names and ages of the students. More information about a specific student can be requested by selecting the Zoom menu operation. This operation displays the studentfrm form (in update mode). The fields of studentfrm are filled with values stored in the hidden columns of "studenttbl." The user can make changes to three fields (sgpa, scomment, and sadvisor). If validated, these changes are written back to the Database table (based on the unique student id), and to the table field's data set. The user can repeat this process for different professor names.
Sample Application
-- Master and student compiled forms (imported objects)
package Compiled_Forms is
exec sql begin declare section;
masterfrm, studentfrm: Integer;
exec sql end declare section;
pragma import_object( masterfrm );
pragma import_object( studentfrm );
end Compiled_Forms;
exec sql include sqlca;
exec sql declare student table -- Graduate student table
(sname char(25), -- Name
sage integer1, -- Age
sbdate char(25), -- Birth date
sgpa float4, -- Grade point average
idno integer, -- Unique student number
scomment varchar(200), -- General comments
sadvisor char(25)); -- Advisor's name
exec sql declare professor table -- Professor table
(pname char(25), -- Professor's name
pdept char(10)); -- Department
with Compiled_Forms; use Compiled_Forms;
with Text_Io; use Text_Io;
with Integer_Text_Io; use Integer_Text_Io;
-- Procedure: Prof_Student
-- Purpose: Main body of "'Professor Student" Master-Detail
-- application.
procedure Prof_Student is
exec sql begin declare section;
-- Graduate student record maps to "student" database table
type Student_Rec is
record
sname: String(1..25);
sage: Short_Short_Integer;
sbdate: String(1..25);
sgpa: Float;
sidno: Integer;
scomment: String(1..200);
sadvisor: String(1..25);
end record;
grad: Student_Rec;
exec sql end declare section;
--
-- Procedure: Load_Students
-- Purpose: Given an advisor name, load into the "studenttbl"
-- table field all the graduate students who report
-- to the professor with that name.
-- Parameters: advisor - User-specified professor name.
-- Uses the global student record "grad."
--
procedure Load_Students( adv : in String ) is
exec sql begin declare section;
advisor : String(1..25) := adv;
exec sql end declare section;
exec sql declare studentcsr cursor for
select sname, sage, sbdate, sgpa,
sidno, scomment, sadvisor
from student
where sadvisor = :advisor;
begin
--
-- Clear previous contents of table field. Load the table
-- field from the database table based on the advisor
-- name. Columns "sname" and "sage" will be displayed,
-- and all others will be hidden.
--
exec frs message 'Retrieving Student Information . . .';
exec frs clear field studenttbl;
exec frs redisplay; -- Refresh for query
exec sql whenever sqlerror goto Load_End;
exec sql whenever not found goto Load_End;
exec sql open studentcsr;
--
-- Before we start the loop, we know that the OPEN
-- was successful and that NOT FOUND was not set.
--
while (sqlca.sqlcode = 0) loop
exec sql fetch studentcsr into :grad;
exec frs loadtable masterfrm studenttbl
(sname = :grad.sname,
sage = :grad.sage,
sbdate = :grad.sbdate,
sgpa = :grad.sgpa,
sidno = :grad.sidno,
scomment = :grad.scomment,
sadvisor = :grad.sadvisor);
end loop;
<<Load_End>> -- Clean up on an error, and close cursors
exec sql whenever not found continue;
exec sql whenever sqlerror continue;
exec sql close studentcsr;
end Load_Students;
--
-- Function: Student_Info_Changed
-- Purpose: Allow the user to zoom in on the details of a
-- selected student. Some of the data can be
-- updated by the user. If any updates are made,
-- incorporate them into the database table.
-- The procedure returns TRUE if any changes are
-- made.
-- Parameters:
-- None
-- Returns:
-- TRUE/FALSE - Changes were made to the database.
-- Sets the global "grad" record with the new data.
--
function Student_Info_Changed return Boolean is
exec sql begin declare section;
changed: Integer; -- Changes made to the form?
valid_advisor: Integer; -- Is the advisor name valid?
exec sql end declare section;
begin
-- Local error handler just prints error and continues
exec sql whenever sqlerror call sqlprint;
exec sql whenever not found continue;
-- Display the detailed student information
exec frs display studentfrm fill;
exec frs initialize
(sname = :grad.sname,
sage = :grad.sage,
sbdate = :grad.sbdate,
sgpa = :grad.sgpa,
sidno = :grad.sidno,
scomment = :grad.scomment,
sadvisor = :grad.sadvisor);
exec frs activate menuitem 'Write';
exec frs begin;
--
-- If changes were made, then update the
-- database table. Only bother with the
-- fields that are not read-only.
--
exec frs inquire_frs form (:changed = change);
if (changed = 1) then
exec frs validate;
exec frs message 'Writing changes to database. . .';
exec frs getform
(:grad.sgpa = sgpa,
:grad.scomment = scomment,
:grad.sadvisor = sadvisor);
-- Enforce integrity of professor name
valid_advisor := 0;
exec sql select 1 into :valid_advisor
from professor
where pname = :grad.sadvisor;
if (valid_advisor = 0) then
exec frs message 'Not a valid advisor name';
exec frs sleep 2;
exec frs resume field sadvisor;
else
exec sql update student set
sgpa = :grad.sgpa,
scomment = :grad.scomment,
sadvisor = :grad.sadvisor
where sidno = :grad.sidno;
end if;
end if;
exec frs breakdisplay;
exec frs end; -- 'Write'
exec frs activate menuitem 'Quit';
exec frs begin;
-- Quit without submitting changes
changed := 0;
exec frs breakdisplay;
exec frs end; -- 'Quit'
exec frs finalize;
return (changed = 1);
end Student_Info_Changed;
--
-- Procedure: Master
-- Purpose: Drive the application, by running "masterfrm"
-- and allowing the user to "zoom" in on a selected
-- student. Parameters: None - Uses the global student "grad"
-- record.
procedure Master is
exec sql begin declare section;
-- Professor record maps to "professor" database table
type Prof_Rec is
record
pname: String(1..25);
pdept: String(1..10);
end record;
prof: Prof_Rec;
-- Useful forms runtime information
lastrow, -- Lastrow in table field
istable: Integer; -- Is a table field?
-- Utility buffers
msgbuf: String(1..100); -- Message buffer
respbuf: String(1..1); -- Response buffer
old_advisor: String(1..25); -- Old advisor before ZOOM
exec sql end declare section;
begin -- Master
--
-- Initialize "studenttbl" with a data set in READ mode.
-- Declare hidden columns for all the extra fields that
-- the program will display when more information is
-- requested about a student. Columns "sname" and "sage"
-- are displayed. All other columns are hidden, to be
-- used in the student information form.
--
exec frs inittable masterfrm studenttbl read
(sbdate = char(25),
sgpa = float4,
sidno = integer,
scomment = char(200),
sadvisor = char(20));
-- Drive the application, by running "masterfrm" and
-- allowing the user to "zoom" in on a selected student.
exec frs display masterfrm update;
exec frs initialize;
exec frs begin;
exec frs message 'Enter an Advisor name . . .';
exec frs sleep 2;
exec frs end;
exec frs activate menuitem 'Students', field 'pname';
exec frs begin;
-- Load the students of the specified professor
exec frs getform (:prof.pname = pname);
-- If no professor name is given, then resume
if (prof.pname(1) = ' ') then
exec frs resume field pname;
end if;
--
-- Verify that the professor exists. If not,
-- print a message and continue. Assume that
-- each professor has exactly one department.
--
exec sql whenever sqlerror call sqlprint;
exec sql whenever not found continue;
prof.pdept := (1..10 => ' ');
exec sql select pdept
into :prof.pdept
from professor
where pname = :prof.pname;
-- If no professor, report error
if (prof.pdept(1) = ' ') then
msgbuf := (1..100 => ' ');
msgbuf(1..59) :=
"No professor with name '" &
prof.pname & "' [RETURN]";
exec frs prompt noecho (:msgbuf, :respbuf);
exec frs clear field all;
exec frs resume field pname;
end if;
-- Fill the department field and load students
exec frs putform (pdept = :prof.pdept);
Load_Students( prof.pname );
exec frs resume field studenttbl;
exec frs end; -- 'Students'
exec frs activate menuitem 'Zoom';
exec frs begin;
--
-- Confirm that user is in "studenttbl" and that
-- the table field is not empty. Collect data from
-- the row and zoom for browsing and updating.
--
exec frs inquire_frs field masterfrm
(:istable = table);
if (istable = 0) then
exec frs prompt noecho
('Select from the student table [RETURN]', :respbuf);
exec frs resume field studenttbl;
end if;
exec frs inquire_frs table masterfrm
(:lastrow = lastrow);
if (lastrow = 0) then
exec frs prompt noecho
('There are no students [RETURN]', :respbuf);
exec frs resume field pname;
end if;
-- Collect all data on student into graduate record
exec frs getrow masterfrm studenttbl
(:grad.sname = sname,
:grad.sage = sage,
:grad.sbdate = sbdate,
:grad.sgpa = sgpa,
:grad.sidno = sidno,
:grad.scomment = scomment,
:grad.sadvisor = sadvisor);
--
-- Display "studentfrm," and, if any changes were made,
-- make the updates to the local table field row.
-- Only make updates to the columns corresponding to
-- writable fields in "studentfrm." If the student
-- changed advisors, then delete the row from the
-- display.
--
old_advisor := grad.sadvisor;
if (Student_Info_Changed) then
if (old_advisor <= grad.sadvisor) then
exec frs deleterow masterfrm studenttbl;
else
exec frs putrow masterfrm studenttbl
(sgpa = :grad.sgpa,
scomment = :grad.scomment,
sadvisor = :grad.sadvisor);
end if;
end if;
exec frs end; -- 'Zoom' ;
exec frs activate menuitem 'Exit';
exec frs begin;
exec frs breakdisplay;
exec frs end; -- 'Exit' ;
exec frs finalize;
end Master;
begin -- Prof_Student
-- Start up Ingres and the FORMS system
exec frs forms;
exec sql whenever sqlerror stop;
exec frs message 'Initializing Student Administrator . . .';
exec sql connect personnel;
exec frs addform :masterfrm;
exec frs addform :studentfrm;
Master;
exec frs clear screen;
exec frs endforms;
exec sql disconnect;
end Prof_Student;