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
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
: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}
Syntax Notes:
• 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)