4. Embedded QUEL for Fortran : Sample Applications : UNIX and VMS--The Employee Query Interactive Forms Application
 
Share this page                  
UNIX and VMS--The Employee Query Interactive Forms Application
This EQUEL/FORMS application uses a form in query mode to view a subset of the Employee table in the Personnel database. An Ingres query qualification is built at runtime using values entered in fields of the form "empform."
The objects used in this application are:
Object
Description
personnel
The program's database environment.
employee
A table in the database, with six columns:
name (c20)
age (i1)
idno (i4)
hired (date)
dept (c10)
salary (money)
empform
A VIFRED form with fields corresponding in name and type to the columns in the Employee database table. The Name and Idno fields are used to build the query and are the only updatable fields. "Empform" is a compiled form.
A display statement drives the application. This statement allows the runtime user to enter values in the two fields that build the query. The Build_Query and Exec_Query procedures make up the core of the query that is run as a result. Note the way the values of the query operators determine the logic that builds the where clause in Build_Query. The retrieve statement encloses a submenu block that allows the user to step through the results of the query.
The retrieved values are not updated, but any employee screen can be saved in a log file using the printscreen statement in the save menu item.
UNIX: The following create statement describes the format of the Employee database table:
##    create employee
##        (name     = c20,     /* Employee name */
##         age      = i1,      /* Employee age */
##         idno     = i4,      /* Unique employee id */
##         hired    = date,    /* Date of hire */
##         dept     = c10,     /* Employee department */
##         salary   = money)   /* Annual salary */


C   Procedure: MAIN
C   Purpose:   Entry point into Employee Query application.

##  program main

##  declare forms
C           Compiled form
    external empfrm
##  integer*4 empfrm
C                For WHERE clause qualification
    character*100 WhereC

C
C   Initialize global WHERE clause qualification buffer to
C   be an Ingres default qualification that is always true
C
    WhereC = '1=1'

##  forms
##  message 'Accessing Employee Query Application . . .'
##  ingres personnel

##  range of e is employee

##  addform empfrm

##  display 'empfrm' query

##  initialize
##  activate menuitem 'Reset'
##  {
##       clear field all
##  }
##  activate menuitem 'Query'
##  {
C               Verify validity of data
##         validate
           call BldQry(WhereC)
           call ExcQry(WhereC)
##  }

##  activate menuitem 'LastQuery'
##  {
            call ExcQry(WhereC)
##  }

##  activate menuitem 'End', frskey3
##  {
##       breakdisplay
##  }
##  finalize

##  clear screen
##  endforms
##  exit
##  end
C  
C   Procedure:  BldQry
C   Purpose:    Build a query from the values in the
C               'name' and 'idno' fields in 'empfrm.'
C   Parameters: WhereC (character string variable to
C               hold WHERE)
C

##  subroutine BldQry(WhereC)

##  declare forms

    character*(*)     WhereC
C            Employee name
##  character*21      Ename
C            Employee identification number
##  integer*4         Eidno
C            Query operators
##  integer*4         nameop, idop
C   Query operator table maps integer values to string query
    operators   character*2  oprtab(6)
    data oprtab/'=', '!=', '<', '>', '<=', '>='/

##  getform empfrm
##       (Ename = name, nameop = getoper(name),
##        Eidno = idno, idop = getoper(idno))

C   Fill in the WHERE clause

       if ((nameop .EQ. 0) .AND. (idop .EQ. 0)) then
          WhereC = '1=1'
       else
C   User entered a query

          WhereC = ' '

          if ((nameop .NE. 0) .AND. (idop .NE. 0)) then
C   Query on both fields
              write (UNIT=WhereC, FMT=100) oprtab(nameop), 
   &                     Ename, oprtab(idop), Eidno 
100           format ('e.name', A2, '"', A21, '" and e.idno', 
   &                     A2, I6 )

          else if (nameop .NE. 0) then

C    Query on the 'name' field. Trailing blanks (A21) not
C    significant because 'name' is type 'C'
C                write (UNIT=WhereC, FMT=110) oprtab(nameop), 
   &                     Ename 
110              format ('e.name', A2, '"', A21, '"' )

          else
C   Query on the 'idno' field
                 write (UNIT=WhereC, FMT=120) oprtab(idop), 
&                       Eidno 
120              format ('e.idno', A2, I6 )

        endif

      endif

## end
C
C    Procedure:  ExcQry
C    Purpose:    Given a query buffer defining a WHERE clause,
C                issue a RETRIEVE to allow the runtime 
C                user to browse the employee
C                found with the given qualification.
C    Parameters: WhereC  
C                - Contains WHERE clause qualification.

##    subroutine ExcQry(WhereC)

##    declare forms

##    character*(*)       WhereC
C            Matches Employee table
C            Employee Name
##    character*21        Ename
C            Employee Age
##    integer*2           Eage
C            Employee Identification Number
##    integer*4           Eidno
C            Employee Hire Date
##    character*26        Ehired
C            Employee Department
##    character*11        Edept
C            Employee Salary
##    real*4              Epay
C            Flag, were any rows found ?
##    integer*4           rows

##    retrieve (Ename = e.name, Eage = e.age, Eidno = e.idno,
##            Ehired = e.hired, Edept = e.dept, Epay = e.salary)
##            where WhereC
##    {
C     Put values on to form and display them
##    putform empfrm
##         (name = Ename, age = Eage, idno = Eidno, hired = Ehired,
##          dept = Edept, salary = Epay)
##    redisplay
##    submenu
##    activate menuitem 'Next', frskey4
##    {

C     Do nothing, and continue with the RETRIEVE loop. The
C     last one will drop out.

##      }

