Embedded SQL/Pascal Declarations
The following sections describe SQL/Pascal declarations.
Embedded SQL Variable Declaration Sections
Embedded SQL statements use Pascal variables to transfer data from the database or a form into the program and vice versa. You must declare Pascal variables and constants to Embedded SQL before using them in any Embedded SQL statements. Pascal variables, types, and constants are declared to Embedded SQL in a declaration section. This section has the following syntax:
exec sql begin declare section;
Pascal constant, type and variable declarations
exec sql end declare section;
Note that placing a label in front of the exec sql end declare section statement causes a preprocessor syntax error.
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 program, as would be the case when a few different Pascal procedures issue embedded statements using local variables. Each procedure can have its own declaration section. For more information on the declaration of variables that are local to Pascal procedures, 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 names as ESQL keywords. You can only use them in quoted string literals. These words are:
Note that not all Pascal compilers reserve every keyword listed. However, the Embedded SQL/Pascal preprocessor does reserve all these words.
Data Types
The Embedded SQL/Pascal preprocessor accepts the data types shown in the following table. The table maps these types to their corresponding Ingres type categories. For a description of the exact type mapping, see
Data Type Conversion in this chapter.
Your program should not redefine any of the above types.
Constants
The following table maps the Pascal constants to their corresponding Ingres type categories.
Integer Data Types
Several Pascal types are considered as integer type by the preprocessor as shown in the following table.
The preprocessor can accept all integer types. Even though some integer types have Pascal constraints, such as the subranges and enumerations, Embedded SQL does not check these constraints, either during preprocessing or at runtime.
The type boolean is handled as a special type of integer. Embedded SQL treats the boolean type as an enumerated type and generates the correct code in order to use this type to interact with an Ingres integer. Enumerated types are described in more detail later.
Indicator Data Types
An indicator type is a 2-byte integer type. There are three ways to use indicator types in an application:
• In a statement that retrieves data from Ingres, you can use an indicator type to determine if its associated host variable was assigned a null.
• In a statement that sets data to Ingres, you can use an indicator type to assign a null to the database column, form field, or table field column.
• In a statement that retrieves character data from Ingres, you can use the indicator type as a check that the associated host variable was large enough to hold the full length of the returned character string.
Embedded SQL/Pascal predefines the 2-byte integer type indicator. As with other types, you should not redefine the indicator type. This type definition is in the file that is included when preprocessing the Embedded SQL statement include sqlca. The type declaration syntax is:
type
Indicator = [word] -32768..32767;
Because the type definition is in the referenced include file, you can only declare variables of type indicator after you have issued include sqlca. This declaration does not preclude you from declaring indicator variables of other 2-byte integer types.
Floating-Point Data Types
The preprocessor accepts three floating-point types. These are
single and
real, which are 4-byte floating-point types, and
double, which is the 8-byte floating-point type. Note that, although the preprocessor accepts
quadruple data type declarations, it does not accept references to variables of type
quadruple. For more information, see
Record Type Definitions in this chapter.
Double Storage Format
Embedded SQL requires that the storage representation for double variables be d_floating, because the Embedded SQL runtime system uses that format for floating-point conversions. If your Embedded SQL program has double variables that interact with the Embedded SQL runtime system, you must make sure they are stored in the d_floating format.
Because the default Pascal format is d_floating, your program will automatically use the correct storage representation unless you use the g_floating compiler option. Any module compiled with this option must not use double variables or float literals to interact with Ingres. Ingres treats float literals as double precision numbers. Note that Embedded SQL recognizes only single, and not double or quadruple, exponential notation for real constants. Thus, any real constants passed to Ingres are always single precision and are unaffected by the g_floating compiler option.
Character Data Types
Three Pascal data types are compatible with Ingres string objects: char, packed array of char, and varying of char. Note that literal string constants are of type packed array of char. Embedded SQL allows only regular Pascal string literals: sequences of printing characters enclosed in single quotes. The VMS Pascal extensions of parenthesized string constructors and of non-printing characters represented by their ASCII values in parentheses are not allowed.
The char data type does have some restrictions. Because of the mechanism used to pass string-valued arguments to the Embedded SQL runtime library, you cannot use a member of a packed array of char or varying of char to interact with Ingres. Also, a plain array of char (that is, not packed or varying) is not compatible with Ingres string objects; an element of such an array, however, is a char and as such is compatible.
For example, given the following legal declarations:
exec sql begin declare section;
type
Alpha = 'a'..'z'; {1 character}
Packed_6 = packed array[1..6]
of Char; {6-char string}
Vary_6 = varying[6] of Alpha; {6-char string}
Array_6 = array[1..6]
of Char; {1-dimensional array}
var
letter: Alpha; {1 character}
p_str_arr: array[1..5]
of Packed_6; {Array of strings}
chr_arr: array[1..6]
of Char; {1-dimensional array}
two_arr: array[1..5]
of Array_6; {2-dimensional array of char}
v_string : Vary_6; {String}
exec sql end declare section;
these usages are legal:
exec frs message letter; {A char is a string}
exec frs message chr_arr[3]; {A char is a string}
exec frs message two_arr[2][5]; {A char is a string}
exec frs message v_string; {A varying array is a string}
exec frs message p_str_arr[2];
{A packed array is a string}
but these usages are illegal:
exec frs message
chr_arr; {An array of chars is not a string}
exec frs message
v_string[2]; {Cannot index a varying array}
exec frs message
p_str_arr[2][3]; {Cannot index a packed array}
Declaration Syntax
This section describes the syntax for variable, type, and constant declarations. It also describes how to declare labels.
Attributes
In type definitions, Embedded SQL allows VMS Pascal attributes both at the beginning of the definition and just before the type name. The only attributes the preprocessor recognizes in type definitions are byte, word, and long. The preprocessor ignores any optional storage unit constant "(n)" appearing with the attribute. The preprocessor also ignores all other attributes, although it allows them.
The following example shows how to use the byte attribute in order to convert a 4-byte integer subrange into a 1-byte variable.
exec sql begin declare section;
var
v_i1 : [byte] -128..127;
exec sql end declare section;
Note that Pascal requires that a size attribute be at least as large as the size of its type. Therefore, the following declaration would be illegal, because 400 will not fit into one byte:
exec sql begin declare section;
var
v_i1 : [byte] 0..400;
exec sql end declare section;
Embedded SQL/Pascal does not allow explicit attribute size conflicts, as, for example:
exec sql begin declare section;
type
i1 = [byte] -128..127; {i1 is a 1-byte integer type}
var
v_i2 : [word] i1; {i1 cannot be extended to 2 bytes}
exec sql end declare section;
Label Declarations
An Embedded SQL block-structured statement is a statement delimited by the begin and end clauses. The select loop and the forms statements display, unloadtable, submenu, formdata, and tabledata are examples of these block-structured statements. All these statements generate Pascal labels in order to handle the complex control flow implicit in the statement.
Because Pascal requires that all labels be declared before their use, Embedded SQL/Pascal requires that you issue an exec sql label statement in the Pascal declaration section of every routine (program, procedure, or function) that issues one of these statements. You must also end the routine with the Embedded SQL exec sql end statement, rather than the Pascal end statement, so that the preprocessor will know the scope of the label declaration.
The syntax for a label declaration is:
exec sql label [label_name {, label_name}];
...
exec sql end ; | .
Syntax Notes:
1. You can use exec frs and exec sql interchangeably with the Embedded SQL label and end statements.
2. The preprocessor ignores label_names, except that they will appear in the generated Pascal label statement.
3. The terminating semicolon of the Embedded SQL label statement is required, even if there are no label_names.
4. Only one Embedded SQL label statement can occur in each routine.
5. Each Embedded SQL label statement must have a matching Embedded SQL end statement. This exec sql end statement replaces the Pascal end statement and can be terminated with a semicolon or a period.
6. The label statement must appear in a Pascal declaration section, and not in an Embedded SQL declare section.
The following example illustrates the use of label declarations:
procedure Unload_Table;
exec frs label; {Must include this statement or exec sql
label,because unloadtable uses labels}
exec sql begin declare section;
var
age : integer;
exec sql end declare section;
begin {unload_table}
exec frs unloadtable 'form' 'table' (:age = emp_age);
exec frs begin;
...
exec frs end;
exec frs end; {Unload_Table}
Constant Declarations
The syntax for a constant declaration is:
const constant_name = constant_expr;
{constant_name = constant_expr;}
where a constant_expr is one of the following:
[+|-] constant_number
[+|-] constant_name
string_constant
Constants can be used to set Ingres values but cannot be assigned values from Ingres.
Syntax Notes:
1. A constant_name must be a legal Pascal identifier beginning with an underscore or alphabetic character.
2. A constant_number can be either an integer or real number.
3. A variable or type name must begin with an alphabetic character, which can be followed by alphanumeric characters or underscores.
4. Embedded SQL/Pascal recognizes only single, and not double or quadruple, exponential notation for constants of type real.
5. The type of a constant_name is determined from the type of its constant_expr.
6. If a "+" or a "-" precedes a constant_name that is used as a constant_expr, the constant_name must be numeric.
7. Embedded SQL/Pascal does not support the declaration of arbitrary constant expressions.
The following example illustrates the use of constants declarations:
exec sql begin declare section;
const
min_sal = 15000.00; {Real}
pi = 3.14159; {Real}
max_emps = +99; {Integer}
max_credit = 100000.00; {Real}
max_debt = -max_credit; {Real}
yes = 'y'; {Char}
exec sql end declare section;
Type Declarations
An Embedded SQL/Pascal type declaration has the following syntax:
type type_name = type_definition;
{type_name = type_definition;}
where type_definition is any of the following:
Each of these type definitions is discussed in its own section below. All type names must be legal Pascal identifiers beginning with an alphabetic or underscore character.
Rename Type Definitions
The declaration for the renaming of a type uses the following syntax:
type new_type_name = type_name;
Syntax Notes:
1. The type_name must be either an Embedded SQL/Pascal type or a type name already declared to Embedded SQL (such as Integer or Real).
2. The new_type_name cannot be Integer, Real or Char or any other type listed at the beginning of this section.
The following example illustrates how to use this declaration:
exec sql begin declare section;
type
NaturalInt = Integer; {A "natural" sized integer}
exec sql end declare section;
Enumeration Type Definitions
The declaration for an enumeration type definition has the following syntax:
type type_name = ( enum_identifier {, enum_identifier} );
Syntax Notes:
1. An enum_identifier must be a legal Pascal identifier beginning with an alphabetic or underscore character.
2. The enum_identifiers are treated as 4-byte integer constant identifiers.
3. The type_name maps to a 1-byte integer if there are fewer than 257 enumerated identifiers. Otherwise, it maps to a 2-byte integer.
4. When using an enumerated identifier as a value in an Embedded SQL statement, only the ordinal position of the identifier in the original enumerated list is important. In assigning a value to a variable of enumeration type, Embedded SQL passes the variable by address and assumes that the value is a legal one for the variable.
The following example illustrates the use of this declaration:
exec sql begin declare section;
type
Table_Field_States =
(undefined, newrow, unchanged, changed, deleted);
exec sql end declare section;
Subrange Type Definitions
The syntax for declaring a subrange type definition is either:
type type_name = [+|-]integer_const .. [+|-]integer_const;
or
type type_name = string_const .. string_const;
Syntax Notes:
1. An integer_const can be either an integer literal or a named integer constant.
2. A string_const must be either a string literal or the name of a string constant. Although the preprocessor accepts any length string constant, the compiler requires the constant to be a single character.
The following example illustrates the use of this declaration:
exec sql begin declare section;
type
alpha = 'a' .. 'z';
months = 1 .. 12;
minmax = -value .. value; {"value" is an integer constant}
updated_states = changed .. deleted; {from previous example}
exec sql end declare section;
Pointer Type Definitions
The declaration for a pointer type definition has the following syntax:
type pointer_name = ^type_name;
Syntax Notes:
The type_name can be either a previously defined type, or a type not yet defined. If the type has not yet been defined, the pointer type definition is a forward pointer definition. In that case, Embedded SQL requires that you define the type_name before using a variable of type pointer_name in an Embedded SQL statement.
The following example illustrates the use of this declaration:
exec sql begin declare section;
type
empptr = ^emprecord; {forward pointer declaration}
emprecord = record
e_name : varying[40] of char;
e_salary : real;
e_id : integer;
e_next : empptr;
end;
var
empnode = empptr;
exec sql end declare section;
...
exec sql select name, salary, id
into :empnode^.e_name,
:empnode^.e_salary,
:empnode^.e_id
from emp;
Varying Length String Type Definition
The declaration for a varying length string type definition has the following syntax:
type varying_type_name = varying [upper_bound] of
char_type_name;
Syntax Notes:
1. The upper_bound of a varying array specification is not parsed by the Embedded SQL preprocessor. Consequently, an illegal upper bound (such as a non-numeric expression) will be accepted by the preprocessor but will later cause Pascal compiler errors. For example, both of the following type declarations are accepted, even though only the first is legal in Pascal:
exec sql begin declare section;
type
string20 = varying[20] of char;
what = varying['upperbound'] of char;
exec sql end declare section;
2. Embedded SQL/Pascal treats a variable of type varying of char as a string, not an array.
The following example illustrates the use of this declaration:
exec sql begin declare section;
type
pname = varying[100] of char;
var
user_name : pname;
exec sql end declare section;
...
exec sql insert into person (name)
values (:user_name);
Array Type Definition
The declaration for an array type definition has the following syntax:
type type_name = [packed] array [dimensions] of type_definition;
Syntax Notes:
1. The dimensions of an array specification are not parsed by the Embedded SQL preprocessor. Consequently, an illegal dimension (such as a non-numeric expression) will be accepted by the preprocessor but will later cause Pascal compiler errors. For example, both of the type declarations shown below are accepted, even though only the first is legal in Pascal.
exec sql begin declare section;
type
square = array[1..10, 1..10] of integer;
what = array['dimensions'] of real;
exec sql end declare section;
The preprocessor only verifies that an array variable is followed by brackets when used (except packed array of char—see below).
2. ESQL/Pascal treats a variable of type packed array of char as a string, not an array. Thus, it is not followed by brackets when used.
3. Components of a packed array cannot be passed to the Embedded SQL runtime routines. Therefore, you should not declare packed arrays to Embedded SQL, except for packed arrays of char, which are passed as a whole (for example, as character strings).
The following example illustrates the use of the array type definition:
exec sql begin declare section;
type
ssid = packed array [1..9] of char;
var
user_ssid : ssid;
exec sql end declare section;
...
exec sql insert into person (ssno)
values (:user_ssid);
Record Type Definitions
The declaration for a record type definition has the following syntax:
type record_type_name =
record
field_list [;]
end;
where field_list is:
field_element {; field_element}
[case [tag_name :] type_name of
[case_element {; case_element}]
[otherwise ( field_list )]]
where field_element is:
field_name {, field_name} : type_definition
and case_element is:
case_label {, case_label} : ( field_list )
Syntax Notes:
1. All clauses of a record component have the same rules and restrictions as they do in a regular type declaration. For example, as with regular declarations, the preprocessor does not check dimensions for correctness.
2. In the case list, the case_labels can be numbers or names. Embedded SQL need not know the names.
3. ESQL/Pascal record declarations must be entirely contained in a declaration section; consequently all of the record components will be declared to the preprocessor. To minimize the effect of this restriction, the types quadruple and set of are allowed as legal types in an Embedded SQL record declaration. It is, however, an error to use variables of those types in Embedded SQL statements.
4. Components of a packed record cannot be passed to the runtime ESQL routines. Thus, do not declare packed records to ESQL.
The following example illustrates the use of the record type definition:
exec sql begin declare section;
type
addressrec = record
street: packed array[1..30] of char;
town: packed array[1..10] of char;
zip: 1 .. 9999;
end;
employeerec = record
name: packed array[1..20] of char;
age: [byte] 0 .. 128;
salary: real;
address: addressrec;
checked: boolean;
scale: Quadruple; {Cannot be used
by Embedded SQL}
end;
exec sql end declare section;
File Type Definitions
The declaration for a file type definition, has the following syntax:
type type_name = file of type_definition;
Syntax Notes:
1. A variable of type file can only be used with Embedded SQL through the file buffer. A file buffer for a given type_definition is referenced in the same manner as a pointer to the same type.
2. Components of a packed file cannot be passed to the Embedded SQL runtime routines. Do not declare packed files to ESQL.
The following example illustrates the use of the file type definition:
exec sql begin declare section;
var
myfile : file of integer;
exec sql end declare section;
...
get (myfile);
exec sql insert into emp (floor)
values (:myfile^);
...
exec sql select floor
into :myfile^;
from emp;
put (myfile);
Set Type Definitions
The declaration for a set type definition has the following syntax:
type type_name = set of type_definition;
Although the preprocessor accepts set definitions, no set variables can be used in Embedded SQL statements. As stated in the section on record declarations, set declarations are accepted only because all record components must be declared to Embedded SQL.
Variable Declarations
An Embedded SQL/Pascal variable declaration has the following syntax:
var var_name {, var_name} : type_definition [:= initial_value];
{var_name {, var_name} : type_definition [:= initial_value];}
Syntax Notes:
1. See the previous sections for information on the type_definition.
2. The initial_value is not parsed by the preprocessor. Consequently, any initial value is accepted, even if it may later cause a Pascal compiler error. Furthermore, the preprocessor accepts an initial value with any variable declaration, even where not allowed by the compiler. For example, both of the following initializations are accepted, even though only the first is legal in Pascal:
exec sql begin declare section;
var
rowcount: integer := 1;
msgbuf: packed array[1..100] of char := 2;
exec sql end declare section;
The following example illustrates the use of variable declarations:
exec sql begin declare section;
var
rows, records: 0..500 := 0;
was_error: boolean;
msgbuf: varying[100] of char := ' ';
operators: array[1..6] of packed array[1..2] :=
('= ', '!=', '< ', '> ', '<=', '>=');
employees: array[1..100] of employeerec;
emp_ptr: ^employeerec;
work_days: (mon, tue, wed, thu, fri);
day_name: varying[8] of char;
random_ints: file of integer;
ind_set: array[1...10] of indicator;
exec sql end declare section;
Formal Parameter Declarations
Most VAX/VMS Pascal formal parameter declarations are acceptable to Embedded SQL.
An Embedded SQL/Pascal formal parameter declaration has the following syntax:
formal_param_section {; formal_param_section}
where formal_param_section is:
formal_var | formal_routine [:= [%mechanism] default_value]
A formal_var has the syntax:
[var | %mechanism] identifier {, identifier} : typename_or_schema
where typename_or_schema is one of the following:
type_name
varying [upper_bound_identifier] of type_name
packed array [schema_dimensions] of typename_or_schema
array [schema_dimensions {; schema_dimensions}] of
typename_or_schema
where schema_dimensions is:
lower_bound_identifier .. upper_bound_identifier : scalar_type_name
A formal_routine has the syntax:
[%mechanism] routine_header
where routine_header is one of the following:
procedure proc_name ( [formal_parameter_declaration] )
function func_name ( [formal_parameter_declaration] )
:return_type_name
In a subprogram declaration, the syntax of a formal parameter declaration is:
procedure proc_name
exec sql begin declare section;
( formal_parameter_declaration )
exec sql end declare section;
;
...
or:
function func_name
exec sql begin declare section;
( formal_parameter_declaration )
exec sql end declare section;
: return_type_name;
...
Syntax Notes:
1. The Embedded SQL preprocessor ignores the names of procedures and functions used as formal parameters, but checks their formal parameters for legality.
2. The default_value is not parsed by the preprocessor. Consequently, any default value is accepted, even if it may later cause a Pascal compiler error. For example, both of the parameter default values shown below are accepted, even though only the first is legal in Pascal:
procedure Load_table
exec sql begin declare section;
(clear_it: boolean := true;
var is_error: boolean := 'false')
exec sql end declare section;
;
...
3. Any mechanism specification is ignored.
The following example illustrates the use of these declarations:
function Getesqlerror
exec sql begin declare section;
( buf : varying[ub] of char )
exec sql end declare section;
: boolean;
procedure Handleerror
exec sql begin declare section;
( procedure errorhandle(err : integer); var
errnum : integer )
exec sql end declare section;
;
function Doappend
exec sql begin declare section;
( emp_id, floor : integer;
name : varying[ub] of char;
salary : real )
exec sql end declare section;
: integer;
The 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 variable declaration. You invoke DCLGEN from the operating system level with the following command:
dclgen language dbname tablename filename recordname [-n] [-q]
where
language
Defines the Embedded SQL host language, in this case, "pascal."
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 Pascal record variable that the command creates. The command generates a record type definition named recordname, followed by "_rec." The command also generates a variable declaration for recordname.
-n
Does not print the DECLARE TABLE statement.
-q
Creates output in QUEL format.
This command creates the declaration file filename. The file contains a record type definition corresponding to the database table and a variable declaration of that record type. The file also includes a declare table statement that serves as a comment and identifies the database table and columns from which the record was generated.
After generating the file, 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 Pascal 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 pascal 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 declaration of the Pascal record type definition "emprec_rec." The last statement is a declaration, using the "emprec_rec" type, for the record variable "emprec." The contents of the employee.dcl file are shown below.
{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);
type emprec_rec = record
eno: [word] -32768 .. 32767;
ename: packed array[1..20] of Char;
age: [byte] -128 .. 127;
job: [word] -32768 .. 32767;
sal: Double;
dept: [word] -32768 .. 32767;
end;
var emprec: emprec_rec;
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;
The emprec record can then be used in a select, fetch, or insert statement.
DCLGEN and Large Objects
You can use DCLGEN to generate an appropriate declare table statement with Pascal 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 Pascal compilers that support structures:
exec sql declare long_obj_table table
(long_column long varchar)
type blobs_rec_rec = record
long_column : varying[0] of char;
end;
var blobs_rec : blobs_rec_rec;
Pre-declared Identifiers
Embedded SQL pre-declares all the standard Pascal types and constants in a scope enclosing the entire program (see
Data Types and
Constants). You should not redefine any of these identifiers, because the runtime library expects the standard definitions.
Program Syntax
The syntax for an Embedded SQL/Pascal program definition is:
program program_name [(identifier {, identifier})];
[exec sql begin declare section;
declarations
exec sql end declare section;]
[procedures, functions, etc.]
begin
[statements]
end.
or:
program program_name [(identifier {, identifier})];
exec sql label [label_declarations];
[exec sql begin declare section;
declarations
exec sql end declare section;]
[procedures, functions, etc.]
begin
[statements]
exec sql end.
where declarations can include any of the following:
const constant_declarations
type type_declarations
var variable_declarations
See the previous sections for descriptions of the various types of declarations.
Syntax Notes:
1. The program_name and the identifiers are not processed by ESQL.
2. The declaration sections can be in any order and can be repeated.
The following example illustrates the above points:
program Test;
exec sql label;
exec sql begin declare section;
var
curformname, curfieldname, curcolname :
varying[12] of char;
curtablerow : integer;
exec sql end declare section;
begin
{Embedded SQL and Pascal statements}
exec sql end.
Procedures
The syntax for an Embedded SQL/Pascal procedure is:
procedure procedure_name
[exec sql begin declare section;
(formal_parameters)
exec sql end declare section;]
;
[exec sql begin declare section;
declarations
exec sql end declare section;]
begin
[statements]
end;
or:
procedure procedure_name
[exec sql begin declare section;
(formal_parameters)
exec sql end declare section;]
;
exec sql label;
[exec sql begin declare section;
declarations
exec sql end declare section;]
begin
[statements]
exec sql end;
Syntax Notes:
1. The procedure_name is not processed by Embedded SQL.
2. Formal parameters and variables declared in a procedure are visible globally to the end of the source file.
The following is an example of an Embedded SQL/Pascal procedure:
procedure AppendRow
exec sql begin declare section;
( name : varying[20] of Char;
age : Integer;
salary : Real )
exec sql end declare section;
;
begin
exec sql insert into emp (name, age, salary)
values (:name, :age, :salary);
end;
Functions
The syntax for an Embedded SQL/Pascal function is:
function function_name
[exec sql begin declare section;
(formal_parameters)
exec sql end declare section;]
: return_type_name;
[exec sql begin declare section;
declarations
exec sql end declare section;]
begin
[statements]
end;
or:
function function_name
[exec sql begin declare section;
(formal_parameters)
exec sql end declare section;]
: return_type_name;
exec sql label;
[exec sql begin declare section;
declarations
exec sql end declare section;]
begin
[statements]
exec sql end;
1. The function_name is not processed by Embedded SQL.
2. Formal parameters and variables declared in a function are globally visible to the end of the source file.
The following is an example of an Embedded SQL/Pascal function:
exec sql begin declare section;
var
errorbuf : varying[100] of char;
exec sql end declare section;
...
function wasdeadlock : boolean;
exec sql begin declare section;
const
EsqlDeadlock = -4700;
var
errnum : Integer;
exec sql end declare section;
begin
errnum := sqlca.sqlcode;
if errnum = EsqlDeadLock then
begin
SetErrorBuf( errnum, errorbuf );
WasDeadlock := TRUE;
end else
begin
errorbuf := ' ';
WasDeadlock := FALSE;
end;
end;
How to Declare External Compiled Forms
You can pre-compile your forms in the Visual Forms Editor (VIFRED). Doing this saves the time 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 the file is created, you can 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, you must declare it in an Embedded SQL declaration section. The Pascal compiler requires that this be an external declaration. The syntax for a compiled form declaration is:
exec sql begin declare section;
var
formname: [external] Integer;
exec sql end declare section;
Syntax Notes:
1. The formname is the actual name of the form. VIFRED gives this
name to the address of the external object. The formname is also used as the title of the form in other Embedded SQL/FORMS statements.
2. The external attribute 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;
var
empform: [external] integer;
exec sql end declare section;
...
exec frs addform :empform; {The global object}
exec frs display empform; {The name of the form}
...
Embedded SQL/Pascal Declarations Example
The following example demonstrates some simple Embedded SQL/Pascal declarations:
program Concluding_Example( input, output );
exec sql include sqlca; {Include error handling}
exec sql begin declare section;
const
max_persons = 1000;
type
shortshortinteger = [byte] -128 .. 127;
shortinteger = [word] -32768 .. 32767; {same as indicator type}
string9 = packed array[1..9] of char;
string12 = packed array[1..12] of char;
string20 = packed array[1..20] of char;
string30 = packed array[1..30] of char;
varstring = varying[40] of char;
record datatypes_rec = {Structure of all types}
d_byte : shortshortinteger;
d_word : shortinteger;
d_long : integer;
d_single : real;
d_double : double;
d_string : string20;
end;
record Persontype_rec = {variant record}
age : shortshortinteger;
flags : integer;
case married : boolean of
true : (spouse_name : string30);
false : (dog_name : string12);
end;
var
empform, deptform : [external] integer;
{compiled forms}
dbname : String9;
formname, tablename, columnname : String12;
salary : Real;
d_rec : Datatypes_Rec;
person : Persontype_Rec;
person_store : array[1..MAX_PERSONS] of Persontype_Rec;
person_null: array[1..10] of Indicator;
exec sql include 'employee.dcl'; {From DCLGEN}
exec sql end declare section;
begin
dbname := 'personnel';
...
end. {Concluding_Example}