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:
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.
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 */
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"
* ## 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.
**
* 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
## }
## }
## 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.
VMS: The create statement describing the format of the Employee database table is shown first:
## 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
* Compiled form
## 01 EMPFORM-ID PIC S9(9) USAGE COMP VALUE IS EXTERNAL empform.
* 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 EDEPT PIC X(10).
## 02 ESALARY USAGE COMP-2.
## 02 DISP_IDNO PIC ZZZZZ9.
* 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.
SBEGIN.
* Initialize WHERE clause qualification buffer to be a default
* qualification that is always true
MOVE "1=1" TO WHERE-CLAUSE.
## FORMS
## MESSAGE "Accessing Employee Query Application..."
## INGRES "personnel"
## ADDFORM EMPFORM-ID
## 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
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.
**
* 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 on to 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.
