A record selection expression is used to select a record in the database. It can be used in a FETCH or FIND statement. The record thus selected becomes the current record of the run unit upon which subsequent statements may operate when accessing the database.

Refer also to the Oracle CODASYL DBMS Programming Guide and the Oracle CODASYL DBMS Programming Reference Manual for additional information.

General Formats

Format 1---Database Key Identifier Access

This format selects a record by a database key value held by the Database Control System (DBCS) in a currency indicator or a keeplist entry.

database-key-identifier

identifies a record according to the rules of database key identifiers. (See Section 4.4, Database Key Identifiers for more information.)

For example:

FIND CURRENT WITHIN PART-REC

Format 2---Set Owner Access

This format selects the record that owns a set.

set-name

is a subschema set name. The DBCS uses the currency indicator for set-name to choose the owner record of that set occurrence. A database exception condition occurs if set-name is a singular set (DB-CONDITION is set to DBM$_SINGTYP) or if the currency indicator for the set type is null (DB-CONDITION is set to DBM$_CSTYP_NULL).

Format 3---DB Key Access

This format selects the record that is referred to by the database key contained in the special register DB-KEY.

Format 4---Record Position Access

This format selects a record by its position within a collection of records and optionally by its record type and contents.

int-exp

is an integer or arithmetic expression resulting in a longword integer value. It cannot be zero. It may be an embedded literal or an integer data-name.

realm-name

is a subschema realm name.

record-name

is a subschema record name.

set-name

is a subschema set name.

rec-key

is a key data item within the subschema record occurrence. The same rec-key can appear only once in a given USING phrase.

bool-expression

is a conditional expression that involves data items of the object record. It is used to specify additional requirements of a qualifying record.

bool-alternate

is one or more sub-expressions (simple-bool-relation). Pairs of sub-expressions are joined by the logical operator AND.

simple-bool-relation

is a simple-condition (bool-condit), an expression, or the negation of either.

bool-condition

is a relation involving two operands joined by a relational operator.

Relational operators can be one of the following:

[NOT] EQUAL (=) TO
[NOT] LESS (<) THAN
[NOT] GREATER (>) THAN
GREATER THAN OR EQUAL (>=) TO
LESS THAN OR EQUAL (<=) TO
[NOT] CONTAIN
[NOT] CONTAINS
[NOT] MATCH
[NOT] MATCHES

The relational operator CONTAINS is used to check that a given data item in the record contains the specified string anywhere within it. At least one of the operands must be the identifier of a nondatabase item or a nonnumeric literal; the other operand must be an elementary item in the record being found or fetched.

The relational operator MATCHES checks that a given item in the records matches the specified pattern string. At least one of the operands must be the identifier of a nondatabase item or a nonnumeric literal; the other must be an elementary item in the record being found or fetched.

The pattern string is formed from any character string; however, two characters, the percent sign (%) and the asterisk (*), have special meanings. If the percent sign (%) occurs in the pattern string, it will match the current single character in the item being matched. If the asterisk (*) occurs in the pattern string, it will match any number of characters starting at the current character in the item being matched. For example:

FIND ALL KEEP-1 WHERE PARTDESC MATCHES '*DISK*' FIND ALL KEEP-1 WHERE PARTDESC MATCHES '*F%%'

To match either the percent sign (%) or asterisk (*) in the pattern string, precede it with the caret (that is, to match percent sign (%) use caret and percent (^%), and to match asterisk (*) use caret and asterisk (^*)).

Collection Clause

The reserved word WITHIN with set-name or realm-name is called the collection clause. Use the collection clause to restrict a search to a specific collection of records in the database. If you do not use the collection clause, the DBCS searches through all the records in the database to which you have access.

The collection clause specifies the object collection. The following rules govern its use:

1. When you use WITHIN realm-name, realm records make up the object collection. The realm currency indicator is called the object currency indicator. The ordering of the object collection is unspecified.
2. When you use WITHIN set-name, the member records of the current set of the specified set type make up the object collection. The set type's currency indicator is called the object currency indicator. The ordering of the object collection is the ordering of the records in the current set of the specified set type.
3. If you use neither WITHIN realm-name nor WITHIN set-name, all records defined in the subschema make up the object collection. The object currency indicator defaults to the currency indicator for the run unit. The ordering of the object collection is unspecified.

Qualification Clause

Use record-name to restrict the search to records of a particular type. If you do not use record-name, the DBCS searches each record type in the subschema. You can further restrict the search to records with specific data item values. You do this by specifying the qualification clause. The qualification clause is either the reserved word USING followed by one or more rec-keys, or the reserved word WHERE followed by a Boolean expression. When USING is specified, the DBCS searches for only those database records whose key data items equal the corresponding data items in your user work area. When WHERE is specified, the DBCS searches for database records of the object record type whose item values cause the Boolean expression to evaluate to true.

