4. Embedded QUEL for Fortran : Sample Applications : UNIX and VMS--The Professor-Student Mixed Form Application
 
Share this page                  
UNIX and VMS--The Professor-Student Mixed Form Application
This EQUEL/FORMS 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 application uses the following objects:
Object
Description
personnel
The program's database environment.
professor
A database table with two columns:
pname (c25)
pdept (c10)
See its create statement below for a full description.
student
A database table with seven columns:
sname (c25)
sage (i1)
sbdate (c25)
sgpa (f4)
sidno (i1)
scomment (text(200))
sadvisor (c25)
See the create statement below for a full description. The sadvisor column is the join field with the pname column in the Professor table.
masterfrm
The main form has fields pname and pdept, which correspond to the information in the Professor table and the Studenttbl table field. The pdept field is display-only. "Masterfrm" is a compiled form.
studenttbl
A table field in "masterfrm" with two columns, "sname" and "sage." When initialized, it also has five more hidden columns corresponding to information in the Student table.
studentfrm
The detail form, with seven fields, which correspond to information in the Student table. Only the Sgpa, Scomment, and Sadvisor fields are updatable. "Studentfrm" is a compiled form.
grad
A global structure, whose members correspond in name and type to the columns of the Student database table, the "studentfrm" form and the Studenttbl table field.
The program uses the "masterfrm" as the general-level master entry, in which the user can only retrieve and browse data, and the "studentfrm" as the detailed screen, in which the user can update specific student information.
The user enters a name in the pname (professor name) field and then selects the Students menu operation. The operation fills the displayed and hidden columns of the studenttbl table field with detailed information about the students of the named professor. The user may then browse the table field (in read mode), which displays only the names and ages of the students. To request more information about a specific student, the user can select the Zoom menu operation. This operation displays the form "studentfrm." 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.
The application runs on UNIX and VMS.
UNIX: The following two create statements describe the Professor and Student database tables:
##  create student            /* Graduate student table */
##    (sname     = c25,       /* Name */
##     sage      = i1,        /* Age */
##     sbdate    = c25,       /* Birth date */
##     sgpa      = f4,        /* Grade point average */
##     sidno     = i4,        /* Unique student number */
##     scomment  = text(200), /* General comments */
##     sadvisor  = c25)       /* Advisor's name */

##  create professor          /* Professor table */
##    (pname     = c25,       /* Professor's name */   
##     pdept     = c10)       /* Department */


C    Procedure:      MAIN
C    Purpose:        Start up program and call Master driver.

##   program main

C    Start up Ingres and the FORMS system
##   declare forms

##   forms
##   message 'Initializing Student Administrator . . .'

##   INGRES personnel
##   range of p IS professor, s IS student

     call Master()

##   clear screen
##   endforms

##   exit

##   end
C
C    Procedure:  Master
C    Purpose:    Drive the application, by running 'mstfrm', and
C                allowing the user to 'zoom' into a
C                selected student.
C    Parameters:
C                None - Uses the global student 'grad' record.
C

##   subroutine Master()
##   declare forms

C        Declare function
     logical StdChg

C    grad student record maps to database table
C           Student's name
##   character*25     Sname
C           Student's age
##   integer*2        Sage
C           Student's birthday
##   character*25     Sbdate
C           Student's grade point average
##   real*4           Sgpa
C           Student's unique id number
##   integer*4        Sidno
C           General comment field
##   character*200    Scomm
C           Student's advisor
##   character*25     Sadv

C    Professor info maps to database table
C           Professor's name
##   character*25     Pname
C           Professor's department
##   character*10     Pdept
C    Useful forms system information 
C           Last row in table field
##   integer*4        lstrow
C           Is it a table field ?
#   integer*4        istab

C    Local utility buffers
C           Message buffer
##   character*100    msgbuf
C           Response buffer
##   character*256    rspbuf
C           Old advisor before ZOOM
##   character*25     oldadv

C    Externally compiled master form 
     external         mstfrm
