Embedded SQL/Ada Declarations
The following sections discuss syntax, types, and definitions of Embedded SQL/Ada declarations.
Embedded SQL Variable Declaration Sections
Embedded SQL statements use Ada variables to transfer data to and from the database or a form into the program. You must declare Ada variables and constants to Embedded SQL before using them in any Embedded SQL statements. Ada variables, types and constants are declared to Embedded SQL in a declaration section. This section has the following syntax:
exec sql begin declare section;
Ada 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. You can incorporate multiple declaration sections into a single program, as would be the case when a few different Ada procedures issue embedded statements using local variables. Each procedure can have its own declaration section. For a discussion of the declaration of variables that are local to Ada procedures, see
The Scope of Variables in this chapter.
Reserved Words in Declarations
The following keywords are reserved by the Embedded SQL/Ada preprocessor. Therefore you cannot declare types or variables with the same name as these keywords:
Data Types
The Embedded SQL/Ada preprocessor defines certain data types from the Ada STANDARD and SYSTEM packages.The following 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.
None of the types listed above should be redefined by your program. If they are redefined, your program might not compile and will not work correctly at runtime.
Constants
The Embedded SQL/Ada preprocessor defines certain constants from the Ada STANDARD and SYSTEM packages.The following table maps the Ada constants to their corresponding Ingres type categories.
Note that if the type or constant is derived from the SYSTEM package, the program unit must specify that the SYSTEM package should be included—Embedded SQL does not do so itself. You cannot refer to a SYSTEM object by using the package name as a prefix, because Embedded SQL does not allow this type of qualification. The types f_float and d_float and the constants max_int and min_int are derived from the SYSTEM package.
Integer Data Types
All integer types and their derivatives are accepted by the preprocessor. Even though some integer types have Ada constraints, such as the types natural and positive, Embedded SQL does not check these constraints, either during preprocessing or at runtime. An integer constant is treated as an Embedded SQL constant value and cannot be the target of an Ingres assignment.
The type boolean is handled as a special type of integer. In Ada, the boolean type is defined as an enumerated type with enumerated literals false and true. 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.
Float Data Types
The preprocessor accepts four floating-point types. The types float and f_float are the 4-byte floating-point types. The types long_float and d_float are the 8-byte floating-point types. Long_float requires some extra definitions for default Ada pragmas to be able to interact with Ingres floating-point types. Note that the preprocessor does not accept the long_long_float and h_float data types.
Long Float Storage Format
Ingres requires that the storage representation for long floating-point variables be d_float, because the Embedded SQL runtime system uses that format for floating-point conversions. If your Embedded SQL program has long_float variables that interact with the Embedded SQL runtime system, you must make sure they are stored in the d_float format. Floating-point values of types g_float and h_float are stored in different formats and sizes. The default Ada format is g_float; consequently, you must convert your long floating-point variables to type d_float. There are three methods you can use to ensure that the Ada compiler always uses the d_float format.
The first method is to issue the following Ada pragma before every compilation unit that declares long_float variables:
pragma long_float( d_float );
exec sql begin declare section;
dbl: long_float;
exec sql end declare section;
Note that the pragma statement is not an Embedded SQL statement, but an Ada statement that directs the compiler to use a different storage format for long_float variables.
The second method is a more general instance of the first. If you are certain that all long_float variables in your Ada program library will use the d_float format, including those not interacting with Ingres, then you can install the pragma into the program library by issuing the following ACS command:
acs set pragma/long_float=d_float
This system-level command is equivalent to issuing the Ada pragma statement for each file that uses long_float variables.
The third method is to use the type d_float instead of the type long_float. This has the advantage of allowing you to mix both d_float and g_float storage formats in the same compilation unit. Of course, all Embedded SQL floating-point variables must be of the d_float type and format. For example:
exec sql begin declare section;
d_dbl: d_float;
exec sql end declare section;
g_dbl: g_float; -- Unknown to Embedded SQL
One side effect of all the above conversions is that some default system package instantiations for the type long_float become invalid because they are set up under the g_float format. For example, the package long_float_text_io, which is used to write long floating-point values to text files, must be reinstantiated. Assuming that you have issued the following ACS command on your program library:
acs set pragma/long_float=d_float
you must reinstantiate the long_float_text_io package before you can use it. A typical file might contain the following two lines, which serve to enter your own copy of long_float_text_io into your library:
with text_io;
package long_float_text_io is new
text_io.float_io(long_float);
A later statement, such as:
with long_float_text_io; use long_float_text_io;
will pick up your new copy of the package, which is defined using the d_float internal storage format.
Character and String Data Types
Both the character and string data types are compatible with Ingres string objects. By default, the string data type is an array of characters.
The character data type does have some restrictions. Because it must be compatible with Ingres string objects, you can use only a one-dimensional array of characters. Therefore, you cannot use a single character or a multi-dimensional array of characters as a Ingres string. Note that you can use a multi-dimensional array of strings. For example, the following four declarations are legal:
subtype Alphabet is Character range 'a'..'z';
type word_5 is array(1..5) of Character;
-- 1-dimensional array
word_6: String(1..6); -- Default string type
word_arr: array(1..5) of String(1..6);
-- Array of strings
However, the declarations below are illegal because they violate the Embedded SQL restrictions for the character type. Although the declarations may not generate Embedded SQL errors, the Ada compiler does not accept the references when used with Embedded SQL statements.
letter: Character; -- 1 character
word_arr: array(1..5) of word_5;
-- 2-dimensional array of char
Both could be declared instead with the less restrictive string type:
letter: String(1..1);
word_arr: array(1..5) of String(1..5);
-- Array of strings
Character strings containing embedded single quotes are legal in SQL, for example:
mary's
User variables may contain embedded single quotes and need no special handling unless the variable represents the entire search condition of a where clause:
where :variable
In this case you must escape the single quote by reconstructing the :variable string so that any embedded single quotes are modified to double single quotes, as in:
mary''s
Otherwise, a runtime error will occur.
For more information on escaping single quotes, see
String Literals in this chapter.
Variable and Number Declaration Syntax
The following sections discuss variable and number declaration syntax.
Simple Variable Declarations
An Embedded SQL/Ada variable declaration has the following syntax:
identifier {, identifier} :
[constant]
[array (dimensions) of]
type_name
[type_constraint]
[:= initial_value];
Syntax Notes:
• The identifier must be a legal Ada identifier beginning with an alphabetic character.
• If you specify the constant clause, the declaration must include an explicit initialization.
• If you specify the constant clause, the declared variables cannot be targets of Ingres assignments.
• The Embedded SQL preprocessor does not parse the dimensions of an array specification. Consequently, the preprocessor accepts unconstrained array bounds and multi-dimensional array bounds. However, an illegal dimension (such as a non-numeric expression) is also accepted but causes Ada compiler errors.
For example, both of the following declarations are accepted, even though only the first is legal Ada:
square: array (1..10, 1..10) of Integer;
bad_array: array ("dimensions") of Float;
• A variable or type name must begin with an alphabetic character, which can be followed by alphanumeric characters or underscores.
• The type_name must be either an Embedded SQL/Ada type (refer to the list of acceptable types earlier in this chapter) or a type name already declared to Embedded SQL.
• The legal type_constraints are described in the next section.
• The preprocessor does not parse initial_value. Consequently, the preprocessor accepts any initial value, even if it can later cause an Ada compiler error. For example, both of the following initializations are accepted, even though only the first is legal Ada:
rowcount: Integer := 1;
msgbuf: String(1..100) := 2; -- Incompatible value
You must not use a single quote in an initial value to specify an Ada attribute. Embedded SQL treats it as the beginning of a string literal and generates an error. For example, the following declaration generates an error:
id: Integer := Integer'First
The following is a sample variable declaration:
rows, records: Integer range 0..500 := 0;
was_error: Boolean;
min_sal: constant Float := 15000.00;
msgbuf: String(1..100) := (1..100 => ' ');
operators: constant array(1..6) of String(1..2) :=
("= ", "!=", "<=", ">=");
Type Constraints
Type constraints can optionally follow the type name in an Ada object declaration. In general, they do not provide Embedded SQL with runtime type information, so they are not fully processed. The following two constraints describe the syntax and restrictions of Embedded SQL type constraints.
The Range Constraint
The syntax of the range constraint is:
range lower_bound .. upper_bound
In a variable declaration, its syntax is:
identifier: type_name range lower_bound .. upper_bound;
Syntax Notes:
• Even if Ada does not allow a range constraint, Embedded SQL does accept it. For example, both of the following range constraints are accepted, although the second is illegal in Ada because the string type is not a discrete scalar type:
digit: Integer range 0..9;
chars: String range 'a'..'z';
• The two bounds, lower_bound and upper_bound, must be integer literals, floating-point literals, character literals, or identifiers. Other expressions are not accepted.
• The bounds are not checked for compatibility with the type_name or with each other. For example, the preprocessor accepts the following three range constraints, even though only the first is legal Ada:
byte: Integer range -128..127;
word: Integer range 1.0..30000.0;
--Incompatible with type name
long: Integer range 1..'z';
--Incompatible with each other
The Discriminant and Index Constraints
The discriminant and index constraints have the following syntax:
(discriminant_or_index_constraint)
In a variable declaration the syntax is:
identifier: type_name (discriminant_or_index_constraint);
Syntax Notes:
• Even if Ada does not allow a constraint, Embedded SQL does accept it. For example, Embedded accepts both of the following constraints, even though the second is illegal in Ada because the integer type does not have a discriminant:
who: String(1..20); -- Legal index constraint
nat: Integer(0); -- Illegal context for discriminant
• The contents of the constraint contained in the parentheses are not processed. Consequently, Embedded SQL accepts any constraint, even if Ada does not allow it. For example, Embedded SQL accepts the following declaration but generates a later Ada compiler error because of the illegal index constraint:
password: String(secret word);
Note that the above type constraints are not discussed in detail after this section, and their rules and restrictions are considered part of the Embedded SQL/Ada declaration syntax.
Formal Parameter Declarations
An Embedded SQL/Ada formal parameter declaration has the following syntax:
identifier {, identifier} :
[in | out | in out
type_name
[:= default_value ]
[;]
Like other Embedded SQL declarations, the formal parameter declaration must occur inside a declaration section. In a subprogram specification, its syntax is:
procedure name
exec sql begin declare section;
( parameter_declaration {; parameter_declaration} )
exec sql end declare section;
is
...
or:
function name
exec sql begin declare section;
( parameter_declaration {; parameter_declaration} )
exec sql end declare section;
return type_name is
...
Syntax Notes:
• The Embedded SQL preprocessor processes only the formal parameter declarations in a subprogram specification.
• If you specify the in mode alone, the declared parameters are considered constants and cannot be targets of Ingres assignments.
• If you do not specify a mode, the default in mode is used and the declared parameters are considered constants.
• The type_name must be either an Embedded SQL/Ada type or a type name already declared to Embedded SQL.
• The preprocessor does not parse the default_value. Consequently, the preprocessor accepts any default value, even if it can later cause a Ada compiler error. For example, Embedded SQL accepts both of the following parameter defaults, even though only the first is legal in Ada:
procedure Load_Table
exec sql begin declare section;
(clear_it: in Boolean := TRUE;
is_error: out Boolean := "FALSE")
exec sql end declare section;
is
...
• You must not use a single quote in a default value to specify an Ada attribute. Embedded SQL treats it as the beginning of a string literal and generates an error.
• You must use the semicolon with all parameter declarations except the last.
• As with all other Embedded SQL/Ada declarations, formal parameter declarations are global from the point of declaration to the end of the file. For more information, see
The Scope of Variables in this chapter.
Number Declarations
An Embedded SQL/Ada number declaration has the following syntax:
identifier {, identifier} :
constant := initial_value;
Syntax Notes:
• You can only use a number declaration for integer numbers. You cannot declare a floating-point number declaration using this format. If you do, Embedded SQL treats it as an integer number declaration, later causing compiler errors. For example, the preprocessor treats the following two number declarations as integer number declarations, even though the second is a float number declaration:
max_employees: constant := 50000;
min_salary: constant := 13500.0; -- Treated as INTEGER
In order to declare a constant float declaration, you must use the constant variable syntax. For example, you should declare the second declaration above as:
min_salary: constant Float := 13500.0;
-- Treated as FLOAT
• The declared numbers cannot be the targets of Ingres assignments.
• The preprocessor does not parse the initial_value. Consequently, the preprocessor accepts any initial value, even if it can later cause an Ada compiler error. For example, Embedded SQL accepts both of the following initializations, even though only the first is a legal Ada number declaration:
no_rows: constant := 0;
bad_num: constant := 123 + "456";
You must not use a single quote in an initial value to specify an Ada attribute. Embedded SQL treats it as the beginning of a string literal and generates an error.
Rename Variables
The syntax for renaming variables is:
identifier: type_name renames declared_object;
Syntax Notes:
• The type_name must be an Embedded SQL/Ada type or a type name already declared to Embedded SQL and the declared_object must be a known Embedded SQL variable or constant.
• The declared_object must be compatible with the type_name in base type, array dimensions, and size.
• If the declared object is a record component, any subscripts used to qualify the component are ignored. For example, the preprocessor accepts both of the following rename statements, even though one of them must be wrong, depending on whether "emprec" is an array:
eage1: Integer renames emprec(2).age;
eage2: Integer renames emprec.age;
Type Declaration Syntax
Embedded SQL/Ada supports a subset of Ada type declarations. In a declaration, the Embedded SQL preprocessor only notes semantic information relevant to the use of the variable in Embedded SQL statements at runtime. The preprocessor ignores other semantic information. Refer to the syntax notes in this section and its subsections for details.
Type Definition
An Embedded SQL/Ada full type declaration has the following syntax:
type identifier [discriminant_part] is type_definition;
Syntax Notes:
• The discriminant_part has the syntax:
(discriminant_specifications)
and is not processed by Embedded SQL. As with variable declarations, Embedded SQL always accepts a discriminant specification, even if Ada does not allow it. For example, Embedded SQL accepts the following declaration but later generates an Ada compiler error because the discriminant type is not a discrete type, and the discriminant part is not allowed in a non-record declaration:
type shapes(name: String := "BOX")
is array(1..10) of String(1..3);
From this point on, discriminant parts are not included in the syntax descriptions or notes.
• The legal type_definitions allowed in type declarations are described below.
Subtype Definition
An Embedded SQL/Ada subtype declaration has the following syntax:
subtype identifier is type_name [type_constraint];
Syntax Notes:
• The type_constraint has the same rules as the type constraint of a variable declaration. The range, discriminant and index constraints are all allowed and are not processed against the type_name being used. For more details about these constraints, see the section above on variable type constraints. The floating-point constraint and the digits clause, which are allowed in subtype declarations, are discussed later.
Integer Type Definitions
The syntax of an Embedded SQL/Ada integer type definition is:
range lower_bound .. upper_bound
In the context of an integer type declaration, the syntax is:
type identifier is range lower_bound .. upper_bound;
In the context of an integer subtype declaration, the syntax is:
subtype identifier is integer_type_name
range lower_bound .. upper_bound;
Syntax Notes:
• In an integer type declaration (not a subtype declaration), Embedded SQL processes the range constraint of an integer type definition to evaluate storage size information. Both lower_bound and upper_bound must be integer literals. Based on the specified range and the actual values of the bounds, Embedded SQL treats the type as a byte-size, a word-size or a longword-size integer. For example:
type Table_Num is range 1..200;
• In an integer subtype declaration, the range constraint is treated as a variable range constraint and is not processed. Consequently, the same rules that apply to range constraints for variable declarations apply to integer range constraints for integer subtype declarations. The base type and storage size information is determined from the integer_type_name used. For example:
subtype Ingres_I1 is Integer range -128..127;
subtype Ingres_I2 is Integer range -32768..32767;
subtype Table_Low is Table_Num range 1..10;
subtype Null_Ind is Short_Integer range -1..0;
-- Null Indicator
Floating-point Type Definitions
The syntax of an Embedded SQL/Ada floating-point type definition is:
digits digit_specification [range_constraint]
In the context of a floating-point type declaration, the syntax is:
type identifier is digits digit_specification [range_constraint];
The syntax of a floating-point subtype declaration is:
subtype identifier is floating_type_name
[digits digit_specification]
[range_constraint];
Syntax Notes:
• The value of digit_specification must be an integer literal. Based on the value of the specification, Embedded SQL determines whether to treat a variable of that type as a 4-byte float or an 8-byte float. The following rules apply:
Note that if the digits specified are out of range, the type is unusable. Recall that Embedded SQL does not accept either the
long_long_float or the
h_float type. For detailed information on the internal storage format for 8-byte floating-point variables, see
Long Float Storage Format in this chapter.
• The range_constraint for floating-point types and subtypes is treated as a variable range constraint and is not processed. Although Embedded SQL allows any range constraint, you should not specify a range constraint that alters the size needed to store the declared type. Embedded SQL obtains its type information from the digits clause, and altering this type information by a range clause, which may require more precision, results in runtime errors.
• The digits clause in a subtype declaration does not have any effect on the Embedded SQL type information. This information is obtained from floating_type_name.
type Emp_Salary is digits 8 range 0.00..500000.00;
subtype Directors_Sal
is Emp_Salary 100500.00..500000.00;
subtype Raise_Percent
is Float range 1.05..1.20;
Enumerated Type Definitions
The syntax of an Embedded SQL/Ada enumerated type definition is:
(enumerated_literal {, enumerated_literal})
In the context of a type declaration, the syntax is:
type identifier is (enumerated_literal {, enumerated_literal});
In the context of a subtype declaration, the syntax is:
subtype identifier is enumerated_type_name [range_constraint];
• An enumerated type declaration can contain no more than 1000 enumerated literals. The preprocessor treats all literals and variables declared with this type as integers. Enumerated literals are treated as though they were declared with the constant clause, and therefore cannot be the targets of Ingres assignments. When using an enumerated literal with Embedded SQL statements, only the ordinal position of the value in relation to the original enumerated list is relevant. When assigning from an enumerated literal, Embedded SQL generates:
enumerated_type_name'pos(enumerated_variable_or_literal)
When assigning from or into an enumerated variable, Embedded SQL passes the object by address and assumes that the value being assigned from or into the variable will not raise a runtime constraint error.
• An enumerated literal can be an identifier or a character literal. Embedded SQL does not store or process enumerated literals that are character literals.
• Enumerated literal identifiers must be unique in their scope. Embedded SQL does not allow the overloading of variables or constants.
• The range_constraint for enumerated subtypes is treated as a variable range constraint and is not processed. The type information is determined from enumerated_type_name.
type Table_Field_States is
(UNDEFINED, NEWROW, UNCHANGED,CHANGED, DELETED);
subtype Updated_States is
Table_Field_States range CHANGED..DELETED;
tbstate: Table_Field_States := UNDEFINED;
• ESQL accepts the predefined enumeration type name Boolean, which contains the two literals FALSE and TRUE. You can use a representation clause for enumerated types. When you do so, however, you should not reference any enumerated literals of that type in embedded statements, though you can reference the variables.
Enumerated literals are interpreted into their integer relative position (pos) and representation clauses invalidate the effect of the pos attribute that the preprocessor generates. The representation clause must be outside of the declare section.
• You can only use enumerated variables and literals to assign to or from Ingres. You cannot use these objects to specify simple numeric objects, such as table field row numbers or sleep statement seconds.
Array Type Definitions
The syntax of an Embedded SQL/Ada array type definition is:
array (dimensions) of type_name;
In the context of a type declaration, the syntax is:
type identifier is array (dimensions) of
type_name [type_constraint];
• The dimensions of an array specification are not parsed by the Embedded SQL preprocessor. Consequently, the preprocessor accepts unconstrained array bounds and multi-dimensional array bounds. However, an illegal dimension (such as a non-numeric expression) is also accepted but later causes Ada compiler errors. For example, both of the following type declarations are accepted, even though only the first is legal in Ada:
type Square is array(1..10, 1..10) of Integer;
type What is array("dimensions") of Float;
Because the preprocessor does not store the array dimensions, it only checks to determine that when you use the array variable, it is followed by a subscript in parentheses.
• The type_constraint for array types is treated as a variable type constraint and is not processed. The type information is determined from type_name.
• Any array built from the base type character (not string) must be exactly one-dimensional. Embedded SQL treats the whole array as though you declared it as type string. If you declare more dimensions for a variable of type character, Embedded SQL still treats it as a one-dimensional array.
• The type string is the only array type.
Record Type Definitions
The syntax of an Embedded SQL/Ada record type definition is:
record
record_component {record_component}
end record;
where record_component is:
component_declaration ; | variant_part; | null;
where component_declaration is:
identifier {, identifier} :
type_name [type_constraint] [:= initial_value]
In the context of a type declaration, the syntax of a record type definition is:
type identifier is
record
record_component { record_component}
end record;
Note that the SQL Reference Guide refers to records as structures and record components as structure members.
Syntax Notes:
• In a component_declaration, all clauses have the same rules and restrictions as they do in a regular type declaration. For example, as in regular declarations, the preprocessor does not check initial values for correctness.
• The variant_part accepts the Ada syntax for variant records: if specified, it must be the last component of the record. The variant discriminant name, choice names, and choice ranges are all accepted. There is no syntactic or semantic checking on those variant objects. Embedded SQL uses only the final component names of the variant part and not any of the variant object names.
• You can specify the null record.
The following example illustrates the use of record type definitions:
type Address_Rec is
record
street: String(1..30);
town: String(1..10);
zip: Positive;
end record;
type Employee_Rec is
record
name: String(1..20);
age: Short_Short_Integer;
salary: Float := 0.0;
address: Address_Rec;
end record;
Incomplete Type Declarations and Access Types
The incomplete type declaration should be used with an access type. The syntax for an incomplete type declaration is:
type identifier [discriminant_part];
Syntax Notes:
• As with other type declarations, the discriminant_part is ignored.
• You must fully define an incomplete type before using any object declared with it.
The syntax for an access type declaration is:
type identifier is access type_name [type_constraint];
• The type_name must be an Embedded SQL/Ada type or a type name already declared to Embedded SQL, whether it is a full type declaration or an incomplete type declaration.
• The type_constraint has the same rules as other type declarations.
type Employee_Rec; -- Incomplete declaration
type Employee is access Employee_Rec;
-- Access to above
type Employee_Rec is -- Real definition
record
name: String(1..20);
age: Short_Short_Integer;
salary: Float := 0.0;
link: Employee;
end record;
Derived Types
The syntax for a derived type is:
type identifier is new type_name [type_constraint];
Syntax Notes:
• The type_name must be an embedded SQL/Ada type or a type name already declared to Embedded SQL, whether it is a full type declaration or an incomplete type declaration.
• Embedded SQL assigns the type being declared the same properties as the type_name specified. The preprocessor makes sure that any variables of a derived type are cast into the original base type when used with the runtime routines.
• The type_constraint has the same rules as other type declarations.
type Dbase_Integer is new Integer;
Private Types
The syntax for a private type is:
type identifier is [limited] private;
This type declaration is treated as an incomplete type declaration. You must fully define a private type before using any object declared with it.
Representation Clauses
With one exception, you must not use representation clauses for any types or objects you have declared to Embedded SQL and intend to use with the Embedded SQL runtime system. Any such clauses causes runtime errors. These clauses include the Ada statement:
for type_or_attribute use expression;
and the Ada pragma:
pragma pack(type_name);
The exception is that you can use a representation clause to specify internal values for enumerated literals. When you do so, however, you should not reference any enumerated literals of the modified enumerated type in embedded statements. The representation clause invalidates the effect of the pos attribute that the preprocessor generates. If the application context is one that requires the assignment from the enumerated type, then you should deposit the literal into a variable of the same enumerated type and assign that variable to Ingres. In all cases, do not include the representation clause in a declare section. For example:
exec sql begin declare section;
type opcode is (opadd, opsub, opmul);
exec sql end declare section;
...
for opcode use (opadd => 1, opsub => 2, opmul => 4);
...
opcode_var := opsub;
exec sql insert into codes values (:opcode_var);
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. Use the following command to invoke DCLGEN from the operating system level:
dclgen language dbname tablename filename recordname [-n] [-q]
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 Ada record variable that the command creates. The command generates a record type definition named recordname, followed by "_rec". It 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 you have generated 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 an Ada 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(14,2) not null,
dept smallint);
and the DCLGEN system-level command is:
dclgen ada 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 Ada record type definition "emprec_rec." The last statement is a declaration, using the "emprec_rec" type, for the record variable "emprec." The exact 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(14,2) not null,
dept smallint);
type emprec_rec is
record
eno: short_integer;
ename: string(1..20);
age: short_short_integer;
job: short_integer;
sal: long_float;
dept: short_integer;
end record;
emprec: emprec_rec;
You should include this file, 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.
The field names of the structure that DCLGEN generates are identical to the column names in the specified table. Therefore, if the column names in the table contain any characters that are illegal for host language variable names you must modify the name of the field before attempting to use the variable in an application.
DCLGEN and Large Objects
When a table contains a large object column, DCLGEN will issue a warning message and map the column to a zero length character string variable. You must modify the length of the generated variable before attempting to use the variable in an application.
For example, assume that the "job_description" table was created in the personnel database as:
create table job_description (job smallint,
description long varchar);
and the DCLGEN system level command is:
dclgen ada personnel job_descriptionjobs.dcl jobs_rec
The contents of the jobs.dcl file would be:
-- Description of table job_description from
-- database personnel
exec sql declare job_description table
(job smallint,
long_column long varchar);
type jobs_rec_rec is
record
job: short_integer;
description: string(1..0);
end record
jobs_rec: jobs_rec_rec;
Indicator Variables
An indicator variable is a 2-byte integer variable. You can use an indicator variable in three possible ways in an application:
• In a statement that retrieves data from Ingres. You can use an indicator variable to determine if its associated host indicator variable was assigned a null.
• In a statement that sets data to Ingres. You can use an indicator variable 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 variable as a check that the associated host variable was large enough to hold the full length of the returned character string. However, the preferred method is to use SQLSTATE.
In order to declare an indicator variable, you should use the short_integer data type. The following example declares two indicator variables:
ind: Short_Integer; -- Indicator variable
ind_arr: array(1..10) of Short_Integer; --Indicator array
When using an indicator variable with an Ada 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. Note that a variable declared with any derivative of the short_integer data type will be accepted as an indicator variable
How to Declare External Compiled Forms
You can pre-compile your forms in the Visual Forms Editor (VIFRED). 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, 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, it must be declared in an Embedded SQL declaration section. The Ada compiler requires that the declaration be in a package and that the objects be imported with the import_object pragma.
The syntax for a compiled form package is:
package compiled_forms_package is
exec sql begin declare section;
formname: Integer;
exec sql end declare section;
pragma import_object( formname );
end compiled_forms_package;
You must then issue the Ada with and use statements on the compiled form package before every compilation unit that refers to the form:
with compiled_forms_package; use compiled_forms_package;
Syntax Notes:
• 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.
• The import_object pragma associates the object with the external form definition. To use this pragma, the package must be issued in the outermost scope of the file.
The example below shows a typical form declaration and illustrates the difference between using the form's object definition and the form's name.
package Compiled_Forms is
exec sql begin declare section;
empform: Integer;
exec sql end declare section;
pragma import_object( empform );
end Compiled_Forms;
...
with Compiled_Forms; use Compiled_Forms;
...
exec frs addform :empform; -- The imported object
exec frs display empform; -- The name of the form
...
Embedded SQL/Ada Declarations Example
The following example demonstrates some simple Embedded SQL/Ada declarations.
package Compiled_Forms is
exec sql begin declare section;
empform, deptform: Integer; -- Compiled forms
exec sql end declare section;
pragma import_object( empform );
pragma import_object( deptform );
end Compiled_Forms;
with Compiled_Forms; use Compiled_Forms;
exec sql include sqlca; -- Include error handling
package Concluding_Example is
exec sql begin declare section;
max_persons: constant := 1000;
dbname: String(1..9):="personnel";
formname, tablename, columnname: String(1..12);
salary: Float;
type datatypes_rec is -- Structure of all types
d_byte: Short_Short_Integer;
d_word: Short_integer;
d_long: Integer;
d_single: Float;
d_double: Long_float;
d_string: String(1..20);
end record;
d_rec: datatypes_rec;
-- Record with a discriminant
record persontype_rec (married: in Boolean) is
age: Short_Short_Integer;
flags: Integer;
case married:
when TRUE =
spouse_name: String(1..30);
when FALSE =
dog_name: String(1..12);
end case;
end record;
person: persontype_rec(TRUE);
person_store: array(1..max_persons) of
persontype_rec(false);
exec sql include 'employee.dcl'; -- From dclgen
ind_var: Short_Integer := -1; -- Indicator
-- variable
exec sql end declare section;
end concluding_examples;