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.

Figure 4-1 Host Variable Declarations

    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

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)	`CHARACTERn``DataName`*
VARCHAR(n)	`CHARACTERn``DataName`*^[1]
SMALLINT	`INTEGER2``DataName`*
INTEGER	`INTEGER``DataName`
REAL	`REAL``DataName` `or`
	`REAL4``DataName`*
FLOAT(1..24)	`REAL``DataName` `or`
	`REAL4``DataName`*
FLOAT(1..53)	`DOUBLE PRECISION``DataName` `or`
	`REAL8``DataName`*
DOUBLE PRECISION	`DOUBLE PRECISION``DataName` `or`
	`REAL8``DataName`*
BINARY	`CHARACTER``DataName` `or`
	`CHARACTERn``DataName`*
VARBINARY	`CHARACTERn``DataName`*
DECIMAL	`DOUBLE PRECISION DataName or`
	`REAL8``DataName`*
DATE	`CHARACTER10``DataName`*
TIME	`CHARACTER8``DataName`*
DATETIME	`CHARACTER23``DataName`*
INTERVAL	`CHARACTER20``DataName`*
^[1]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 INTEGER2` `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

 or

 FLOAT(n)

 where 

 n = 1 through 24

-3.402823 E+38 through -1.175495 E-38

and

1.175495 E-38 through 3.402823 E+38

and

4 bytes

DOUBLE PRECISION

 or

 FLOAT

 or

 FLOAT(n) 

 where 

 n = 25 through 53

-1.79769313486231 E+308 through -2.22507385850721 E-308

and

+2.22507385850721 E-308 through +1.79769313486231 E+308

and

8 bytes

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 DATA TYPE	CHARACTER*n	INTEGER	DOUBLE PRECISION 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.

Figure 4-2 Declaring Host Variables for Single-Row Query Result

 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

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.

Figure 4-3 Declaring Host Variables for Multiple-Row Query Result

   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

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.

Figure 4-4 Declaring Host Variables for Dynamic Commands

    SUBROUTINE Prepare

    .

    .

    .

    EXEC SQL BEGIN DECLARE SECTION

    CHARACTER*1024               DynamicCommand

    EXEC SQL END DECLARE SECTION

    .

    .

    .

        EXEC SQL PREPARE  CommandOnTheFly

   1                FROM :DynamicCommand

    .

    .

    .

    RETURN

    END

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.

Figure 4-5 Declaring Host Variables for Savepoint Numbers

   SUBROUTINE SavePoint1

   .

   .

   .

   EXEC SQL BEGIN DECLARE SECTION

   INTEGER              SavePoint1

   EXEC SQL END DECLARE SECTION

   .

   .

   .

       EXEC SQL SAVEPOINT :SavePoint1

   .

   .

   .

   RETURN

   END

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.

Figure 4-6 Declaring Host Variables for Message Catalog Messages

        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

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.

Figure 4-7 Declaring Host Variables Passed From Subprograms

          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



          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

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.

Figure 4-8 Declaring Host Variables for MPE XL File Values

       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

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.

Figure 4-9 Declaring Host Variables for DBEnvironment Names

         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;

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.