4. Embedded SQL for Fortran : Sample Applications : The Professor-Student Mixed Form Application
 
Share this page                  
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:
Object
Description
personnel
The program's database environment.
professor
A database table with two columns:
      pname (char(25))
      pdept (char(10)).
See its declare table statement in the program for a full description.
student
A database table with seven columns:
      sname (char(25))
      sage (integer1)
      sbdate (char(25))
      sgpa (float4)
      sidno (integer)
      scomment (varchar(200))
      sadvisor (char(25))
See its declare table statement for a full description. The "sadvisor" column is the join field with the "pname" column in the "professor" table.
masterfrm
The main form has the "pname" and "pdept" fields, which correspond to the information in the "professor" table, and "studenttbl" table field. The "pdept" field is display-only.
studenttbl
A table field in "masterfrm" with the "sname" and "sage" columns. When initialized, it also has five 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. All other fields are display-only.
grad
A Fortran common area, whose fields 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 you can only retrieve and browse data, and the "studentfrm" as the detailed screen, in which you can update specific student information.
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 LoadStudents procedure.
The program assumes that each professor is associated with exactly one department. You can then browse the table field (in read mode), which displays only the names and ages of the students. You can request more information about a specific student 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". You 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. You can repeat this process for different professor names.
Note:  Records can be used in this application but variables must be used with F77.
The application runs in UNIX, VMS, and Windows environments.
C
C Program: ProfessorStudent
C Purpose: Main entry point into "Professor-Student" mixed-form 
C              master-detail application.
C
      program ProfessorStudent
      exec sql include sqlca
 
C Graduate student table
      exec sql declare student table
     1    (sname     char(25), 
     2     sage      integer1,
     3     sbdate    char(25), 
     4     sgpa      float4,
     5     sidno     integer4,
     6     scomment  char(200),
     7     sadvisor  char(25))
C Professor table
      exec sql declare professor table
     1     (pname    char(25), 
     2      pdept    char(10))

      exec sql begin declare section
C Externally compiled master and student form
           integer masterfrm, studentfrm
      exec sql end declare section
      external masterfrm, studentfrm
C 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
C
C Subroutine: Master
C Purpose:    Drive the application, by running "masterfrm" and
C              allowing the user to "zoom" into a selected student.
C Parameters:
C              None - Uses the global student "grad" common area. 
C
      subroutine Master
      exec sql include sqlca
      exec sql begin declare section
 
C Global grad common area maps to database table
            character*25    sname
            integer*2       sage
            character*25    sbdate
            real            sgpa
            integer         sidno
            character*200   scomment
            character*25    sadvisor
 
C Professor info maps to database table
            character*25 pname
            character*10 pdept
C Useful forms system information
C Lastrow in table field
            integer lastrow 
C Is a table field?
            integer istable
C Local utility buffers
C Message buffer
            character*100 msgbuf 
C Response buffer
            character respbuf 
C Old advisor before ZOOM
            character*25 oldavisor
      exec sql end declare section
C Make definition global
      common /grad/ sgpa, sidno, sage, sname, sbdate, scomment, 
     1     sadvisor
C Function defined below
      logical StudentInfoChanged
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.
C
      exec frs inittable masterfrm studenttbl read
     1    (sbdate = char(25),
     2     sgpa   = float4,
     3     sidno    = integer4,
     4     scomment = char(200),
     5     sadvisor = char(25))
      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
C Load the students of the specified professor
           exec frs getform (:pname = pname)
C If no professor name is given, resume
           if (pname(1:1) .eq.' ') then
               exec frs resume field pname
           endif
C
C Verify that the professor exists. Local error
C handling just prints the message and continues.
C Assume that each professor has exactly one
C department.
C
           exec sql whenever sqlerror call sqlprint
           exec sql whenever not found continue
           pdept = ' '
           exec sql select pdept
     1          into :pdept
     2          from professor
     3          where pname = :pname
           if (pdept(1:1) .eq.' ') then
                write (msgbuf, 10) pname
10              format ('No professor with name "', a, 
     1                   '" [Press RETURN]')
                exec frs prompt noecho (:msgbuf, :respbuf)
                exec frs clear field all
                exec frs resume field pname
           endif
C Fill the department field and load students
           exec frs putform (pdept = :pdept)
C Refresh for query
           exec frs redisplay
           call loadstudents(pname)
           exec frs resume field studenttbl
      exec frs end
      exec frs activate menuitem 'Zoom'
      exec frs begin
C
C Confirm that user is in "studenttbl" and that
C the table field is not empty. Collect data from
C the row and zoom for browsing and updating.
C
           exec frs inquire_frs field masterfrm
     1         (:istable = table)
           if (istable .eq. 0) then
               exec frs prompt noecho
     1             ('Select from the student table [Press RETURN]',
     2               :respbuf)
               exec frs resume field studenttbl
           endif
           exec frs inquire_frs table masterfrm
     1          (:lastrow = lastrow)
           if (lastrow .eq. 0) then
                exec frs prompt noecho
     1              ('There are no students [Press RETURN]',
     2                :respbuf)
                exec frs resume field pname
           endif
