2. Embedded SQL for C : 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 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 the studenttbl table field. The pdept field is display-only. This form is a compiled form.
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 corresponds to information in the Student table. Only the sgpa, scomment, and sadvisor fields are updatable. All other fields are display-only. This form 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 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. The studentcsr database cursor in the Load_Students procedure does this. 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 the sgpa, scomment, and sadvisor fields. 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:  The application uses function prototypes and ifdef statements to enable you to build it using either the ESQL/C or ESQL/C++ precompiler.
Sample Application
# include <stdio.h>
# include <string.h>
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;
  /* Global grad student record maps to database table */
  struct {
    char sname[26];
    short sage;
    char sbdate[26];
    float sgpa;
    int sidno;
    char scomment[201];
    char sadvisor[26];
  } grad;
 
EXEC SQL END DECLARE SECTION;
/*
** Function prototypes for C++ only so that this is compatible
** with old-style C compilers
*/
# ifdef __cplusplus
void Master(void);
 void Load_Students(char *adv);
 int Student_Info_Changed(void);
 # endif /* __cplusplus */
 
/*
** Procedure: MAIN
** Purpose: Start up program and call Master driver.
*/
main()
{
  /* 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;
 
  Master();
 
  EXEC FRS CLEAR SCREEN;
  EXEC FRS ENDFORMS;
  EXEC SQL DISCONNECT;
 }
 
 
/*
** 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.
*/

# ifdef __cplusplus
void
Master(void)
# else
Master()
# endif /* __cplusplus */
{
  EXEC SQL BEGIN DECLARE SECTION;
    /* Professor info maps to database table */
    struct {
      char pname[26];
      char pdept[11];
    } prof;
 
    /* Useful forms system information */
    int lastrow, /* Lastrow in table field */
         istable; /* Is a table field? */
 
    /* Local utility buffers */
    char msgbuf[100]; /* Message buffer */
    char respbuf[256]; /* Response buffer */
    char old_advisor[26]; /* Old advisor before ZOOM */
 
    /* Externally compiled master form */
    extern int *masterfrm;
  EXEC SQL END DECLARE SECTION;
 
  EXEC FRS 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.
  */
  EXEC FRS INITTABLE masterfrm studenttbl READ
    (sbdate = char(25),
     sgpa = float4,
     sidno = integer4,
     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, resume */
    if (prof.pname[0] == '\0')
      EXEC FRS RESUME FIELD pname;
 
    /*
    ** Verify that the professor exists. Local error handling
    ** just prints the message and continues. Assume that each
    ** professor has exactly one department.
    */
    EXEC SQL WHENEVER SQLERROR CALL SQLPRINT;
    EXEC SQL WHENEVER NOT FOUND CONTINUE;
    prof.pdept[0] = '\0';
    EXEC SQL SELECT pdept
      INTO :prof.pdept
      FROM professor
      WHERE pname = :prof.pname;
 
    if (prof.pdept[0] == '\0')
    {
      sprintf(msgbuf,
        "No professor with name \"%s\" [RETURN]",
        prof.pname);
      EXEC FRS PROMPT NOECHO (:msgbuf, :respbuf);
      EXEC FRS CLEAR FIELD ALL;
      EXEC FRS RESUME FIELD pname;
    }
 
    /* Fill the department field and load students */
    EXEC FRS PUTFORM (pdept = :prof.pdept);
    EXEC FRS REDISPLAY; /* Refresh for query */
 
    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)
    {
      EXEC FRS PROMPT NOECHO
        ('Select from the student table [RETURN]',
         :respbuf);
      EXEC FRS RESUME FIELD studenttbl;
    }
 
    EXEC FRS INQUIRE_FRS table masterfrm
      (:lastrow = lastrow);
 
    if (lastrow == 0)
    {
      EXEC FRS PROMPT NOECHO
        ('There are no students [RETURN]',
         :respbuf);
      EXEC FRS RESUME FIELD pname;
    }
 
    /* 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 the row from the display.
    */
    strcpy(old_advisor, grad.sadvisor);
    if (Student_Info_Changed())
    {
      if (strcmp(old_advisor, grad.sadvisor) != 0)
        EXEC FRS DELETEROW masterfrm studenttbl;
      Else
        EXEC FRS PUTROW masterfrm studenttbl
           (sgpa = :grad.sgpa,
           scomment = :grad.scomment,
           sadvisor = :grad.sadvisor);
     }
 
 
  EXEC FRS END; /* "Zoom" */
 
  EXEC FRS ACTIVATE MENUITEM 'Exit';
  EXEC FRS BEGIN;
    EXEC FRS BREAKDISPLAY;
  EXEC FRS END; /* "Exit" */
  EXEC FRS FINALIZE;
 
} /* 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.
*/

# ifdef __cplusplus
void
Load_Students(char *adv)
# else
Load_Students(adv)
char *adv;
# endif /* __cplusplus */
{
  EXEC SQL BEGIN DECLARE SECTION;
    char *advisor = adv;
  EXEC SQL END DECLARE SECTION;
 
  EXEC SQL DECLARE studentcsr CURSOR FOR
    SELECT sname, sage, sbdate, sgpa, sidno, scomment, sadvisor
    FROM student
    WHERE sadvisor = :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 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)
  {
    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);
  }
 
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;
 
} /* Load_Students */
 
/*
** Procedure: Student_Info_Changed
** Purpose: Allow the user to zoom in on 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 data in the global "grad" record.
** Returns:
** TRUE/FALSE - Changes were made to the database.
** Sets the global "grad" record with the new data. */
 
# ifdef __cplusplus
int
Student_Info_Changed(void)
# else
int
Student_Info_Changed()
# endif /* __cplusplus */
 
{
  EXEC SQL BEGIN DECLARE SECTION;
    int changed; /* Changes made to data in form */
    int valid_advisor; /* Valid advisor name? */
    extern int *studentfrm; /* Compiled form */
 
  EXEC SQL END DECLARE SECTION;
 
  /* Control ADDFORM to only initialize once */
  static int loadform = 0;
 
  if (!loadform)
  {
    EXEC FRS MESSAGE 'Loading Student form . . .';
    EXEC FRS ADDFORM :studentfrm;
    loadform = 1; }
 
 
  /* 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
    (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)
    {
      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)
      {
        EXEC FRS MESSAGE 'Not a valid advisor name';
        EXEC FRS SLEEP 2;
        EXEC FRS RESUME FIELD sadvisor;
      }
 
      else
      {
        EXEC SQL UPDATE student SET
          sgpa = :grad.sgpa,
          scomment = :grad.scomment,
          sadvisor = :grad.sadvisor
          WHERE sidno = :grad.sidno;
        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;
  return (changed == 1);
 } /* Student_Info_Changed *