##   integer*4        mstfrm

##   addform mstfrm
C
C    Initialize 'studenttbl' with a data set in READ mode.
C    Declare hidden columns for all the extra fields that
C    the program will display when more information is
C    requested about a student. Columns 'sname' and 'sage'
C    are displayed, all other columns are hidden, to be
C    used in the student information form.

##   inittable #mstfrm studenttbl read
##        (#sbdate    = c25,
##         #sgpa      = f4,
##         #sidno     = i4,
##         #scomment  = text(200),
##         #sadvisor  = c20)

##   display #mstfrm update

##    initialize
##    {
##       message 'Enter an Advisor name . . .'
##       sleep 2
##    }

##    activate menuitem 'Students', field 'pname' 
##    {
C        Load the students of the specified professor 
         Pname = ' '
##       getform (Pname = #pname)

C        If no professor name is given then resume 
         if (Pname .EQ. ' ') then
##       resume field #pname
         endif
C
C       Verify that the professor exists.   Local error
C       handling just prints the message, and continues.
C       We assume that each professor has exactly one
C       department.
C
      Pdept = ' '
##    retrieve (Pdept = p.#pdept)
##         where p.#pname = Pname

      if (Pdept .EQ. ' ') then 
          msgbuf = 'No professor with name "' // Pname // 
   &               '"[RETURN]'
##        prompt noecho (msgbuf, rspbuf)
##        clear field all
##        resume field #pname 
      endif

C     Fill the department field and load students
##    putform (#pdept = Pdept)
##    redisplay                /* Refresh for query */
      call LdStd (Pname)

##    resume field studenttbl

##  }                /* 'Students' */

##  activate menuitem 'Zoom'
##  {
C
C     Confirm that user is on 'studenttbl', and that
C     the table field is not empty. Collect data from
C     the row and zoom for browsing and updating.

##    inquire_frs field #mstfrm (istab = table)

    if (istab .EQ. 0) then
##      prompt noecho
##           ('Select from the student table [RETURN]', rspbuf)
##      resume field studenttbl 
    endif

##  inquire_frs table #mstfrm (lstrow = lastrow)

    if (lstrow .EQ. 0) then
##      prompt noecho ('There are no students [RETURN]', rspbuf)
##      resume field #pname 
    endif

C   Collect all data on student into global record
##  getrow #mstfrm studenttbl 
##      (Sname     = #sname,
##       Sage      = #sage, 
##       Sbdate    = #sbdate, 
##       Sgpa      = #sgpa,
##       Sidno     = #sidno,
##       Scomm     = #scomment,
##       Sadv      = #sadvisor)
C
C   Display 'stdfrm', and if any changes were made
C   make the updates to the local table field row.
C   Only make updates to the columns corresponding to
C   writable fields in 'stdfrm'.   If the student
C   changed advisors, then delete this row from the
C   display.
C
    oldadv = Sadv 
    if (StdChg (Sname, Sage, Sbdate, Sgpa, Sidno, Scomm, 
  &      Sadv)) then 
        if (oldadv .NE. Sadv) then
##          deleterow #mstfrm studenttbl
        else
##         putrow #mstfrm studenttbl
##           (#sgpa       = Sgpa,
##           <x:c3>#scomment   = Scomm,
##           #sadvisor    = Sadv) 
        endif 
       endif

##   }                 /* 'Zoom' */

##    activate menuitem 'Quit', frskey2
##    {
##       breakdisplay
##    }               /* 'Quit' */

##    finalize

##    end
C
C   Procedure: LdStd 
C   Purpose:   Given an advisor name, load into the 'studenttbl'
C               table field all the students who report to the
C               professor with that name.
C   Parameters:
C               advisor - User specified professor name.
C               Uses the global student record.
C

##  subroutine LdStd(advisor)

##  declare forms

##  character*(*)  advisor
C   grad student record maps to database table
C         Student's name
##  character*25     Sname
C         Student's age
##  integer*2        Sage
C       Student's birthday
##  character*25     Sbdate
C         Student's grade point average
##  real*4           Sgpa
C        Student's unique id number
##  integer*4        Sidno
C        General comment field
##  character*200    Scomm
C         Student's advisor
##  character*25     Sadv
C
C   Clear previous contents of table field. Load the table
C   field from the database table based on the advisor name.
C   Columns 'sname' and 'sage' will be displayed, and all
C   others will be hidden.

##  message 'Retrieving Student Information . . .'
##  clear field studenttbl

##  retrieve 
##        (Sname     = s.#sname, 
##         Sage      = s.#sage, 
##         Sbdate    = s.#sbdate, 
##         Sgpa      = s.#sgpa,
##         Sidno     = s.#sidno, 
##         Scomm     = s.#scomment, 
##         Sadv      = s.#sadvisor)
##         where s.sadvisor          = advisor
##  {
##         loadtable #mstfrm studenttbl
##           (#sname     = Sname,
##             #sage     = Sage,
##             #sbdate   = Sbdate,
##             #sgpa     = Sgpa,
##             #sidno    = Sidno,
##             #scomment = Scomm,
##             #sadvisor = Sadv)

##  }

##  end
C
C   Procedure: StdChg
C   Purpose:   Allow the user to zoom into the details of a
C              selected student. Some of the data can be updated
C              by the user. If any updates were made, then
C              reflect these back into the database table.
C              The procedure returns TRUE if any changes were made.
C   Parameters:
C              None - Uses with data in the global 'grad' record.
C   Returns:
C              TRUE/FALSE - Changes were made to the database.
C              Sets the global 'grad' record with the new data.
C

##  logical function StdChg(Sname, Sage, Sbdate, Sgpa, Sidno,
##&                        Scomm, Sadv)
##  declare forms

C   grad student record maps to database table
C         Student's  name
##  character*25     Sname
C         Student's age
##  integer*2        Sage
C         Student's birthday
##  character*25     Sbdate
C         Student's grade point average
##  real*4           Sgpa
C         Student's unique id number
##  integer*4        Sidno
C         General comment field
##  character*200    Scomm
C         Student's advisor
##  character*25     Sadv
C   Changes made to date in form
##  integer*4        chnged
C         Valid advisor name ?
##  integer*4        vldadv

C         Compiled form 
    external         stdfrm
##  integer*4        stdfrm

C   Control ADDFORM to only initialize once 
    integer*4     ldform 
    data          ldform/0/

    if (ldform .EQ. 0) then
##      message 'Loading Student form . . .'
##      addform stdfrm 
        ldform = 1
    endif
##  display #Stdfrm fill
##  initialize
##       (#sname       = Sname,
##        #sage       = Sage,
##        #sbdate       = Sbdate,
##        #sgpa       = Sgpa,
##        #sidno      = Sidno,
##        #scomment      = Scomm,
##        #sadvisor      = Sadv)

##  activate menuitem 'Write'
##  {

C      If changes were made then update the database
C      table. Only bother with the fields that are not
C      read-only.

##     inquire_frs form (chnged = change)

       if (chnged .EQ. 1) then
##         validate

##         message 'Writing changes to database. . .'
##         getform
##            (Sgpa    = #sgpa,
##             Scomm   = #scomment,
##             Sadv    = #sadvisor)
C          Enforce integrity of professor name   
           vldadv = 0
##         retrieve (vldadv = 1)
##              where p.pname = Sadv

           if (vldadv .EQ. 0) then
##             message 'Not a valid advisor name'
##             sleep 2
##             resume field sadvisor 
           else
##             replace s (#sgpa = Sgpa,
##                        scomment = Scomm,
##                        sadvisor = Sadv)
##             where s.#sidno = Sidno
##             breakdisplay 
             endif 
          endif
##      }
                         /* 'Write' */  

##      activate menuitem 'End', frskey3
##      {
C           Quit without submitting changes 
            chnged = 0
##          breakdisplay
##      }
                      /* 'Quit' */

