Was this helpful?
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:
Object
Description
personnel
The program's database environment.
person
A table in the database, 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 field includes the hidden column:
    number (integer)
personrec
A local structure, whose members correspond in name and type to columns in the Person table and the persontbl table field.
When the application starts, a database cursor is opened to load the table field with data from the Person table. After 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 the user's updates are transferred back into the Person table.
Sample Application
program Table_Edit( input, output );
exec sql include sqlca;

exec sql declare person table
    (name char(20),     {Person name}
     age smallint,     {Age}
     number integer);     {Unique id number}

exec frs label exit_label;
exec sql begin declare section;
const
        not_found = 100; {SQLCA value for no rows}
type
        String1 = packed array [1..1] of Char;
        String13 = packed array [1..13] of Char;
        String20 = packed array [1..20] of Char;
        String100 = packed array [1..100] of Char;
        Short_Integer = [word] -32768 .. 32767;

        {Table field row states}
        Row_States = (
            row_undef, {Empty or undefined row}
            row_new, {Appended by user}
            row_unchange, {Loaded by program, not updated}
            row_change, {Loaded by program and updated}
            row_delete {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: Row_States; {State of data set row}
        recnum, {Record number}
        lastrow: Integer; {Last row in table field}
        {Utility buffers}
        search: String20; {Name to find in search loop}
        password: String13; {Password buffer}
        msgbuf: String100; {Message buffer}
        respbuf: String1; {Response buffer}
exec sql end declare section;

var
        {Error handling variables for database updates}
        update_error: Boolean; {Error in updates?}
        update_commit: Boolean; {Commit updates}

{
| Load the information from the "person" table into the person variables.
| Also save away the maximum person ID number.
}

function Load_Table : Integer;
        label
            Load_End;

        exec sql begin declare section;
        var
            {Person information}
            pname: String20; {Full name}
            page: Short_Integer; {Age}
            pnumber: Integer; {Unique person number}
            maxid: Integer; {Maximum person id number}
        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;

begin                     {Load_Table}
        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) do
        begin
            {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);
        end;

Load_End:
        exec sql whenever sqlerror continue;
        exec sql close loadtab;

        Load_Table := maxid;
end; {Load_Table}

begin                 {Table_Edit}
        {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;

        update_error := FALSE;
        update_commit := TRUE;

        {Verify that the user can edit the "person" table}
        exec frs prompt noecho
            ('Password for table editor: ', :password);
        if (password <> 'MASTER_OF_ALL') then
        begin
            exec frs message 'No permission for task. Exiting . . .';
            exec frs endforms;
            exec sql disconnect;
            goto exit_label;
        end;
        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 that
        | will be loaded into the table field, hide the unique
        | person number.
        }
        exec frs inittable personfrm persontbl fill (number = integer);

        pmaxid := Load_Table;

        {Display the form and allow runtime editing}
        exec frs display personfrm update;
        exec frs initialize;
        exec frs begin;
            {
            | Provide menu items, as well as system FRS keys,
            | to scroll to both extremes of the table field.
            }
            exec frs scroll personfrm persontbl to 1;
        exec frs end;

exec frs activate menuitem 'Top';
exec frs begin;
        exec frs scroll personfrm persontbl TO 1; {Backward}
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 at least one
        | UNDEFINED row in FILL mode.
        }
    
        exec frs inquire_frs table personfrm
                (:lastrow = lastrow(persontbl));
        if (lastrow = 0) then
        begin
            exec frs message 'Nobody to Remove';
            exec frs sleep 2;
            exec frs resume field persontbl;
        end;

        exec frs deleterow personfrm persontbl; {Recorded for 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.
        }
    
        search := ' ';
        exec frs prompt ('Person''s name : ', :search);
        if (search[1] = ' ') then
            exec frs resume field persontbl;

            exec frs unloadtable personfrm persontbl
                (:pname = name, :recnum = _record, :state = _state);
            exec frs begin;
                {Do not compare with deleted rows}
                if ((state <> row_delete) and (pname = search)) then
                 begin
                    exec frs scroll personfrm persontbl to :recnum;
                    exec frs resume field persontbl;
                 end;
            exec frs end;
            {Fell out of loop without finding name. Issue error.}
            msgbuf := 'Person ''' + search +
                 ''' not found in table [HIT RETURN] ';
            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 person 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;

update_commit := TRUE;

{
| Hard code the error handling in the UNLOADTABLE loop,
| as we want to cleanly exit the loop.
}
exec sql whenever sqlerror continue;

exec frs message 'Exiting Person Application . . .';

exec frs unloadtable personfrm persontbl
        (:pname = name, :page = age,
          :pnumber = number, :state = _state);
exec frs begin;
        case state of
            row_new:
            begin
                {Filled by user. Insert with new unique id.}
                pmaxid := pmaxid + 1;
                exec sql insert into person (name, age, number)
                    values (:pname, :page, :pmaxid);
            end;

            row_change:
            begin
                {Updated by user. Reflect in table.}
                exec sql update person set
                    name = :pname, age = :page
                    where number = :pnumber;
            end;

            row_delete:
            {
            | Deleted by user, so delete from table. Note that
            | only original rows, not rows appended at runtime,
            | are saved by the program.
            }
            exec sql delete from person
                where number = :pnumber;

            otherwise
            {
            | Else UNDEFINED or UNCHANGED --
            | No updates required.
            }
            ;
        end; {case}
        {
        | 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) then {Error}
    begin
        exec sql inquire_sql (:msgbuf = errortext);
        exec sql rollback to savept;
        update_error := true;
        update_commit := false;
        exec frs endloop;
    end else if (sqlca.sqlcode = NOT_FOUND) then
    begin
        msgbuf := 'Person " + pname +
             " not updated. Abort all updates? ';
        exec frs prompt noecho (:msgbuf, :respbuf);
        if ((respbuf = 'Y') or (respbuf = 'y')) then
        begin
            update_commit := false;
            exec sql rollback to savept;
            exec frs endloop;
        end;
    end;
    exec frs end;

    if (update_commit) then
        exec sql commit; {Commit the updates}
    exec frs endforms; {Terminate the FORMS and INGRES}
    exec sql disconnect;

    if (update_error) then
    begin
        writeln( 'Your updates were aborted because of error:' );
        writeln( msgbuf );
    end;
exit_label:;
exec frs end. {Table_Edit}
Last modified date: 08/28/2024