The SQL precompiler recognizes only a subset of valid host
language variable declarations. If you refer to a variable
declaration that SQL does not recognize in an embedded SQL
statement, the precompiler generates a fatal error when it
encounters that reference.
Oracle Rdb databases and the various host languages supported
by the SQL precompiler do not necessarily support the same set
of data types. The precompiler recognizes host language variable
declarations that are equivalent to SQL data types plus a subset
of other host language variable declarations.
o For host language variable declarations of data types that are
equivalent to SQL data types, the precompiler passes values
directly between the database and the host language variable.
o Host language floating point data types will be interpreted as
having representations as determined by the /FLOAT qualifier
on the precompiler command line and individual language
rules. These rules are discussed in the host language-specific
sections that follow. In these sections, selects will be shown
from a table defined as follows:
CREATE TABLE TESTTBL (
KEYFIELD CHAR(10) PRIMARY KEY,
FLOAT1 REAL,
FLOAT2 DOUBLE PRECISION);
o For each host language, the precompiler also supports a
limited number of host language variable declarations that
do not correspond to SQL data types. SQL converts database
values to the host language data type and host language values
to the supported data type. SQL makes this conversion only for
a subset of valid host language declarations.
The following table shows the date-time data types that the
precompiler supplies.
Table 6 Precompiler Date-Time Data Mapping
Module Language
and Interactive
SQL Precompiler
DATE SQL_DATE
DATE_ANSI SQL_DATE_ANSI
DATE_VMS SQL_DATE_VMS
TIME SQL_TIME
TIMESTAMP SQL_TIMESTAMP
INTERVAL YEAR SQL_INTERVAL (YEAR)
INTERVAL YEAR TO SQL_INTERVAL (YEAR TO MONTH)
MONTH
INTERVAL MONTH SQL_INTERVAL (MONTH)
INTERVAL DAY SQL_INTERVAL (DAY)
INTERVAL DAY TO SQL_INTERVAL (DAY TO HOUR)
HOUR
INTERVAL DAY TO SQL_INTERVAL (DAY TO MINUTE)
MINUTE
INTERVAL DAY TO SQL_INTERVAL (DAY TO SECOND)
SECOND
INTERVAL HOUR SQL_INTERVAL (HOUR)
INTERVAL HOUR TO SQL_INTERVAL (HOUR TO MINUTE)
MINUTE
INTERVAL HOUR TO SQL_INTERVAL (HOUR TO SECOND)
SECOND
INTERVAL MINUTE SQL_INTERVAL (MINUTE)
INTERVAL MINUTE SQL_INTERVAL (MINUTE TO SECOND)
TO SECOND
INTERVAL SECOND SQL_INTERVAL (SECOND)
o For all other host language variable declarations, the
precompiler generates an error when it encounters a reference
to them in embedded SQL statements.
NOTE
The ANSI/ISO SQL standard specifies that variables used
in embedded SQL statements must be declared within a
pair of embedded SQL BEGIN DECLARE . . . END DECLARE
statements. The Oracle Rdb SQL precompiler does not enforce
this restriction. If you use the BEGIN DECLARE . . . END
DECLARE statements, SQL generates a warning message when
it encounters a variable declared outside of a BEGIN
DECLARE . . . END DECLARE block.
If ANSI/ISO SQL compliance is important for your
application, you should include all declarations for
variables used in embedded SQL statements within a BEGIN
DECLARE . . . END DECLARE block. See the Oracle Rdb SQL
Reference Manual on the SQL module language for more
information on the BEGIN DECLARE statement.
If you do not declare character variables using syntax that
specifies a character set or by defining the RDB$CHARACTER_SET
logical name, the SQL precompiler uses the UNSPECIFIED character
set. When you use the UNSPECIFIED character set, the precompiler
does not check to see if the character set of the variables
matches the character sets of the database. For more information
regarding the logical name, see the Oracle Rdb SQL Reference
Manual.
The RDB$CHARACTER_SET logical name is deprecated and will not be
supported in a future release.
1 – Character Parameters
To ensure that you specify the length of character variables
correctly, use the following guidelines:
o For the C language, any character variables that correspond
to character data type columns must be defined as the length
of the longest valid column value in octets, plus 1 octet to
allow for the null terminator.
o For other languages supported by the SQL precompiler, any
character variables that correspond to character data type
columns must be defined as the length of the longest valid
column value in octets.
o When calculating the length of the longest valid column value,
you must take into consideration whether the SQL precompiler
interprets the length of columns in characters or octets. A
program can control how the SQL precompiler interprets the
length of columns in the following ways:
- The CHARACTER LENGTH clause of the DECLARE MODULE statement
- The DIALECT clause of the DECLARE MODULE statement
- For dynamic SQL, the SET CHARACTER LENGTH statement
2 – Ada Variables
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;
3 – C Variables
NOTE
C is a case-sensitive language. The names of objects
declared in a C program are case sensitive, but the names
of SQL tables and other names are not case sensitive.
Therefore, you must be careful about C constructs that
you specify in SQL statements. These constructs include
variable names and labels of program sections. See the
Oracle Rdb Guide to SQL Programming for more information
about declaring C variables.
The following list describes the variable declaration syntax for
character data types that the SQL precompiler supports in C:
o char x[n]
o char *x, assumes LONG VARCHAR type (that is, char x[16383])
o char CHARACTER SET character-set-name clause
The CHARACTER SET character-set-name clause is optional.
o $SQL_VARCHAR (n)
o $SQL_VARCHAR (n) CHARACTER SET character-set-name
The CHARACTER SET clause is optional.
For information about the supported character sets, see the
Oracle Rdb SQL Reference Manual.
The following list describes the variable declaration syntax that
the SQL precompiler supports in C:
o Data type keywords
o Storage class identifiers and modifiers
o struct
o union
o typedef
o Initial value assignments
o Arrays
Only single-dimension arrays are supported and only to declare
an indicator array for use with a reference to a structure
in SQL statements. Furthermore, the size of the array must be
specified explicitly. Although you can use any data type for
indicator array elements, Oracle Rdb recommends that you use
variables of the data type integer, such as int or short.
o Pointers
Only a single level of pointer variables are supported and
only those that point to elementary data types.
Because C pointer variables cannot specify length attributes,
SQL sometimes must allocate the largest possible piece of
memory to process statements that refer to char pointer
variables. SQL cannot determine the length of char pointer
variables and allocates 16,383 bytes of memory for each
variable in the following cases:
- The SQL statement contains a concatenated value expression
or a substring.
- The SQL statement refers to the char pointer variable in a
predicate, such as WHERE EMP_ID = :POINTER_VAR.
- The SQL statement converts the contents of the char pointer
variable to a numeric data type in the database.
Avoid the use of char pointer variables in these cases because
such a large memory allocation for char pointer variables
wastes memory and degrades performance, especially for remote
database access.
o Valid declaration syntax
The following are examples of valid declaration syntax:
a_var[10];
$SQL_VARCHAR(10) x,y,z;
int SQLCODE;
struct
{
char b_var[5];
short int c_var;
} a_record;
union
{
char string_date[17];
struct
{
char year_var1[2];
char year_var2[2];
char month_var[2];
char day_var[2];
char hour_var[2];
char minute_var[2];
char second_var[2];
char hundredth_var[2];
} date_group;
} date_union;
int indicator_item[2];
globaldef double c_var;
static d_var;
char *x;
o Invalid declaration syntax
Table 7 Supported C Datatypes
C
type
or
typedefSQL type Comments and Restrictions
char CHARACTER
char LONG VARCHAR Assumed to be VARCHAR (16383)
*
char CHARACTER n must be be an integer literal;
[n] #define names or expressions are not
supported.
int INTEGER Cannot be specified as unsigned.
short SMALLINT Cannot be specified as unsigned.
short INTEGER Cannot be specified as unsigned.
int
long INTEGER Cannot be specified as unsigned.
int
float REAL
double DOUBLE PRECISION
enum INTEGER
long INTEGER On OpenVMS the data type long is 32
bits
int8 TINYINT Requires #include <ints.h>
int16 SMALLINT Requires #include <ints.h>
__int16SMALLINT
int32 INTEGER Requires #include <ints.h>
__int32INTEGER
int64 BIGINT Requires #include <ints.h>
__int64BIGINT
$SQL_VARCHAR (n) The CHARACTER SET clause is optional.
SQL_DATE The SQL precompiler will transform the
SQL_DATE_ANSI pseudo types in natvie C datatypes
SQL_DATE_VMS
SQL_TIME
SQL_TIMESTAMP
SQL_INTERVAL (DAY Use this data type for variables that
TO SECOND) represent the difference between two
dates or times. Precompiler Date-Time
Data Mapping lists all the supported
INTERVAL data types.
4 – COBOL Variables
The following list describes the variable declaration syntax for
character data types that the SQL precompiler supports in COBOL:
o PICTURE IS can be abbreviated as PICTURE or PIC.
o CHARACTER SET character-set-name PICTURE IS.
o PICTURE clauses for numeric variables must begin with S (must
be signed) and cannot include P characters.
o PICTURE clauses cannot include editing characters.
For information about the supported character sets, see the
Oracle Rdb SQL Reference Manual.
The following list describes the variable declaration syntax that
the SQL precompiler supports in COBOL:
o PICTURE IS clause
- PICTURE IS can be abbreviated as PICTURE or PIC.
- PICTURE clauses for numeric variables must begin with S
(must be signed) and cannot include P characters.
- PICTURE clauses cannot include editing characters.
o USAGE IS clause
- USAGE IS must immediately follow a PICTURE clause.
- USAGE IS can be abbreviated as USAGE or omitted completely.
- USAGE IS must have as an argument BINARY, COMPUTATIONAL,
COMPUTATIONAL-1, COMPUTATIONAL-2, or COMPUTATIONAL-3.
COMPUTATIONAL can be abbreviated as COMP in all USAGE IS or
DISPLAY declarations. BINARY is a synonym for COMPUTATIONAL
or COMP.
o VALUE IS clause
VALUE IS can be abbreviated as VALUE and is allowed without
restriction.
o IS EXTERNAL clause
IS EXTERNAL can be abbreviated as EXTERNAL and is allowed
without restriction.
o IS GLOBAL clause
IS GLOBAL can be abbreviated as GLOBAL and is allowed without
restriction.
o SIGN clause
SIGN is allowed but must immediately follow a PICTURE clause
or a USAGE IS clause.
o Group data items
- Group data items are allowed without restriction.
- Variables associated with the SQL VARCHAR and LONG VARCHAR
data types must be declared as group data items with two
elementary items at level 49. The first elementary item
must be a small integer to contain the actual length of
the character string. The second elementary item must be a
character string long enough to contain the string itself.
* Declaration for an SQL column
* defined as VARCHAR (80):
*
01 VARYING_STRING.
49 STRING_LENGTH PIC S9(4) USAGE IS COMP.
49 STRING_TEXT PIC X(80).
o OCCURS n TIMES clause
- OCCURS clauses are permitted only for declarations of
indicator arrays. Although you can use any data type for
indicator array elements, Oracle Rdb recommends that you
declare them as integers (PIC S9(9) COMP).
- Multidimension tables (nested OCCURS clauses) and variable-
occurrence data items (OCCURS DEPENDING ON clause) are not
supported.
o REDEFINES clauses
You can refer to host language variables that have a REDEFINES
clause or that are subordinate to a REDEFINES clause.
o SQL date-time data types
- 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.)
The precompiler replaces these data types with host language
data declarations that are supported in the compilers
themselves.
5 – FORTRAN Variables
The following list describes the variable declaration syntax
for character data types that the SQL precompiler supports in
FORTRAN:
o CHARACTER
o CHARACTER character-set-name
For information about the supported character sets, see the
Oracle Rdb SQL Reference Manual.
The following list describes the variable declaration syntax that
the SQL precompiler supports in FORTRAN:
o Declarations - See the following table
o Initial values assigned in the declaration
o STRUCTURE declarations
o UNION declarations within structures
o RECORD statements
o DIMENSION statements
- DIMENSION statements are permitted only for declarations
of indicator arrays. Although you can use any data type for
indicator array elements, Oracle Rdb recommends that you
use variables of the INTEGER data type.
- Multidimension arrays and dynamic-sized arrays are not
supported.
Table 8 Supported FORTRAN Datatypes
FORTRAN type SQL type Comments and Restrictions
BYTE TINYINT
CHARACTER*n CHAR The n represents a positive integer
literal
INTEGER INTEGER
INTEGER*1 TINYINT
INTEGER*2 SMALLINT
INTEGER*4 INTEGER
INTEGER*8 BIGINT
LOGICAL INTEGER
LOGICAL*1 TINYINT
LOGICAL*2 SMALLINT
LOGICAL*4 INTEGER
LOGICAL*8 BIGINT
REAL REAL
REAL*4 REAL
REAL*8 DOUBLE
PRECISION
STRUCTURE VARCHAR The named structure can be used
/name/ to define other FORTRAN host
integer*2 variables. The len component of
len the structure must be set to the
character*n correct length of the string before
body use as a parameter to SQL. The
END n represents a positive integer
STRUCTURE literal
SQL_DATE The SQL precompiler will transform
SQL_DATE_ the pseudo types in native FORTRAN
ANSI datatypes.
SQL_DATE_VMS
SQL_TIME
SQL_
TIMESTAMP
SQL_INTERVAL Use this data type for variables
(DAY TO that represent the difference
SECOND) between two dates or times.
Precompiler Date-Time Data Mapping
lists all the supported INTERVAL
data types.
Implicit declarations are not supported. SQL generates a "host
variable was not declared" error when it encounters an implicitly
declared variable in an SQL statement.
6 – Pascal Variables
The following list describes the variable declaration syntax that
the SQL precompiler supports in Pascal:
o Data type keywords
Declarations can include only the following Pascal data
types:
- INTEGER8, INTEGER16, INTEGER32, and INTEGER64
- REAL
- SINGLE
- DOUBLE
- F_FLOAT
- D_FLOAT
- G_FLOAT
- S_FLOAT
- T_FLOAT
- CHAR
- PACKED ARRAY [1..n] OF CHAR;
- VARYING [u] OF CHAR
- [BYTE] -128..127;
- [WORD] -32768..32767;
- Date-time data types (Precompiler Date-Time Data Mapping
lists these data types.)
In addition, the SQL Pascal precompiler provides the following
data types:
- SQL_LONG_VARCHAR
- SQL_DATE
- SQL_SMALLINT
- SQL_INDICATOR
- SQL_BIGINT
- SQL_QUAD
- 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 Records
The SQL precompiler supports Pascal record definitions. It
also supports nested records such as the following:
type_record_type = record
employee_id : employee_id_str;
last_name : last_name_str;
first_name : first_name_str;
middle_init : middle_init_str;
address_dat1: address_str;
address_dat2: address_str;
city : city_str;
state : state_str;
postal_code : postal_code_str;
sex : sex_str;
status_code : status_code_str;
end;
name_rec = record
last_name : last_name_str;
first_name : first_name_str;
middle_init : middle_init_str;
end;
address_rec = record
address_dat1 : address_str;
address_dat2 : address_str;
city : city_str;
state : state_str;
postal_code : postal_code_str;
end;
rec_in_rec = record
employee_id : employee_id_str;
emp_name : name_rec;
emp_addr : address_rec;
sex : sex_str;
status_code : status_code_str;
end;
rec_in_rec_in_rec = record
nested_again : rec_in_rec;
end;
A record that is used in an SQL statement cannot contain a
pointer to another record.
The SQL precompiler does not support variant records.
o Initial value assignments
The SQL precompiler supports initial values assigned in the
declaration:
dateind : SQL_INDICATOR:=0;
o Arrays
Packed arrays are supported to declare SQL character strings.
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 [WORD]-
32768..32767 or SQL_INDICATOR.
o Pointers
The SQL precompiler for Pascal supports one level of pointers.
type
a = ^integer;
var
b : a; (* the use of the variable b is supported *)
c : ^a; (* do not use any form of variable c in an SQL statement)
NOTE
The Pascal precompiler for SQL gives an incorrect %SQL-I-
UNMATEND error when it parses a declaration of an array
of records. It does not associate the END with the record
definition, and the resulting confusion in host variable
scoping causes a fatal error.
To avoid the problem, declare the record as a type and then
define your array of that type. For example:
main.spa:
program main (input,output);
type
exec sql include 'bad_def.pin'; !gives error
exec sql include 'good_def.pin'; !ok
var
a : char;
begin
end.
---------------------------------------------------------------
bad_def.pin
x_record = record
y : char;
variable_a: array [1..50] of record
a_fld1 : char;
b_fld2 : record;
t : record
v : integer;
end;
end;
end;
end;
---------------------------------------------------------------
good_def.pin
good_rec = record
a_fld1 : char;
b_fld2 : record
t : record
v: integer;
end;
end;
end;
x_record = record
y : char
variable_a : array [1..50] of good_rec;
end;
7 – PLI Variables
The following list describes the variable declaration syntax that
the SQL precompiler supports in PL/I:
o Declarations
Declarations can include only the following PL/I data types:
- CHARACTER
CHARACTER can be abbreviated as CHAR.
- CHARACTER VARYING
CHARACTER VARYING can be abbreviated as CHAR VAR.
- Date-time data types (Precompiler Date-Time Data Mapping
lists these data types.)
- TINYINT
TINYINT is FIXED BINARY(7).
- FIXED BINARY, FIXED DECIMAL
BINARY can be abbreviated as BIN, and DECIMAL can be
abbreviated as DEC. Scale factors are not allowed on FIXED
BINARY declarations.
- FLOAT BINARY, FLOAT DECIMAL
- 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.)
- DECIMAL data type is converted to FIXED
- NUMERIC data type is converted to PACKED
o Storage class attributes
Any of the storage class attributes (BASED, AUTOMATIC,
DEFINED, STATIC, variable, EXTERNAL, and INTERNAL) is allowed.
The BASED attribute declarations must include a location
reference.
o INITIAL attribute
o Structures
Structures are allowed without restriction.
o Arrays
Arrays are permitted only for declarations of indicator
arrays. Although you can use any data type for indicator
array elements, Oracle Rdb recommends that you declare them
as INTEGER variables.
Multidimension array items are not supported. Arrays of
structures are not supported. Arrays that are in a group that
is itself an array are not supported. Dynamic-sized arrays are
not supported.