Was this helpful?
Variable Declarations
The following sections describe variable declarations.
Embedded SQL Variable Declaration Sections
Embedded SQL statements use BASIC variables to transfer data to and from the database or a form into the program. BASIC constants can also be used in those SQL statements transferring data from the program into the database. You must declare BASIC variables, constants, and structure definitions to SQL before using them in any Embedded SQL statements. The preprocessor does not allow implicit variable declarations. For this reason, the "%" and "$" suffixes cannot be used with variable names. BASIC variables are declared to SQL in a declaration section. This section has the following syntax:
exec sql begin declare section
   BASIC variable declarations
exec sql end declare section
Embedded SQL variable declarations are global to the program file from the point of declaration onwards. Multiple declaration sections can be incorporated into a single file, as would be the case when a few different BASIC subprograms issue embedded statements using local variables. Each subprogram can have its own declaration section. For a discussion of the declaration of variables and types that are local to BASIC subprograms, see The Scope of Variables in this chapter.
Reserved Words in Declarations
All Embedded SQL keywords are reserved. Therefore, you cannot declare variables with the same name as ESQL keywords. You can only use them in quoted string literals. These words are:
byte
case
com
common
constant
decimal
dim
dimension
double
dynamic
external
integer
long
map
real
record
single
string
variant
word
The Embedded SQL preprocessor does not distinguish between uppercase and lowercase in keywords. In generating BASIC code, it converts any uppercase letters in keywords to lowercase.
Data Types
The Embedded SQL preprocessor accepts the following elementary BASIC data types. The table below maps these types to their corresponding Ingres type categories. For a description of exact type mapping, see Data Type Conversion in this chapter.
BASIC Type
Ingres Type
string
character
integer
integer
long
integer
word
integer
byte
integer
real
float
single
float
double
float
double
decimal
Because BASIC supports the packed decimal datatype, the Ingres decimal type is mapped to it.  For example, the BASIC packed decmial declarations:
declare decimal pack1
declare decimal (p,s) pack2
correspond to the Ingres decimal types:
decimal (15, 2)
decimal (p,s)
In addition, the preprocessor accepts the BASIC record type in variable declarations, providing the record has been predefined in an Embedded SQL declaration section.
The data types gfloat and hfloat are illegal and will cause declaration errors.
Neither the preprocessor nor the runtime support routines support gfloat or hfloat floating-point arithmetic. Consequently, the precision of floating-point data is less than that which is available in VMS BASIC programs. You should not compile the BASIC source code with the command line qualifiers gfloat or hfloat if you intend to pass those floating-point values to or from Ingres objects.
The following sections discuss the variable declarations and the use of variables in Embedded SQL statements.
String Data Type
The Embedded SQL preprocessor accepts both fixed-length and dynamic string declarations. Strings can be declared using any of the declarations listed later. Note that you can indicate string length only for non-dynamic strings, that is, for string declarations appearing in common, map, or record declarations. For example,
common (globals) string ename = 30
is acceptable, but
declare string bad_str_var = 30 ! length is illegal
will generate an error.
The reference to an uninitialized BASIC dynamic string variable in an embedded statement that assigns the value of that string to Ingres will result in a runtime error because that restriction does not apply to the retrieval of data into an uninitialized dynamic string variable.
Integer Data Type
Embedded SQL/BASIC accepts all BASIC integer data type sizes. It is important that the preprocessor know about integer size because it generates code to load data in and out of program variables. The preprocessor assumes that integer size is four bytes by default. However, you can inform the preprocessor of a non-default integer size by using the -i flag on the preprocessor command line. For detailed information on this flag, see Advanced Processing in this chapter.
You can explicitly override the default size or the preprocessor -i command-line flag by using the BASIC subtype words byte, word, or long in the variable declaration, as these examples illustrate:
declare byte one_byte_int
common (globals) word two_byte_int
external long four_byte_int
These declarations instruct the preprocessor to create integer variables of one, two, and four bytes respectively, regardless of the default setting.
You can use an integer variable with any numeric-valued object to assign or receive numeric data. For example, you can use such a variable to set a field in a form or to select a column from a database table. It can also specify simple numeric objects, such as table field row numbers.
Real Data Type
As with the integer data type, the preprocessor must know the size of real data variables so that these variables can interact with Ingres correctly at runtime. The preprocessor accepts two sizes of real data: 4-byte variables (the default) and 8-byte variables. Again, you can change the default size with a flag on the preprocessor command line—in this case, the -r flag. For detailed information on this flag, see Advanced Processing in this chapter.
You can explicitly override the default size by using the BASIC subtype words single or double in a variable declaration. For example, the following two declarations:
declare single four_byte_real
map (myarea) double eight_byte_real
create real variables of four and eight bytes, respectively, regardless of the default setting.
A real variable can be used in Embedded SQL statements to assign or receive numeric data (both real and integer) to and from database columns, form fields, and table field columns. It cannot be used to specify numeric objects, such as table field row numbers.
Decimal Data Type
The preprocessor accepts variable declarations of the decimal data type. Note that because the current implementation of Ingres does not store data in packed decimal format, Ingres converts the contents of a decimal variable to and from a double at runtime. Therefore, although decimal variables can interact with Ingres, the movement of data at runtime, both before and after database manipulation, can lead to some loss of precision.
Decimal variables can be used in Embedded SQL statements to transmit numeric values to and from database columns, form fields, and table field columns. You cannot, however, use decimal variables with Ingres integer objects, such as table field row numbers.
The default scale and precision for both decimal variables and decimal symbolic constants in EQUEL/BASIC is the BASIC default of (15,2). The preprocessor does not support the BASIC compile flag /decimal_size. Compiling with the flag will not change the default precision and scale of decimal variables as far as the preprocessor is concerned. You should always specify the precision and scale when declaring a decimal variable or constant. For example:
declare decimal (10.4) constant = 1.2345 – Preferred declaration
declare decimal constant = 1.234         – Will use default (15,2) thus
                                           scale is truncated to two places.
