The following sections describe variable declarations.

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:

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.

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:

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.

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.

Because BASIC supports the packed decimal datatype, the Ingres decimal type is mapped to it.  For example, the BASIC packed decmial declarations:

correspond to the Ingres decimal types:

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.

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,

is acceptable, but

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.

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:

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.

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:

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.

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:

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.

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 for an Embedded SQL/BASIC variable has the following syntax:

The declare statement for an Embedded SQL/BASIC constant has the syntax:

The following example illustrates the use of the declare statement:

The dimension statement can be used to declare arrays to the preprocessor. Its syntax is:

The following example illustrates the use of the dimension statement:

Embedded ESQL/BASIC supports the BASIC common and map variable declarations. The syntax for a common variable declaration is as follows:

The syntax for a map variable declaration is as follows:

The following example uses the common and map variable declarations:

You can inform Embedded SQL/BASIC of variables and constants declared in an external module. The syntax for a variable is as follows:

The syntax for a constant is as follows:

 

Embedded SQL/BASIC applies the same restrictions on type as VAX-11 BASIC.

Embedded SQL/BASIC accepts BASIC record definitions. The syntax of a record definition is:

where record_component can be any of the following:

type identifier [(dimensions)] [= str_length]
              {, [type] identifier [(dimensions)] [= str_length]}

In turn, the syntax of a group_clause is:

The syntax of a variant_clause is:

where case_clause consists of:

The following example contains record type definitions:

An indicator variable is a 2-byte integer variable. There are three possible ways to use them in an application:

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:

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 (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:

where

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:

and the DCLGEN system-level command is:

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:

This file should be included, by means of the Embedded SQL include statement, in an Embedded SQL declaration section:

You can then use the emprec record in a select, fetch, or insert statement.

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:

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:

results in the following DCLGEN generated output for BASIC compilers that support structures:

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:

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:

The example below shows a typical form declaration and illustrates the difference between using the form's object definition and the form's name.

The following example demonstrates some simple Embedded SQL/BASIC declarations: