Linux--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. |
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.
For readability, all EQUEL reserved words are in uppercase.
Linux: 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 */
IDENTIFICATION DIVISION.
PROGRAM-ID. EMPLOYEE-QUERY.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
## DECLARE
* For WHERE clause qualification
## 01 WHERE-CLAUSE PIC X(100).
* Query operators
## 01 NAME-OP PIC S9(8) USAGE COMP.
## 01 ID-OP PIC S9(8) USAGE COMP.
* Were rows found?
## 01 ROWS PIC S9(8) USAGE COMP.
## 01 FORM-VALUES.
## 02 ENAME PIC X(20).
## 02 EIDNO PIC S9(8) USAGE COMP.
## 02 EAGE PIC S9(2) USAGE COMP.
## 02 EHIRED PIC X(25).
## 02 ESALARY PIC S9(6)V9(2) USAGE COMP-3.
## 02 DISP-IDNO PIC ZZZZZ9.
## (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 */
IDENTIFICATION DIVISION.
PROGRAM-ID. EMPLOYEE-QUERY.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
## DECLARE
* For WHERE clause qualification
## 01 WHERE-CLAUSE PIC X(100).
* Query operators
## 01 NAME-OP PIC S9(8) USAGE COMP.
## 01 ID-OP PIC S9(8) USAGE COMP.
* Were rows found?
## 01 ROWS PIC S9(8) USAGE COMP.
## 01 FORM-VALUES.
## 02 ENAME PIC X(20).
## 02 EIDNO PIC S9(8) USAGE COMP.
## 02 EAGE PIC S9(2) USAGE COMP.
## 02 EHIRED PIC X(25).
## 02 ESALARY PIC S9(6)V9(2) USAGE COMP-3.
## 02 DISP-IDNO PIC ZZZZZ9.
* Compiled forms are not yet accepted as EXTERNAL due
* to restrictions noted in the chapter
* that describes how to link the RTS with compiled forms.
* Consequently the declarations of external form
* objects and the corresponding ADDFORM statement
* have been commented out and replaced by a CALL
* "add_formname" statement.
## 01 empform PIC S9(9) USAGE COMP-5 IS EXTERNAL.
* Query operator table that maps integer values to string
* query operators
01 OPER-MASKS.
02 FILLER VALUE "= " PIC X(3).
02 FILLER VALUE "!= " PIC X(3).
02 FILLER VALUE "< " PIC X(3).
02 FILLER VALUE "> " PIC X(3).
02 FILLER VALUE "<= " PIC X(3).
02 FILLER VALUE ">= " PIC X(3).
01 OPER-TABLE REDEFINES OPER-MASKS.
02 OPER OCCURS 6 TIMES PIC X(3).
PROCEDURE DIVISION.
EXAMPLE SECTION.
XBEGIN.
* Initialize WHERE clause qualification buffer to be an
* Ingres default qualification that is always true
MOVE "1=1" TO WHERE-CLAUSE.
## FORMS
## MESSAGE "Accessing Employee Query Application..."
## INGRES "personnel"
* to restrictions noted in the chapter
* that describes how to link the RTS with compiled forms.
* Consequently the declarations of external form
* objects and the corresponding ADDFORM statement
* have been commented out and replaced by a CALL
* "add_formname" statement.
## 01 empform PIC S9(9) USAGE COMP-5 IS EXTERNAL.
* Query operator table that maps integer values to string
* query operators
01 OPER-MASKS.
02 FILLER VALUE "= " PIC X(3).
02 FILLER VALUE "!= " PIC X(3).
02 FILLER VALUE "< " PIC X(3).
02 FILLER VALUE "> " PIC X(3).
02 FILLER VALUE "<= " PIC X(3).
02 FILLER VALUE ">= " PIC X(3).
01 OPER-TABLE REDEFINES OPER-MASKS.
02 OPER OCCURS 6 TIMES PIC X(3).
PROCEDURE DIVISION.
EXAMPLE SECTION.
XBEGIN.
* Initialize WHERE clause qualification buffer to be an
* Ingres default qualification that is always true
MOVE "1=1" TO WHERE-CLAUSE.
## FORMS
## MESSAGE "Accessing Employee Query Application..."
## INGRES "personnel"
* ## ADDFORM empform
CALL "add_empform".
## DISPLAY #empform QUERY
## INITIALIZE
## ACTIVATE MENUITEM "Reset"
## {
## CLEAR FIELD ALL
## }
## ACTIVATE MENUITEM "Query"
## {
* Verify validity of data
## VALIDATE
PERFORM BUILD-QUERY THROUGH ENDBUILD-QUERY.
PERFORM EXEC-QUERY THROUGH ENDEXEC-QUERY.
## }
## ACTIVATE MENUITEM "LastQuery"
## {
PERFORM EXEC-QUERY THROUGH ENDEXEC-QUERY.
## }
## ACTIVATE MENUITEM "End", FRSKEY3
## {
## BREAKDISPLAY
## }
## FINALIZE
## ENDFORMS
## EXIT
STOP RUN.
CALL "add_empform".
## DISPLAY #empform QUERY
## INITIALIZE
## ACTIVATE MENUITEM "Reset"
## {
## CLEAR FIELD ALL
## }
## ACTIVATE MENUITEM "Query"
## {
* Verify validity of data
## VALIDATE
PERFORM BUILD-QUERY THROUGH ENDBUILD-QUERY.
PERFORM EXEC-QUERY THROUGH ENDEXEC-QUERY.
## }
## ACTIVATE MENUITEM "LastQuery"
## {
PERFORM EXEC-QUERY THROUGH ENDEXEC-QUERY.
## }
## ACTIVATE MENUITEM "End", FRSKEY3
## {
## BREAKDISPLAY
## }
## FINALIZE
## ENDFORMS
## EXIT
STOP RUN.
**
* Paragraph: BUILD-QUERY
*
* Build a query from the values in the "name and "idno"
* fields in "empform."
**
BUILD-QUERY.
## GETFORM #empform (
## ENAME = name, NAME-OP = GETOPER(name),
## EIDNO = idno, ID-OP = GETOPER(idno)
## )
* Fill in the WHERE clause
MOVE SPACES TO WHERE-CLAUSE.
IF NAME-OP = 0 AND ID-OP = 0 THEN
MOVE "1 = 1" TO WHERE-CLAUSE
ELSE IF NAME-OP NOT = 0 AND ID-OP NOT = 0 THEN
* Query on both fields
MOVE EIDNO TO DISP-IDNO
STRING "e.name " DELIMITED BY SIZE,
OPER(NAME-OP) DELIMITED BY " ",
"""" DELIMITED BY SIZE,
ENAME DELIMITED BY SIZE,
"""" DELIMITED BY SIZE,
" and e.idno " DELIMITED BY SIZE,
OPER(ID-OP) DELIMITED BY " ",
DISP-IDNO DELIMITED BY SIZE INTO WHERE-CLAUSE
ELSE IF NAME-OP NOT = 0 THEN
* Query on the 'name' field
STRING "e.name " DELIMITED BY SIZE,
OPER(NAME-OP) DELIMITED BY " ",
"""" DELIMITED BY SIZE,
ENAME DELIMITED BY SIZE,
"""" DELIMITED BY SIZE INTO WHERE-CLAUSE
ELSE
* Query on the 'idno' field
MOVE EIDNO TO DISP-IDNO
STRING "e.idno " DELIMITED BY SIZE,
OPER(ID-OP) DELIMITED BY " ",
DISP-IDNO DELIMITED BY SIZE INTO WHERE-CLAUSE
END-IF.
ENDBUILD-QUERY.
EXIT.
* Paragraph: BUILD-QUERY
*
* Build a query from the values in the "name and "idno"
* fields in "empform."
**
BUILD-QUERY.
## GETFORM #empform (
## ENAME = name, NAME-OP = GETOPER(name),
## EIDNO = idno, ID-OP = GETOPER(idno)
## )
* Fill in the WHERE clause
MOVE SPACES TO WHERE-CLAUSE.
IF NAME-OP = 0 AND ID-OP = 0 THEN
MOVE "1 = 1" TO WHERE-CLAUSE
ELSE IF NAME-OP NOT = 0 AND ID-OP NOT = 0 THEN
* Query on both fields
MOVE EIDNO TO DISP-IDNO
STRING "e.name " DELIMITED BY SIZE,
OPER(NAME-OP) DELIMITED BY " ",
"""" DELIMITED BY SIZE,
ENAME DELIMITED BY SIZE,
"""" DELIMITED BY SIZE,
" and e.idno " DELIMITED BY SIZE,
OPER(ID-OP) DELIMITED BY " ",
DISP-IDNO DELIMITED BY SIZE INTO WHERE-CLAUSE
ELSE IF NAME-OP NOT = 0 THEN
* Query on the 'name' field
STRING "e.name " DELIMITED BY SIZE,
OPER(NAME-OP) DELIMITED BY " ",
"""" DELIMITED BY SIZE,
ENAME DELIMITED BY SIZE,
"""" DELIMITED BY SIZE INTO WHERE-CLAUSE
ELSE
* Query on the 'idno' field
MOVE EIDNO TO DISP-IDNO
STRING "e.idno " DELIMITED BY SIZE,
OPER(ID-OP) DELIMITED BY " ",
DISP-IDNO DELIMITED BY SIZE INTO WHERE-CLAUSE
END-IF.
ENDBUILD-QUERY.
EXIT.
**
* Paragraph: EXEC-QUERY
*
* 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.
**
EXEC-QUERY.
## RANGE OF e IS employee
## RETRIEVE (EIDNO = e.idno, ENAME = e.name, EAGE = e.age,
## EHIRED = e.hired, ESALARY = e.salary)
## WHERE WHERE-CLAUSE
## {
* Put values onto form and display them
## PUTFORM #empform (
## idno = EIDNO, name = ENAME, age = EAGE,
## hired = EHIRED, 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
## }
## }
* Paragraph: EXEC-QUERY
*
* 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.
**
EXEC-QUERY.
## RANGE OF e IS employee
## RETRIEVE (EIDNO = e.idno, ENAME = e.name, EAGE = e.age,
## EHIRED = e.hired, ESALARY = e.salary)
## WHERE WHERE-CLAUSE
## {
* Put values onto form and display them
## PUTFORM #empform (
## idno = EIDNO, name = ENAME, age = EAGE,
## hired = EHIRED, 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
## }
## }
## INQUIRE_EQUEL (ROWS = ROWCOUNT)
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
ENDEXEC-QUERY.
EXIT.
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
ENDEXEC-QUERY.
EXIT.
Last modified date: 11/09/2022