Declaring Host Variables

Figure 4-1 Host Variable Declarations

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.

Table 4-1 “Data Type Declarations” 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 “Data Type Declarations”.

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 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.

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;

Values can range from -2,147,483,648 to +2,147,483,647 in a column defined as INTEGER.

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.

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.)

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.

	NOTE: 300/400 DECIMAL fields used in arithmetic operations on Series 300 and Series 400 systems are converted to FLOAT to perform the operation, then converted back to DECIMAL. This may result in a less precise rather than a more precise result, in contrast to what would normally be anticipated.

• 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.

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 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.

Table 4-3 Pascal Data Type Equivalency and Compatibility

• 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.

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

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

• 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.

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

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;

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.

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.

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:

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.

Table 4-4 Program Element Declarations

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," for further information regarding the sqlca.

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.

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.

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.

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  ;

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;

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);

 

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;

 

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.