The Scope of Variables
All constants, types, and variables declared in an Embedded SQL declaration section can be referenced, and are accepted by the preprocessor, from the point of declaration to the end of the file, regardless of the Pascal scope of the declaration. This holds true for local variables and formal parameters. Once an object has been declared to Embedded SQL, it should not be re-declared to Embedded SQL for use in a different Pascal scope; the preprocessor will use the type information supplied by the original declaration. The object must, however, be re-declared to Pascal in the second scope to avoid errors from the Pascal compiler.
In the following program fragment, the variable "dbname" is passed as a parameter to the second procedure. In the first procedure, "dbname" is a local variable. In the second procedure, it 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
program Decl_Test( input, output );
exec sql include sqlca;
exec sql begin declare section;
type
String15 = packed array[1..15] of Char;
exec sql end declare section;
procedure Open_Db( dbname: String15 ); forward;
procedure Access_Db;
exec sql begin declare section;
var
dbname: String15;
exec sql end declare section;
begin
{"Dbname" is local to this procedure.}
exec frs prompt ('Database: ', :dbname);
Open_Db( dbname );
Process_Db;
end;
{ procedure Open_Db(dbname: String15); }
procedure Open_Db;
begin
exec sql whenever sqlerror stop;
{"Dbname" is known from the local declaration
in " Access_Db".}
exec sql connect :dbname;
...
end;
begin {Decl_Test}
...
Access_Db;
...
end. {Decl_Test}
Note that you can declare record components with the same name if they are in 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 = record
firstname: varying[20] of Char;
lastname: varying[20] of Char;
age: Integer;
end;
Mother = record
firstname: varying[20] of Char;
lastname: varying[20] of Char;
num_child: 1..10;
children: array[1..10] of Child;
end;
exec sql end declare section;
Special care should be taken when using variables with a declare cursor statement. The scope of 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 Pascal compiler at the point that the open is issued.
program Bad_Cursors( input, output );
{This example contains an error}
procedure Init_Csr1 (num: Integer);
exec sql begin declare section;
var
number: Integer;
exec sql end declare section;
begin
number := num;
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;
exec sql begin declare section;
var
ename: varying[15] of Char;
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}
begin
...
end. {Bad_Cursors}