Record Variables

You can use a record variable in two different ways. First, you can use the record as a simple variable, implying the use of all its components. This would be appropriate in the Embedded SQL select, fetch and insert statements. Second, you can use a component of a record to refer to a single element. Of course, this component must be a scalar value (integer, floating-point or character string).

The syntax for referring to a complete record is the same as referring to a simple variable:

1. The recordname can refer to a main or nested record. It can be an element of an array of records. Any variable reference that denotes a record is acceptable. For example:

:emprec                   {A simple record}
:record_array[i]          {An element of an array of records}
:record.minor2.minor3     {A nested record at level 3}

2. In order to be used as a collection of variables, the final record in the reference must have no nested records or arrays. All the components of the record will be enumerated by the preprocessor and must have scalar values. The preprocessor generates code as though the program had listed each record component in the order in which it was declared.

3. You must not use a record with a variant part as a complete record. The preprocessor generates explicit references to each of its components, including the components of the variant. Because the preprocessor generates references to all variant components, the use of a record with a variant part results in either a "wrong number of values" preprocessor error or a runtime error.

The example below uses the employee.dcl file generated by DCLGEN to retrieve values into a record.

exec sql begin declare section;
     exec sql include 'employee.dcl';
            {see above for description}
exec sql end declare section;
     ...
exec sql select *
     into :emprec
     from employee
     where eno = 123;

The example above generates code as though the following statement had been issued instead:

exec sql select *
     into     :emprec.eno, :emprec.ename, :emprec.age,
              :emprec.job, :emprec.sal, :emprec.dept
     from employee
     where eno = 123;

The example below fetches the values associated with all the columns of a cursor into a record.

exec sql begin declare section;
     exec sql include 'employee.dcl';
                          {see above for description}
exec sql begin declare section;

exec sql declare empcsr cursor for
     select *
     from employee
     order by ename;
     ...

exec sql fetch empcsr into :emprec;

The example below inserts values by looping through a locally declared array of records whose elements have been initialized:

exec sql declare person table
        (pname         char(30),
         page         integer1,
         paddr         varchar(50));

type
        person_rec = record
        name:         packed array[1..30] of char;
        age:         [byte] -128 .. 127;
        addr:        varying[50] of char;
    end;
var
        person: array[1..10] of person_rec;
exec sql end declare section;
        ...

for i := 1 to 10 do
begin
exec sql insert into person
values (:person[i]);
end;

The insert statement in the example above generates code as though the following statement had been issued instead:

exec sql insert into person
values (:person[i].name, :person[i].age,
:person[i].addr);

The syntax Embedded SQL uses to refer to a record component is the same as in Pascal:

:record_name{^ |[subscript]}.component{^|[subscript]}
{.component{^ | [subscript]}}

that is, the name of the record, followed by any number of pointer dereference operators or array subscripts, followed by one or more field names (with any number of pointer dereference operators or array subscripts attached).

1. The last record component denoted by the above reference must be a scalar value (integer, floating-point or character string). There can be any combination of arrays and records, but the last object referenced must be a scalar value. Thus, the following references are all legal:

{Assume correct declarations for "employee", "person" and other records.}
:employee.sal                {Component of a record}
:person[3].name              {Component of an element of an array}
:rec1.mem1.mem2.age          {Deeply nested component}

2. Any array subscripts or pointer references referred to in the record reference, and not at the very end of the reference, are not checked by the preprocessor. Consequently, both of the following references are accepted, even though one must be wrong, depending on whether "person" is an array:

The following example uses the array of records "emprec" to load values into the table field "emptable" in form "empform."

type
     EmployeeRec = record
            ename: packed array[1..20] of Char;
            eage: [word] -32768 .. 32767;
            eidno: Integer;
            ehired: packed array[1..25] of Char;
            edept: packed array[1..10] of Char;
            esalary: Real;
    end;

var
    emprec: array[1..100] of EmployeeRec;
    i: Integer;
exec sql end declare section;
        ...

for i := 1 to 100 do
begin
     exec frs loadtable empform emptable
        (name = :emprec[i].ename, age = :emprec[i].eage,
         idno = :emprec[i].eidno, hired = :emprec[i].ehired,
         dept = :emprec[i].edept, salary = :emprec[i].esalary);
end;