Requirements and Options for Host Languages › Using SQL in a COBOL Application Program › Defining Host Variables › Defining Bulk Structures

Defining Bulk Structures

A bulk structure is a group element or a record which contains a subordinate array for holding multiple occurrences of input or output values. Bulk structures are used in bulk SELECT, INSERT, and FETCH statements for retrieving or storing multiple rows of data.

Format of a Bulk Structure

A bulk structure consists of three levels:

• The highest level is the structure itself (level 01 through 47).
• The second level is a multiply occurring group item (level 02 through 48).
• The third level consists of elementary or variable length data items (variable length data items are group elements consisting 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.

Bulk Structure Example

The following is an example of a valid bulk structure:

EXEC SQL  BEGIN DECLARE SECTION   END-EXEC.
  02  BULK-DATA.
    04  BULK-ROW OCCURS 20 TIMES.
      05  EMP-ID    PIC 999.
      05  EMP-NAME  PIC X(30).
      05  DEPT-NAME PIC X(30).
EXEC SQL  END DECLARE SECTION    END-EXEC.

Referring to a Bulk Structure

When referring to a bulk structure in a SELECT, FETCH, or INSERT statement, the name of the highest level is used:

EXEC SQL
  FETCH EMPCURS BULK :BULK-DATA
END-EXEC.

Indicator Variables

An indicator variable can be associated with a data item within the structure as follows:

• The indicator variable must immediately follow the data item with which it is associated
• The picture of the indicator variable must be S9(n) where n is between 4 and 8
• The usage of the indicator variable must be SQLIND

  On encountering the SQLIND usage, the precompiler interprets the variable as an indicator associated with the preceding variable. SQLIND is replaced with COMP in the generated source.

Restrictions

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

• BLANK WHEN ZERO
• JUSTIFIED
• OCCURS (except at the second level)
• REDEFINES
• SIGN
• SYNCHRONIZED

Fillers may appear within the structure; however, their data content is not preserved across a bulk SELECT or FETCH.

Using INCLUDE TABLE

A bulk structure can be defined for a given table by using the INCLUDE TABLE statement with a NUMBER OF ROWS clause. The statement in this example will generate a bulk structure capable of holding 20 entries:

EXEC SQL
  INCLUDE TABLE EMPLOYEE NUMBER OF ROWS 20
END-EXEC.