5. Embedded SQL for Ada : Ada Variables and Data Types : Variable Usage : Record Variables
 
Share this page                  
Record Variables
You can use a record variable in two different ways. First, you can use the record as a simple variable, implying that all its components are used. 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).
How to Use a Record as a Collection of Variables
The syntax for referring to a complete record is the same as referring to a simple variable:
:recordname
Syntax Notes:
• 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
• In order to be used as a collection of variables, the final record in the reference must have no nested records or arrays. The preprocessor enumerates all the components of the record and they 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.
• 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 but not to discriminants, which it ignores (see the section above on the discriminant constraint), the use of a record with a variant part results in either a "wrong number of values" preprocessor error or a runtime error.
The following example 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 following example inserts values by looping through a locally declared array of records whose elements have been initialized:
exec sql begin declare section;
        exec sql declare person table
              (pname    char(30),
               page     integer1,
               paddr    varchar(50));

        type Person_Rec is record
               name:  String(1..30);
               age:   Short_Short_Integer;
               addr:  String(1..50);
        end record;
        person: array(1..10) of Person_Rec;
exec sql end declare section;
                ...

for i in 1..10 loop
    exec sql insert into person
                values (:person(i));
end loop;
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);
Record Components Usage
The syntax Embedded SQL uses to refer to a record component is the same as in Ada:
:record.component{.component}
Syntax Notes:
• 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
• You must fully qualify all record components when referenced. You can shorten the qualification by using the Ada renames clause in another declaration to rename some components or nested records.
The following example uses the array of emprec records to load values into the emptable tablefield in empform form.
exec sql begin declare section;
        type Employee_Rec is
                record
                    ename:    String(1..20);
                    eage:     Short_Integer;
                    eidno:    Integer;
                    ehired:   String(1..25);
                    edept:    String(1..10);
                    esalary:  Float;
                end record;
          emprec: array(1..100) of Employee_Rec;
    exec sql begin declare section;
        ...

for i in 1..100 loop
        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 loop;
If you want to shorten the reference to the record, you can use the renames clause to rename a particular member of the emprec array, as in the following example:
for i in 1..100 loop
    declare
          exec sql begin declare section;
                er: Employee_Rec renames emprec(i);
          exec sql end declare section;
    begin
          exec frs loadtable empform emptable
             (name = :er.ename, age = :er.eage,
              idno = :er.eidno, hired = :er.ehired,
          dept = :er.edept, salary = :er.esalary);
    end;
end loop;