Record Data Items
You can use a record data item (also known as a structure variable) in two different ways. First, you can use the record or a group item in the record as a simple variable, implying that all its elementary items (also known as structure members) are used. This is appropriate in the embedded SQL select, fetch, and insert statements. Second, you can refer to an elementary data item in the record alone.
Use the following syntax to refer to a record or group item:
:{groupname IN | OF }recordname
Alternatively, you can use the following "dot" notation, in which the record or group item is specified from the outer level inwards:
:recordname{.groupname}
Syntax Notes:
• The recordname can refer to either a record or a group item. It can be an element of a table of group items. Any reference that yields a record or group item is acceptable. For example:
* A record or unambiguous group item reference
:EMPREC
* A group item in a table of group items
:EMPREC-TABLE(SUB1)
* A group item subordinate to two group items
:GROUP3 IN GROUP2 IN REC
:REC.GROUP2.GROUP3
• To be used as a collection of variables, the record (or group item) referenced must have no subordinate groups or tables. The preprocessor enumerates all the elements of the record, which must be elementary items. The preprocessor generates code as though the program had listed each elementary item of the record in the order in which it was declared.
• The qualification of a record item can be elliptical; that is, you do not need to specify all the names in the hierarchy in order to reference the item. You must not, however, use an ambiguous reference that does not clearly qualify an item. For example, assume the following declaration:
01 PERSON.
02 NAME.
03 LAST PIC X(18).
03 FIRST PIC X(12).
02 AGE PIC S9(4) USAGE COMP.
02 ADDR PIC X(50).
If the variable NAME was referenced, the preprocessor would assume the reference was to the group item NAME IN PERSON. However, if there also existed the declaration:
01 CHILD.
02 NAME.
03 LAST PIC X(18).
03 FIRST PIC X(12).
02 PARENT PIC X(30).
the reference to NAME would be ambiguous, because it could refer to either NAME IN PERSON or NAME IN CHILD.
The following example uses the employee.dcl file, generated by DCLGEN, to retrieve values into a record.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
* See above for description.
EXEC SQL INCLUDE 'employee.dcl' END-EXEC.
EXEC SQL END DECLARE SECTION END-EXEC.
EXEC SQL SELECT *
INTO :EMPREC
FROM employee
WHERE eno = 123
END-EXEC.
The example above generates code as though the following statement had been issued instead:
EXEC SQL SELECT *
INTO :ENO IN EMPREC, :ENAME IN EMPREC, :AGE IN EMPREC,
:JOB IN EMPREC, :SAL IN EMPREC, :DEPT IN EMPREC
FROM employee
WHERE eno = 123
END-EXEC.
The following example fetches the values associated with all the columns of a cursor into a record:
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
* See above for description.
EXEC SQL INCLUDE 'employee.dcl' END-EXEC.
EXEC SQL END DECLARE SECTION END-EXEC.
EXEC SQL DECLARE empcsr CURSOR FOR
SELECT *
FROM employee
ORDER BY ename
END-EXEC.
...
EXEC SQL FETCH empcsr INTO :EMPREC END-EXEC.
The following example inserts values by looping through a locally declared table of records whose items have been initialized:
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL INCLUDE SQLCA END-EXEC.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
EXEC SQL DECLARE person TABLE
(pname char(30),
page integer1,
paddr varchar(50)) END-EXEC.
01 PERSON-REC.
02 PERSON OCCURS 10 TIMES.
03 NAME PIC X(30).
03 AGE PIC S9(4) USAGE COMP.
03 ADDR PIC X(50).
EXEC SQL END DECLARE SECTION END-EXEC.
01 SUB1 PIC S9(4) USAGE COMP.
PROCEDURE DIVISION.
BEGIN.
* Initialization code.
PERFORM VARYING SUB1 FROM 1 TO 10
UNTIL SUB1 > 10
EXEC SQL INSERT INTO person
VALUES (:PERSON(SUB1))
END-EXEC
END-PERFORM.
The insert statement in the example just shown generates code as though the following statement had been issued instead:
EXEC SQL INSERT INTO person
VALUES (:NAME IN PERSON(SUB1), :AGE IN PERSON(SUB1), :ADDR IN PERSON(SUB1))
END-EXEC