Record Data Type
The Embedded SQL preprocessor supports the declaration and use of user-defined record variables. You can declare a variable of type record if you have already defined the record in an Embedded SQL declaration section. Later sections discuss the syntax of record declarations and their use in Embedded SQL statements.
Variable and Constant Declaration Syntax
Embedded SQL/BASIC variables and constants can be declared in a variety of ways when those declarations are in a declare section. The following sections enumerate these declaration statements and describe their syntax.
The Declare Statement
The declare statement for an Embedded SQL/BASIC variable has the following syntax:
declare type identifier [(dimensions)] {, [type] identifier [(dimensions)]}
The declare statement for an Embedded SQL/BASIC constant has the syntax:
declare type constant identifier = literal {, identifier = literal}
Syntax Notes:
If you specify the word constant, the declared constants cannot be targets of Ingres assignments.
The type must be a BASIC type acceptable to the preprocessor (see previous section) or, in the case of variables only, a record type already defined in the Embedded SQL declaration section. Note that the type is mandatory for Embedded SQL/BASIC declarations, because the preprocessor has no notion of a default type. You need only specify the type once when declaring a list of variables of the same type.
The dimensions of an array specification are not parsed by the preprocessor. Consequently, the preprocessor does not check bounds. Note also that the preprocessor will accept an illegal dimension, such as a non-numeric value, but this will later cause BASIC compiler errors.
The following example illustrates the use of the declare statement:
exec sql begin declare section
        declare integer enum, eage, string ename
        declare single constant minsal = 12496.62
        declare real esal(100)
        declare word null_ind ! Null indicator
exec sql end declare section
The Dimension Statement
The dimension statement can be used to declare arrays to the preprocessor. Its syntax is:
dimension | dim type identifier(dimensions) {, [type] identifier (dimensions)}
Syntax Notes:
The type must be a BASIC type acceptable to the preprocessor (see previous section) or a record already defined in the Embedded SQL declaration section. Note that the type is mandatory for Embedded SQL/BASIC declarations because the preprocessor has no notion of a default type. You need only specify the type once when declaring a list of variables of the same type.
The dimensions of an array specification are not parsed by the preprocessor. Consequently, the preprocessor does not check bounds. Note also that the preprocessor will accept an illegal dimension, such as a non-numeric value, but it will later cause BASIC compiler errors. Furthermore, the preprocessor does not distinguish between executable and declarative dimension statements. If you have used the dimension statement to declare an executable array to Embedded SQL/BASIC, subsequent executable dimension statements of the same array in a declaration section will cause a redeclaration error.
The following example illustrates the use of the dimension statement:
exec sql begin declare section
    dim string employee_names(100,20)
                ! declarative DIM statement
    dimension long emp_id(100,2,2)
    dimension double expenses(numdepts)
                ! executable DIM statement

exec sql end declare section
Static Storage Variable Declarations
Embedded ESQL/BASIC supports the BASIC common and map variable declarations. The syntax for a common variable declaration is as follows:
common | com [(com_name)]
              type identifier [(dimensions)] [= str_length]
              {, [type] identifier [(dimensions)] [= str_length]}
The syntax for a map variable declaration is as follows:
map | map dynamic (map_name)
              type identifier [(dimensions)] [= str_length]
              {, [type] identifier [(dimensions)] [= str_length]}
