2. Embedded SQL for C : Sample Applications : The Table Editor Table Field Application
 
Share this page                  
The Table Editor Table Field Application
This application edits the Person table in the Personnel database. It is a forms application that allows the user to update a person's values, remove the person, or add new persons. Various table field utilities are provided with the application to demonstrate how they work.
The objects used in this application are shown in the following table:
Object
Description
personnel
The program's database environment.
person
A database table with three columns:
   Name (char(20))
   Age (smallint)
   Number (integer)
Number is unique.
personfrm
The VIFRED form with a single table field.
persontbl
A table field in the form, with two columns:
   name (char(20))
   age (integer)
When initialized the table file includes the hidden column number (integer).
At the start of the application, a database cursor is opened to load the table field with data from the Person table. After loading the table field, you can browse and edit the displayed values. You can add, update, or delete entries. When finished, the values are unloaded from the table field, and your updates are transferred back into the Person table.
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 person TABLE
  (name char(20), /* Person name */
   age smallint, /* Age */
   number integer); /* Unique id number */
 
/*
** Function prototypes for C++ only so that this is compatible
** with old-style C compilers
*/
# ifdef __cplusplus
int Load_Table(void);
# endif /* __cplusplus */

/*
** Procedure: MAIN
** Purpose: Entry point into Table Editor program.
*/

main()
{
/* Table field row states */
# define stUNDEF 0 /* Empty or undefined row */
# define stNEW 1 /* Appended by user */
# define stUNCHANGED 2 /* Loaded by program - not updated */
# define stCHANGE 3 /* Loaded by program - since changed */
# define stDELETE 4 /* Deleted by program */

# define NOT_FOUND 100 /* SQLCA value for no rows */

  EXEC SQL BEGIN DECLARE SECTION;
 
    /* Person information */
    char pname[21]; /* Full name (with C null) */
    int page, /* Age of person */
         pnumber; /* Unique person number */
    int maxid; /* Max person id number */
 
    /* Table field entry information */
    int state, /* State of data set entry */
         record, /* Record number */
         lastrow; /* Last row in table field */
 
 
    /* Utility buffers */
    char msgbuf[100], /* Message buffer */
         respbuf[256]; /* Response buffer */
  EXEC SQL END DECLARE SECTION;
 
  int update_error; /* Update error from database */
  int xact_aborted; /* Transaction aborted */
 
  /* Set up error handling for main program */
  EXEC SQL WHENEVER SQLWARNING CONTINUE;
  EXEC SQL WHENEVER NOT FOUND CONTINUE;
  EXEC SQL WHENEVER SQLERROR STOP;
 
  /* Start up INGRES and the INGRES/FORMS system */
  EXEC SQL CONNECT 'personnel';
 
  EXEC FRS FORMS;
 
  /* Verify that the user can edit the "person" table */
  EXEC FRS PROMPT NOECHO ('Password for table editor: ', :respbuf);
 
  if (strcmp(respbuf, "MASTER_OF_ALL") != 0)
  {
    EXEC FRS MESSAGE 'No permission for task. Exiting . . .';
    EXEC FRS ENDFORMS;
    EXEC SQL DISCONNECT;
    exit( 1 );
  }
 
  /* We assume no SQL errors can happen during screen updating */
  EXEC SQL WHENEVER SQLERROR CONTINUE;
 
  EXEC FRS MESSAGE 'Initializing Person Form . . .';
  EXEC FRS FORMINIT personfrm;
 
  /*
  ** Initialize "persontbl" table field with a data set in FILL mode,
  ** so that the runtime user can append rows. To keep track of
  ** events occurring to original rows loaded into the table field,
  ** hide the unique person number.
  */

  EXEC FRS INITTABLE personfrm persontbl FILL (number = integer);
 
  maxid = Load_Table();
 
EXEC FRS DISPLAY personfrm UPDATE;
  EXEC FRS INITIALIZE;
 
  EXEC FRS ACTIVATE MENUITEM 'Top';
  EXEC FRS BEGIN;
    /*
    ** Provide menu items, as well as the system FRS key,
    ** to scroll to both extremes of the table field.
    */
    EXEC FRS SCROLL personfrm persontbl TO 1;
  EXEC FRS END;
 
  EXEC FRS ACTIVATE MENUITEM 'Bottom';
  EXEC FRS BEGIN;
    EXEC FRS SCROLL personfrm persontbl TO END; /* Forward */  EXEC FRS END;
 
 
  EXEC FRS ACTIVATE MENUITEM 'Remove';
  EXEC FRS BEGIN;
    /*
    ** Remove the person in the row the user's cursor is on.
    ** If there are no persons, exit operation with message.
    ** Note that this check cannot really happen, as there is
    ** always an UNDEFINED row in FILL mode.
    */
    EXEC FRS INQUIRE_FRS table personfrm
        (:lastrow = lastrow(persontbl));
    if (lastrow == 0)
    {
      EXEC FRS MESSAGE 'Nobody to Remove';
      EXEC FRS SLEEP 2;
      EXEC FRS RESUME FIELD persontbl;
    }
    EXEC FRS DELETEROW personfrm persontbl; /* Record later */
  EXEC FRS END;
 
  EXEC FRS ACTIVATE MENUITEM 'Find';
  EXEC FRS BEGIN;
    /*
    ** Scroll user to the requested table field entry.
    ** Prompt the user for a name, and if one is typed in,
    ** loop through the data set searching for it.
    */
    EXEC FRS PROMPT ('Person''s name : ', :respbuf);
    if (respbuf[0] == '\0')
      EXEC FRS RESUME FIELD persontbl;
 
    EXEC FRS UNLOADTABLE personfrm persontbl
      (:pname = name,
       :record = _record,
       :state = _state);
    EXEC FRS BEGIN;
 
      /* Do not compare with deleted rows */
      if ((strcmp(pname, respbuf) == 0) &&
          (state != stDELETE))
      {
        EXEC FRS SCROLL personfrm persontbl
          TO :record;
        EXEC FRS RESUME FIELD persontbl;
      }
 
    EXEC FRS END;
 
    /* Fell out of loop without finding name */
    sprintf(msgbuf,
      "Person \"%s\" not found in table [HIT RETURN] ",
respbuf);
EXEC FRS PROMPT NOECHO (:msgbuf, :respbuf);
EXEC FRS END;

EXEC FRS ACTIVATE MENUITEM 'Exit';
EXEC FRS BEGIN;
EXEC FRS VALIDATE FIELD persontbl;
EXEC FRS BREAKDISPLAY;
EXEC FRS END;
EXEC FRS FINALIZE;


/*
** Exit person table editor and unload the table field. If any
** updates, deletions or additions were made, duplicate these
** changes in the source table. If the user added new people,
** assign a unique id to each person before adding the person to
** the table. To do this, increment the previously-saved maximum
** id number with each insert.
*/

/* Do all the updates in a transaction */
EXEC SQL SAVEPOINT savept;

/*
** Hard code the error handling in the UNLOADTABLE loop, as
** we want to cleanly exit the loop.
*/
EXEC SQL WHENEVER SQLERROR CONTINUE;

update_error = 0;
xact_aborted = 0;

EXEC FRS MESSAGE 'Exiting Person Application . . .';
EXEC FRS UNLOADTABLE personfrm persontbl
(:pname = name, :page = age,
:pnumber = number, :state = _state);
EXEC FRS BEGIN;

/* Appended by user. Insert with new unique id. */
if (state == stNEW)
{
maxid = maxid + 1;
EXEC SQL INSERT INTO person (name, age, number)
VALUES (:pname, :page, :maxid);
}
/* Updated by user. Reflect in table. */
else if (state == stCHANGE)
{
EXEC SQL UPDATE person SET
name = :pname, age = :page
WHERE number = :pnumber;
}
/*
** Deleted by user, so delete from table. Note that only
** original rows, not rows appended at runtime, are
** saved by the program.
*/
else if (state == stDELETE)
{
EXEC SQL DELETE FROM person
WHERE number = :pnumber;
}
/* Else UNDEFINED or UNCHANGED - No updates */


/*
** Handle error conditions -
** If an error occurred, abort the transaction.
** If no rows were updated, inform user and prompt
** for continuation.
*/
if (sqlca.sqlcode < 0) /* Error */
{
EXEC SQL INQUIRE_INGRES (:msgbuf = ERRORTEXT);
EXEC SQL ROLLBACK TO savept;
update_error = 1;
xact_aborted = 1;
EXEC FRS ENDLOOP;
}
else if (sqlca.sqlcode == NOT_FOUND)
{
sprintf(msgbuf,
"Person \"%s\" not updated. Abort all updates? ",
pname);
EXEC FRS PROMPT (:msgbuf, :respbuf);
if (respbuf[0] == 'Y' || respbuf[0] == 'y')
{
EXEC SQL ROLLBACK TO savept;
xact_aborted = 1;
EXEC FRS ENDLOOP;
}
}

EXEC FRS END;

if (!xact_aborted)
EXEC SQL COMMIT; /* Commit the updates */

EXEC FRS ENDFORMS; /* Terminate the FORMS and INGRES */
EXEC SQL DISCONNECT;

if (update_error)
{
printf( "Your updates were aborted because of error:\n" );
printf( msgbuf );
printf( "\n" );
}

} /* Main Program */

