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 will 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
## package Compiled_Empform is
## empform: Integer;
pragma import_object( empform );
## end Compiled_Empform;
with Compiled_Empform; use Compiled_Empform;
with Text_Io; use Text_Io;
with Integer_Text_Io; use Integer_Text_Io;
## with equel_forms;
## procedure Employee_Query is
-- Initialize global WHERE clause qualification buffer to
-- be an Ingres default qualification that is
-- always true.
## where_clause: String(1..100) :=
## ('1', '=', '1', others => ' ');
-- Procedure: Build_Query
-- Purpose: Build an Ingres query from the values in the "name" and -- "idno" fields in "empform".
-- Parameters:
-- None
## procedure Build_Query is
## ename: String(1..20);
## eidno: Integer;
-- Query operator table that maps integer values to
-- string query operators.
## operators: array(1..6) of String(1..2) :=
## ("= ", "!", "< ", "> ", "<=", ">=");
-- Operators corresponding to the two fields,
-- that index into the "operators" table.
## opername, operidno: Integer;
## begin
## getform #empform
## (ename = name, opername = getoper(name),
## eidno = idno, operidno = getoper(idno))
-- Fill in the WHERE clause
where_clause := (1..100 => ' ');
if (opername = 0 and operidno = 0) then
-- Default qualification
where_clause(1..3) := "1=1";
elsif (opername = 0 and operidno /= 0) then
-- Query on the "idno" field
where_clause(1..8) :=
"e.idno" & operators(operidno);
put( where_clause(9..100), eidno );
elsif (opername /= 0 and operidno = 0) then
-- Query on the "name" field
where_clause(1..30) :=
"e.name" & operators(opername) &
"""" & ename & """";
else -- (opername /= 0 and operidno /= 0)
-- Query on both fields
where_clause(1..43) :=
"e.name" & operators(opername) &
"""" & ename & """ and " &
"e.idno" & operators(operidno);
put( where_clause(44..100), eidno );
end if;
## end Build_Query;
-- Procedure: Exec_Query
-- Purpose: Given a query buffer, defining a WHER
-- clause issue a RETRIEVE to allow the
-- runtime use to
-- browse the employees found with the given
-- qualification.
-- Parameters:
-- None
## procedure Exec_Query is
## ename: String(1..20); -- Employee data
## eage: Short_Integer;
## eidno: Integer;
## ehired: String(1..25);
## edept: String(1..10);
## esalary: Float;
rows: Boolean := FALSE; -- Were rows found
## begin
-- 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 := TRUE;
-- 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.
null;
## }
## 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 (not rows) then
## message "No rows found for this query"
else
## clear field all
## message "Reset for next query"
end if;
## sleep 2
## end Exec_Query;
# begin
## 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
Build_Query;
Exec_Query;
## }
## activate menuitem "LastQuery"
## {
Exec_Query;
## }
## activate menuitem "End"
## {
## breakdisplay
## }
## finalize
## clear screen
## endforms
## exit
## end Employee_Query;