Syntax Notes:
The type must be a BASIC type acceptable to the preprocessor (see previous section) or a record type already defined to Embedded SQL/BASIC. Note that the type is mandatory for Embedded SQL/BASIC declarations because the preprocessor has no notion of a default type. You need only specify the type once when declaring a list of variables of the same type.
The dimensions of an array specification are not parsed by the preprocessor. Consequently, the preprocessor does not check bounds. Note also that the preprocessor will accept an illegal dimension, such as a non-numeric value, but it will later cause BASIC compiler errors.
The string length, if present, must be a simple integer literal.
The com_name or map_name clause is not parsed by the preprocessor. Consequently, the preprocessor will accept common and map areas of the same name in a single declaration section. It will also accept a map dynamic statement whose com_name has not appeared in another map statement. Either of these situations will later cause BASIC compiler errors.
The following example uses the common and map variable declarations:
exec sql begin declare section
    common (globals) string address = 30, integer zip
    map (ebuf) byte eage, string
                ename = 20, single emp_num
    common (globals) integer empid (200)

exec sql end declare section
The External Statement
You can inform Embedded SQL/BASIC of variables and constants declared in an external module. The syntax for a variable is as follows:
external type identifier {, identifier}
The syntax for a constant is as follows:
external type constant identifier {, identifier}
 
Embedded SQL/BASIC applies the same restrictions on type as VAX-11 BASIC.
exec sql begin declare section
    external integer empform, infoform
    external single constant emp_minsal

exec sql end declare section
Record Type Definitions
Embedded SQL/BASIC accepts BASIC record definitions. The syntax of a record definition is:
record identifier
               record_component
              {record_component}
end record [identifier]
where record_component can be any of the following:
type identifier [(dimensions)] [= str_length]
              {, [type] identifier [(dimensions)] [= str_length]}
group_clause
variant_clause
In turn, the syntax of a group_clause is:
group identifier [(dimensions)]
               record_component
              {record_component}
end group [identifier]
The syntax of a variant_clause is:
variant
               case_clause
              {case_clause}
end variant
where case_clause consists of:
case
              record_component
Syntax Notes:
The type must be a BASIC type acceptable to the preprocessor (see previous section) or a record type already defined in the declaration section. Note that the type is mandatory for Embedded SQL/BASIC declarations because the preprocessor has no notion of a default type. You need only specify the type once when declaring a list of variables of the same type.
Use the str_length clause only with record components of type string.
Record definitions must appear before declarations using that record type.
The following example contains record type definitions:
exec sql begin declare section
    record emp_history
            string ename = 30
            group prev_employers(10)
                string comp_name = 30
                real salary
                integer num_years
            end group prev_employers
    end record emp_history
    record emp_sports
            string ename = 30
            variant
                case
                    group golf
                        integer handicap
                        string club_name
                    end group golf
                case
                    group baseball
                        integer batting_avg
                        string team_name
                    end group baseball
                case
                    group tennis
                        integer seed
                        string club_name
                    end group tennis
            end variant
    end record emp_sports

exec sql end declare section
Indicator Variables
An indicator variable is a 2-byte integer variable. There are three possible ways to use them in an application:
In a statement that retrieves data from Ingres, you can use an indicator variable to determine if its associated host variable was assigned a null value.
In a statement that sets data to Ingres, you can use an indicator variable to assign a null to the database column.
In a statement that retrieves character data from Ingres, you can use the indicator variable as a check that the associated host variable was large enough to hold the full length of the returned character string. You can use SQLSTATE to do this. Although you can use SQLCODE as well, it is preferable to use SQLSTATE because SQLCODE is a deprecated feature.
You can declare an indicator using the integer word subtype or, if you used the -i2 preprocessor command line flag, you can declare an indicator as an integer. The following example declares two indicator variables, one a single variable and the other an array of indicators:
declare word ind, ind_arr(10)
When using an indicator variable with a BASIC record, you must declare the indicator variable as an array of 2-byte integers. In the above example, you can use the variable "ind_arr" as an indicator array with a record assignment.
DCLGEN Utility
DCLGEN (Declaration Generator) is a record-generating utility that maps the columns of a database table into a record that can be included in a declaration section.
Use the following command to invoke DCLGEN from the operating system level:
dclgen language dbname tablename filename recordname [-n] [-q]
where
language
Defines the Embedded SQL host language, in this case, ada.
dbname
Defines the name of the database containing the table.
tablename
Defines the name of the database table.
filename
Defines the output file into which the record declaration is placed.
recordname
Defines the name of the BASIC record variable that the command generates. The command generates a record definition named recordname followed by an underscore character (_) and a declaration for a record variable of recordname.
-n
Does not print the DECLARE TABLE statement.
-q
Creates output in QUEL format.
This command creates the declaration file filename, containing a record corresponding to the database table. The file also includes a record statement for the record variable, as well as a declare table statement that serves as a comment and identifies the database table and columns from which the record was generated.
Once the file has been generated, you can use an Embedded SQL include statement to incorporate it into the variable declaration section. The following example demonstrates how to use DCLGEN in a BASIC program.
Assume the Employee table was created in the Personnel database as:
exec sql create table employee
        (eno      smallint not null,
         ename    char(20) not null,
         age      integer1,
         job      smallint,
         sal      decimal not null,
         dept     smallint)
