Host structure support in the declare section of COBOL embedded SQL applications

Host structure support provides a shorthand for referring to a set of elementary data items in an SQL statement. For example, the following group data item can be used to access some of the columns in the STAFF table of the SAMPLE database:

    01 staff-record. 
        05 staff-id       pic s9(4) comp-5. 
        05 staff-name. 
            49 l          pic s9(4) comp-5. 
            49 d          pic x(9). 
        05 staff-info.
            10 staff-dept pic s9(4) comp-5. 
            10 staff-job  pic x(5). 

Group data items in the declare section can have any of the valid host variable types described previously as subordinate data items. This includes all numeric and character types, as well as all large object types. You can nest group data items up to 10 levels. Note that you must declare VARCHAR character types with the subordinate items at level 49, as in the example shown previously. If they are not at level 49, the VARCHAR is treated as a group data item with two subordinates, and is subject to the rules of declaring and using group data items. In the previous example, staff-info is a group data item, whereas staff-name is a VARCHAR. The same principle applies to LONG VARCHAR, VARGRAPHIC, and LONG VARGRAPHIC. You may declare group data items at any level between 02 and 49.

You can use group data items and their subordinates in four ways:

Method 1.

The entire group may be referenced as a single host variable in an SQL statement:

    EXEC SQL SELECT id, name, dept, job
      INTO :staff-record 
      FROM staff WHERE id = 10 END-EXEC. 

The precompiler converts the reference to staff-record into a list, separated by commas, of all the subordinate items declared within staff-record. Each elementary item is qualified with the group names of all levels to prevent naming conflicts with other items. This is equivalent to the following method.

Method 2.

The second way of using group data items:

   EXEC SQL SELECT id, name, dept, job
     INTO 
     :staff-record.staff-id, 
     :staff-record.staff-name, 
     :staff-record.staff-info.staff-dept, 
     :staff-record.staff-info.staff-job
     FROM staff WHERE id = 10 END-EXEC.

Assuming there are no other host variables with the same names as the subordinates of staff-record, the preceding statement can also be coded as in method 3, eliminating the explicit group qualification.

Method 3.

Here, subordinate items are referenced in a typical COBOL fashion, without being qualified to their particular group item:

   EXEC SQL SELECT id, name, dept, job
     INTO 
     :staff-id, 
     :staff-name, 
     :staff-dept, 
     :staff-job
     FROM staff WHERE id = 10 END-EXEC.

As in pure COBOL, this method is acceptable to the precompiler as long as a given subordinate item can be uniquely identified. If, for example, staff-job occurs in more than one group, the precompiler issues an error indicating an ambiguous reference:

   SQL0088N Host variable "staff-job" is ambiguous.

Method 4.

To resolve the ambiguous reference, you can use partial qualification of the subordinate item, for example:

   EXEC SQL SELECT id, name, dept, job
     INTO 
     :staff-id, 
     :staff-name, 
     :staff-info.staff-dept, 
     :staff-info.staff-job
     FROM staff WHERE id = 10 END-EXEC.

Because a reference to a group item alone, as in method 1, is equivalent to a comma-separated list of its subordinates, there are instances where this type of reference leads to an error. For example:

   EXEC SQL CONNECT TO :staff-record END-EXEC.

Here, the CONNECT statement expects a single character-based host variable. By giving the staff-record group data item instead, the host variable results in the following precompile-time error:

   SQL0087N Host variable "staff-record" is a structure used where 
            structure references are not permitted. 

Other uses of group items that cause an SQL0087N to occur include PREPARE, EXECUTE IMMEDIATE, CALL, indicator variables, and SQLDA references. Groups with only one subordinate are permitted in such situations, as are references to individual subordinates, as in methods 2, 3, and 4 shown previously.