The qualification clause optionally specifies the following additional rules for determining the object record type, the qualifying record, and the key data item:

1. If record-name is used, its record type is called the object record type.
2. Rec-key must be a data item defined in the subschema database record type, record-name.
3. If the key data item consists of one or more rec-keys, it describes additional criteria for selecting a record.
4. If the USING clause is used, a qualifying record is an occurrence of the object record whose corresponding variables in the user work area (UWA) equal the contents of the key data item.
5. If the WHERE clause is used, each occurrence of the object record type for which the bool-expression is true is called a qualifying record.
6. If you do not use the qualification clause, each occurrence of record-name, regardless of its contents, is called the qualifying record.
7. At least one operand in every relation condition of a WHERE Boolean expression must be an elementary item in the record being found or fetched.

Position Clause

The reserved words FIRST, LAST, NEXT, PRIOR, ANY, DUPLICATE, and RELATIVE make up the position clause. The position clause selects a specific qualifying record in the object collection. The position clause rules follow:

1. Int-exp refers to an embedded integer literal or to an integer variable. Int-exp can be either an integer or an arithmetic expression. Both result in a longword integer value. Int-exp cannot be zero.
2. ANY is equivalent to FIRST.
3. DUPLICATE is equivalent to NEXT.
4. Using the FIRST clause is equivalent to assigning +1 to int-exp. It refers to the first record in the object collection.
5. Using the LAST clause is equivalent to assigning --1 to int-exp. It refers to the last record in the object collection.
6. Using the NEXT clause is equivalent to using RELATIVE +1. It refers to the record in the object collection immediately after the current position.
7. Using the PRIOR clause is equivalent to using RELATIVE --1. It refers to the record in the object collection immediately before the current position.
8. If you use int-exp without the RELATIVE clause, the DBCS selects the record whose ordinal position in the object collection is equal to int-exp.
   • If int-exp is positive, the ordinal position is relative to the first record of the object collection.
   • If int-exp is negative, the ordinal position is relative to the last record of the object collection.
   • If int-exp is zero, a database exception condition occurs.
   • If int-exp is greater than the cardinality of the collection, a database exception condition occurs.
9. If you use the RELATIVE int-exp clause, the selected record is the one whose ordinal position in the object collection, relative to the position specified by the object currency indicator, is equal to int-exp.
   If the object currency indicator does not specify a position in the object collection, it is as though the RELATIVE clause did not appear.
   • If int-exp is positive, the search in the object collection proceeds in the next direction.
   • If int-exp is negative, the search in the object collection proceeds in the prior direction.
   • If int-exp is zero, a database exception condition occurs.
   • If the current position, modified by int-exp, is zero or is greater in magnitude than the cardinality of the collection, a database exception condition occurs.

All Formats

The record selection expression causes a database exception condition to occur if:

1. The selected set currency indicator does not identify a record (DB-CONDITION is set to DBM$_CSTYP_NULL).
2. Int-exp is zero (DB-CONDITION is set to DBM$_BADZERO).
3. The program attempts to find or fetch a record following the last record in the selected collection (DB-CONDITION is set to DBM$_END).
4. The program attempts to find or fetch a record preceding the first record in the selected collection (DB-CONDITION is set to DBM$_END).
5. A realm required to carry out the record selection is not in ready mode (DB-CONDITION is set to DBM$_NOTIP).
6. The bool-exp clause is used and any specified data item is not included in the Subschema Record Description entry for the object record type.

See Section 4.8.3, Exception Conditions and the USE Statement statement for an explanation of DBM$_symbolic constants.

4.7 Set Membership Options and DML Verbs

The Compaq COBOL data manipulation language (DML) verbs CONNECT, DISCONNECT, ERASE, MODIFY, RECONNECT, and STORE can affect a record's set membership. The effects of these verbs depend on the INSERTION and RETENTION clauses declared for the record's membership in each set in the schema.

The member's INSERTION clause determines whether the record is automatically inserted into a set when it is stored:

• If the INSERTION IS AUTOMATIC clause is used, the member is automatically inserted into the set.
• If the INSERTION IS MANUAL clause is used, the member must be manually inserted into the set with a CONNECT statement.

The member's RETENTION clause determines whether the record can be removed from a set with the verbs ERASE, DISCONNECT, and RECONNECT. If the RETENTION IS FIXED clause is used, you cannot remove the record from a set occurrence at all unless you erase the record at the same time. If the RETENTION IS MANDATORY clause is used, you cannot use DISCONNECT to remove the record from a set occurrence; you can use RECONNECT to move it from one occurrence of the set type to another. If the RETENTION IS OPTIONAL clause is used, you can use either DISCONNECT or RECONNECT to remove the record from a set occurrence.

