6. Embedded SQL for BASIC : BASIC 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 members are used. This would be appropriate in the Embedded SQL select, fetch, and insert statements. Second, you can use a member of a record to refer to a single element. Of course, this member must be a scalar value (integer, real 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
Note:   
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
:rec_array(i)           ! An element of an array of records
:rec::minor2::minor3    ! A nested record at level 3
To be used as a collection of variables, the final record in the reference must have no nested records, groups, or arrays. The preprocessor will enumerate all the members of the record. The members must have scalar values. The preprocessor generates code as though the program had listed each record member in the order in which it was declared.
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 end 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))
 record person_
    string    name = 30
        word     age
        string    addr = 50
end record

declare person_ person(10)
 declare word i

exec sql end declare section
...

for i=1 to 10
     exec sql insert into person &
        values (:person(i))
 next i
The insert statement in the above example 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 Member Usage
The syntax Embedded SQL uses to refer to a record member is the same as in BASIC:
:record::member{::member}
Note:   
The record member denoted by the above reference must be a scalar value (integer, real 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:
:employee::sal           ! Member of a record
:person(3)::name         ! Member of an element of an array
:rec1::mem2::mem3::age   ! Deeply nested member
All record components must be fully qualified when referenced. Elliptical references, such as references that omit group names, are not allowed.
The following example uses the record "emprec", similar to the record generated by DCLGEN, to put values into the form "empform".
exec sql begin declare section
    record emprec_
        long          idno
        string        ename = 20
        word          age
        string        hired = 25
        double        salary
        string        dept = 10
    end record
    declare emprec_ emprec

exec sql end declare section
    ...

exec frs putform empform &
    (eno = :emprec::idno, ename = :emprec::ename, &
      age = :emprec::age, hired = :emprec::hired, &
      sal = :emprec::salary, dept = :emprec::dept)