6. Embedded SQL for BASIC : Sample Applications : The Table Editor Table Field Application
 
Share this page                  
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).
At the start of the application, a database cursor is opened to load the table field with data from the Person table. After loading the table field, you can browse and edit the displayed values. You can add, update, or delete entries. When finished, the values are unloaded from the table field and, in a multi-statement transaction, your updates are transferred back into the Person table.
Also for readability, the BASIC exclamation point (!) is used as an end-of-line comment indicator.
Sample Application
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