The Scope of Variables
All 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. This may not be true for the BASIC compiler, which only allows variables to be referred to in the scope of the program unit in which they were declared. If you have two unrelated subprograms in the same file, each of which contains a variable with the same name to be used by Embedded SQL, you should not re-declare the variable to Embedded SQL. The preprocessor will use the data type information supplied the first time you declared the variable.
In the following program fragment, the variable "dbname" is passed as a parameter between two subroutines. In the first subroutine, the variable is a local variable. In the second subroutine, the variable is a formal parameter passed as a string to be used with the connect statement. In both subroutines, the preprocessor uses the data attributes from the variable's declaration in the first subroutine.
Example: Variable declaration
100 sub Scopes
exec sql include sqlca
exec sql begin declare section
declare string dbname
exec sql end declare section
! Prompt for and read database name
print 'Database: '
input dbname
call open_db(dbname)
...
end sub
200 sub Open_Db(string dbname)
exec sql include sqlca
exec sql whenever sqlerror stop
exec sql connect :dbname
! Declared to SQL in first subroutine
...
end sub
Special care should be taken when using variables in 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 an explicitly declared variable name for the BASIC compiler at the point that the open is issued, possibly resulting in a runtime error. Because BASIC allows implicit variable declarations (although Embedded SQL does not), the compiler itself will not, however, generate an error message.
100 sub Init_Csr ! This example contains an error
exec sql include sqlca
exec sql begin declare section
declare integer number ! a local variable
exec sql end declare section
exec sql declare cursor1 cursor for &
select ename, age &
from employee &
where eno = :number
! initialize "number" to a particular value
...
end sub
200 sub process_csr
exec sql include sqlca
exec sql begin declare section
declare string ename
declare integer eage
exec sql end declare section
exec sql open cursor1
! illegal evaluation of "number"
exec sql fetch cursor1 into :ename, :eage
end sub
Note that you must issue include sqlca statement in each subprogram containing Embedded SQL statements.
Last modified date: 08/28/2024