/*
** Procedure: Load_Table
** Purpose: Load the table field from the "person" table. The
** columns "name" and "age" will be displayed, and
** "number" will be hidden.
** Parameters:
** None
** Returns:
** Maximum employee number
*/
# ifdef __cplusplus
int
Load_Table(void)
# else
int
Load_Table()
# endif /* __cplusplus */


{
EXEC SQL BEGIN DECLARE SECTION;
/* Person information */
char pname[21]; /* Full name */
int page, /* Age of person */
pnumber; /* Unique person number */
int maxid; /* Max person id number to return */
EXEC SQL END DECLARE SECTION;

EXEC SQL DECLARE loadtab CURSOR FOR
SELECT name, age, number
FROM person;

/* Set up error handling for loading procedure */
EXEC SQL WHENEVER SQLERROR GOTO Load_End;
EXEC SQL WHENEVER NOT FOUND GOTO Load_End;

EXEC FRS MESSAGE 'Loading Person Information . . .';

/* Fetch the maximum person id number for later use */
EXEC SQL SELECT max(number)
INTO :maxid
FROM person;

EXEC SQL OPEN loadtab;

while (sqlca.sqlcode == 0)
{
/* Fetch data into record and load table field */
EXEC SQL FETCH loadtab INTO :pname, :page, :pnumber;

EXEC FRS LOADTABLE personfrm persontbl
(name = :pname, age = :page, number = :pnumber);
}

Load_End:
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL CLOSE loadtab;

return maxid;

} /* Load_Table */