The ERASE statement always removes the erased record from all sets of which it is a member. ERASE also affects sets owned by that record. If you use the ERASE ALL option, all members of sets owned by an erased record are erased in a recursive process. If you use ERASE without the ALL option, the effect on each set member depends on the member's RETENTION clause: FIXED members are erased; OPTIONAL members are not erased (but are removed from the set, since the set is about to vanish). If any members have a RETENTION MANDATORY clause, an exception occurs because they can exist in the database without being members of this set occurrence. However, the Database Control System does not know into which set occurrence the members should be inserted.

The MODIFY statement may reposition a record within the same set occurrence if its sort key for that set is one of the data items being modified.

See the Oracle CODASYL DBMS DML documentation that summarizes the effects of the various verbs on the record directly modified and on any members (ERASE only).

4.8 Programming for Database Exceptions and Error Handling

Your program must contain logic to accommodate exceptions and errors. The items of syntax in Compaq COBOL that are used for this purpose are ON ERROR, AT END, and USE.

4.8.1 Database On Error Condition

The database on error exception condition occurs when the DBCS encounters a database exception condition for any data manipulation language (DML) statement.

The ON ERROR phrase in a DML statement allows the selection of an imperative statement sequence when any database exception condition occurs.

The NOT ON ERROR phrase allows execution of an imperative statement when a database exception condition does not occur.

The format is as follows:

Stment is an imperative statement.

When a database exception condition occurs and the statement contains an ON ERROR phrase:

1. The imperative statement associated with the ON ERROR phrase executes.
2. The NOT ON ERROR phrase, if specified, is ignored.
3. Control is transferred to the end of database statement unless control has been transferred by executing the imperative statement of the ON ERROR phrase.

When a database exception condition occurs and the statement does not contain an ON ERROR phrase:

1. The applicable USE FOR DB-EXCEPTION, if specified, executes.
2. If an applicable USE procedure does not exist, the run unit terminates abnormally.
3. The NOT ON ERROR phrase, if specified, is ignored.

When a database exception condition does not occur:

1. The imperative statement associated with the NOT ON ERROR phrase, if specified, is executed.
2. The ON ERROR phrase, if specified, is ignored.
3. Control is transferred to the end of the database statement unless control has been transferred by executing the imperative statement of the NOT ON ERROR phrase.

Use the ON ERROR phrase to transfer execution control to the associated statements' error handling routine, where your program can supply useful and effective debugging information. (See Section 4.8.3 for examples and Glossary of Oracle DBMS-Related Terms for more information on Oracle CODASYL DBMS Database Special Registers.) The ON ERROR phrase can be part of every DML statement. It allows you to plan the graceful termination of a program that would otherwise terminate abnormally. (In a FETCH or FIND statement, you cannot specify both the ON ERROR and AT END phrases in the same statement.) For example:

PROCEDURE DIVISION. . . . RECONNECT PARTS_RECORD WITHIN ALL ON ERROR DISPLAY "Exception on RECONNECT" PERFORM PROCESS-EXCEPTION. . . . PROCESS-EXCEPTION. DISPLAY "Database Exception Condition Report". DISPLAY " ". DISPLAY "DB-CONDITION = ", DB-CONDITION WITH CONVERSION. DISPLAY "DB-CURRENT-RECORD-NAME = ", DB-CURRENT-RECORD-NAME. DISPLAY "DB-CURRENT-RECORD-ID = ", DB-CURRENT-RECORD-ID WITH CONVERSION. DISPLAY " ". CALL "DBM$SIGNAL". STOP RUN.

4.8.2 At End Condition

An at end condition occurs when a program detects the end of a file. The at end condition may occur as a result of a FETCH or FIND statement execution in your database program. You use the AT END phrase to specify the action your program is to take when an at end condition occurs.

The NOT AT END phrase specifes the action your program takes if an AT END does not occur.

Use the AT END phrase of the FETCH and FIND statements to handle the end of a collection of records condition. Your program will terminate if: (1) an at end condition occurs, (2) the program does not include the AT END phrase, and (3) there is no applicable USE statement.

When an at end condition occurs and the statement contains an AT END phrase:

1. The imperative statement associated with the AT END phrase, if specified, executes.
2. The NOT AT END phrase, if specified, is ignored.
3. Control is transferred to the end of the I/O statement unless control has been transferred by executing the imperative statement of the AT END phrase.

When an at end condition occurs and the statement does not contain an AT END phrase:

