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-query 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
10 ## declare ingres
! Person information corresponds to "person" table
## declare string pname, ! Full name
## word p_age, ! Age
## integer pnumber, ! Unique person number
## integer persmaxid ! Maximum person id number
! Table field row states
declare byte constant ROW_UNDEF = 0 ! Empty or undefined row
declare byte constant ROW_NEW = 1 ! Appended by user
declare byte constant ROW_UNCHANGE=2 ! Prog loaded,not updated
declare byte constant ROW_CHANGE = 3 ! Prog loaded and updated
declare byte constant ROW_DELETE = 4 ! Deleted by program
! Table field entry information
## declare integer state, ! State of data set row
## recnum, ! Record number
## lastrow ! Last row in table field
! Utility buffers
## declare string search, ! Name to find in loop
## msgbuf, ! Message buffer
## password, ! Password buffer
## respbuf ! Response buffer
! Error handling variables for database updates
## declare integer upd_err, ! Updates error
## upd_rows ! Number of rows updated
declare byte upd_commit ! Commit updates
declare byte save_changes ! (1 = true, 0 = false)
! Start up Ingre and the Ingre/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
goto endprog
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 = 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, p_age = p.age, pnumber = p.number)
## {
## loadtable personfrm persontbl
## (name = pname, age = p_age, number = pnumber)
## }
! Fetch the maximum person id number for later use.
! PERFORMANCE max() will do a sequential scan of table.
## retrieve (persmaxid = 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.
## prompt ("Name of person: ", search)
if (len(search) = 0%) 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 = 'Person "' + search + &
'" not found in table [HIT RETURN] '
## prompt noecho (msgbuf, respbuf)
## }
## activate menuitem "Save", frskey8
## {
## validate field persontbl
save_changes = 1
## breakdisplay
## }
## activate menuitem "Quit", frskey2
## {
save_changes = 0
## breakdisplay
## }
## finalize
if (save_changes = 0%) then
## endforms
## exit
goto endprog
end if
! 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 transaction (for simplicity,
! this transaction does not restart on DEADLOCK error: 4700)
## begin transaction
upd_commit = 1%
! Handle errors in the UNLOADTABLE loop, as we want to
! cleanly exit the loop, after cleaning up the transaction.
## unloadtable personfrm persontbl
## (pname = name, p_age = age,
## pnumber = number, state = _state)
## {
select state
case = ROW_NEW
! Filled by user. Insert with new unique id
persmaxid = persmaxid + 1%
## repeat append to person
## (name = @pname,
## age = @p_age,
## number = @persmaxid);
case = ROW_CHANGE
! Updated by user. Reflect in table
## repeat replace p
## (name = @pname, age = @p_age)
## where p.number = @pnumber
case = 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
case else
! Else UNDEFINED or UNCHANGED
! No updates required.
end select
! 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 = 0%
## message "Aborting updates . . ."
## abort
## endloop
else
if (upd_rows = 0%) then ! May want to stop
msgbuf = 'Person "' + pname + &
'" not updated. Abort all updates?'
## prompt noecho (msgbuf, respbuf)
if (respbuf = "Y" or respbuf = "y") then
upd_commit = 0%
## abort
## endloop
end if
end if
end if
## }
if (upd_commit) then
## end transaction ! Commit the updates
end if
## endforms ! Terminate the Forms and Ingres
## exit
endprog:
## end