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:
The program uses "masterfrm" as the general-level master entry, in which data can only be retrieved and browsed. It uses "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 form "studentfrm" (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 will be written back to the database table (based on the unique student id), and to the table field's data set. This process can be repeated for different professor names.
Also for readability, the BASIC exclamation point (!) is used as an end-of-line comment indicator.
Sample Application
10 !
! Program: Professor_Student
! Purpose: Main entry point into "Professor-Student"
! mixed-form master detail application.
!
exec sql include sqlca
exec sql declare student table &
(sname char(25), &
sage integer1, &
sbdate char(25), &
sgpa float4, &
sidno integer, &
scomment varchar(200), &
sadvisor char(25))
exec sql declare professor table &
(pname char(25), &
pdept char(10))
exec sql begin declare section
! Externally compiled master and student form
external integer masterfrm, studentfrm
exec sql end declare section
! 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
call Master
exec frs clear screen
exec frs endforms
exec sql disconnect
end ! of Professor_Student
!
! Subroutine: Master
! Purpose: Drive the application, by running 'masterfrm', and
! allowing the user to 'zoom' into a selected student.
! Parameters: None - Uses the global student 'grad' record.
!
100 sub Master
exec sql include sqlca
exec sql begin declare section
! Global grad student record maps to database table
record grad_student
string sname = 25
word sage
string sbdate = 25
real sgpa
integer sidno
string scomment = 200
string sadvisor = 25
end record
common (grad_area) grad_student grad
! Professor info maps to database table
record professor
string pname = 25
string pdept = 10
end record
declare professor prof
! Useful forms system information
declare integer lastrow ! Lastrow in table field
declare integer istable ! Is a table field?
! Local utility buffers
declare string msgbuf ! Message buffer
declare string respbuf ! Response buffer
declare string old_advisor ! Old advisor before Zoom
exec sql end declare section
external byte function Student_Info_changed
! Function defined below
declare string tmp_pname ! Temporary string param
!
! 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))
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 = '') then
exec frs resume field pname
end if
!
! Verify that the professor exists. Local error
! handling just prints the message, and continues.
! We assume that each professor has exactly one
! department.
!
exec sql whenever sqlerror call sqlprint
exec sql whenever not found continue
prof::pdept = ' '
exec sql select pdept &
into :prof::pdept &
from professor &
where pname = :prof::pname
if (prof::pdept = '') then
msgbuf = '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)
exec frs redisplay ! Refresh for query
tmp_pname = prof::pname
call Load_Students(tmp_pname)
exec frs resume field studenttbl
exec frs end ! 'Students'
exec frs activate menuitem 'Zoom'
exec frs begin
!
! Confirm that user is on "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 global 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 this row from the
! display.
!
old_advisor = grad::sadvisor
if (Student_Info_Changed = 1) 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 sub ! Master
!
! Subroutine: Load_Students
! Purpose: Given an advisor name, load into the 'studenttbl'
! table field all the students who report to the
! professor with that name.
! Parameters:
! advisor - User specified professor name.
! Uses the global student record.
!
200 sub Load_Students(string tmp_advisor)
exec sql include sqlca
exec sql begin declare section
declare string advisor
exec sql end declare section
!
! Global grad student - do not redeclare the structure as it
! was declared in subroutine "Master"
!
record grad_student
string sname = 25
word sage
string sbdate = 25
real sgpa
integer sidno
string scomment = 200
string sadvisor = 25
end record
common (grad_area) grad_student grad
exec sql declare studentcsr cursor for &
select sname, sage, sbdate, sgpa, &
sidno, scomment, sadvisor &
from student &
where sadvisor = :advisor
! Move string parameter into variable known by preprocessor
advisor = tmp_advisor
!
! 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 sql whenever sqlerror goto EndLoad ! End loading
exec sql whenever not found goto EndLoad
exec sql open studentcsr
!
! Before we start the loop we know that the OPEN was
! successful and that NOT FOUND was not set.
!
while (sqlcode = 0)
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)
next
! Clean up on an error, and close cursors
exec sql whenever not found continue
exec sql whenever sqlerror continue
EndLoad: exec sql close studentcsr
end sub ! Load_Students
!
! Function: Student_Info_Changed
! Purpose: Allow the user to zoom into the details of
! a selected student. Some of the data can be
! updated by the user.If any updates were made,
! then reflect these back into the database table.
! The procedure returns TRUE if any changes were made.
! Parameters: None - Uses with data in the global "grad" record.
! Returns: TRUE/FALSE - Changes were made to the database.
! Sets the global "grad" record with the new data.
!
300 function byte Student_Info_Changed
exec sql include sqlca
exec sql begin declare section
declare integer changed ! Changes made to data in form
declare integer valid_advisor ! Valid advisor name ?
exec sql end declare section
!
! Global grad student - do not redeclare the structure as it
! was declared in subroutine "Master"
!
record grad_student
string sname = 25
word sage
string sbdate = 25
real sgpa
integer sidno
string scomment = 200
string sadvisor = 25
end record
common (grad_area) grad_student grad
! Local error handle just prints error, and continues
exec sql whenever sqlerror call sqlprint
exec sql whenever not found continue
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 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
end if
exec frs message 'Writing changes to database. . .'
exec sql update student set &
sgpa = :grad::sgpa, &
scomment = :grad::scomment, &
sadvisor = :grad::sadvisor &
where sidno = :grad::sidno
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
Student_Info_Changed = changed
end function ! Student_Info_Changed