6. Embedded SQL for BASIC : 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:
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 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 the studenttbl table field. The pdept field is display only.
studenttbl
A table field in "masterfrm" with two columns, "sname" and "sage". 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 global BASIC record, 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 "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