Requirements and Options for Host Languages › Using SQL in a COBOL Application Program › Defining Host Variables › Non-bulk Structures and Indicator Arrays

Non-bulk Structures and Indicator Arrays

About Non-bulk Structures

A non-bulk structure is a group element or record which is used to represent a list of host variables within an SQL statement. When reference is made to a non-bulk structure, it is interpreted as a reference to all of the subordinate elements within the structure.

About Indicator Arrays

An indicator array is a group element or record which contains one multiply occurring subordinate element used as an array of indicator variables. Indicator arrays hold indicator values for items within a non-bulk structure.

Format of a Non-bulk Structure

A non-bulk structure consists of two levels:

• The highest level is the structure itself (level 01 through 48)
• The second level consists of elementary or variable length data items (variable length data elements are group elements which consist of a halfword length field followed by a character or graphics field)

The number, type, and order of data items at the lowest level must correspond to the number, data type, and order of column values being retrieved or inserted.

Non-bulk Structure Example

This is an example of a valid non-bulk structure:

EXEC SQL  BEGIN DECLARE SECTION   END-EXEC.
  01  EMP-INFO.
      05  EMP-ID    PIC 999.
      05  EMP-NAME  PIC X(30).
      05  DEPT-NAME PIC X(30).
EXEC SQL  END DECLARE SECTION    END-EXEC.

Format of an Indicator Array

An indicator array consists of two levels:

• The highest level represents the entire array (level 01 through 48)
• The second level is a multiply occurring element that defines a halfword or fullword field

This is an example of a valid indicator array:

EXEC SQL  BEGIN DECLARE SECTION   END-EXEC.
  02  INDS.
    04  IND SQLIND OCCURS 20 TIMES.
EXEC SQL  END DECLARE SECTION    END-EXEC.

Referring to a Non-bulk Structure

A non-bulk structure can be referred to anywhere a list of host variables can be specified:

• The INTO clause of a SELECT or FETCH statement; for example:

  EXEC SQL
    FETCH EMPCURS INTO :EMP-INFO
  END-EXEC.
  

• The VALUES clause of an INSERT statement

Unlike bulk processing, a single SQL statement can contain more than one reference to a non-bulk structure. Each such reference is interpreted as a list of host variable references. The union of all such host variables together with any elementary host variables must correspond to a single result row (or input row, in the case of an INSERT statement).

Referring to an Indicator Array

To associate indicator variables with the elements of the non-bulk structure, the name of an indicator array is specified immediately following the name of the non-bulk structure:

EXEC SQL
  FETCH EMPCURS INTO :INFO :INDS
END-EXEC.

Note: Either the name of the group or its subordinate element may be used to refer to an indicator array.

Association of Indicator Variables and Non-bulk Structure Elements

The number of occurrences in the indicator array need not be the same as the number of elements in the non-bulk structure with which it is used. If there are more indicators than elements, the remaining indicators are ignored, although their contents are not necessarily preserved. If there are fewer indicators than elements, an indicator is associated with each element in the structure until all indicators are assigned. The remaining elements do not have associated indicators. This may result in an error if an attempt is made to return a null value into an element with no associated indicator.

Restrictions

The following COBOL clauses must not appear within a non-bulk structure definition:

• BLANK WHEN ZERO
• JUSTIFIED
• OCCURS
• REDEFINES
• SIGN
• SYNCHRONIZED

Fillers having a character data type may appear within the structure. However, their data content is not preserved across a SELECT or FETCH.

Note: Unless the included table has no nullable columns an INCLUDE TABLE table-name precompiler directive cannot be used to define the non-bulk structure; any nullable column would cause the precompiler to insert an associated indicator variable which makes the structure unusable for reference in the FETCH statement.