Adabas Native SQL data retrieval statements operate in one of two modes: single-record processing mode and multiple-record processing mode.
The READ ISN statement always operates in single-record mode. The Adabas Native SQL statements in the following list can be used in either single-record processing or multiple-record processing mode:
COMPARE
FIND
FIND COUPLED
HISTOGRAM
READ LOGICAL
READ PHYSICAL SEQUENCE
SORT.
Adabas Native SQL generates the appropriate data declarations and code for multiple-record processing if the keyword FOR is present in the DECLARE clause of the statement (see list above). If the FOR keyword is not coded or if the DECLARE clause is omitted, Adabas Native SQL generates code for single-record processing.
This document covers the following topics:
If single-record processing is to be used, the OPEN, FETCH and CLOSE statements are not required and only the FIND, READ, etc., statement is required. Adabas Native SQL generates executable code from this statement, which must therefore appear in the procedure division of COBOL programs. In FORTRAN, the statement must be included within the executable statements.
Single-record processing should be used if the user needs to access only one record from the file.
Example (single-record processing):
.
.
.
EXEC ADABAS
SELECT PERSON
FROM PERSONNEL
WHERE PERSONNEL-NUMBER = 180001
END-EXEC
DISPLAY NAME FIRST-NAME AGE SEX.
In this example, the program uses the single-record processing method to display data from the record located by the WHERE criterion.
The OPEN, FETCH and CLOSE statements are used for multiple-record processing. The set of records to be processed is determined using a COMPARE, FIND, HISTOGRAM, READ or SORT statement, followed by an OPEN statement. The records are then processed one by one using the FETCH statement, which will normally be coded in a loop. Finally, the CLOSE statement, which releases the ISN list and other Adabas resources, must be issued if the records were located by a FIND, COMPARE or SORT statement, i.e., if an ISN list was created.
It is the FETCH statement that actually reads each record from the database file and retrieves the values in the fields specified in the SELECT clause of the COMPARE, FIND, HISTOGRAM, READ or SORT statement. The OPEN, FETCH and CLOSE statements generate executable Adabas commands, whereas the COMPARE, FIND, HISTOGRAM, READ or SORT statement merely sets up parameter lists for later use.
The keyword FOR must be specified in the DECLARE clause of COMPARE, FIND, HISTOGRAM, READ or SORT in multiple-record processing mode. Using the DECLARE clause, you define a cursor that associates a 'cursor-name' with the statement. Once the cursor has been defined, it may be referred to in the OPEN, FETCH and CLOSE statements. These statements have the following syntax:
cursor-name is the name used in the FIND, READ, SORT, COMPARE or HISTOGRAM statement that was previously declared. The cursor-name provides the link between the parameter-defining statement (FIND, READ, SORT, COMPARE or HISTOGRAM) and the corresponding executable statements (OPEN, FETCH and CLOSE).
Example (multiple-record processing):
.
.
.
.
EXEC ADABAS
DECLARE PERS CURSOR FOR
SELECT PERSON
FROM PERSONNEL
WHERE NAME > = 'BROWN'
END-EXEC
.
.
.
.
EXEC ADABAS
OPEN PERS
END-EXEC
EXEC ADABAS
FETCH PERS
END-EXEC
PERFORM READ-PERSONNEL UNTIL ADACODE = 3.
EXEC ADABAS
CLOSE PERS
END-EXEC
.
.
.
.
READ-PERSONNEL.
DISPLAY NAME FIRST-NAME AGE SEX.
EXEC ADABAS
FETCH PERS
END-EXEC