and the DCLGEN system-level command is:
dclgen basic personnel employee employee.dcl emprec
The employee.dcl file created by this command contains a comment and three statements. The first statement is the declare table description of "employee" which serves as a comment. The second statement is a definition of the BASIC record "emprec_". The last statement is a declare statement for the record "emprec". The contents of the employee.dcl file are:
!    Description of table employee from database personnel
    exec sql declare employee table
         (eno     smallint not null,         &
            ename     char(20) not null,       &
            age         integer1,              &
            job         smallint,              &
            sal         decimal not null,      &
            dept    smallint)

    record emprec_
            word     eno
            string   ename = 20
            byte     age
            word     job
            double   sal
            word     dept
    end record
    declare emprec_ emprec
This file should be included, by means of the Embedded SQL include statement, in an Embedded SQL declaration section:
exec sql begin declare section
        exec sql include 'employee.dcl'
exec sql end declare section
You can then use the emprec record in a select, fetch, or insert statement.
DCLGEN and Large Objects
You can use DCLGEN to generate an appropriate declare table statement with BASIC variables for tables that contain long varchar columns. For columns that have a limited length, the variables generated will be identical to the variables generated for the Ingres varchar datatype. For columns with unlimited length, such as:
create table long_obj_table(blob_col long varchar);
DCLGEN will issue an error message and generate a character string variable with zero length. You can modify the length of the generated variable before attempting to use the variable in an application.
For example the following table definition:
create tablelongobj_table
    (long_column long varchar));
results in the following DCLGEN generated output for BASIC compilers that support structures:
exec sql declare long_obj_table table     &
    (long_column long varchar)

record blobs_rec_
    string long column = 0
end record blobs_rec_
declare blobs_rec_ blobs_rec
How to Declare External Compiled Forms
You can precompile your forms in the Visual Forms Editor (VIFRED). This saves the time that would be otherwise required at runtime to extract the form's definition from the database forms catalogs. When you compile a form in VIFRED, VIFRED creates a file in your directory describing the form in the VAX-11 MACRO language. VIFRED prompts you for the name of the file with the MACRO description. After you have created the file, use the following VMS command to assemble it into a linkable object module:
macro filename
This command produces an object file containing a global symbol with the same name as your form. Before the Embedded SQL/FORMS statement addform can refer to this global object, the object must be declared in an Embedded SQL declaration section with the following syntax:
external integer formname
Syntax Notes:
The formname is the actual name of the form. VIFRED gives this name to the address of the global object. The formname is also used as the title of the form in other Embedded SQL/FORMS statements.
The external statement associates the object with the external form definition.
The example below shows a typical form declaration and illustrates the difference between using the form's object definition and the form's name.
exec sql begin declare section
    external integer empform
    ...

exec sql end declare section
    ...
exec frs addform :empform    ! The global object
exec frs display empform     ! The name of the form
    ...
Embedded SQL/BASIC Declarations Example
The following example demonstrates some simple Embedded SQL/BASIC declarations:
exec sql include sqlca
exec sql begin declare section
    declare byte           d_byte ! variables of each data type
    declare word           d_integer2
    declare long           d_integer4
    declare integer        d_integer_def
    declare single         d_real4
    declare double         d_real8
    declare real           d_real_def
    declare decimal(6,2)   d_decimal
    declare string         d_string
    declare integer constant num_depts = 10 ! constant
    common(globs) real e_raise ! static storage variables
    map (ebuf) string ename = 20
    dim string             emp_names(100,30) ! array declarations
    declare integer     dept_id(10)
    common(globs) string e_address(40) = 30
    record person ! Variant record
        byte age
        long flags
        variant
            case
                group emp_list
                    string full_name = 30
                end group
            case
                group emp_directory
                    string firstname = 12
                    string lastname = 8
                end group
        end variant
    end record

declare person p_table(100)           ! Array of records

exec sql include 'employee.dcl'       ! From DCLGEN

external integer empform, deptform    ! Compiled forms
dim word indicators(10)               ! Array of null indicators

exec sql end declare section
Last modified date: 04/03/2024