HP 3000 Manuals HP 3000 Manuals
Declaring Host Variables
You must declare all host variables in the Host Variable Declaration
Section of the program unit where the host variable is used. In other
words, a host variable used in the main program unit must be defined in
the Host Variable Declaration Section of the main program unit. A host
variable used in a subprogram unit must be defined in the Host Variable
Declaration Section of that subprogram unit.
Declaring Variables for Data Types
All FORTRAN program units that contain embedded SQL commands must have a
Host Variable Declaration Section. If your program unit does not use
host variables but does contain embedded SQL statements, it must still
contain a Host Variable Declaration Section to satisfy FORTRAN
preprocessor requirements. If your program unit does not contain
embedded SQL statements then no Host Variable Declaration Section is
needed. The Host Variable Declaration Section is delimited by the EXEC
SQL BEGIN DECLARE SECTION and EXEC SQL END DECLARE SECTION commands.
Host variables must be declared in every program unit where they are
used. A Type Declaration Section may exist in any program or subprogram
unit:
* If the program unit uses host variables from a called program or
subprogram unit, you declare these host variables in the Host
Variable Declaration Section of both the calling and the called
program units.
* If host variable values come from an MPE XL data file or are
written to an MPE XL data file in the program, you also declare
these host variables in the Host Variable Declaration Section.
Regardless of where in a program unit the host variables are declared,
they must appear between the BEGIN DECLARE SECTION and END DECLARE
SECTION commands, as shown in Figure 4-1. These commands and any host
variable declarations they delimit are referred to as a Declare Section.
No more than one Declaration Section can appear in any one program unit.
Variable Declarations
Each host variable is declared by using a FORTRAN variable type
declaration. The declaration contains the same components as any FORTRAN
variable type declaration:
EXEC SQL BEGIN DECLARE SECTION
CHARACTER*16 OrderNumber
| |
| |
| a variable name
|
a variable type
EXEC SQL END DECLARE SECTION
The host variable name must be the same as the corresponding host
variable name used in the SQL commands of that program unit. The
variable type must satisfy ALLBASE/SQL and FORTRAN data type preprocessor
requirements.
______________________________________________
| |
| PROGRAM Main |
| . |
| . |
| . |
| EXEC SQL INCLUDE SQLCA |
| . |
| . |
| . |
| EXEC SQL BEGIN DECLARE SECTION |
| . |
| . Declarations for host variables|
| . |
| EXEC SQL END DECLARE SECTION |
| . |
| . Embedded SQL commands |
| . |
| END |
| |
| SUBROUTINE Query |
| . |
| . |
| . |
| EXEC SQL INCLUDE SQLCA |
| . |
| . |
| . |
| EXEC SQL BEGIN DECLARE SECTION |
| . |
| . Declarations for host variables|
| . |
| EXEC SQL END DECLARE SECTION |
| . |
| . Embedded SQL commands |
| . |
| RETURN |
| END |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
______________________________________________
Figure 4-1. Host Variable Declarations
Data Types.
Table 4-1 summarizes how to write data descriptions for host variables
holding each type of ALLBASE/SQL data. It also illustrates how to
declare indicator variables, and host variables that hold dynamic
commands, savepoint numbers, message catalog messages, and DBEnvironment
names. Only the type declarations shown in Table 4-1 are supported by
the FORTRAN preprocessor. The preprocessor does not support user defined
data types.
You can also declare program variables that are not host variables within
a declare section. All variables that appear in a declare section,
however, must have FORTRAN data types among those illustrated in Table
4-1. Table 4-2 shows data descriptions for ALLBASE/SQL program elements.
CHARACTER Data.
You can insert strings ranging from 1 to 3996 bytes into a CHARACTER
column.
When ALLBASE/SQL assigns data to a CHARACTER 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 bytes. ALLBASE/SQL stores the
actual length of the string in a four-byte field preceding the string
itself. In addition, ALLBASE/SQL stores only the actual value of the
string, not any trailing blanks.
The CHARACTER data type in FORTRAN is equivalent to the VARCHAR data type
in ALLBASE/SQL. The VendorRemarks column in the PurchDB.Vendors table is
defined as VARCHAR(60). It is therefore declared as follows:
CHARACTER*60 VendorRemarks
SMALLINT Data.
You can assign values ranging from -32768 to 32767 to a column defined as
SMALLINT. The INTEGER*2 data type in FORTRAN is equivalent to the
SMALLINT data type in ALLBASE/SQL.
INTEGER*2 VariableName
INTEGER Data.
You can assign values ranging from -2,147,483,648 to 2,147,483,647 to a
column defined as INTEGER.
Table 4-1. Data Description Entries for Host Variables
--------------------------------------------------------------------------------------------
| | |
| SQL DATA TYPES | FORTRAN DATA DECLARATIONS |
| | |
--------------------------------------------------------------------------------------------
| | |
| CHAR(1) | CHARACTER DataName |
| | |
| CHAR(n) | CHARACTER*n DataName |
| | |
| VARCHAR(n) | CHARACTER*n DataName * |
| | |
| SMALLINT | INTEGER*2 DataName |
| | |
| INTEGER | INTEGER DataName |
| | |
| REAL | REAL DataName or |
| | |
| | REAL*4 DataName |
| | |
| FLOAT(1..24) | REAL DataName or |
| | |
| | REAL*4 DataName |
| | |
| FLOAT(1..53) | DOUBLE PRECISION DataName or |
| | |
| | REAL*8 DataName |
| | |
| DOUBLE PRECISION | DOUBLE PRECISION DataName or |
| | |
| | REAL*8 DataName |
| | |
| BINARY | CHARACTER DataName or |
| | |
| | CHARACTER*n DataName |
| | |
| VARBINARY | CHARACTER*n DataName |
| | |
| DECIMAL | DOUBLE PRECISION DataName or |
| | |
| | REAL*8 DataName |
| | |
| DATE | CHARACTER*10 DataName |
| | |
| TIME | CHARACTER*8 DataName |
| | |
| DATETIME | CHARACTER*23 DataName |
| | |
| INTERVAL | CHARACTER*20 DataName |
| | |
| | * This declaration is for non-dynamic commands only. Refer |
| | to the chapter, "Using Dynamic Operations", for a |
| | description of how to use VARCHAR dynamically. |
| | |
--------------------------------------------------------------------------------------------
Table 4-2. Program Element Declarations
--------------------------------------------------------------------------------------------
| | |
| PROGRAM ELEMENT | FORTRAN DATA DECLARATIONS |
| | |
--------------------------------------------------------------------------------------------
| | |
| Indicator variable | SQLIND or INTEGER*2 IndVarName |
| | |
--------------------------------------------------------------------------------------------
| | |
| Dynamic commands | CHARACTER*n CommandName |
| | |
--------------------------------------------------------------------------------------------
| | |
| Savepoint numbers | INTEGER SavepointName |
| | |
--------------------------------------------------------------------------------------------
| | |
| Message catalog messages | CHARACTER*n MessageName |
| | |
--------------------------------------------------------------------------------------------
| | |
| DBEnvironment name | CHARACTER*n DBEnvironmentName |
| | |
--------------------------------------------------------------------------------------------
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. (This conforms to ANSI SQL86 level 2 specifications.) The
keyword REAL, and FLOAT(1) through FLOAT(24), 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-3. 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. Each byte contains two hexadecimal digits. For example,
suppose you insert data via a host variable into a database column
defined as binary. The host variable contains the digits, 1234. In the
database, these four digits are stored in two bytes. Each nibble (half
byte) contains one digit in hexadecimal format.
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.
When deciding on whether to use LONG BINARY versus LONG VARBINARY, and if
space is your main consideration, you would choose LONG VARBINARY.
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 in this document titled "Defining and Using Long Columns" for
information about using LONG column data.
DECIMAL Data.
The DECIMAL data type is not supported by FORTRAN 77. The DECIMAL data
type is compatible with a FORTRAN DOUBLE PRECISION data type.
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.
DATE, TIME, DATETIME, and INTERVAL data types are declared as character
strings. (See the previous section, "CHARACTER Data.") For example:
EXEC SQL BEGIN DECLARE SECTION
C *** DATETIME DATA TYPE ***
CHARACTER*23 BatchStamp
C *** DATE DATA TYPE ***
CHARACTER*10 TestDate
SQLIND TestDateInd
C *** TIME DATA TYPE ***
CHARACTER*8 TestStart
SQLIND TestStartInd
C *** INTERVAL DATA TYPE ***
CHARACTER*20 LabTime
SQLIND LabTimeInd
EXEC SQL END DECLARE SECTION
C *** DECLARE and OPEN CURSOR C1 here. ***
C *** Nulls not allowed for BatchStamp. ***
EXEC SQL FETCH C1
1 INTO :BatchStamp,
2 :TestDate :TestDateInd,
2 :TestStart :TestStartInd,
4 :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.
(This conforms to ANSI SQL1 level 2 with addendum-1 and FIPS 127
standards.)
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. 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:
* NULL.
* USER (this indicates the current DBEUser ID).
* 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 information on using the data types in your program. Also refer to
the section, "Data Type 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 document 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
Indicator Variable Declaration.
Each indicator variable must be declared immediately following the host
variable it describes.
SQLIND VariableNameInd
When the FORTRAN preprocessor encounters SQLIND, it generates the
following declaration in its place in the modified source file:
INTEGER*2 VariableNameInd
Dynamic Command Variable Declaration.
The maximum size of the host variables used to hold dynamic commands is
32,762 bytes.
Savepoint Number Variable Declaration.
Since the maximum savepoint number is 2,147,483,647, a host variable for
holding a savepoint number should be declared as an INTEGER.
Message Catalog Variable Declaration.
The maximum size of host variables used to hold messages from the
ALLBASE/SQL message catalog is 32,762 bytes.
DBEnvironment Name.
The maximum file name, 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.
Data Type 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 on the same expression.
Data types for which type conversion can be performed are called
compatible data types. Table 4-4 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-4. Data Type Equivalency and Compatibility
-------------------------------------------------------------------------------------------------
| | | | |
| ALLBASE/SQL | CHARACTER*n | INTEGER | DOUBLE PRECISION |
| DATA TYPE | | | REAL*8 |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| CHAR | E | I | I |
| | | | |
| VARCHAR | E | I | I |
| | | | |
| BINARY | C | I | I |
| | | | |
| VARBINARY | C | I | I |
| | | | |
| DATE | C | I | I |
| | | | |
| TIME | C | I | I |
| | | | |
| DATETIME | C | I | I |
| | | | |
| INTERVAL | C | I | I |
| | | | |
| SMALLINT | I | E | C |
| | | | |
| INTEGER | I | E | C |
| | | | |
| DECIMAL | I | C | C |
| | | | |
| REAL | I | C | C |
| | | | |
| FLOAT | I | C | C |
| | | | |
-------------------------------------------------------------------------------------------------
In some cases, data conversion may lead to truncation or overflow.
Character Data Conversion.
When ALLBASE/SQL moves string data of one type to a host variable
declared as a compatible type, the following occurs:
* When moving CHAR data to a VARCHAR variable, ALLBASE/SQL places
the length of the string in the appropriate variable and pads the
string on the right with spaces to fill up the VARCHAR string
variable.
* When moving VARCHAR data to a CHAR variable, ALLBASE/SQL pads the
string on the right with spaces to fill up the CHAR string
variable.
When ALLBASE/SQL stores the value of a string host variable into a
CHARACTER column, ALLBASE/SQL pads the value on the right with spaces to
fill up the column.
Character 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 in bytes
before truncation:
SUBROUTINE Select
.
.
.
EXEC SQL BEGIN DECLARE SECTION
CHARACTER*40 LittleString
SQLIND LittleStringInd
EXEC SQL END DECLARE SECTION
.
.
.
EXEC SQL SELECT BigString
1 INTO :LittleString :LittleStringInd
.
.
.
RETURN
END
When the string in column BigString is too long to fit in host variable
LittleString, ALLBASE/SQL puts the actual length of the string in bytes
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. If this happens, the results of truncation may be
unpredictable. It is the programmer's responsibility to verify the
language definition of the column that is to receive data. If the
character column is defined for a native language, truncation will always
occur 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 of the lesser type is converted into data of
the greater type, and the result is expressed in the greater type.
ALLBASE/SQL numeric types available in FORTRAN have the following
precedence:
* DOUBLE PRECISION
* INTEGER
The following example illustrates numeric type conversion:
SUBROUTINE Select
.
.
.
EXEC SQL BEGIN DECLARE SECTION
INTEGER Discount
INTEGER PurchasePrice
EXEC SQL END DECLARE SECTION
.
.
.
EXEC SQL SELECT (MAX)PurchasePrice * :Discount
1 INTO :PurchasePrice
2 FROM PurchDB.OrderItems
.
.
.
RETURN
END
The query illustrated contains an aggregate function, MAX, in the select
list. The argument of the function is the PurchasePrice column, defined
in the PartsDBE DBEnvironment as DECIMAL(10,2). Therefore, the FORTRAN
result of the function is DOUBLE PRECISION, a data type compatible with
DECIMAL. Since the host variable named Discount is declared as an
INTEGER, a data type compatible with DOUBLE PRECISION, ALLBASE/SQL
converts the value in Discount to a DOUBLE PRECISION quantity.
After subtraction, data conversion occurs again before the DOUBLE
PRECISION result is stored in the INTEGER host variable MAXPurchasePrice.
In this case, the fractional part of the DOUBLE PRECISION 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 Host Variables for Data Values and Indicator Variables
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 declare section illustrated in Figure 4-2 contains variable types
equivalent to the data types in the PurchDB.OrderItems table:
* PurchasePrice is declared as a DOUBLE PRECISION variable because
it holds the DECIMAL result of an aggregate function on a DECIMAL
column.
* Discount is declared as a DOUBLE PRECISION variable because it is
used in an arithmetic expression with a DECIMAL value column,
PurchasePrice.
* OrderQty is declared as an INTEGER*2 variable because it holds the
SMALLINT column, OrderQty.
* OrderQtyInd is an indicator variable, necessary because the
resulting of OrderQty can contain only null values. Note in the
INFO example above that this column allows null values.
* OrderNumber is declared as INTEGER because the column whose data
it holds is INTEGER.
__________________________________________________________
| |
| SUBROUTINE Select |
| . |
| . |
| . |
| EXEC SQL BEGIN DECLARE SECTION |
| DOUBLE PRECISION Discount |
| DOUBLE PRECISION PurchasePrice |
| INTEGER*2 OrderQty |
| SQLIND OrderQtyInd |
| INTEGER OrderNumber |
| EXEC SQL END DECLARE SECTION |
| . |
| . |
| . |
| EXEC SQL SELECT PurchasePrice * :Discount, |
| 1 OrderQty |
| 2 INTO :PurchasePrice, |
| 3 :OrderQty :OrderQtyInd |
| 4 FROM PurchDB.OrderItems |
| 5 WHERE OrderNumber = :OrderNumber|
| . |
| . |
| . |
| RETURN |
| END |
__________________________________________________________
Figure 4-2. Declaring Host Variables for Single-Row Query Result
The declare section illustrated in Figure 4-3 depicts how to declare host
variables used in a SELECT command:
* Discount is a DOUBLE PRECISION host variable because it is used in
an arithmetic expression with an aggregate function on a DECIMAL
value. This eliminates any data truncation in the arithmetic
operation between compatible but not equivalent data types.
* PurchasePrice is declared as a DOUBLE PRECISION host variable
because it holds the DECIMAL result of an aggregate function on a
DECIMAL column, PurchasePrice.
* OrderQty is declared as an INTEGER*2 variable because it holds the
SMALLINT result of an aggregate function on a SMALLINT column,
OrderQty.
* OrderQtyInd is an indicator variable, necessary because the result
of OrderQty is null if column OrderQty contains only null values.
Note in the previous INFO command example that the column OrderQty
allows null values.
* OrderNumber is an INTEGER variable because the column whose data
it holds, OrderNumber, is INTEGER.
* LowValue and HighValue are both declared as INTEGER host variables
because they hold data compared with that in a column defined as
INTEGER.
* GroupCriterion is declared as an INTEGER host variable because its
value is compared in the HAVING clause with the result of a COUNT
function, which is always an INTEGER value.
___________________________________________________________________
| |
| SUBROUTINE Select |
| . |
| . |
| . |
| EXEC SQL BEGIN DECLARE SECTION |
| DOUBLE PRECISION Discount |
| DOUBLE PRECISION PurchasePrice |
| INTEGER*2 OrderQty |
| SQLIND OrderQtyInd |
| INTEGER OrderNumber |
| INTEGER LowValue |
| INTEGER HighValue |
| INTEGER GroupCriterion |
| EXEC SQL END DECLARE SECTION |
| . |
| . |
| . |
| EXEC SQL DECLARE Maxcursor CURSOR FOR |
| 1 SELECT PurchasePrice * :Discount, |
| 2 OrderQty, |
| 3 OrderNumber |
| 4 FROM PurchDB.OrderItems |
| 5 WHERE OrderNumber |
| 6 BETWEEN :LowValue AND :HighValue |
| 7 GROUP BY OrderQty, OrderNumber |
| 8 HAVING COUNT(ItemNumber) > :GroupCriterion|
| . |
| . |
| . |
| EXEC SQL FETCH Maxcursor |
| 1 INTO :PurchasePrice, |
| 2 :OrderQty :OrderQtyInd, |
| 3 :OrderNumber |
| . |
| . |
| . |
| RETURN |
| END |
___________________________________________________________________
Figure 4-3. Declaring Host Variables for Multiple-Row Query Result
Declaring Host Variables for Dynamic Commands
The command illustrated in Figure 4-4 names a host variable,
DynamicCommand, for receiving an SQL command at runtime. This host
variable should be declared as a CHARACTER variable.
___________________________________________________
| |
| SUBROUTINE Prepare |
| . |
| . |
| . |
| EXEC SQL BEGIN DECLARE SECTION |
| CHARACTER*1024 DynamicCommand|
| EXEC SQL END DECLARE SECTION |
| . |
| . |
| . |
| EXEC SQL PREPARE CommandOnTheFly |
| 1 FROM :DynamicCommand |
| . |
| . |
| . |
| RETURN |
| END |
___________________________________________________
Figure 4-4. Declaring Host Variables for Dynamic Commands
Declaring Host Variables for Savepoint Numbers
The command illustrated in Figure 4-5 below sets a savepoint. The number
associated with the savepoint is the number ALLBASE/SQL places in the
host variable named SavePoint1. This host variable should be declared as
INTEGER.
_________________________________________
| |
| SUBROUTINE SavePoint1 |
| . |
| . |
| . |
| EXEC SQL BEGIN DECLARE SECTION |
| INTEGER SavePoint1 |
| EXEC SQL END DECLARE SECTION |
| . |
| . |
| . |
| EXEC SQL SAVEPOINT :SavePoint1|
| . |
| . |
| . |
| RETURN |
| END |
_________________________________________
Figure 4-5. Declaring Host Variables for Savepoint Numbers
Declaring Host Variables for Message Catalog Messages
The command illustrated in Figure 4-6 below puts a message from the
ALLBASE/SQL message catalog into a host variable named SQLMessage. The
following example illustrates how the host variable for holding the
message might be declared.
_______________________________________________
| |
| SUBROUTINE SQLStatusCheck |
| . |
| . |
| . |
| EXEC SQL BEGIN DECLARE SECTION |
| CHARACTER*132 SQLMessage|
| EXEC SQL END DECLARE SECTION |
| . |
| . |
| . |
| EXEC SQL SQLEXPLAIN :SQLMessage |
| WRITE(6,102)SQLMessage |
| 10 FORMAT(A132) |
| . |
| . |
| . |
| RETURN |
| END |
_______________________________________________
Figure 4-6. Declaring Host Variables for Message Catalog Messages
The host variable is declared as a CHARACTER data type. Regardless of
how it is declared, ALLBASE/SQL moves spaces into the host variable
before returning the message. Therefore the program does not have to
initialize the host variable each time SQLEXPLAIN is executed.
Declaring Host Variables Passed from Subprograms
The example illustrated in Figure 4-7 below illustrates how to pass a
host variable value between one subprogram unit and another subprogram
unit. The passed host variable must be declared in both the
CallingProgram's and the CalledProgram's type declaration section.
Variables that are not used in an SQL command in the program need to be
declared outside the Type Declaration Section for Host Variables.
_________________________________________________________________
| |
| SUBPROGRAM CallingProgram |
| . |
| . |
| . |
| LOGICAL*2 PositiveResponse |
| CHARACTER*16 PartNumber |
| CHARACTER*30 PartName |
| DOUBLE PRECISION SalesPrice |
| |
| EXEC SQL INCLUDE SQLCA |
| |
| EXEC SQL BEGIN DECLARE SECTION |
| EXEC SQL END DECLARE SECTION |
| . |
| . |
| . |
| EXEC SQL CONNECT TO 'PartsDBE' |
| . |
| . |
| . |
| IF (PositiveResponse) THEN |
| WRITE(6,102) 'INSERT rows into the Parts Table.' |
| 102 FORMAT (A80) |
| |
| CALL InsertSubpgm (PartNumber,PartName,SalesPrice)|
| |
| WRITE(6,103) PartNumber,PartName,SalesPrice |
| 103 FORMAT('Part Number is: ',A16, |
| 1 'Part Name is: ',A30, |
| 2 'Sales Price is: ',F10.2) |
| ENDIF |
| . |
| . |
| . |
| RETURN |
| END |
_________________________________________________________________
Figure 4-7. Declaring Host Variables Passed From Subprograms
___________________________________________________________________
| |
| SUBROUTINE INSERTSubpgm (PartNumber,PartName,SalesPrice)|
| . |
| . |
| . |
| EXEC SQL INCLUDE SQLCA |
| . |
| . |
| . |
| EXEC SQL BEGIN DECLARE SECTION |
| CHARACTER*16 PartNumber |
| CHARACTER*30 PartName |
| DOUBLE PRECISION SalesPrice |
| EXEC SQL END DECLARE SECTION |
| . |
| . |
| . |
| EXEC SQL INSERT INTO PurchDB.Parts |
| 1 (PartNumber, |
| 2 PartName, |
| 3 SalesPrice) |
| 4 VALUES (:PartNumber, |
| 5 :PartName |
| 6 :SalesPrice) |
| . |
| . |
| . |
| RETURN |
| END |
___________________________________________________________________
Figure 4-7. Declaring Host Variables Passed From Subprograms (page 2 of 2)
Note that the INCLUDE SQLCA clause is in both the calling and the called
code. The SQLCA Common Block must always be named in this clause in any
program unit that has SQL commands to be executed. If the SQLCA Common
Block is not included, the FORTRAN preprocessor will issue a warning
message stating that the SQLCA Common Block is not included.
Declaring Host Variables for MPE XL File Values
The example illustrated in Figure 4-8 below illustrates the use of a host
variable to hold data from an MPE XL file. As shown below, the host
variable from the file is declared the same way as a host variable
entered from the terminal.
_____________________________________________________________________
| |
| SUBROUTINE Dates |
| |
| EXEC SQL INCLUDE SQLCA |
| |
| EXEC SQL BEGIN DECLARE SECTION |
| CHARACTER*8 OrderDate |
| INTEGER OrderNumber |
| INTEGER ItemNumber |
| CHARACTER*30 PartName |
| CHARACTER*16 PartNumber |
| DOUBLE PRECISION PurchasePrice |
| EXEC SQL END DECLARE SECTION |
| . |
| . |
| . |
| OPEN (10, FILE = 'OrderDateFile', |
| 1 ACCESS = 'sequential', STATUS = 'old') |
| READ (10,103) OrderDate |
| 103 FORMAT(A8) |
| CLOSE (10) |
| |
| EXEC SQL SELECT A.OrderNumber, |
| 1 A.ItemNumber, |
| 2 C.PartName, |
| 3 A.PurchasePrice |
| 4 INTO :OrderNumber, |
| 5 :ItemNumber, |
| 6 :PartName, |
| 7 :PurchasePrice |
| 8 FROM PurchDB.OrderItems A, |
| 9 PurchDB.SupplyPrice B, |
| 1 PurchDB.Parts C, |
| 2 PurchDB.Orders D |
| 3 WHERE A.VendPartNumber = B.VendPartNumber|
| 4 AND B.PartNumber = C.PartNumber |
| 5 AND A.OrderNumber = D.OrderNumber |
| 6 AND D.OrderDate = :OrderDate |
| . |
| . |
| . |
| RETURN |
| END |
_____________________________________________________________________
Figure 4-8. Declaring Host Variables for MPE XL File Values
Declaring Host Variables for DBEnvironment Names
The DBEnvironment whose name is stored in the host variable named SomeDBE
is declared and initialized as illustrated in Figure 4-9.
_______________________________________________________
| |
| EXEC SQL BEGIN DECLARE SECTION |
| . |
| . |
| . |
| CHARACTER*128 SomeDBE |
| . |
| . |
| . |
| EXEC SQL END DECLARE SECTION |
| . |
| . |
| . |
| WRITE (6,101) 'Enter DBEnvironment name >'|
| 101 FORMAT (A80) |
| READ (5,102) SomeDBE |
| 102 FORMAT (A128) |
| . |
| . |
| . |
| EXEC SQL CONNECT to :SomeDBE; |
_______________________________________________________
Figure 4-9. Declaring Host Variables for DBEnvironment Names
The host variable is declared as a CHARACTER. In this example, it is
declared as a variable large enough to hold the relative file name of any
DBECon file. Note that in this case, the DBEnvironment name does not
have to be delimited by single quotation marks.