##      finalize

        if (chnged .EQ. 1) then 
            StdChg = .TRUE. 
        else 
            StdChg = .FALSE. 
        endif

        return

##      end  

VMS: The following two create statements describe the professor and student database tables:
##    create student                 /* Graduate student table */
##           (sname    = c25,        /* Name */
##            sage     = i1,         /* Age */
##            sbdate   = c25,        /* Birth date */
##            sgpa     = f4,         /* Grade point average */
##            sidno    = i4,         /* Unique student number */
##            scomment = text(200),  /* General comments */
##            sadvisor = c25)        /* Advisor's name */

##    create professor               /* Professor table */
##           (pname    = c25,        /* Professor's name */
##            pdept    = c10)        /* Department */

!
!       Procedure: MAIN
!       Purpose:   Start up program and call Master driver.
!
##      program main

        ! Start up Ingres and the FORMS system
##      declare forms

##      forms
##      message 'Initializing Student Administrator . . .'

##      ingres personnel
##      range of p IS professor, s IS student

        call Master()
##      clear screen
##      endforms
##      exit

##      end
!
!       Procedure: 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.
!

##      subroutine Master()
##      declare forms

        logical Student_Info_Changed ! function

        ! grad student record maps to database table
##      structure /grad_student/
##          character*25    sname
##          integer*2       sage
##          character*25    sbdate
##          real*4          sgpa
##          integer*4       sidno
##          character*200   scomment
##          character*25    sadvisor
##      end structure
##      record /grad_student/ grad

        ! Professor info maps to database table
##      structure /professor/
##          character*25 pname
##          character*10 pdept
##      end structure
##      record /professor/ prof

        ! Useful forms system information 
##      integer*4 lastrow ! Lastrow in table field
##      integer*4 istable ! Is a table field?

        ! Local utility buffers
##      character*100 msgbuf     ! Message buffer
##      character*256 respbuf    ! Response buffer
##      character*25 old_advisor ! Old advisor before ZOOM

        ! Externally compiled master form
        external masterfrm
##      integer*4 masterfrm
##      addform masterfrm

        !
        ! 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.
        !

##      inittable #masterfrm studenttbl read
##           (sbdate   = c25,
##            sgpa     = float,
##            sidno    = integer,
##            scomment = c200,
##            sadvisor = c20)

##      display #masterfrm update

##      initialize
##      {
##          message 'Enter an Advisor name . . .'
##          sleep 2
##      }

##      activate menuitem 'Students', field 'pname' 
##      {
            ! Load the students of the specified professor
##          getform (prof.pname = pname)

            ! If no professor name is given then resume
            if (prof.pname .EQ. ' ') then
##               resume field pname
            endif
            !
            ! Verify that the professor exists. Local error
            ! handling just prints the message, and continues.
            ! We assume that each professor has exactly one
            ! department.
            !

            prof.pdept = ' '
##          retrieve (prof.pdept = p.pdept)
##               where p.pname = prof.pname

            if (prof.pdept .EQ. ' ') then
            msgbuf = 'No professor with name "' // prof.pname //
     1                      '"[RETURN]'
##               prompt noecho (msgbuf, respbuf)
##               clear field all
##               resume field pname
            endif

            ! Fill the department field and load students
##          putform (pdept = prof.pdept)
##          redisplay /* Refresh for query */

            call Load_Students(prof.pname)

##          resume field studenttbl

##      }              /* 'Students' */

##      activate menuitem 'Zoom'
##      {
        !
        ! 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.
        !
##      inquire_frs field #masterfrm (istable = table)

        if (istable .EQ. 0) then
##            prompt noecho
##             ('Select from the student table [RETURN]', respbuf)
##            resume field studenttbl
        endif

##      inquire_frs table #masterfrm (lastrow = lastrow)
        if (lastrow .EQ. 0) then
##       prompt noecho ('There are no students [RETURN]', respbuf)
##       resume field pname
        endif

        ! Collect all data on student into global record
