Requirements and Options for Host Languages › Using SQL in a CA ADS Application › 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.

All data descriptions used by CA ADS are defined within the dictionary.

Bulk Structure Example

The following is an example of a valid bulk structure definition using IDD syntax:

ADD ELEMENT EMP-ID PIC 999.
ADD ELEMENT EMP-NAME PIC X(30).
ADD ELEMENT DEPT-NAME PIC X(30).
ADD ELEMENT BULK-ROW SUB ELEMENTS ARE
    (EMP-ID EMP-NAME DEPT-NAME).
ADD ELEMENT BULK-DATA SUB ELEMENT
    BULK-ROW OCCURS 20.

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.

Restrictions

The following restrictions apply to bulk structures defined for use with CA ADS:

• The following clauses may not appear within the lowest level element definitions:
  • BLANK WHEN ZERO IS ON
  • JUSTIFY IS ON
  • OCCURS
  • (R) indicating redefinition
  • SIGN IS LEADING/TRAILING
  • SYNC
• Indicator variables cannot be defined for elements within the bulk structure
• The bulk structure must be either a record or the first element within the record