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
## with equel_forms;
## procedure Table_Edit is
-- Person information corresponds to "person" table
## pname: String(1..20); -- Full name
## page: Short_Integer; -- Age
## pnumber: Integer; -- Unique person number
## pmaxid: Integer; -- Maximum person id number
-- Table field row states
ROW_UNDEF: constant := 0;-- Empty or undefined row
ROW_NEW: constant := 1;-- Appended by user
ROW_UNCHANGE: constant := 2;
-- Loaded by program - not updated
ROW_CHANGE: constant := 3;
-- Loaded by program and updated
ROW_DELETE: constant := 4;-- Deleted by program
-- Table field entry information
## state, -- State of data set row (see above)
## recnum, -- Record number
## lastrow: Integer; -- Last row in table field
-- Utility buffers
## search: String(1..20); -- Name to find in search loop
## msgbuf: String(1..80); -- Message buffer
## password: String(1..13); -- Password buffer
## respbuf: String(1..1); -- 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
-- Start up Ingres and the 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
## message "No permission for task. Exiting . . ."
## endforms
## exit
return;
end if;
## 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 = integer4)
-- 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)
## {
## loadtable personfrm persontbl
## (name = pname, age = page, number = pnumber)
## }
-- 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
## {
## scroll personfrm persontbl TO 1 -- Backward
## }
## activate menuitem "Bottom", frskey6
## {
## scroll personfrm persontbl to end -- Forward
## }
## activate menuitem "Remove"
## {
-- 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
## message "Nobody to Remove"
## sleep 2
## resume field persontbl
end if;
## deleterow personfrm persontbl
-- Recorded for later
## }
## activate menuitem "Find", frskey7
## {
-- 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 := (1..20 = ' ');
## prompt ("Person's name : ", search)
if (search(1) = ' ') then
## resume field persontbl
end if;
## unloadtable personfrm persontbl
## (pname = name, recnum = _record,
## state = _state)
## {
-- Do not compare with deleted rows
if (state /= row_delete and pname = search)
then
## scroll personfrm persontbl to recnum
## resume field persontbl
end if;
## }
-- Fell out of loop without finding name.
-- Issue error.
msgbuf := (1..80 = ' ');
msgbuf(1..62) := "Person '" & search &
"' not found in table [HIT RETURN] ";
## prompt noecho (msgbuf, respbuf)
## }
## activate menuitem "Save", frskey8
## {
## validate field persontbl
save_changes := TRUE;
## breakdisplay
## }
## activate menuitem "Quit", frskey2
## {
save_changes := FALSE;
## breakdisplay
## }
## finalize
if (save_changes) then
-- 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.
## 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)
## {
case (state) is
when row_new =>
-- Filled by user. Insert with new unique id
pmaxid := pmaxid + 1;
## repeat append to person
## (name = @pname,
## age = @page,
## number = @pmaxid);
when row_change =>
-- Updated by user. Reflect in table
## repeat replace p
## (name = @pname, age = @page)
## where p.number = @pnumber
when row_delete =>
-- 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
when others =>
-- Else UNDEFINED or UNCHANGED
-- No updates required.
null;
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
upd_commit := FALSE;
## message "Aborting updates . . ."
## abort
## endloop
elsif (upd_rows = 0) then -- May want to stop
msgbuf := (1..80 = ' ');
msgbuf(1..62) :=
"Person '" & pname &
"' not updated. Abort all updates? ";
## prompt noecho (msgbuf, respbuf)
if (respbuf = "Y" or respbuf = "y") then
upd_commit := FALSE;
## abort
## endloop
end if;
end if;
## }
if (upd_commit) then
## end transaction -- Commit the updates
end if;
end if; -- If saving changes
## endforms -- Terminate the FORMS and Ingres
## exit
## end Table_Edit;