The Table Editor Table Field Application
This EQUEL/FORMS application uses a table field to edit the Person table in the Personnel database. It 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 their use and their interaction with an Ingres database.
The objects used in this application are:
At the start of the application, a retrieve statement is issued to load the table field with data from the Person table. Once the table field has been loaded, the user can browse and edit the displayed values. Entries can be added, updated or deleted. When finished, the values are unloaded from the table field, and, in a multi-statement transaction, the user's updates are transferred back into the Person table.
The following create statement describes the format of the Person database table:
## create person
## (name = c20, { Person name }
## age = i2, { Age }
## number = i4) { Unique id number }
## program TableEdit( input, output );
## type
## String13 = packed array[1..13] of Char;
## String20 = packed array[1..20] of Char;
## String80 = packed array[1..80] of Char;
## Short_Integer = [Word] -32768 .. 32767;
## declare
## procedure Table_Edit;
label
exit_label;
## type
## {Table field row states}
## RowStates = (
## RowUndef, {Empty or undefined row}
## RowNew, {Appended by user}
## RowUnchange, {Loaded by program - not updated}
## RowChange, {Loaded by program and updated}
## RowDelete {Deleted by program}
## );
## var
## {Person information corresponds to "person" table}
## pname: String20; {Full name}
## page: Short_Integer; {Age}
## pnumber: Integer; {Unique person number}
## pmaxid: Integer; {Maximum person id number}
## {Table field entry information}
## state: RowStates; {State of data set row (see above)}
## recnum, {Record number}
## lastrow: Integer; {Lastrow in table field}
{Utility buffers}
## search: String20; {Name to find in search loop}
## msgbuf: String80; {Message buffer}
## password: String13; {Password buffer}
## respbuf: Char; {Response buffer}
{Error-handling variables for database updates}
## upd_err, {Updates error}
## upd_rows: Integer; {Number of rows updated}
upd_commit: Boolean; {Commit updates}
## save_changes: Boolean; {Save changes or Quit}
## begin {Table_Edit}
{
| Start up Ingres and the Ingres/Forms system
| We assume no Ingres errors will happen during
| screen updating
}
## ingres personnel
## forms
{Verify that the user can edit the "person" table}
## prompt noecho ('Password for table editor: ', password)
if (password <> 'MASTER_OF_ALL') then
begin
## message 'No permission for task. Exiting . . .'
## endforms
## exit
goto exit_label;
end;
## message 'Initializing Person Form . . .'
## 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 that will be loaded into the table field, hide
| the unique person number.
}
## inittable personfrm persontbl fill (number = integer)
{
| Load the information from the "person" table into the
| person variables. Also save away the maximum person
| id number.
}
## message 'Loading Person Information . . .'
## range of p IS person
{Fetch data into person record, and load table field}
## retrieve (pname = p.name, page = p.age,
## pnumber = p.number)
## begin
## loadtable personfrm persontbl
## (name = pname, age = page, number = pnumber)
## end {Retrieve}
{
| Fetch the maximum person id number for later use.
| Performance max will do sequential scan of table.
}
## retrieve (pmaxid = max(p.number))
{Display the form and allow runtime editing}
## display personfrm update
## initialize
## {
## | Provide a menu, as well as the system FRS key to scroll
## | to both extremes of the table field. Note that a comment
## | between DISPLAY loop components MUST be marked with a
## }
##
## activate menuitem 'Top', frskey5
## begin
## scroll personfrm persontbl TO 1 {Backward}
## end {Top}
## activate menuitem 'Bottom', frskey6
## begin
## scroll personfrm persontbl to end{Forward}
## end {Bottom}
## activate menuitem 'Remove'
## 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 at least one
| UNDEFINED row in FILL mode.
}
## inquire_frs table personfrm
## (lastrow = lastrow(persontbl))
if (lastrow = 0) then
begin
## message 'Nobody to Remove'
## sleep 2
## resume field persontbl
end;
## deleterow personfrm persontbl {Recorded for later}
## end {Remove}
## activate menuitem 'Find', frskey7
## 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.
}
search := ' ';
## prompt ('Person''s name : ', search)
if (search[1] = ' ') then
## resume field persontbl
## unloadtable personfrm persontbl
## (pname = name, recnum = _record,
## state = _state)
## begin
{Do not compare with deleted rows}
if ((state <> RowDelete) and (pname = search))
then
begin
## scroll personfrm persontbl to recnum
## resume field persontbl
end;
## end; {Unloadtable}
{Fell out of loop without finding name. Issue error.}
msgbuf := 'Person ''' + search +
''' not found in table. [HIT RETURN] ';
## prompt noecho (msgbuf, respbuf)
## end {Find}
## activate menuitem 'Save', frskey8
## begin
## validate field persontbl
save_changes := TRUE;
## breakdisplay
## end {Save}
## activate menuitem 'Quit', frskey2
## begin
save_changes := FALSE;
## breakdisplay
## end {Quit}
## finalize
if (not save_changes) then {Quit application}
begin
## endforms
## exit
goto exit_label;
end;
{
| 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 we must assign a unique person
| id before returning it to the database table. To do
| this, we increment the previously saved maximum id
| number with each APPEND.
}
## message 'Exiting Person Application . . .'
{
| Do all the updates in a multi-statement transaction
| (for simplicity, this transaction does not restart on
| deadlock error).
}
## begin transaction
upd_commit := TRUE;
{
| Handle errors in the UNLOADTABLE loop, as we want to
| cleanly exit the loop, after cleaning up the transaction.
}
## unloadtable personfrm persontbl
## (pname = name, page = age,
## pnumber = number, state = _state)
## begin
case (state) of
RowNew:
begin
{
| Filled by user.
| Insert with new unique id
}
pmaxid := pmaxid + 1;
## repeat append to person
## (name = @pname,
## age = @page,
## number = @pmaxid);
end; {RowNew}
RowChange:
begin
{Updated by user. Reflect in table}
## repeat replace p
## (name = @pname, age = @page)
## where p.number = @pnumber
end; {RowChange}
RowDelete:
begin
{
| Deleted by user, so delete from table.
| Note that only original rows are saved
| by the program, and not rows appended
| at runtime.
}
## repeat delete p
## where p.number = @pnumber
end; {RowDelete}
otherwise
begin
{
| Else UNDEFINED or UNCHANGED
| No updates required.
}
;
end; {Otherwise}
end; {case}
{
| Handle error conditions -
| If an error occurred, then abort the transaction.
| If a no rows were updated then inform user, and
| prompt for continuation.
}
## inquire_equel (upd_err = errorno, upd_rows = rowcount)
if (upd_err> 0) then {Abort on error}
begin
upd_commit := FALSE;
## message 'Aborting updates . . .'
## abort
## endloop
end else if (upd_rows = 0) then {May want to stop}
begin
msgbuf := 'Person ''' + pname +
''' not updated. Abort all updates? ';
## prompt noecho (msgbuf, respbuf)
if ((respbuf = 'Y') or (respbuf = 'y')) then
begin
upd_commit := FALSE;
## abort
## endloop
end;
end;
## end; {unloadtable}
if (upd_commit) then
## end transaction {Commit the updates}
## endforms {Terminate the Forms and Ingres}
## exit
exit_label:
## end; {Table_Edit}
## begin {main}
Table_Edit;
## end. {main}