7. Embedded SQL for Pascal : 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 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 fields pname and pdept, which correspond to the information in the Professor table, and table field studenttbl. 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 fields sgpa, scomment, and sadvisor are updatable. All other fields are display-only.
grad
A global structure, 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 data can only be retrieved and browsed, and the "studentfrm" as the detailed screen, in which specific student information can be updated.
The runtime user enters a name in the pname field and then selects the Students menu operation. The operation fills the table field "studenttbl" with detailed information of the students reporting to the named professor. This is done by the database cursor "studentcsr" in the procedure "Load_Students." 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.
Sample Application
{
| Procedure: Prof_Student
| Purpose: Main body of "Professor Student" Master-Detail application.
}

program Prof_Student( input, output );

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}
     sidno        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}

exec sql begin declare section;
type
    Short_Short_Integer = [byte] -128..127;

    String1   = packed array[1..1]   of Char;
    String10  = packed array[1..10]  of Char;
    String25  = packed array[1..25]  of Char;
    String100 = packed array[1..100] of Char;
    String200 = packed array[1..200] of Char;

    {Graduate student record maps to "student" database table }
    Student_Rec = record
        sname: String25;
        sage: Short_Short_Integer;
        sbdate: String25;
        sgpa: Real;
        sidno: Integer;
        scomment: String200;
        sadvisor: String25;
    end;
var
    grad: Student_Rec;
    {Master and student compiled forms (imported objects)}
    masterfrm, studentfrm: [external] Integer;
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.
|                 Columns "sname" and "sage" will be displayed, and
|                 all other columns will be hidden.
| Parameters:     advisor - User specified professor name.
|                 Uses the global student record "grad".
}

procedure Load_Students( var adv : String25 );
    label
        Load_End;
    exec sql begin declare section;
    var
        advisor : String25;
    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             {Load_Students}
    advisor := adv;

    {
    | 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) do
    begin
        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;

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 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
    | Returns: TRUE/FALSE - Changes were made to the database.
    | Sets the global "grad" record with the new data.
    }

function Student_Info_Changed : Boolean;
    exec frs label;
    exec sql begin declare section;
    var
        changed: Integer; {Changes made to the form?}
        valid_advisor: Integer; {Is the advisor name valid?}
    exec sql end declare section;

begin             {Student_Info_Changed}
    {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
        begin
            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
            begin
                exec frs message
                    'Not a valid advisor name';
                exec frs sleep 2;
                exec frs resume field sadvisor;
            end else
            begin
                exec sql update student set
                    sgpa = :grad.sgpa,
                    scomment = :grad.scomment,
                    sadvisor = :grad.sadvisor
                    where sidno = :grad.sidno;
            end;
        end;
        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 = 1);
exec frs end; {Student_Info_Changed}

{
| 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.
}

procedure Master;
    exec frs label;
    exec sql begin declare section;
    type
        {Professor record maps to "professor" database table }
         Prof_Rec = record
            pname: String25;
            pdept: String10;
         end;
    var
             prof: Prof_Rec;

           {Useful forms runtime information }
            lastrow,         {Lastrow in table field }
            istable: Integer;     {Is a table field? }
            {Utility buffers }
            msgbuf: String100;         {Message buffer }
            respbuf: String1;          {Response buffer }
            old_advisor: String25;     {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" into 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, resume }
        if (prof.pname[1] = ' ') then
            exec frs resume field pname;

        {
        | Verify that the professor exists. If not print
        | 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 := ' ';
        exec sql select pdept
            into :prof.pdept
            from professor
            where pname = :prof.pname;

        {If no professor, report error}
        if (prof.pdept[1] = ' ') then
        begin
            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;

        {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
        begin
            exec frs prompt noecho
                ('Select from the student table [return]',
                     :respbuf);
            exec frs resume field studenttbl;
        end;

        exec frs inquire_frs table masterfrm
                (:lastrow = lastrow);
        if (lastrow = 0) then
        begin
            exec frs prompt noecho
                ('There are no students [RETURN]',
                    :respbuf);
            exec frs resume field pname;
        end;

        {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
        begin
            if (old_advisor <> grad.sadvisor) then
            begin
                exec frs deleterow
                    masterfrm studenttbl;
            end else
            begin
                exec frs putrow masterfrm studenttbl
                    (sgpa = :grad.sgpa,
                     scomment = :grad.scomment,
                     sadvisor = :grad.sadvisor);
            end;
        end;
    exec frs end;         {"Zoom"}
    exec frs activate menuitem 'Exit';
    exec frs begin;
        exec frs breakdisplay;
    exec frs end;         {"Exit"}
    exec frs finalize;
exec frs 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}