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