##      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(grad) .EQ. .TRUE.) then
            if (old_advisor .NE. grad.sadvisor) then
##              deleterow #masterfrm studenttbl
            else
##              putrow #masterfrm studenttbl
##                 (sgpa = grad.sgpa,
##                  scomment = grad.scomment,
##                  sadvisor = grad.sadvisor)
        endif
      endif

##    }        /* 'Zoom' */

##    activate menuitem 'Quit', frskey2
##    {
##          breakdisplay
##    }        /* 'Quit' */

##    finalize

##    end      ! Master
!
! Procedure:  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.
!

##    subroutine Load_Students(advisor)

##    declare forms

##    character*(*) advisor

      ! grad student record maps to database table
##    structure /grad_student/
##        character*25    sname
##        integer*2       sage
##        character*25    sbdate
##        real*4          sgpa
##        integer*4       sidno
##        character*200   scomment
##        character*25       sadvisor
##    end structure
##    record /grad_student/ grad
      !
      ! 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.
      !
##    message 'Retrieving Student Information . . .'
##    clear field studenttbl
##    retrieve 
##         (grad.sname = s.sname, 
##          grad.sage = s.sage, 
##          grad.sbdate = s.sbdate, 
##          grad.sgpa = s.sgpa,
##          grad.sidno = s.sidno, 
##          grad.scomment = s.scomment, 
##          grad.sadvisor = s.sadvisor)
##          where s.sadvisor = advisor
##    {
##          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    Load_Students
!
! Procedure:  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.
!

##    logical function Student_Info_Changed(grad)

##    declare forms
      ! grad student record maps to database table
##    structure /grad_student/
##        character*25 sname
##        integer*2 sage
##        character*25   sbdate
##        real*4         sgpa
##        integer*4      sidno
##        character*200  scomment
##        character*25   sadvisor
##    end structure
##    record /grad_student/ grad

##    integer*4 changed       ! Changes made to data in form
##    integer*4 valid_advisor ! Valid advisor name ?

      external studentfrm
##    integer*4 studentfrm ! Compiled form

      ! Control ADDFORM to only initialize once
      integer*4 loadform
      data loadform/0/

      if (loadform .EQ. 0) then
##        message 'Loading Student form . . .'
##        addform studentfrm
          loadform = 1
      endif
##    display #studentfrm fill
##    initialize
##           (sname     = grad.sname,
##            sage      = grad.sage,
##            sbdate    = grad.sbdate,
##            sgpa      = grad.sgpa,
##            sidno     = grad.sidno,
##            scomment  = grad.scomment,
##            sadvisor  = grad.sadvisor)

##    activate menuitem 'Write'
##    {
          !
          ! If changes were made then update the database
          ! table. Only bother with the fields that are not
          ! read-only.
          !
##        inquire_frs form (changed = change)

          if (changed .EQ. 1) then
##            validate
##            message 'Writing changes to database. . .'

##            getform
##               (grad.sgpa = sgpa,
##                grad.scomment = scomment,
##                grad.sadvisor = sadvisor)

                  ! Enforce integrity of professor name
                  valid_advisor = 0
##                retrieve (valid_advisor = 1)
##                    where p.pname = grad.sadvisor

                  if (valid_advisor .EQ. 0) then
##                    message 'Not a valid advisor name'
##                    sleep 2
##                    resume field sadvisor
                  else
##                    replace s (sgpa = grad.sgpa,
##                        scomment = grad.scomment,
##                        sadvisor = grad.sadvisor)
##                        where s.sidno = grad.sidno
##                    breakdisplay
                  endif
        endif
##    }               /* 'Write' */

##    activate menuitem 'End', frskey3
##    {
              ! Quit without submitting changes
              changed = 0
##            breakdisplay
##    }               /* 'Quit' */
##    finalize

      if ( changed .EQ. 1) then
           Student_Info_Changed = .TRUE.
      else
           Student_Info_Changed = .FALSE.
      endif

      return
##    end