The Scope of Variables
The preprocessor can reference and accept all variables declared in an Embedded SQL declaration section from the point of declaration to the end of the file, regardless of the Ada scope of the declaration. This holds true for declarations in a package body or specification (even if they are private), formal parameters, and local variables of functions and procedures. Once an object has been declared to Embedded SQL, it must be the same size and type. It must not be re-declared to Embedded SQL for use in a different Ada scope; the preprocessor uses the type information supplied by the original declaration. The object must, however, be re-declared to Ada in the second scope to avoid errors from the Ada compiler.
This restriction means that two package specifications cannot declare two different objects with the same name. The following example generates an error because of the redeclaration of the object "ptr":
package Stack is
exec sql begin declare section;
stack_max: constant := 50;
ptr: Integer range 1..stack_max;
stack_arr: array(1..stack_max) of integer;
exec sql end declare section;
end Stack;
package Employees is
exec sql begin declare section;
ename_arr: array(1..1000) of string(1..20);
ptr: string(1..20);
exec sql end declare section;
end Employees;
In the following program fragment, the variable "dbname" is passed as a parameter to the second procedure. In the first declaration section, the variable is a local variable. In the second procedure, the variable is a formal parameter passed as a string to be used with the connect statement. The declaration of "dbname" as a formal parameter to the second procedure should not occur in an Embedded SQL declaration section. In both procedures, the preprocessor uses the type information from the variable's declaration in the first procedure.
Example: Variable declaration
package Decl_Test is
procedure Open_Db(dbname: in String);
procedure Access_Db;
end Decl_Test;
exec sql include sqlca;
package body Decl_Test is
procedure Access_Db is
exec sql begin declare section;
dbname: String(1..15);
exec sql end declare section;
begin
-- Prompt for database name
put( "Database:" );
get( dbname );
Open_Db( dbname );
...
end Access_Db;
procedure Open_Db (dbname: in String) is
begin
exec sql whenever error stop;
exec sql connect :dbname;
...
end Open_Db;
end Decl_Test;
Note that you can declare record components with the same name but different record types. The following example declares two records, each of which has the components "firstname" and "lastname":
exec sql begin declare section;
type child is
record
firstname: String(1..15);
lastname: String(1..20);
age: Integer;
end record;
type some_childs is array(1..10) of child;
type mother is
record
firstname: String(1..15);
lastname: String(1..20);
num_child: Integer range 1..10;
children: Some_Childs;
end record;
exec sql end declare section;
Special care should be taken when using variables with a declare cursor statement. The variables used in such a statement must also be valid in the scope of the open statement for that same cursor. The preprocessor actually generates the code for the declare at the point that the open is issued and, at that time, evaluates any associated variables. For example, in the following program fragment, even though the variable "number" is valid to the preprocessor at the point of both the declare cursor and open statements, it is not a valid variable name for the Ada compiler at the point that the open is issued.
package Bad_Cursors is -- This example contains an error
procedure Init_Csr1 is
exec sql begin declare section;
number: Integer;
exec sql end declare section;
begin
exec sql declare cursor1 cursor for
select ename, age
from employee
where eno = :number;
-- Initialize "number" to a particular value
...
end Init_Csr1;
procedure Process_Csr1 is
exec sql begin declare section;
ename: String(1..15);
age: Integer;
exec sql end declare section;
begin
-- illegal evaluation of "number"
exec sql open cursor1;
exec sql fetch cursor1 into :ename, :age;
...
end Process_Csr1;
end Bad_Cursors;
If you must use a group of types and variables in multiple subprograms and package bodies, you can put their declarations in a package and explicitly issue with and use clauses before each subprogram or package that uses them. The following example declares two variables inside a package specification. The variables are used by two procedures, each of which must be preceded by the with and use clauses:
package Vars is
exec sql begin declare section;
var1: Integer;
var2: String(1..3);
exec sql end declare section;
end Vars;
with Vars; use Vars; -- Explicit Ada visibility clauses
procedure Read_Vars is
begin
-- Embedded sql statements that retrieve "var1" and
-- "var2"
end Read_Vars;
with Vars; use Vars; -- Explicit ada visibility clauses
procedure Write_Vars is
begin
-- Embedded sql statements that insert "var1"
-- and "var2"
end Write_Vars;