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:
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
C Procedure: MAIN
C Purpose: Entry point into Employee Query application.
C
## 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
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.
C
## 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
C Do nothing, and continue with the RETRIEVE loop. The
C last one will drop out.
C
## }
## 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