##      activate menuitem 'Save', frskey8
##      {
C     Save screen data in log file
##            printscreen (file = 'query.log')
C     Drop through to next employee
##      }  

##    activate menuitem 'End', frskey3
##      {
C     Terminate the RETRIEVE loop
##        endretrieve
##      }
##    }

##    inquire_equel (rows = ROWCOUNT)
      if (rows .EQ. 0) then
##       message 'No rows found for this query'
      else
##       clear field all
##       message 'No more rows. Reset for next query'
      endif

##    sleep 2

##    end  

VMS: The following create statement describes the format of the Employee database table:
##      create employee
##          (name     = c20,    /* Employee name */
##           age      = i1,     /* Employee age */
##           idno     = i4,     /* Unique employee id */
##           hired    = date,   /* Date of hire */
##           dept     = c10,    /* Employee department */
##           salary   = money)  /* Annual salary */


!     Procedure:   MAIN
!     Purpose:     Entry point into Employee Query application.

##    program main

##    declare forms


      external empfrm        ! Compiled form
##    integer*4 empfrm
      character*100 WhereC   ! For WHERE clause qualification
      !
      ! Initialize global WHERE clause qualification buffer to
      ! be an Ingres default qualification that is 
      ! always true
      !
      WhereC = '1=1'

##    forms
##    message 'Accessing Employee Query Application . . .'
##    ingres personnel
##    range of e is employee

##    addform empfrm

##    display 'empfrm' query
##    initialize

##    activate menuitem 'Reset'
##    {
##          clear field all
##    }

##    activate menuitem 'Query'
##    {
            ! Verify validity of data
##          validate
            call BldQry(WhereC)
            call ExcQry(WhereC)
##    }

##    activate menuitem 'LastQuery'
##    {
            call ExcQry(WhereC)
##    }

##    activate menuitem 'End', frskey3
##    {
##          breakdisplay
##    }
##    finalize

##    clear screen
##    endforms
##    exit
##    end
!
!    Procedure:    BldQry
!    Purpose:      Build a query from the values in the
!                  'name' and 'idno' fields in 'empfrm.'
!    Parameters:   WhereC (character string variable to
!                  hold WHERE)
!

##    subroutine BldQry(WhereC)

##    declare forms

      character*(*)  WhereC
##    character*21   Ename          ! Employee name
##    integer*4      Eidno        ! Employee identification number
##    integer*4      nameop, idop   ! Query operators

      ! Query operator table maps integer values to string query
      ! operators
      character*2 oprtab(6)
      data oprtab/'=', '!=', '<', '>', '<=', '>='/
##    getform empfrm
##         (Ename = name, nameop = getoper(name),
##          Eidno = idno, idop = getoper(idno))

      ! Fill in the WHERE clause

      if ((nameop .EQ. 0) .AND. (idop .EQ. 0)) then
          WhereC = '1=1'
      else
            ! User entered a query
            WhereC = ' '

            if ((nameop .NE. 0) .AND. (idop .NE. 0)) then
                ! Query on both fields
                write (UNIT=WhereC, FMT=100) oprtab(nameop), 
     1               Ename, oprtab(idop), Eidno 
100             format ('e.name', A2, '"', A21, '" and e.idno', 
     1               A2, I6 )

            else if (nameop .NE. 0) then
                ! 
                ! Query on the 'name' field. Trailing blanks 
                ! (A21) not significant because 'name' is type 
                ! 'C'
                write (UNIT=WhereC, FMT=110) oprtab(nameop), 
     1               Ename 
110             format ('e.name', A2, '"', A21, '"' )

            else
                ! Query on the 'idno' field
                write (UNIT=WhereC, FMT=120) oprtab(idop), 
     1                 Eidno 
120             format ('e.idno', A2, I6 )

            endif
      endif

##    end
!
!     Procedure:  ExcQry
!     Purpose:    Given a query buffer defining a WHERE clause,
!                 issue a RETRIEVE to allow the runtime user to 
!                 browse the employee found with the given 
!                 qualification.
!     Parameters: WhereC
!                 - Contains WHERE clause qualification.

##    subroutine ExcQry(WhereC)

##    declare forms
##    character*(*) WhereC ! Matches Employee table
##    character*21  Ename  ! Employee Name
      integer*2     Eage   ! Employee Age
##    integer*4     Eidno  ! Employee Identification Number
##    character*26  Ehired ! Employee Hire Date
##    character*11  Edept  ! Employee Department
##    real*4        Epay   ! Employee Salary
##    integer*4     rows   ! Flag, were any rows found ?
##    retrieve (Ename = e.name, Eage = e.age, Eidno = e.idno,
##            Ehired = e.hired, Edept = e.dept, Epay = e.salary)
##            where WhereC
##    {
              ! Put values on to form and display them
##            putform empfrm
##              (name = Ename, age = Eage, idno = Eidno, hired =
##               Ehired, dept = Edept, salary = Epay)
##            redisplay
##            submenu
##            activate menuitem 'Next', frskey4
##            {
                ! 
                ! Do nothing, and continue with the RETRIEVE
                ! loop. The last one will drop out.
                ! 
##            }
##            activate menuitem 'Save', frskey8
##            {
                ! Save screen data in log file
##              printscreen (file = 'query.log')
                ! Drop through to next employee
##            }    

##            activate menuitem 'End', frskey3
##           {
                ! Terminate the RETRIEVE loop
##          endretrieve
##        }
##    

##    inquire_equel (rows = ROWCOUNT)
      if (rows .EQ. 0) then
##            message 'No rows found for this query'
      else
##            clear field all
##            message 'No more rows. Reset for next query'
      endif

##    sleep 2

##    end