HP 3000 Manuals HP 3000 Manuals
Declaring Host Variables
In the declaration section, you declare all host variables you use in any
executable section of your program.
Creating Declaration Sections
Host variables may be declared in either a global or local declaration
section of a main program, but only in a local declaration section of a
subprogram and only in a Level 1 procedure. You can reference the host
variables in any level procedure. These declarations can appear in the
main source code file and/or in any files included by this file.
At run time, the scope of a host variable is the same as that of any
other Pascal variables declared in the same declaration section. At
preprocessing time, however, all host variable declarations are treated
as global declarations.
In any declaration section, you declare host variables in what is known
as a declaration section. A declaration section consists of the SQL
command BEGIN DECLARE SECTION, one or more variable declarations, and the
SQL command END DECLARE SECTION, as shown in Figure 4-1 . More than
one declaration section may appear in any declaration section. The same
host variable name cannot appear in more than one declaration section.
Each host variable is declared by using a Pascal type description. The
declaration contains the same components as any Pascal variable
declaration:
EXEC SQL BEGIN DECLARE SECTION;
OrderNumber : integer;
| |
| |
| a data type
|
a data name
EXEC SQL END DECLARE SECTION;
The data name must be the same as the corresponding host variable name in
the statement section. The data type must satisfy ALLBASE/SQL data type
and Pascal preprocessor requirements.
_________________________________________________
| |
| Program EXAMPLE (input, output); |
| var |
| . |
| . |
| . |
| EXEC SQL BEGIN DECLARE SECTION; |
| . |
| . Declarations for global host variables.|
| . |
| EXEC SQL END DECLARE SECTION; |
| . |
| . |
| . |
| PROCEDURE QUERY; |
| var |
| . |
| . |
| . |
| EXEC SQL BEGIN DECLARE SECTION; |
| . |
| . Declarations for local host variables. |
| . |
| EXEC SQL END DECLARE SECTION; |
| . |
| . |
| . |
| begin |
| . |
| . |
| . |
| end; |
| . |
| . |
| . |
| begin |
| . |
| . |
| . |
| end. |
| |
| |
| |
| |
_________________________________________________
Figure 4-1. Host Variable Declarations
Declaring Variables for Data Types.
Table 4-1 summarizes the syntax of data type descriptions for host
variables holding each type of ALLBASE/SQL data. It also illustrates how
to declare indicator variables, arrays for holding multiple rows, and
host variables that hold dynamic commands, savepoint numbers, message
catalog messages, and DBEnvironment names. Only the type descriptions
shown in Table 4-1 are supported by the Pascal preprocessor. The
preprocessor does not, for example, support user defined types.
You can also declare program variables that are not host variables within
a declaration section. All variables that appear in a declaration
section, however, must have Pascal data types illustrated in Table 4-1
.
CHAR Data.
You can insert strings ranging from 1 to 3996 characters into a CHAR
column.
When ALLBASE/SQL assigns data to a char host variable, it adds blanks if
necessary on the right of the string to fill up the accepting variable.
VARCHAR Data.
VARCHAR strings can range from 1 to 3996 characters. ALLBASE/SQL stores
only the actual value of the string, not any trailing blanks.
The string data type in Pascal is equivalent to the VARCHAR data type in
ALLBASE/SQL. The string data type in Pascal stores the actual length of
the string in a four-byte field preceding the string itself. The
VendorRemarks column in the PurchDB.Vendors table is defined as
VARCHAR(60). It is therefore declared as follows:
VendorRemarks : string[60];
On output, you can use the Pascal strlen function to determine the actual
length of data ALLBASE/SQL assigns to an output host variable declared as
a string. On input, ALLBASE/SQL automatically stores only the actual
value of the string.
SMALLINT Data.
Values can range from -32768 to +32767 in a column defined as SMALLINT.
When the Pascal preprocessor detects a host variable declared as
SmallInt, it defines the host variable as follows in SQLTYPE:
type
SmallInt = shortint;
INTEGER Data.
Values can range from -2,147,483,648 to +2,147,483,647 in a column
defined as INTEGER.
Table 4-1. Data Type Declarations
--------------------------------------------------------------------------------------------
| | |
| SQL DATA TYPES | PASCAL TYPE DESCRIPTION |
| | |
--------------------------------------------------------------------------------------------
| | |
| CHAR(1) | DataName : char; |
| | |
| CHAR(n) | DataName : array [1..n] of char; or DataName : packed array |
| | [1..n] of char; |
| | |
| VARCHAR(n) | DataName : string[n]; |
| | |
| BINARY(1) | DataName : char; |
| | |
| BINARY(n) | DataName : array [1..n] of char; or DataName : packed array |
| | [1..n] of char; |
| | |
| VARBINARY(n) | DataName : string[n]; |
| | |
| SMALLINT | DataName : smallint; |
| | |
| INTEGER | DataName : integer; |
| | |
| REAL | DataName : real; |
| | |
| FLOAT | DataName : longreal; |
| | |
| DECIMAL | DataName : longreal; |
| | |
| DATE | DataName : packed array[1..10] of char; 1 |
| | |
| TIME | DataName : packed array[1..8] of char; 1 |
| | |
| DATETIME | DataName : packed array[1..23] of char; 1 |
| | |
| INTERVAL | DataName : packed array[1..20] of char; 1 |
| | |
--------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
| |
| 1 Applies to default format specification only. |
| |
------------------------------------------------------------------------------------------
FLOAT Data.
ALLBASE/SQL offers the option of specifying the precision of floating
point data. You have the choice of a 4-byte or an 8-byte floating point
number. The keywords REAL and FLOAT(1) through FLOAT(24) specifications
map to a 4-byte float. The FLOAT(25) through FLOAT(53) and DOUBLE
PRECISION specifications map to an 8-byte float.
The REAL data type could be useful when the number you are dealing with
is very small, and you do not require a great deal of precision.
However, it is subject to overflow and underflow errors if the value goes
outside its range. It is also subject to greater rounding errors than
double precision. With the DOUBLE PRECISION (8-byte float) data type,
you can achieve significantly higher precision and have available a
larger range of values.
By using the CREATE TABLE or ALTER TABLE command, you can define a
floating point column by using a keyword from the following table. See
the ALLBASE/SQL Reference Manual for complete syntax specifications.
Table 4-2. ALLBASE/SQL Floating Point Column Specifications
------------------------------------------------------------------------------------------------
| | | |
| Possible Keywords | Range of Possible Values | Stored In |
| | | and Boundary |
| | | Aligned On |
| | | |
------------------------------------------------------------------------------------------------
| | | |
| REAL | -3.402823 E+38 through -1.175495 E-38 | 4 bytes |
| or | and | |
| FLOAT(n) | 1.175495 E-38 through 3.402823 E+38 | |
| where | and | |
| | 0 | |
| n = 1 through 24 | | |
| | | |
------------------------------------------------------------------------------------------------
| | | |
| DOUBLE PRECISION | -1.79769313486231 E+308 through -2.22507385850721 E-308 | 8 bytes |
| or | and | |
| FLOAT | +2.22507385850721 E-308 through +1.79769313486231 E+308 | |
| or | and | |
| FLOAT(n) | 0 | |
| where | | |
| | | |
| n = 25 through 53 | | |
| | | |
------------------------------------------------------------------------------------------------
Floating Point Data Compatibility.
Floating point data types are compatible with each other and with other
ALLBASE/SQL numeric data types (DECIMAL, INTEGER, and SMALLINT). All
arithmetic operations and comparisons and aggregate functions are
supported.
BINARY Data.
As with other data types, use the CREATE TABLE or ALTER TABLE command to
define a BINARY or VARBINARY column. Up to 3996 bytes can be stored in
such a column.
BINARY data is stored as a fixed length of left-justified bytes. It is
zero padded up to the fixed length you have specified. VARBINARY data is
stored as a variable length of left-justified bytes. You specify the
maximum possible length. (Note that CHAR and VARCHAR data is stored in a
similar manner except that CHAR data is blank padded.)
Binary Data Compatibility.
BINARY and VARBINARY data types are compatible with each other and with
CHAR and VARCHAR data types. They can be used with all comparison
operators and the aggregate functions MIN and MAX, but arithmetic
operations are not allowed.
Using the LONG Phrase with Binary Data Types.
If the amount of data in a given column of a row can exceed 3996 bytes,
it must be defined as a LONG column. Use the CREATE TABLE or ALTER TABLE
command to specify the column as either LONG BINARY or LONG VARBINARY.
LONG BINARY and LONG VARBINARY data is stored in the database just as
BINARY and VARBINARY data, except that its maximum possible length is
practically unlimited.
Use LONG VARBINARY when saving space is your main consideration.
However, LONG BINARY offers faster data access.
LONG BINARY and LONG VARBINARY data types are compatible with each other,
but not with other data types. Also, the concept of inputting and
accessing LONG column data differs from that of other data types. Refer
to the ALLBASE/SQL Reference Manual for detailed syntax and to the
chapter "Programming with LONG Columns" for information about using LONG
column data.
DECIMAL Data.
The DECIMAL data type is not supported in Pascal, but it is compatible
with a Pascal longreal data type. If you use the DECIMAL data type in a
dynamically preprocessed PREPARE statement with an output data buffer or
in BULK operations, you must code Pascal statements yourself to convert
Binary Coded Decimal (BCD) representation to character representation.
If you use an input buffer with dynamic preprocessing, you must also
write code that converts the character representation to BCD format
before the data is placed in the input buffer. An example of Pascal code
to do this conversion is shown in Procedure BCDToString in the sample
program pasex10a, Figure 10-9.
When you use DECIMAL values in arithmetic operations and certain
aggregate functions, the precision and scale of the result are functions
of the precisions and scales of the values in the operation. Refer to
the ALLBASE/SQL Reference Manual for a complete account of how to
calculate the precision and scale of DECIMAL results.
DATE, TIME, DATETIME, and INTERVAL Data.
EXEC SQL BEGIN DECLARE SECTION;
BatchStamp : packed array[1..23] of char; (* DATETIME DATA TYPE *)
TestDate : packed array[1..10] of char; (* DATE DATA TYPE *)
TestDateInd : SqlInd;
TestStart : packed array[1..8] of char; (* TIME DATA TYPE *)
TestStartInd : SqlInd;
LabTime : packed array[1..20] of char; (* INTERVAL DATA TYPE *)
LabTimeInd : SqlInd;
EXEC SQL END DECLARE SECTION;
(* DECLARE and OPEN CURSOR C1 here. Nulls not allowed for BatchStamp. *)
EXEC SQL FETCH C1
INTO :BatchStamp,
:TestDate :TestDateInd,
:TestStart :TestStartInd,
:LabTime :LabTimeInd;
Using Default Data Values
You can choose a default value other than NULL when you create or alter a
table by using the DEFAULT specification. Then when data is inserted,
and a given column is not in the insert list, the specified default value
is inserted. Or when you alter a table, adding a column to existing
rows, every occurrence of the column is initialized to the default value.
When a table or column is defined with the DEFAULT specification, you
will not get an error if a column defined as NOT NULL is not specified in
the insert list of an INSERT command. Without the DEFAULT specification,
if a column is defined as NOT NULL, it must have some value inserted into
it. However, if the column is defined with the DEFAULT specification, it
satisfies both the requirement that it be NOT NULL and have some value,
in this case, the default value (unless the DEFAULT value is NULL). If a
column not in an insert list does allow a NULL, then a NULL is inserted
instead of the default value.
Your default specification options are as follows:
* NULL.
* USER (this indicates the current DBEUserID).
* A constant.
* The result of the CURRENT_DATE function.
* The result of the CURRENT_TIME function.
* The result of the CURRENT_DATETIME function.
Complete syntax for the CREATE TABLE and ALTER TABLE commands as well as
definitions of the above options are found in the ALLBASE/SQL Reference
Manual .
In effect, by choosing any option other than NULL, you assure the
column's value to be NOT NULL and of a particular format, unless and
until you use the UPDATE command to enter another value.
In the following example, the OrderNumber column defaults to the constant
5, and it is possible to insert a NULL value into the column:
CREATE PUBLIC TABLE PurchDB.Orders (
OrderNumber INTEGER DEFAULT 5,
VendorNumber INTEGER,
OrderDate CHAR(8))
IN OrderFS
However, suppose you want to define a column default and specify that the
column cannot be null. In the next example, the OrderNumber column
defaults to the constant 5, and it is not possible to insert a NULL value
into this column:
CREATE PUBLIC TABLE PurchDB.Orders (
OrderNumber INTEGER DEFAULT 5 NOT NULL,
VendorNumber INTEGER,
OrderDate CHAR(8))
IN OrderFS
Coding Considerations.
Any default value must be compatible with the data type of its
corresponding column. For example, when the default is an integer
constant, the column for which it is the default must be created with an
ALLBASE/SQL data type of INTEGER, REAL, or FLOAT.
In your application, you input or access data for which column defaults
have been defined just as you would data for which defaults are not
defined. In this chapter, refer to the section "Declaring Variables for
Data Types" for information on using the data types in your program.
Also refer to the section "Declaring Variables for Compatibility" for
information relating to compatibility.
When the DEFAULT Clause Cannot be Used.
* You can specify a default value for any ALLBASE/SQL column except
those defined as LONG BINARY or LONG VARBINARY. For information on
these data types, see the section in this chapter titled "Using
the LONG Phrase with Binary Data Types."
* With the CREATE TABLE command, you can use either a DEFAULT NULL
specification or the NOT NULL specification. An error results if
both are specified for a column as in the next example:
CREATE PUBLIC TABLE PurchDB.Orders (
OrderNumber INTEGER DEFAULT NULL NOT NULL,
VendorNumber INTEGER,
OrderDate CHAR(8))
IN OrderFS
Declaring Variables for Compatibility
Under the following conditions, ALLBASE/SQL performs data type conversion
when executing SQL commands containing host variables:
* When the data types of values transferred between your program and
a DBEnvironment do not match.
* When data of one type is moved to a host variable of a different
type.
* When values of different types appear in the same expression.
Data types for which type conversion can be performed are called
compatible data types. Table 4-3 summarizes data type-host variable
compatibility. It also points out which data type combinations are
incompatible and which data type combinations are equivalent, i.e.,
require no type conversion. E describes an equivalent situation, C a
compatible situation, and I an incompatible situation.
Table 4-3. Pascal Data Type Equivalency and Compatibility
--------------------------------------------------------------------------------------------------------
| | | | | | | |
| ALLBASE/ | CHAR | STRING | SMALLINT | INTEGER | LONGREAL | PACKED ARRAY |
| SQL | | | | | | OF CHAR |
| DATA | | | | | | |
| TYPES | | | | | | |
| | | | | | | |
--------------------------------------------------------------------------------------------------------
| | | | | | | |
| CHAR | E | C | I | I | I | E |
| | | | | | | |
| VARCHAR | C | E | I | I | I | C |
| | | | | | | |
| BINARY | C | C | I | I | I | C |
| | | | | | | |
| VARBINARY | C | C | I | I | I | C |
| | | | | | | |
| DATE | E | C | I | I | I | I |
| | | | | | | |
| TIME | E | C | I | I | I | I |
| | | | | | | |
| DATETIME | E | C | I | I | I | I |
| | | | | | | |
| INTERVAL | E | C | I | I | I | I |
| | | | | | | |
| SMALLINT | I | I | E | C | C | I |
| | | | | | | |
| INTEGER | I | I | C | E | C | I |
| | | | | | | |
| DECIMAL | I | I | C | C | C | I |
| | | | | | | |
| REAL | I | I | C | C | E | I |
| | | | | | | |
| FLOAT | I | I | C | C | E | I |
| | | | | | | |
--------------------------------------------------------------------------------------------------------
As the following example illustrates, the INFO command available in ISQL
provides the information you need to declare host variables compatible
with or equivalent to ALLBASE/SQL data types. It also provides the
information you need to determine whether an indicator variable is needed
to handle null values.
isql=> INFO PurchDB.OrderItems;
Column Name Data Type (length) Nulls Allowed
---------------------------------------------------------------------
ORDERNUMBER Integer NO
ITEMNUMBER Integer NO
VENDPARTNUMBER Char (16) YES
PURCHASEPRICE Decimal (10,2) NO
ORDERQTY Smallint YES
ITEMDUEDATE Char (8) YES
RECEIVEDQTY Smallint YES
The example identified in Figure 4-2 produces a single-row query result.
The declare section contains data types equivalent to or compatible with
the data types in the PurchDB.OrderItems table:
* OrderNumber is an integer variable because the column whose data
it holds is INTEGER.
* PurchasePrice is declared as a LONGREAL variable because it holds
a DECIMAL column.
* Discount is declared as a LONGREAL variable because it is used in
an arithmetic expression with a DECIMAL column.
* OrderQty is declared as a SmallInt variable because it holds the
SMALLINT result of a SMALLINT column, OrderQty.
* OrderQtyInd is an indicator variable, necessary because the
resulting OrderQty can contain null values. Note in the INFO
example above that this column allows null values.
_______________________________________________________
| |
| var |
| . |
| . |
| . |
| |
| EXEC SQL BEGIN DECLARE SECTION; |
| . |
| . |
| . |
| Discount : longreal; |
| PurchasePrice : longreal; |
| OrderQty : SmallInt; |
| OrderQtyInd : SqlInd; |
| OrderNumber : integer; |
| . |
| . |
| . |
| EXEC SQL END DECLARE SECTION; |
| . |
| . |
| . |
| begin |
| . |
| . |
| . |
| EXEC SQL SELECT PurchasePrice * :Discount,|
| OrderQty, |
| INTO :PurchasePrice, |
| :OrderQty :OrderQtyInd |
| FROM PurchDB.OrderItems |
| WHERE OrderNumber = :OrderNumber|
| |
_______________________________________________________
Figure 4-2. Declaring Host Variables for Single-Row Query Result
The example identified in Figure 4-3 is similar to that in Figure 4-2.
This query, however, is a BULK query, which may return a multiple-row
query result. It also incorporates a HAVING clause. Here are some
points to notice:
* OrdersArray is the name of the array for storing the query result.
It can hold up to 26 rows. Each row in the array has the same
format as that in the single-row query result just discussed.
* FirstRow and TotalRows are declared as SmallInt variables, since
their maximum value is the size of the array, in this case, 26.
* GroupCriterion is an integer variable because its value is
compared in the HAVING clause with the result of a COUNT function,
which is always an INTEGER value.
___________________________________________________________________________
| |
| Program EXAMPLE (input, output); |
| . |
| . |
| . |
| var |
| |
| EXEC SQL BEGIN DECLARE SECTION; |
| . |
| . |
| . |
| Discount : longreal; |
| OrdersArray : packed array[1..26] of packed record|
| PurchasePrice : longreal; |
| OrderQty : SmallInt; |
| OrderQtyInd : SqlInd; |
| OrderNumber : integer; |
| end; |
| FirstRow : SmallInt; |
| TotalRows : SmallInt; |
| LowValue : integer; |
| HighValue : integer; |
| GroupCriterion : integer; |
| EXEC SQL END DECLARE SECTION; |
| . |
| . |
| . |
| begin |
| . |
| . |
| . |
| EXEC SQL BULK SELECT PurchasePrice * :Discount, |
| OrderQty, |
| OrderNumber |
| INTO :OrdersArray, |
| :FirstRow, |
| :TotalRows |
| FROM PurchDB.OrderItems |
| WHERE OrderNumber |
| BETWEEN :LowValue AND :HighValue |
| GROUP BY OrderQty, OrderNumber |
| HAVING COUNT(ItemNumber) > :GroupCriterion; |
| |
| |
| |
___________________________________________________________________________
Figure 4-3. Declaring Host Variables for Multiple-Row Query Result
String Data Conversion.
When ALLBASE/SQL moves VARCHAR data to a packed array of CHAR variable of
a larger size, it pads the string on the right with spaces to fill up the
host variable. When ALLBASE/SQL stores the value in a string host
variable into a CHAR column, it pads the value on the right with spaces
to fill up the column.
String Data Truncation.
If the target host variable used in a SELECT or FETCH operation is too
small to hold an entire string, the string is truncated. You can use an
indicator variable to determine the actual length of the string before
truncation:
EXEC SQL BEGIN DECLARE SECTION.
LittleString : packed array[1..n] of char;
LittleStringInd : SqlInd;
.
.
.
EXEC SQL END DECLARE SECTION.
.
.
.
begin
.
.
.
EXEC SQL SELECT BigString
INTO :LittleString :LittleStringInd;
.
.
.
When the value in column BigString is too long to fit in host variable
LittleString, ALLBASE/SQL puts the actual byte length of the string into
indicator variable LittleStringInd.
If a column is too small to hold a string in an INSERT or an UPDATE
operation, the string is truncated and stored, but ALLBASE/SQL gives no
error or warning indication.
It is possible to store native language data in a character column
defined as ASCII. It is the programmer's responsibility to verify the
language definition of the column that is to receive the data. If the
character column is defined for a native language, truncation always
occurs on a proper character boundary for that language.
Numeric Data Conversion.
When you use numeric data of different types in an expression or
comparison operation, data types with less precision are converted into
data types of greater precision. The result has the greater precision.
ALLBASE/SQL numeric types available in Pascal have the following
precedence, from highest to lowest:
1. FLOAT
2. DECIMAL
3. INTEGER
4. SMALLINT
The following example illustrates numeric type conversion:
EXEC SQL BEGIN DECLARE SECTION;
Discount : integer;
MaxPurchasePrice : integer;
.
.
.
EXEC SQL END DECLARE SECTION;
.
.
.
begin
.
.
.
EXEC SQL SELECT MAX(PurchasePrice) * :Discount
INTO :MaxPurchasePrice
FROM PurchDB.OrderItems;
The select list of the query illustrated contains an aggregate function,
MAX. The argument of the function is the PurchasePrice column, defined in
the PartsDBE DBEnvironment as DECIMAL(10,2). Therefore the result of the
function is DECIMAL. Since the host variable named Discount is declared
as an integer, a data type compatible with DECIMAL, ALLBASE/SQL converts
the value in Discount to a DECIMAL quantity having a precision of 10 and
a scale of 0.
After multiplication, data conversion occurs again before the DECIMAL
result is stored in the integer host variable MaxPurchasePrice . In this
case, the fractional part of the DECIMAL value is truncated.
Refer to the ALLBASE/SQL Reference Manual for additional information on
how type conversion can cause truncation and overflow of numeric values.
Declaring Variables for Program Elements
The following section discusses how to declare elements specific to
ALLBASE/SQL programs. Table 4-4 provides the syntax of these special
elements.
Table 4-4. Program Element Declarations
------------------------------------------------------------------------------
| | |
| PROGRAM ELEMENT | PASCAL TYPE DESCRIPTION |
| | |
------------------------------------------------------------------------------
| | |
| Indicator variable | IndVarName : SqlInd; |
| | |
------------------------------------------------------------------------------
| | |
| Array of n rows | ArrayName : packed or unpacked array |
| | [1..n] of packed or unpacked record |
| | |
| Data values | Column1Name : Valid data type; |
| | Column2Name : Valid data type; |
| | |
------------------------------------------------------------------------------
| | |
| Indicator variable | IndVarName : SqlInd; |
| | |
| StartIndex | StartIndexName : smallint or |
| | |
| | StartIndexName : integer; |
| | |
| NumberOfRows | NumRowsName : smallint or |
| | |
| | NumRowsName : integer; |
| | |
------------------------------------------------------------------------------
| | |
| Dynamic commands | CommandName : packed array[1..n] of char; or |
| | |
| | CommandName : string[n]; |
| | |
------------------------------------------------------------------------------
| | |
| Savepoint numbers | SavepointName : smallint; or |
| | |
| | SavepointName ; integer; |
| | |
------------------------------------------------------------------------------
| | |
| Message catalog messages | MessageName : packed array[1..n] of char; or |
| | |
| | MessageName : string[n]; |
| | |
------------------------------------------------------------------------------
| | |
| DBEnvironment name | DBEName : packed array[1..n] of char; or |
| | |
| | DBEName : string[n]; |
| | |
------------------------------------------------------------------------------
SQLCA Array.
Every ALLBASE/SQL Pascal main program must have the SQL Communications
Area (SQLCA) declared in the global declaration part. You can use the
INCLUDE command to declare the SQLCA:
EXEC SQL INCLUDE SQLCA;
When the preprocessor parses this command, it inserts the following type
definition into the modified source file:
sqlca: Sqlca_Type:
Optionally, you can use this type definition in the global declaration
part of your source file instead of using the INCLUDE command to declare
the sqlca.
Refer to the chapter, "Runtime Status Checking and the SQLCA," for
further information regarding the sqlca.
Dynamic Processing Arrays.
For programs which accept dynamic queries, you include three special
declarations in a declaration part:
EXEC SQL INCLUDE SQLDA;
This command causes the preprocessor to declare the SQLDA as
type SQLDA_TYPE, defined in the preprocessor-generated type
declaration include file.
SQLFmts : array [1..MaxFmtArray] of SQLFormat_type;
This declaration identifies the format array and its size.
MaxFmtArray is a constant representing the maximum number
of columns you expect in the query result. SQLFORMAT_TYPE
is defined in the type declaration include file.
DataBuffer : packed array [1..MaxDataBuff] or char;
This declaration identifies a data buffer and its size.
MaxDataBuff is a constant representing the maximum number
of bytes you will need to hold the number of rows you
request in the SqlNRow field of the SQLDA.
See the chapter on "Using Dynamic Operations" for more information.
Bulk Processing Arrays.
When you declare a structure array for holding the results of a BULK
SELECT or BULK FETCH operation, ensure that you declare the fields in the
same order as in the select list. (For single-row query results,
however, the order of declaration does not have to match the select list
order.) In addition, each indicator variable field must be declared in
the declaration of the structure array immediately after the host
variable field it describes. And if used, the bulk processing indicator
variables (starting index and number of rows) are referenced in order,
immediately following the reference to your array name. Refer back again
to Figure 4-3 for an example.
Indicator Variables.
Each indicator variable field used in a BULK SELECT must be declared
immediately following the host variable field it describes, as shown in
Figure 4-3. Figure 4-2 shows the indicator variable optionally declared
immediately following the host variable field. If a column allows nulls,
a null indicator must be declared for it.
Dynamic Commands.
The maximum size for the host variables used to hold dynamic commands is
32,762 bytes. However, in Figure 4-4, the host variable is declared to
hold the maximum size of dynamic command: 2048 bytes.
__________________________________________________
| |
| var |
| . |
| . |
| . |
| |
| EXEC SQL BEGIN DECLARE SECTION; |
| . |
| . |
| . |
| Dynamic Command : string[2048];|
| . |
| . |
| . |
| EXEC SQL END DECLARE SECTION; |
| . |
| . |
| . |
| begin |
| . |
| . |
| . |
| EXEC SQL PREPARE CommandOnTheFly |
| FROM :DynamicCommand ; |
__________________________________________________
Figure 4-4. Declaring Host Variables for Dynamic Commands
Savepoint Numbers.
Savepoint numbers are positive numbers ranging from 1 to 2,147,483,647.
A host variable for holding a savepoint number should be declared as an
integer.
_________________________________________________
| |
| : |
| EXEC SQL BEGIN DECLARE SECTION; |
| Savepoint1 : integer; |
| : |
| EXEC SQL END DECLARE SECTION; |
| : |
| EXEC SQL SAVEPOINT :Savepoint1; |
| : |
| EXEC SQL ROLLBACK WORK TO :Savepoint1;|
| |
_________________________________________________
Figure 4-5. Declaring Host Variables for Savepoint Numbers
Messages from the Message Catalog.
The maximum size of a message catalog message is 256 bytes.
Figure 4-6 illustrates how a host variable for holding a message might be
declared.
___________________________________________
| |
| Program EXAMPLE (input, output); |
| : |
| var |
| |
| EXEC SQL BEGIN DECLARE SECTION; |
| SQLMessage :string[256] ;|
| : |
| EXEC SQL END DECLARE SECTION; |
| : |
| begin |
| : |
| EXEC SQL SQLEXPLAIN :SQLMessage ; |
| writeln (SQLMessage); |
| |
___________________________________________
Figure 4-6. Declaring Host Variables for Message Catalog Messages
DBEnvironment Name.
The maximum pathname (either relative or absolute) of a DBECon file is
128 bytes. The DBECon file name is the same as the DBEnvironment name.
The name you store in this host variable does not have to be delimited by
single quotation marks.
_____________________________________________
| |
| var |
| EXEC SQL BEGIN DECLARE SECTION; |
| . |
| . |
| . |
| SomDBE : string[128]; |
| . |
| . |
| . |
| EXEC SQL END DECLARE SECTION; |
| . |
| . |
| . |
| begin |
| prompt ('Enter DBEnvironment name> ');|
| readln (SomeDBE); |
| EXEC SQL CONNECT to :SomeDBE; |
| |
_____________________________________________
Figure 4-7. Declaring Host Variables for DBEnvironment Names
This host variable can be declared as a string or as a character array.
In the example, it is declared as a character array large enough to hold
the absolute file name of any DBECon file.