10 !

    ! Program:     Table_Edit

    ! Purpose:     Main entry point to edit the "person" table in the

    !              database, using a table field.

    !

 

    exec sql include sqlca

    exec sql declare person table             &

        (name    char(20),                   &

         age     smallint,                   &

         number  integer)

 

    exec sql begin declare section

    ! Person information

   declare string p_name             ! Full name

   declare integer p_age             ! Age of person

   declare integer p_number          ! Unique person number

    declare integer maxid             ! Max person id number

    ! Table field entry information

    declare integer state             ! State of data set entry

    declare integer recnum            ! Record number

    declare integer lastrow           ! Last row in table field

 

    ! Utility buffers

    declare string msgbuf             ! Message buffer

    declare string respbuf             ! Response buffer for prompts

    exec sql end declare section

    declare byte update_error             ! Update error from database

    declare byte xact_aborted             ! Transaction aborted

    external integer function Load_Table     ! Function to fill table field

 

    ! Table field row states

    declare byte constant ROWUNDEF     = 0 ! Empty or undefined row

    declare byte constant ROWNEW     = 1 ! Appended by user

    declare byte constant ROWUNCHANGD     = 2 ! Loaded by program, same

    declare byte constant ROWCHANGD     = 3 ! Loaded by program, changed

    declare byte constant ROWDELETE     = 4 ! Deleted by program

    declare byte constant NOTFOUND     = 100 ! SQL value for no rows

    ! 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 (respbuf <> 'MASTER_OF_ALL') then

        exec frs endforms

        exec sql disconnect

        print 'No permission for task. Exiting . . .'

        stop

    end if

 

    ! 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 that will be 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, 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 ! 'Top'

    exec frs activate menuitem 'Bottom'
    exec frs begin
        exec frs scroll personfrm persontbl to end ! Forward
    exec frs end ! 'Bottom'

    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) then
            exec frs message 'Nobody to Remove'
            exec frs sleep 2
            exec frs resume field persontbl
        end if
    exec frs deleterow personfrm persontbl ! Record it later
    exec frs end ! 'Remove'

    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 ('Name of person: ', :respbuf)
        if (respbuf = '') then
            exec frs resume field persontbl
        end if

        exec frs unloadtable personfrm persontbl             &
            (:p_name = name,                                 &
             :recnum = _record,                             &
             :state = _state)
        exec frs begin
        ! Do not compare with deleted rows
        if ((p_name = respbuf) and (state <> ROWDELETE)) then
            exec frs scroll personfrm persontbl to :recnum
            exec frs resume field persontbl
        end if
        exec frs end
        ! Fell out of loop without finding name
        msgbuf =
         'Person "'+respbuf+'" not found in table [HIT RETURN] '
        exec frs prompt noecho (:msgbuf, :respbuf)

    exec frs end ! 'Find'

    exec frs activate menuitem 'Exit'
    exec frs begin
        exec frs validate field persontbl
        exec frs breakdisplay
    exec frs end ! 'Exit'

    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 we
! must assign a unique person id before returning it 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                        &
    (:p_name = name, :p_age = age,                              &
     :p_number = number, :state = _state)
exec frs begin
    ! Appended by user. Insert with new unique id
    if (state = ROWNEW) then
        maxid = maxid + 1
        exec sql insert into person (name, age, number)         &
            values (:p_name, :p_age, :maxid)

    ! Updated by user. Reflect in table
    else
    if (state = ROWCHANGD) then
        exec sql update person set                              &
            name = :p_name, age = :p_age                       &
            where number = :p_number
!
! Deleted by user, so delete from table. Note that only
! original rows are saved by the program, and not rows
! appended at runtime by the user.
!
        else
            if (state = rowdelete) then
                exec sql delete from person                    &
                    where number = :p_number
            end if
        end if
        end if         ! ignore undefined or unchanged - No updates
        !
        ! Handle error conditions -
        ! If an error occurred, then abort the transaction.
        ! If a no rows were updated then inform user, and
        ! prompt for continuation.
        !
        if (sqlcode < 0) then
! SQL error
            exec sql inquire_sql (:msgbuf = errortext)
            exec sql rollback to savept
            update_error = 1
            xact_aborted = 1
            exec frs endloop
        else
            if (sqlcode = NOTFOUND) then
                msgbuf = 'Person "' + p_name + &
                    '" not updated. Abort all updates?'
                exec frs prompt (:msgbuf, :respbuf)
                if (respbuf = 'Y' or respbuf = 'y') then
                    exec sql rollback to savept
                    xact_aborted = 1
                    exec frs endloop
                end if
             end if
         end if
    exec frs end                 ! 'Unloadtable'
    if (xact_aborted = 0) then
        exec sql commit                 ! Commit the updates
    end if
    exec frs endforms             ! Terminate the Forms and Ingres
    exec sql disconnect
    if (update_error = 1) then
        print 'Your updates were aborted because of error:';
        print msgbuf
    end if

    end ! of Table_Edit - Main Program
!
! Function:     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
!

20 function integer Load_Table
    exec sql include sqlca
    !
   ! Declare person information:
   ! The preprocessor already knows that these variables have been
   ! declared from their declarations in the main program.
   !
   declare string p_name             ! Full name
    declare integer p_age             ! Age of person
    declare integer p_number          ! Unique person number
    declare integer maxid             ! Max person id number to return
    exec sql declare loadtab cursor for                      &
        select name, age, number                             &
        from person
    ! Set up error handling for loading procedure
    exec sql whenever sqlerror goto Closeld ! Close loadtab
    exec sql whenever not found goto Closeld ! Close loadtab
    exec frs message 'Loading Person Information . . .'

    maxid = 0
    ! Fetch the maximum person id number for later use
    exec sql select max(number) &
        into :maxid &
        from person
    exec sql open loadtab
        while (sqlcode = 0)
            ! Fetch data into record, and load table field
            exec sql fetch loadtab into :p_name, :p_age, :p_number
            exec frs loadtable personfrm persontbl &
                (name = :p_name, age = :p_age, number = :p_number)
        next
        exec sql whenever sqlerror continue
        Closeld: exec sql close loadtab
        Load_Table = maxid

end function                     ! of Load_Table