1. If the at end condition is associated with a FETCH or FIND statement, an applicable USE FOR DB-EXCEPTION procedure, if specified, executes. If the AT END phrase or USE FOR DB-EXCEPTION procedure is not specified, the run unit terminates abnormally.
2. If the at end condition is associated with a FETCH or FIND statement, DB-CONDITION is set to DBM$_END.
3. The NOT AT END phrase, if specified, is ignored.

When an at end condition (or any other error condition) does not occur:

1. The AT END phrase, if specified, is ignored.
2. The imperative statement associated with the NOT AT END phrase, if specified, is executed.
3. Control is transferred to the end of the I/O statement unless control has been transferred by executing the imperative statement of the NOT AT END phrase.

Planning for exception conditions is an effective way to increase program and programmer productivity. A program with USE statements is more flexible than a program without them. They minimize operator intervention and often reduce or eliminate the time you need to debug and rerun the program.

The USE statement traps unsuccessful run-time Oracle CODASYL DBMS exception conditions that cause the execution of a Declaratives procedure. A Declaratives procedure can:

• Supply useful and effective database debugging information (see Section 2.2 for more information on Oracle CODASYL DBMS Database Special Registers)
• Provide alternate processing paths for specific exception conditions

Two sets of USE statements follow:

• The first set, shown in Example 4-1, consists of a single USE statement. This database USE procedure executes for any and all database exception conditions. If you select this set, it must be the only database USE statement in the Declaratives Section. Its format is:

  USE [ GLOBAL ] FOR DB-EXCEPTION.

  Example 4-1 A Single USE Statement

  PROCEDURE DIVISION. DECLARATIVES. 200-DATABASE-EXCEPTIONS SECTION. USE FOR DB-EXCEPTION. DB-ERROR-ROUTINE. DISPLAY "Database Exception Condition Report". DISPLAY "-------------------------------------------". DISPLAY "DB-CONDITION = ", DB-CONDITION WITH CONVERSION. DISPLAY "DB-CUR-REC-NAME = ", DB-CURRENT-RECORD-NAME. DISPLAY "DB-CURRENT-RECORD-ID = ", DB-CURRENT-RECORD-ID WITH CONVERSION. DISPLAY "DB-CUR-REC-ID = ", DB-CRID. DISPLAY " ". CALL "DBM$SIGNAL". END DECLARATIVES.

• The second set, shown in Example 4-2, consists of one or more Format 1 USE statements, and one Format 2 USE statement.

  Format 1
  

  maximum

  USE [GLOBAL] FOR DB-EXCEPTION ON DBM$_exception-condition [, DBM$_exception-condition]...

  
  A Format 1 database declarative executes whenever a database exception condition occurs and the corresponding DBM$_exception-condition is explicitly stated in the USE statement.

  Format 2
  

  USE [ GLOBAL ] FOR DB-EXCEPTION ON OTHER.

  
  A Format 2 declarative executes whenever a database exception condition occurs and the corresponding DBM$_exception-condition is not explicitly stated in any Format 1 USE statement.

Example 4-2 Multiple USE Statements

PROCEDURE DIVISION. DECLARATIVES. 200-DATABASE-EXCEPTIONS SECTION. USE FOR DB-EXCEPTION ON DBM$_CRELM_NULL, DBM$_CRTYPE_NULL. 200-DATABASE. PERFORM 300-REPORT-DATABASE-EXCEPTIONS. IF DB-CONDITION = ..... GO TO ... IF DB-CONDITION = ..... GO TO ... STOP RUN. 225-DATABASE-EXCEPTIONS SECTION. USE FOR DB-EXCEPTION ON DBM$_DUPNOTALL. 225-DATABASE. PERFORM 300-REPORT-DATABASE-EXCEPTIONS. GO TO ... 250-DATABASE-EXCEPTIONS SECTION. USE FOR DB-EXCEPTION ON OTHER. 250-DATABASE. PERFORM 300-REPORT-DATABASE-EXCEPTIONS. EVALUATE DB-CONDITION WHEN ..... GO TO ... WHEN ..... GO TO ... WHEN ..... GO TO ... WHEN ..... GO TO ... WHEN ..... GO TO ... WHEN ..... GO TO ... WHEN ..... GO TO ... WHEN ..... GO TO ... WHEN ..... GO TO ... WHEN OTHER PERFORM... . STOP RUN. 300-REPORT-DATABASE-EXCEPTIONS. DISPLAY "Database Exception Condition Report". DISPLAY " ". DISPLAY "DB-CONDITION = ", DB-CONDITION WITH CONVERSION. DISPLAY "DB-CUR-REC-NAME = ", DB-CURRENT-RECORD-NAME. DISPLAY "DB-CURRENT-RECORD-ID = ", DB-CURRENT-RECORD-ID DISPLAY " ". CALL "DBM$SIGNAL".