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:
The application is driven by a display statement that 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 used to build 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.
No updates are performed on the values retrieved, but any particular employee screen may be saved in a log file through the printscreen statement.
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
10 ## declare ingres
## external integer empform
! Program: Employee_Query
! Initialize global WHERE clause qualification buffer
! to be an Ingres default qualification that is always true.
## common(globs) string where_clause = 100
where_clause = '1=1'
## forms
## message "Accessing Employee Query Application . . ."
## ingres personnel
## range of e is employee
## addform empform
## display #empform query
## initialize
## activate menuitem "Reset"
## {
## clear field all
## }
## activate menuitem "Query"
## {
! Verify validity of data
## validate
call Build_Query
call Exec_Query
## }
## activate menuitem "LastQuery"
## {
call Exec_Query
## }
## activate menuitem "End", frskey3
## {
## breakdisplay
## }
## finalize
## clear screen
## endforms
## exit
## end ! main program
! Procedure: Build_Query
! Purpose: Build an Ingres query from the values in the
! "name" and "idno" fields in "empform".
! Parameters:
! None
100 ## sub Build_Query
! Global WHERE clause qualification buffer
## common(globs) string where_clause = 100
## declare string ename, integer eidno
! Query operator table that maps integer values to
! string query operators.
## dim string operators(6)
mat read operators
data "= ", "!=", "< ", "> ", "<=", ">="
110 ! Operators corresponding to the two fields,
! that index into the "operators" table.
## declare integer opername, operidno
## getform #empform
## (ename = name, opername = getoper(name),
## eidno = idno, operidno = getoper(idno))
! Fill in the WHERE clause
if (opername = 0% and operidno = 0%) then
! Default qualification
where_clause = '1=1'
else
if (opername = 0% and operidno <> 0%) then
! Query on the "idno" field
where_clause = 'e.idno' + &
operators(operidno) + str$(eidno)
else
if (opername <> 0% and operidno = 0%) then
! Query on the "name" field
where_clause = 'e.name' + &
operators(opername) + &
'"' + ename + '"'
else ! (opername <> 0% and operidno <> 0%)
! Query on both fields
where_clause = 'e.name' + &
operators(opername) + &
'"' + ename + '" and ' &
+ 'e.idno' + &
operators(operidno) + &
str$(eidno)
end if
end if
end if
## end sub ! Build_Query
! Subroutine: Exec_Query
! Purpose: Given a query buffer, defining a WHERE clause
! issue a RETRIEVE to allow the runtime use to
! browse the employees found with the given
! qualification.
! Parameters:
! None
200 ## sub Exec_Query
! Global WHERE clause qualification buffer
## common(globs) string where_clause = 100
## declare string ename, ! Employee data
## word eage,
## integer eidno,
## string ehired,
## string edept,
## real esalary
declare byte rows ! Were rows found
rows = 0%
! Issue query using WHERE clause
## retrieve (
## ename = e.name, eage = e.age,
## eidno = e.idno, ehired = e.hired,
## edept = e.dept, esalary = e.salary)
## where where_clause
## {
rows = 1%
! Put values up and display them
## putform #empform (
## name = ename, age = eage,
## idno = eidno, hired = ehired,
## dept = edept, salary = esalary)
## 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
## }
## }
if (rows = 0%) then
## message "No rows found for this query"
else
## clear field all
## message "No more rows. Reset for next query"
end if
## sleep 2
## end sub ! Exec_Query