C Collect all data on student into global record
           exec frs getrow masterfrm studenttbl
     1          (:sname = sname,
     2           :sage = sage,
     3           :sbdate = sbdate,
     4           :sgpa = sgpa,
     5           :sidno = sidno,
     6           :scomment = scomment,
     7           :sadvisor = sadvisor)
C
C Display "studentfrm", 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 "studentfrm". If the student
C changed advisors, then delete the row from the
C display.
C
           oldavisor = sadvisor
           if (StudentInfoChanged()) then
                if (oldavisor .ne. sadvisor) then
                      exec frs deleterow masterfrm studenttbl
                else
                      exec frs putrow masterfrm studenttbl
     1                   (sgpa     = :sgpa,
     2                    scomment = :scomment,
     3                   sadvisor = :sadvisor)
                endif
           endif
      exec frs end
      exec frs activate menuitem 'Exit'
      exec frs begin
             exec frs breakdisplay
      exec frs end
      exec frs finalize
      end
C
C Subroutine:  LoadStudents
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 LoadStudents(advisor)

      exec sql include sqlca
      exec sql begin declare section
             character*(*) advisor
      exec sql end declare section
C Global "grad" common fields
          character*25     sname
          integer*2        sage
          character*25     sbdate
          real             sgpa
          integer          sidno
          character*200    scomment
          character*25     sadvisor
      common /grad/ sgpa, sidno, sage, sname, sbdate, scomment,
     1           sadvisor
       exec sql declare studentcsr cursor for
     1      select sname, sage, sbdate, sgpa,
     2          sidno, scomment, sadvisor
     3          from student
     4          where sadvisor = :advisor
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.
C
      exec frs message 'Retrieving Student Information . . .'
      exec frs clear field studenttbl
C End loading
      exec sql whenever sqlerror go to 100 
      exec sql whenever not found go to 100
      exec sql open studentcsr
C
C Before we start the loop, we know that the OPEN was
C successful and that NOT FOUND was not set.
C
55    if (sqlcod .ne. 0) go to 555
       exec sql fetch studentcsr into :sname, :sage, :sbdate, 
      1     sgpa, :sidno, :scomment, :sadvisor
       exec frs loadtable masterfrm studenttbl
     1   (sname    = :sname,
     2    sage     = :sage,
     3    sbdate   = :sbdate,
     4    sgpa     = :sgpa,
     5    sidno    = :sidno,
     6    scomment = :scomment,
     7    sadvisor = :sadvisor)

      go to 55
555   continue

C Clean up on an error, and close cursors
      exec sql whenever not found continue

100   exec sql whenever sqlerror continue
      exec sql close studentcsr
      end
C
C Function: StudentInfoChanged
C Purpose:  Allow the user to zoom into the details of a selected
C            student. Some of the data can be updated by the user.
C            If any updates were made, then reflect these back into
C            the database table. The procedure returns TRUE if any
C            changes were made.
C Parameters:
C            None - Uses data in the global "grad" common area.
C Returns:
C           true/false - Changes were made to the database.
C           Sets the global "grad" common area with the new data.
C
      logical function StudentInfoChanged()
      exec sql include sqlca
      exec sql begin declare section
C Changes made to data in form
          integer changed 
C Valid advisor name?
          integer validadvisor
      exec sql end declare section
C Global "grad" common fields
      character*25   sname     
      integer*2      sage
      character*25   sbdate
      real           sgpa
      integer        sidno
      character*200  scomment
      character*25   sadvisor
      common /grad/ sgpa, sidno, sage, sname, sbdate, scomment,
     1      sadvisor
C Local error handler 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
     1     (sname = :sname,
     2      sage = :sage,
     3      sbdate = :sbdate,
     4      sgpa = :sgpa,
     5      sidno = :sidno,
     6      scomment = :scomment,
     7      sadvisor = :sadvisor)

      exec frs activate menuitem 'Write'
      exec frs begin
C
C If changes were made, then update the database
C table. Only bother with the fields that are not
C read-only.
C
           exec frs inquire_frs form (:changed = change)

           if (changed .eq. 1) then
                exec frs validate
                exec frs getform
     1               (:sgpa = sgpa,
     2                :scomment = scomment,
     3                :sadvisor = sadvisor)
C Enforce integrity of professor name
                validadvisor = 0
                exec sql select 1 into :validadvisor
     1               from professor
     2               where pname = :sadvisor
                if (validadvisor .eq. 0) then
                     exec frs message 'Not a valid advisor name'
                     exec frs sleep 2
                     exec frs resume field sadvisor
                endif
                exec frs message 'Writing changes to database. . .'
                exec sql update student set
     1                sgpa = :sgpa,
     2                scomment = :scomment,
     3                sadvisor = :sadvisor
     4                where sidno = :sidno
           endif
           exec frs breakdisplay
      exec frs end
      exec frs activate menuitem 'Quit'
      exec frs begin
C Quit without submitting changes
           changed = 0
           exec frs breakdisplay
      exec frs end
      exec frs finalize
      StudentInfoChanged = (changed .EQ. 1)

      end