Variable Usage
BASIC variables declared in an Embedded SQL declaration section can substitute for most non key-word elements of Embedded SQL statements. Of course, the variable and its data type must make sense in the context of the element. When you use a BASIC variable in an Embedded SQL statement, you must precede the variable with a colon. You must further verify that the statement using the variable is in the scope of the variable's declaration. As an example, the following select statement uses the variables "namevar" and "numvar" to receive data, and the variable "idno" as an expression in the where clause:
exec sql select ename, eno &
into :namevar, :numvar &
from employee &
where eno = :idno
Various rules and restrictions apply to the use of BASIC variables in Embedded SQL statements. The sections below describe the usage syntax of different categories of variables and provide examples of such use.
Simple Variables
A simple scalar-valued variable (integer, real or character string) is referred to by the syntax:
:simplename
Syntax Notes:
• If you use the variable to send values to Ingres, it can be any scalar-valued variable or constant.
• If you use the variable to receive values from Ingres, it can only be a scalar-valued variable.
• The reference to an uninitialized BASIC dynamic string variable in an embedded statement that assigns the value of that string to Ingres results in a runtime error because an uninitialized dynamic string points at a zero address. This restriction does not apply to the retrieval of data into an uninitialized dynamic string variable.
The following program fragment demonstrates a typical message-handling routine that has two scalar valued variables, "buffer" and "seconds."
100 sub message_handle
exec sql include sqlca
exec sql begin declare section
declare string buffer = 50
declare integer seconds
exec sql end declare section
...
exec frs message :buffer
exec frs sleep :seconds
...
end sub
Array Variables
An array variable is referred to by the syntax:
:arrayname (subscripts)
Syntax Notes:
• You must subscript the variable, because only scalar-valued elements (integers, reals, and character strings) are legal SQL values.
• When you declare the array, the Embedded SQL preprocessor does not parse the array bounds specification. Consequently, the Embedded SQL preprocessor will accept illegal bounds values. Also, when an array is referenced, the subscript is not parsed. The preprocessor confirms only the use of an array subscript with an array variable. You must ensure that the subscript is legal and that the correct number of indices is used.
• Arrays of null indicator variables used with structure assignments should not include subscripts when referenced.
In the following example, a variable is used as a subscript and need not be declared in the declaration section, as it is not parsed.
exec sql begin declare section
declare string formnames(3)
exec sql end declare section
data 'empform', 'deptform', 'helpform'
declare integer i
for i = 1 to 3
read formnames(i)
exec frs forminit :formnames(i)
next i
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)
Indicator Variables
The syntax for referring to an indicator variable is the same as for a simple variable, except that an indicator variable is always associated with a host variable:
:host_variable:indicator_variable
or
:host_variable indicator :indicator_variable
Syntax Notes:
• The indicator variable can be a simple variable, an array element or a record member that yields a 2-byte integer (the word subtype). For example:
dcl word ind_var, ind_arr(5)
:var_1:ind_var
:var_2:ind_arr(2)
• If the host variable associated with the indicator variable is a record, the indicator variable should be an array of 2-byte integers. In this case the array should not be dereferenced with a subscript.
• When you use an indicator array, the first element of the array corresponds to the first member of the record, the second element with the second member, and so on. Indicator array elements generated by the preprocessor begin at subscript 1 and not at subscript 0.
The following example uses the employee.dcl file generated by DCLGEN, to retrieve values into a record and null values into the array "empind".
exec sql include sqlca
exec sql begin declare section
exec sql include 'employee.dcl'
! see above for description
declare word empind(10)
exec sql end declare section
exec sql select * &
into :emprec:empind &
from employee
The above example generates code as though the following statement had been issued:
exec sql select * &
into :emprec::eno:empind(1), :emprec::ename:empind(2), &
:emprec::age:empind(3), :emprec::job:empind(4), &
:emprec::sal:empind(5), :emprec::dept:empind(6), &
from employee
Note that there are three different types of colon qualifiers. The first colon indicates that a host variable is used. The second double-colon indicates that a structure member is used. The third colon is the indicator variable colon.