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 retrieved values, 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 }
## program Employees;
## type
## String2 = packed array[1..2] of Char;
## String10 = packed array[1..10] of Char;
## String20 = packed array[1..20] of Char;
## String25 = packed array[1..25] of Char;
## VString100 = varying[100] of Char;
## Float = Real;
## Short_Integer = [Word] -32768 .. 32767;
## var
## empform : [External] Integer;
## declare
## procedure Employee_Query;
## var
{Global WHERE clause qualification buffer}
## where_clause: VString100;
{
| Procedure: Build_Query
| Purpose: Build an Ingres query from the values in the
| "name" and "idno" fields in "empform".
| Parameters:
| None
}
## procedure Build_Query;
## type
## opers = array[1..6] of String2;
## var
## ename: String20;
## eidno: Integer;
{
| Query operator table that maps integer values to
| string query operators.
}
## operators: opers;
{
| Operators corresponding to the two fields,
| that index into the "operators" table.
}
## name_op, id_op: Integer;
## begin {Build_Query}
operators := opers ('= ', '!=', '< ', '> ', '<=', '>=');
## getform #empform
## (ename = name, name_op = getoper(name),
## eidno = idno, id_op = getoper(idno))
{Fill in the WHERE clause}
if ((name_op = 0) and (id_op = 0)) then
begin
where_clause := '1=1'; {Default qualification}
end else if ((name_op = 0) and (id_op <> 0)) then
begin
{Query on the "idno" field}
WriteV( where_clause,
'e.idno', operators[id_op],
eidno);
end else if ((name_op <> 0) and (id_op = 0)) then
begin
{Query on the "name" field}
where_clause :=
'e.name' + operators[name_op] +
'"' + ename + '"';
end else { ((name_op <> 0) and (id_op <> 0)) }
begin
{Query on both fields}
WriteV( where_clause,
'e.name', operators[name_op],
'"', ename, '" and ',
'e.idno', operators[id_op],
eidno);
end;
## end; {Build_Query}
{
| Procedure: 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
}
## procedure Exec_Query;
## var
## ename: String20; {Employee data}
## eage: Short_Integer;
## eidno: Integer;
## ehired: String25;
## edept: String10;
## esalary: Float;
## rows: Integer; {Were rows found}
## begin {Exec_Query}
{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
## begin {retrieve}
{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
## begin
{
| Do nothing, and continue with the
| retrieve loop. The last one will
| drop out.
}
## end {Next}
## activate menuitem 'Save', frskey8
## begin
{Save screen data in log file}
## printscreen (file = 'query.log')
{Drop through to next employee}
## end {Save}
## activate menuitem 'End', frskey3
## begin
{Terminate the RETRIEVE loop}
## endretrieve
## end {End}
## end {retrieve}
## inquire_equel (rows = rowcount)
if (rows = 0) then
begin
## message 'No rows found for this query'
end else
begin
## clear field all
## message 'No more rows. Reset for next query'
end;
## sleep 2
## end; {Exec_Query}
## begin {Employee_Query}
## forms
## message 'Accessing Employee Query Application . . .'
## ingres personnel
## range of e is employee
## addform empform
## display #empform query
## initialize
## activate menuitem 'Reset'
## begin
## clear field all
## end {Reset}
## activate menuitem 'Query'
## begin
{Verify validity of data}
## validate
Build_Query;
Exec_Query;
## end {Query}
## activate menuitem 'LastQuery'
## begin
Exec_Query;
## end {LastQuery}
## activate menuitem 'End'
## begin
## breakdisplay
## end {End}
## finalize
## clear screen
## endforms
## exit
## end; {Employee_Query};
## begin {main}
Employee_Query;
## end. {main}