SQL lets you declare host language variables directly or by
calling the Ada package, SQL_STANDARD.
You must use the SQL_STANDARD package if you want to conform to
the ANSI/ISO SQL standard. This package defines the data types
that are supported by the ANSI/ISO SQL standard. To use the
package, first copy the file SYS$COMMON:[SYSLIB]SQL$STANDARD.ADA
to your own Ada library, and then compile the package.
The package SQL_STANDARD declares the following ANSI-standard
data types:
o CHAR
o SMALLINT
The data type SMALLINT contains one subtype: INDICATOR_TYPE.
o INT
o REAL
o DOUBLE_PRECISION
o SQLCODE_TYPE
The data type SQLCODE_TYPE contains two subtypes: NOT_FOUND
and SQL_ERROR.
o SQLSTATE_TYPE
If ANSI/ISO SQL compliance is not important for your application,
you can declare host language variables directly. The following
list describes the variable declaration syntax that the SQL
precompiler supports in Ada:
o Standard package data types
- STRING
- CHARACTER
- SHORT_SHORT_INTEGER
- SHORT_INTEGER
- INTEGER
- FLOAT
By default, Ada recognizes the FLOAT data type as an F-
floating representation of floating-point data. However,
Ada also allows you to override the default and specify
that FLOAT denotes an IEEE S-Floating representation by
using the FLOAT_REPRESENTATION(IEEE_FLOAT) pragma or using
ACS CREATE LIBRARY or SET PRAGMA commands. This default
can also be overridden at installation time. SQL does
not recognize whether or not you override the F-floating
default for the FLOAT data type. If you do override the
FLOAT default, you will get Ada compile-time errors. These
compile-time errors can be overcome by using a /FLOAT=IEEE_
FLOAT qualifier with the SQL$PRE command.
To avoid problems with the ambiguity in the FLOAT data
type, use the SYSTEM package F_FLOAT and IEEE_SINGLE_FLOAT
data types.
- LONG_FLOAT
By default, Ada recognizes the LONG_FLOAT data type as a
G-floating representation of floating-point data. However,
Ada also allows you to override the default and specify
that LONG_FLOAT denotes an IEEE S-Floating representation
by using the FLOAT_REPRESENTATION(IEEE_FLOAT) pragma or
using ACS CREATE LIBRARY or SET PRAGMA commands. This
default can also be overridden at installation time.
In addition, if the FLOAT_REPRESENTATION is VAX_FLOAT
(the default), Ada allows you to specify that the LONG_
FLOAT data type be represented by a D-Floating format
by specifying the LONG_FLOAT(D_FLOAT) pragma. SQL does
not recognize whether or not you override the G-floating
default for the LONG_FLOAT data type. If you do override
the LONG_FLOAT default, you will get Ada compile-time
errors. These compile-time errors can be overcome by using
a /FLOAT qualifier with the SQL$PRE command to specify
either D_FLOATING or IEEE_FLOATING as appropriate.
To avoid problems with the ambiguity in the LONG_FLOAT data
type, use the SYSTEM package G_FLOAT, D_FLOAT, and IEEE_
DOUBLE_FLOAT data types.
NOTE
SQL$PRE will issue a warning (%SQL-W-NOFLOAT) if you
use a /FLOAT qualifier with an /ADA qualifier because
the ADA command does not have a /FLOAT qualifier.
But if you use a pragma FLOAT REPRESENTATION to
override the default floating point formats you
must use the /FLOAT qualifier to let SQL$PRE know
about this floating point format since it does not
recognize the pragma. Simply ignore the warning. In
addition to supporting IEEE formats, SQL$PRE allows
the default G_FLOAT format for 64-bit floating point
types to be overridden using a combination of the
pragma FLOAT REPRESENTATION specifying VAX_FLOAT and
the pragma LONG FLOAT specifying D_FLOAT. To use this
combination, specify an SQL$PRE qualifier of /FLOAT=D_
FLOAT.
The following example shows an Ada program with embedded
SQL that will work correctly with SQL$PRE/ADA/FLOAT=IEEE:
PRAGMA FLOAT REPRESENTATION IEEE_FLOAT;
WITH SYSTEM; USE SYSTEM;
WITH STANDARD; USE STANDARD;
WITH SQL_STANDARD; USE SQL_STANDARD;
. . .
PROCEDURE TESTIT IS
EXEC SQL BEGIN DECLARE SECTION;
KEYFIELD : STRING(1..10);
FLOATER : LONG_FLOAT; -- package STANDARD
SQLFLOATER : REAL; -- package SQL_STANDARD
GFLOATER : G_FLOAT; -- package SYSTEM
SFLOATER : IEEE_SINGLE_FLOAT; -- package SYSTEM
TFLOATER : IEEE_DOUBLE_FLOAT; -- package SYSTEM
EXEC SQL END DECLARE SECTION;
. . .
BEGIN
. . .
KEYFIELD := "1.0 ";
EXEC SQL SELECT FLOAT1, FLOAT2 INTO :SQLFLOATER, :GFLOATER
WHERE KEYFIELD = :KEYFIELD;
. . .
KEYFIELD := "2.0 ";
EXEC SQL SELECT FLOAT1, FLOAT2 INTO :SFLOATER, :TFLOATER
WHERE KEYFIELD = "KEYFIELD;
. . .
KEYFIELD := "3.0 ";
EXEC SQL SELECT FLOAT1, FLOAT2 INTO :FLOATER, TFLOATER
WHERE KEYFIELD = KEYFIELD;
o Date-time data types
The precompiler translates lines in a precompiled program that
contain any of the date-time data types.
NOTE
Oracle Rdb reserves the right to change the code
generated in translation of date-time data types at any
time, without prior notice.
- SQL_DATE, SQL_DATE_ANSI, SQL_DATE_VMS
- SQL_TIME, SQL_TIMESTAMP
- SQL_INTERVAL (DAY TO SECOND)
Use this data type for variables that represent the
difference between two dates or times. (Precompiler Date-
Time Data Mapping lists all the supported INTERVAL data
types.)
o SQL definition package
The precompiler generates a package that includes definitions
for the following data types if Ada object declarations refer
to them:
- SQL_VARCHAR_n
Use this data type for variables that correspond to VARCHAR
and LONG VARCHAR columns in a database, where n is the
length specified in the definition of the columns (always
16,383 characters for LONG VARCHAR columns).
SQL declares a two-field Ada record when it encounters
SQL_VARCHAR_n, with one field, t, containing the character
string, and the second field, l, containing an integer
denoting the length of the string.
You can refer to the l field to determine the actual length
of a varying character string, and refer to the t field to
refer to the string itself. This excerpt from the online
sample program sql_all_datatypes.sqlada refers to the l
field to see if the value in an SQL_VARCHAR_n field is
null.
.
.
.
-- Variables for main program use
type ALL_DATATYPES_RECORD_TYPE IS
record
.
.
.
VARCHAR_VAR : sql_varchar_40;
end record;
.
.
.
-- The following if statements evaluate the contents of main variables
-- and then set indicators as appropriate.
.
.
.
if all_datatypes_record.varchar_var.l = 0 then
indicator_group(7) := -1; end if;
- SQLDA_ACCESS
Specifying this data type declares an SQLDA structure.
It offers an advantage over an embedded INCLUDE SQLDA
statement because you can use it in more than one
declaration to declare multiple SQLDA structures.
o CDD_TYPES package data types (must specify WITH CDD_TYPES)
- DATE_TIME_DATATYPE (Oracle Rdb recommends that you use
SQL_TIMESTAMP)
- SHORT_INTEGER_ARRAY (for indicator arrays only)
o SYSTEM package data types (must specify WITH SYSTEM)
- D_FLOAT
- G_FLOAT
- F_FLOAT
- IEEE_SINGLE_FLOAT
- IEEE_DOUBLE_FLOAT
o Arrays
Single-dimension arrays are supported to declare an indicator
array to refer to a structure in SQL statements. The elements
of the array must be declared as word integers (SHORT_
INTEGER).
Character arrays are supported as types or subtypes but cannot
refer to derived types.
SQL does not allow references to unconstrained arrays.
o Types
The precompiler recognizes types for all the preceding data
types plus records, derived types, and arrays.
- Records can refer to any recognized type.
- Derived types (NEW keyword) can refer to any recognized
type. SQL allows but ignores range constraints in derived
types.
SQL does not allow references to types that use discriminants
in any way or to access types. SQL does not allow references
to integer (RANGE keyword), floating-point (DIGITS keyword),
or fixed-point (DELTA keyword) types.
o Subtypes
Subtypes can refer to any recognized type. SQL allows but
ignores range constraints in subtypes.
o Assignments from expressions in declarations
o Context structure types
When you write applications for the Ada precompiler, you
should declare a context structure by declaring a variable
of data type SQLCONTEXT_REC instead of declaring a structure.
When you declare a variable with the data type SQLCONTEXT_REC,
the Ada precompiler generates a context structure for you. For
example, you declare the variable using the following code:
context_struc.sqlcontext_ver := 1;
context_struc.sqlcontext_tid.sqlcontext_tid_type := 1
context_struc.sqlcontext_tid.sqlcontext_tid_len := 16;
context_struc.sqlcontext_tid.sqlcontext_tid_value(1) := 0;
context_struc.sqlcontext_tid.sqlcontext_tid_value(2) := 0;
context_struc.sqlcontext_tid.sqlcontext_tid_value(3) := 0;
context_struc.sqlcontext_tid.sqlcontext_tid_value(4) := 0;
context_struc.sqlcontext_end := 0;