Variable Usage
Ada variables declared to Embedded SQL can substitute for many 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 an Ada 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;
When referencing a variable, you cannot use an Ada attribute, because the attribute is introduced by a single quote. Embedded SQL treats this single quote as the beginning of a string literal and generates a syntax error.
Various rules and restrictions apply to the use of Ada 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:
• If you use the variable to send data to Ingres, it can be any scalar-valued variable, constant, or enumerated literal.
• If you use the variable to receive data from Ingres, it cannot be a variable declared with the constant clause, a formal parameter that does not specify the out mode, a number declaration, or an enumerated literal.
• A string variable (a 1-dimension array of characters) is referenced as a simple variable.
The following program fragment demonstrates a typical message-handling routine that uses two scalar-valued variables, "buffer" and "seconds":
procedure Msg
exec sql begin declare section;
(buffer: String; seconds: Integer)
exec sql end declare section;
is
begin
exec frs message :buffer;
exec frs sleep :seconds;
end Msg;
A special case of a scalar type is the enumerated type. Embedded SQL treats all enumerated literals and any variables declared with an enumerated type as integers. When an enumerated literal is used in an Embedded SQL statement, only the ordinal position of the value in relation to the original enumerated list is relevant. When assigning from an enumerated variable or literal, Embedded SQL generates the following:
enumerated_type_name'pos(enumerated_variable_or_literal)
When assigning from or into an enumerated variable, the preprocessor passes the object by address and assumes that the value being assigned from or into the variable does not raise a runtime constraint error. In order to relax the restriction imposed by the preprocessor on enumerated literal assignments (of enumerated types that have included representation clauses to modify their values), you should assign the literal to a variable of the same enumerated type before using it in an embedded statement. For example, the following enumerated type declares the states of a table field row, and the variable of that type always receives one of those values:
exec sql begin declare section;
type Table_Field_States is
(undefined, newrow, unchanged, changed, deleted);
tbstate: Table_Field_States := undefined;
ename: String(1..20);
exec sql end declare section;
...
exec frs getrow empform employee (:ename = name,
:tbstate = _state);
case tbstate is
when undefined =>
...
end case;
Another example retrieves the value TRUE (an enumerated literal of type boolean) into a variable when a database qualification is successful:
exec sql begin declare section;
found: Boolean;
name: String(1..30);
exec sql end declare section;
...
exec sql select :true
into :found
from personnel
where ename = :name;
if (not found) then
...
end if;
Note that a colon precedes the Ada enumerated literal "TRUE." The colon is required before all named Ada 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})
Syntax Notes:
• You must subscript the variable because only scalar-valued elements (integers, floating-point, and character strings) are legal Embedded SQL values.
• When you declare the array, the Embedded SQL preprocessor does not parse the array bounds specification. Consequently, the preprocessor accepts illegal bounds values. Also, when you reference an array, the subscript is not parsed, allowing you to use illegal subscripts. The preprocessor only confirms that you used an array subscript for an array variable. You must make sure that the subscript is legal and that you used the correct number of indices.
• A character string variable is not an array and cannot be subscripted in order to reference a single character or a slice of the string. For example, if the following variable were declared:
abc: String(1..3) := "abc";
you could not reference
:abc(1)
to access the character "a". To perform such a task, you should declare the variable as an array of three one-character long strings:
abc: array(1..3) of String(1..1) := ("a","b","c");
Note that you can only declare variables of the Ada character type as a one-dimensional array. When you use a variable of that type, you must not subscript it.
• Arrays of null indicator variables used with record assignments should not include subscripts when referenced.
In the following example, the loop variable "i" is used as a subscript and need not be declared to Embedded SQL, as it is not parsed.
exec sql begin declare section;
formnames: array(1..3) of String(1..8);
exec sql end declare section;
...
for i in 1..3 loop
exec frs forminit :formnames(i);
end loop;
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;
Access Variables
An access variable must qualify another object using the dot operator, and using the same syntax as a record component:
:access.reference
Syntax Notes:
• By the time you reference an access variable, you must fully define the type to which it is pointing. This is true even for access types that were declared to point at incomplete types.
• The final object denoted by the above reference must be a scalar value (integer, floating-point or character string). There can be any combination of arrays, records or access variables, but the last object referenced must be a scalar value.
• If an access variable is pointing at a scalar-valued type, then the qualification must include the Ada .all clause to refer to the scalar value. If you use the .all clause, it must be the last component in the qualification. For example:
exec sql begin declare section;
type Access_Integer is access Integer;
ai: Access_Integer;
exec sql end declare section;
...
ai := new Integer'(2);
exec frs sleep :ai.all;
In the following example, an access type to an employee record is used to load a linked list of values into the Employee database table.
exec sql begin declare section;
type Employee_Rec;
type Emp_Link is access Employee_Rec;
type Employee_Rec is
record
ename: String(1..20);
eage: Short_integer;
eidno: Integer;
enext: Emp_Link;
end record;
elist: Emp_Link;
exec sql end declare section;
...
while (elist <= null) loop
exec sql insert into employee (name, age, idno)
values (:elist.ename, :elist.eage, :elist.eidno);
elist := elist.enext;
end loop;
Indicator Variables
The syntax for referring to an indicator variable is the same as for a simple variable, except that an indicator 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 component that yields a 2-byte integer (short_integer). For example:
ind: Short_Integer; -- Indicator variable
ind_arr: array(1..10) of Short_Integer;
-- Indicator array
:var_1:ind_var
:var_2:ind_arr(2)
• If the host variable associated with the indicator variable is a record, then 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 component of the record, the second element with the second component, and so on. Indicator array elements begin at subscript 1 regardless of the range with which the array was declared.
The following example uses the employee.dcl file generated by DCLGEN and the empind array to retrieve values and nulls into a structure.
exec sql include sqlca;
exec sql begin declare section;
exec sql include 'employee.dcl';
-- See above for description
empind: array(1..10) of short_integer;
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;