Variable Usage
Pascal variables declared to Embedded SQL 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. To use a Pascal variable (or named constant) in an Embedded SQL statement, you must precede it 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 "idnovar" as an expression in the where clause:
exec sql select name, num
into :namevar, :numvar
from employee
where idno = :idnovar;
You should not use the Pascal type-cast operator (::) in Embedded SQL statements. The preprocessor ignores it and does not change the type of the variable.
Various rules and restrictions apply to the use of Pascal 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, floating-point, or character string) is referred to by the syntax:
:simplename
Syntax Notes:
1. If you use the variable to send data to Ingres, it can be any scalar-valued variable, constant, or enumerated literal.
2. If you use the variable to receive data from Ingres, it can only be a scalar-valued variable.
3. Packed or varying arrays of characters (for example, character strings) are referenced as simple variables.
The following program fragment demonstrates a typical message-handling routine that uses two scalar-valued variables, "buffer" and "seconds":
exec sql begin declare section;
var
buffer : packed array[1..80] of char;
seconds : integer;
exec sql end declare section;
begin
...
exec frs message :buffer;
exec frs sleep :seconds;
end;
A special case of a scalar type is the enumerated type. As mentioned in the section describing declarations, Embedded SQL treats all enumerated literals and any variables declared with an enumerated type as integers. When used in an Embedded SQL statement, only the ordinal position of the value in relation to the original enumerated list is relevant. When assigning into an enumerated variable, Embedded SQL will pass the object by address and assume that the value being assigned into the variable will not raise a runtime error. For example, the following enumerated type declares the states of a table field row, and the variable of that type will always receive one of those values:
exec sql begin declare section;
type
Table_field_states =
(undefined, newrow, unchanged, changed, deleted);
var
tbstate: table_field_states;
ename: varying[20] of char;
exec sql end declare section;
...
tbstate := undefined;
exec frs getrow empform employee
(:ename = name, :tbstate = _state);
case tbstate of
undefined:
...
deleted:
...
end;
Another example retrieves the value TRUE (a predefined constant of type boolean) into a variable when a database qualification is successful:
exec sql begin declare section;
var
found: boolean;
exec sql end declare section;
...
found := false;
exec sql select :true
into :found
from emp
where age > 62;
if not found then
begin
...
end;
Note that a colon precedes the Pascal constant "TRUE." The colon is required before all Pascal named objects—constants and enumerated literals, as well as variables—used in Embedded SQL statements.
Array Variables
An array variable is referred to by the syntax:
:arrayname[subscript{,subscript}] {[subscript{,subscript}]}
Syntax Notes:
1. The variable must be subscripted because only scalar-valued elements (integers, floating-point and character strings) are legal Embedded SQL values.
2. When the array is declared, the array bounds specification is not parsed by the Embedded SQL preprocessor. Consequently, illegal bounds values will be accepted. Also, when an array is referenced, the subscript is not parsed, allowing the use of illegal subscripts. The preprocessor only confirms the use of an array subscript for an array variable. You must make sure that the subscript is legal and that the correct number of indices are used.
3. An array of characters is not a string unless it is packed or varying.
4. A packed or varying array of characters is considered a simple variable, not an array variable, in its usage. It therefore cannot be subscripted in order to reference a single character. For example, assuming the following variable declaration and subsequent assignment:
exec sql begin declare section;
var
abc : packed array[1..3] of char;
exec sql end declare section;
...
abc := 'abc';
you could not reference
:abc[1]
to access the character "a." To perform such a task, you should declare the variable as a plain (not packed or varying) array, as, for example:
exec sql begin declare section;
var
abc : array[1..3] of char;
exec sql end declare section;
...
abc := ('a', 'b', 'c');
5. Arrays of indicator variables used with structure assignments should not include subscripts when referenced.
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).
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:
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 begin declare section;
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);
Record Components Usage
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).
Syntax Notes:
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:
:person[1].age
:person.age
The following example uses the array of records "emprec" to load values into the table field "emptable" in form "empform."
exec sql begin declare section;
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;
Pointer Variables
A pointer variable references an object in the same way as in Pascal—the name of the pointer is followed by a caret (^):
:pointer_name^
Any further referencing required to fully qualify an object, such as a member of a pointed-to record, follows the usual Pascal syntax.
Syntax Notes:
1. The final object denoted by the pointer reference must be a scalar value (integer, floating-point or character string) or a record (if this is a legal simple record reference). There can be any combination of arrays, records or pointer variables, as long as the last object referenced has a scalar value or is a legal simple record.
2. The pointer reference is also used with file type variables (see the under in this chapter).
In the following example, a pointer to an employee record is used to load a linked list of values into the database table "employee":
exec sql begin declare section;
type
EmpLink = ^EmployeeRec;
EmployeeRec = record
ename: packed array [1..20] of Char;
eage: Integer;
eidno: Integer;
enext: EmpLink;
end;
var
elist: EmpLink;
exec sql end declare section;
...
while (elist <> nil) do
begin
exec sql insert into employee (name, age, idno)
values (:elist^.ename, :elist^.eage,
:elist^.eidno);
elist := elist^.enext;
end;
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:
1. The indicator variable can be a simple variable, an array element or a record component that yields a 2-byte integer. The type indicator has already been declared by the preprocessor. For example:
ind_var, ind_arr[5] : Indicator;
:var_1:ind_var
:var_2:ind_arr[2]
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.
3. When using 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 begin at subscript 1, regardless of the lower bound with which the array was declared.
The following example uses the employee.dcl file generated by DCLGEN to retrieve values into a structure and null values into the array "empind".
exec sql include sqlca;
exec sql begin declare section
exec sql include 'employee.dcl';
var
empind : array[1..10] of Indicator;
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;