Declaring Host Variables

Host variables may be declared wherever you can declare variables in C programs. For the purpose of this discussion, we define declaration part as the portion of a C program where variables having the scope of a file, a function, or a block can be declared.

At run time, the scope of a host variable is the same as that of any other C variable declared in the same declaration part. At preprocessing time, however, all host variable declarations are treated as global declarations. Therefore host variables having the same name in different declaration parts must also have the same C type description in each variable declaration.

Creating Declaration Sections

Host variables must be declared in what is known as a declare section. A declare 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 3-1). More than one declare section may appear in a given declaration part. However, a host variable name may appear only once in a given declaration part.

Each host variable is declared by using a C type declaration. The declaration contains the same components as any C variable declaration:

   EXEC SQL BEGIN DECLARE SECTION;



       int            OrderNumber;

       |              |

       |              |

       |              data name

       |

       data type



   EXEC SQL END DECLARE SECTION;

The data name must be the same as the host variable name in the corresponding SQL statement. The data type must satisfy ALLBASE/SQL data type and ALLBASE/SQL C preprocessor requirements.

Figure 3-1 Host Variable Declarations

.

.

.

EXEC SQL BEGIN DECLARE SECTION;

.

.  Declarations for global host variables.

.

EXEC SQL END DECLARE SECTION;

.

.

.

int query()

{

.

.

.

EXEC SQL BEGIN DECLARE SECTION;

.

.  Declarations for local host variables.

.

EXEC SQL END DECLARE SECTION;

.

.

.

.

EXEC SQL BEGIN DECLARE SECTION;

.

.  Declarations for local host variables.

.

EXEC SQL END DECLARE SECTION;

.

.

}

.

.

.

main()

{

.

.

.

}

Declaring Variables for Data Types

Any variable can be used as a host variable. Table 3-1 summarizes C data declarations for host variables of each ALLBASE/SQL data type. Only the type descriptions shown in Table 3-1 are supported by the C preprocessor. Note in particular that the preprocessor does not support user-defined data types.

CHAR Data

A CHAR column can be declared for character strings ranging from 1 to 3996 bytes.

A single byte of the char data type in ALLBASE/SQL is directly equivalent to a single byte of the CHAR data type in C. Strings greater than one byte in C are associated with the char array data type. Character arrays in C are not directly equivalent to the ALLBASE/SQL CHAR data type; however, they are compatible. ALLBASE/SQL handles the conversion between ALLBASE/SQL CHAR data types and C char array data types through code generated by the preprocessor and embedded in the application's modified source code file.

For C strings (character arrays), C has the convention of using an ASCII 0 ('\0'), the null character, to mark the end of the string. Therefore, char host variables declared in C must have a size one greater than their ALLBASE/SQL column definition, to allow for the null character at the end of the string. The PartNumber column in the PurchDB.Parts table is defined as CHAR(30). The associated host variable is therefore declared:

   char     PartNumber[31];

When ALLBASE/SQL assigns CHAR data to a char array host variable, the total length of the ALLBASE/SQL CHAR field is stored in the host variable including any trailing blanks on the right of the data string. An ASCII 0 is then added after the last byte of the string.

VARCHAR Data

A VARCHAR column can be declared for strings ranging from 1 to 3996 bytes. ALLBASE/SQL stores only the actual value of the string, not any trailing blanks.

Strings greater than one character in C are equivalent to the VARCHAR data type in ALLBASE/SQL. C host variables for VARCHAR data types in ALLBASE/SQL are declared the same as variables declared for CHAR data types. The only difference is that when ALLBASE/SQL assigns VARCHAR data to a char array host variable, no trailing blanks are added. An ASCII 0 is placed after the last character of the C string based on the specified length of the string.

The VendorRemarks column in the PurchDB.Vendors table is defined as VARCHAR(60). It is therefore declared:

      char       VendorRemarks[61];

Table 3-1 Data Type Declarations

SQL DATA TYPES	C DATA DECLARATIONS
`CHAR(1)`	`char``dataname``;`
`CHAR(n)`	`char``dataname``[n+1];`
`VARCHAR(n)`	`char``dataname``[n+1];` *
`SMALLINT`	`short``dataname``; or`
	`short int``dataname``;`
`INTEGER`	`int``dataname``; or`
	`long int``dataname``; or`
	`long``dataname``;`
`REAL`	`float``dataname``;`
`FLOAT(1..24)`	`float``dataname``;`
`FLOAT(25..53)`	`double``dataname``;`
`DOUBLE PRECISION`	`double``dataname``;`
`BINARY`	`sqlbinary``dataname``;`
	`sqlbinary``dataname``[n];`
`VARBINARY`	`sqlvarbinary``dataname``[m];` **
`DECIMAL`	`double``dataname``;`
`DATE`	`char``dataname``[11];`
`TIME`	`char``dataname``[9];`
`DATETIME`	`char``dataname``[24];`
`INTERVAL`	`char``dataname``[21];`
	* This declaration is for non-dynamic commands only. Refer to the chapter, "Using Dynamic Operations," for a description of how to use VARCHAR dynamically. \break{} ** See the "BINARY Data" section later in this chapter for the calculation of m.

Table 3-2 Program Element Declarations

PROGRAM ELEMENT	C DATA DECLARATIONS
Indicator variable	`sqlind` `indvarname`;
Array of n rows	`struct` `structtypename`{
Data values	`validdatatype column1name`; `validdatatype column2name`;
Indicator variable	`sqlind` `indvarname`; }`structname``[n];`
StartIndex	`short` `startindexname``; or` `int` `startindexname``;`
NumberOfRows	`short` `numrowsname``; or` `int` `numrowsname``;`
Dynamic commands	`char` `commandname``[n+1];`
Savepoint numbers	`int` `savepointname``;`
Message catalog messages	`char` `messagename``[n+1];`
DBEnvironment name	`char` `DBEName``[n+1];`

SMALLINT Data

You can assign values ranging from -32,768 to +32,767 to a column defined as SMALLINT.

INTEGER Data

You can assign values ranging from -2,147,483,648 to +2,147,483,647 to a column defined as INTEGER.

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) 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 3-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 0	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 0	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 statement 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 the same manner except that CHAR data is blank padded.)

Binary Data Compatibility

BINARY and VARBINARY data types in an ALLBASE/SQL database 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.

Declaring Host Variables for BINARY Data

Host variables for BINARY data columns must be declared as sqlbinary, as in the following example:

   EXEC SQL BEGIN DECLARE SECTION;

   .

   .

   .

   sqlbinary   BinaryHostVariableName[n];

   .

   .

   .

   EXEC SQL END DECLARE SECTION;

The host variable array size n equals the length of the column as defined in the database.

At preprocessing time, the ALLBASE/SQL preprocessor sqlbinary data type is defined as an unsigned char with the following statement in the SQL Type Include File:

   typedef unsigned char sqlbinary;

An sqlbinary host variable is used for fixed length data. It is your responsibility to appropriately load binary data into the host variable before an insert or update operation.

In the following example, data from a binary column defined with a length of 12 is selected into an sqlbinary host variable.

   .

   .

   .



   EXEC SQL BEGIN DECLARE SECTION;



   sqlbinary   BinaryHV[12];



   EXEC SQL END DECLARE SECTION;



   .

   .

   .



   EXEC SQL SELECT BinaryColumn

              INTO :BinaryHV

              FROM TableA;

   .

   .

   .

Declaring Host Variables for VARBINARY Data

Host variables for VARBINARY data columns must be declared as sqlvarbinary, as in the following example:

   EXEC SQL BEGIN DECLARE SECTION;

   .

   .

   .

   sqlvarbinary   VarbinaryHostVariableName[m]

   .

   .

   .

   EXEC SQL END DECLARE SECTION;

At preprocessing time, the ALLBASE/SQL preprocessor sqlvarbinary data type is defined as an integer with the following statement in the SQL Type Include File:

   typedef int sqlvarbinary;

You specify the host variable array size m based on the following formula:

   m = 1 + [n/4]

where:

m = the host variable array size
n = the length of the column as defined in the database
[n/4] = the LeastInteger of (n/4)

LeastInteger is the smallest integer >= (n/4).

In order to pass information between an sqlvarbinary host variable and the database, a special format is used. The internal format of an sqlvarbinary host variable is illustrated below:

   Array Index        0         1        ...                    m-1



   Byte Offset   0         4                               (m-1)*4     m*4

                 +---------+----------------------------------+---------+

                 | Length  |        Data                                |

                 +---------+----------------------------------+---------+

where:

Length, in the first four bytes, represents the actual data length.
Data starts from byte offset four and represents the varbinary data.
(m-1)
4 equals the byte offset of the last element of the sqlvarbinary array.
m
4 equals the end of the sqlvarbinary array.

For example, to declare an sqlvarbinary host variable for a varbinary column having a maximum length of nine, you define the host variable with an array size of four (one four byte element to hold the actual data length and three four byte elements for the data):

   EXEC SQL BEGIN DECLARE SECTION;



   sqlvarbinary   VarbinaryHV[4];



   EXEC SQL END DECLARE SECTION;

Inserting and Updating VARBINARY Data

Before issuing an INSERT or UPDATE statement, you must load the sqlvarbinary host variable using the format mentioned in the previous section. Two examples of loading data into an sqlvarbinary host variable are presented below. The first example loads data from a buffer; the second example loads data from a union structure.

Using a Buffer

Suppose your varbinary column is defined in the database with a maximum length of 16. You want to to load it with data from a buffer called bbuff as follows:

   .

   .

   .



   int  length;

   char bbuff[16];



   EXEC SQL BEGIN DECLARE SECTION;



   Derive the host variable length from the formula described in the previous section,

   "Declaring Host Variables for Varbinary Data," as follows:

   1 + the LeastInteger of (16/4).

   sqlvarbinary   VarbinaryHV[5];



   EXEC SQL END DECLARE SECTION;



   .

   .

   .

   Load the first array element with the actual data length.

   Here we'll assume actual length to be 11 bytes.          



   VarbinaryHV[0] = 11; 



   Load the data in bbuff, starting at the second element of the array. 



   memcpy(&VarbinaryHV + 1, &bbuff, 11);



   EXEC SQL INSERT INTO TableA VALUES (:VarbinaryHV, ...);



   .

   .

   .

Using a Union Structure

Here is another method of loading the same host variable. In the following example, the data length and data are loaded into a union structure and then into the sqlvarbinary host variable:

   .

   .

   .



   int  length;

   char bbuff[16];



   union u_t {

            struct {

                int   length;

                int   data[4];

                } s1;

            int ubuff[5];

            } u1;



   EXEC SQL BEGIN DECLARE SECTION;



   .

   .

   .



   Derive the host variable length from the formula described in the previous section,

   "Declaring Host Variables for Varbinary Data," as follows:

   1 + the LeastInteger of (16/4).



   sqlvarbinary   VarbinaryHV[5];



   EXEC SQL END DECLARE SECTION;



   .

   .

   .

   Load the length field of the structure with the actual column data length.

    Here we'll assume actual length to be 11 bytes.          

   u1.s1.length = 11;



   Load the data in bbuff, into the data field of the structure.          

   memcpy(u1.s1.data, &bbuff, 11);



   Load the length and the data into the varbinary host variable. 

   memcpy(&VarbinaryHV, &(u1.ubuff), sizeof(u1.ubuff));



   EXEC SQL INSERT INTO TableA VALUES (:VarbinaryHV, ...);



   .

   .

   .

Selecting and Fetching VARBINARY Data

After the successful execution of a SELECT or FETCH statement, the length of the data returned for any varbinary column is found in the first element of the related host variable array.

Two examples of retrieving data from an sqlvarbinary host variable are presented below. The first example loads retrieved data into a buffer; the second example loads retrieved data into a union structure. The same data declarations used for the examples in the previous section are assumed.

Using a Buffer

The following example selects data into an sqlvarbinary host variable, loads it into a buffer named bbuff, and saves the length of the data in an integer variable named length:

   .

   .

   .



   EXEC SQL SELECT * FROM TableA INTO :VarbinaryHV, ...;



   memcpy (&bbuff, &VarbinaryHV+1, VarbinaryHV[0]);

   length = VarbinaryHV[0];



   .

   .

   .

Using a Union Structure

The following example selects data into an sqlvarbinary host variable, loads it into a union structure, saves the length of the data in an integer variable named length, and saves the data in a buffer named bbuff:

   .

   .

   .



   EXEC SQL SELECT * FROM TableA INTO :VarbinaryHV, ... ;



   memcpy(&(u1.ubuff), &VarbinaryHV, sizeof(u1.ubuff));

   length = u1.s1.length;

   memcpy(&bbuff, &(u1.s1.data), u1.s1.length);



   .

   .

   .

DECIMAL Data

The DECIMAL data type is not supported by ALLBASE/SQL C. The DECIMAL data type is compatible with an ALLBASE/SQL C double 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, "CHAR Data.") For example:

   /*  Declare host variables and, where applicable, indicator variables.  */



   EXEC SQL BEGIN DECLARE SECTION;       /*    DATETIME DATA TYPE     */

        char  BatchStamp[24];            /*    DATE DATA TYPE         */

        char  TestDate[11];

        sqlind    TestDateInd;           /*    TIME DATA TYPE         */

        Char  TestStart[9];

        sqlind    TestStartInd;          /*    INTERVAL DATE TYPE     */

        char  LabTime[21];

        sqlind    LabTimeInd;

   EXEC SQL END DECLARE SECTION;



   /*DECLARE and OPEN CURSOR C1 here.  Nulls not allowed for BatchStamp.*/



   EXEC SQL FETCH C1

        INTO :BatchStamp,

             :TestDate :TestDateInd,

             :TestSart :TestStartInd,

             :LabTime  :LabTimeInd;

See the chapter, "Understanding Date/Time Functions," for information on using date/time data types with date/time functions.

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

Declaring Variables for Compatibility

Under the following conditions, ALLBASE/SQL performs data type conversion when executing SQL commands containing host variables under the following circumstances:

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 3-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 3-4 C Data Type Equivalency and Compatibility

`ALLBASE\SQLDataTypes`	`char`	`char[n]`	`short`\`shortint`	`intlonglongint`	`doublelongfloat`
`CHAR`	`E`	`C`	`I`	`I`	`I`
`VARCHAR`	`C`	`C`	`I`	`I`	`I`
`BINARY`	`C`	`C`	`I`	`I`	`I`
`VARBINARY`	`I`	`I`	`C`	`C`	`I`
`DATE`	`C`	`C`	`I`	`I`	`I`
`TIME`	`C`	`C`	`I`	`I`	`I`
`DATETIME`	`C`	`C`	`I`	`I`	`I`
`INTERVAL`	`C`	`C`	`I`	`I`	`I`
`SMALLINT`	`I`	`I`	`E`	`C`	`C`
`INTEGER`	`I`	`I`	`C`	`E`	`C`
`FLOAT`	`I`	`I`	`C`	`C`	`E`
`DECIMAL`	`I`	`I`	`C`	`C`	`C`

As the following example illustrates, the ISQL INFO command 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

For example, the query in Figure 3-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:

PurchasePrice is declared as a double variable because it holds the DECIMAL result of an aggregate function on a DECIMAL column.
Discount is declared as a double variable because it is used in an arithmetic expression with a DECIMAL column, PurchasePrice.
OrderQty is declared as a short int variable because it holds the 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.
OrderNumber is an integer variable because the column whose data it holds is INTEGER.

Figure 3-2 Declaring Host Variables for Single-Row Query Results

.

EXEC SQL BEGIN DECLARE SECTION;

      double                   Discount  ;

      double                   PurchasePrice  ;

      short int                OrderQty  ;

      sqlind                   OrderQtyInd  ;

      int                      OrderNumber  ;

.

.

EXEC SQL END DECLARE SECTION;

.

{

.

     EXEC SQL SELECT  PurchasePrice * :Discount  ,

                      OrderQty,

                INTO :PurchasePrice,

                     :OrderQty :OrderQtyInd

                FROM  PurchDB.OrderItems

               WHERE  OrderNumber = :OrderNumber

.

.

}

The example in Figure 3-3 is similar to that in Figure 3-2. This query, however, is a BULK query, which may return a multiple-row query result. And it incorporates a HAVING clause.

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 short int 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 3-3 Declaring Host Variables for Multiple-Row Query Results

.

.

EXEC SQL BEGIN DECLARE SECTION;

      double                    Discount;

    struct {

          double                PurchasePrice;

          short int             OrderQty;

          sqlind                OrderQtyInd;

          int                   OrderNumber;

        } OrdersArray[26];

      short int                 FirstRow;

      short int                 TotalRows;

      int                       LowValue;

      int                       HighValue;

      int                       GroupCriterion;

.

.

EXEC SQL END DECLARE SECTION;

.

.

{

.

.

     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  ;

.

.

}

String Data Conversion

When ALLBASE/SQL stores the characters in a C string into a CHAR column, the final ASCII 0 is removed and any remaining positions to the right are padded with spaces. Internally, when ALLBASE/SQL stores the characters in a C string to a VARCHAR column, it only stores the string up to but not including the ASCII 0. The length of the string is stored in a four-byte header in the front of each VARCHAR data type.

When ALLBASE/SQL moves VARCHAR data to a character array variable, only the length of the string is moved, no trailing blanks are added. ALLBASE/SQL embeds preprocessor generated code into the modified source code file to place an ASCII 0 at the end of the string variable. Refer to the "Using Dynamic Operations" chapter in this manual for further information specific to dynamic operations.

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 in bytes before truncation:

   EXEC SQL BEGIN DECLARE SECTION;

       char          LittleString[n];

       sqlind        LittleStringInd;

   .

   .

   .

   EXEC SQL END DECLARE SECTION;

   .

   .

   .

   {

   .

   .

   .

       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 length of the string in bytes into indicator variable LittleStringInd. In this example, the maximum number of bytes that can be stored in LittleString is n-1, since the last byte is always reserved for the ASCII 0 null character.

If a column is too small to hold a string in an INSERT or an UPDATE operation, the string is truncated and stored. The sqlca sqlwarn[1] field is set to W when this occurs.

It is possible to store native language data in a character column defined as n-computer. 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 have the following precedence, from highest to lowest:

FLOAT
DECIMAL
INTEGER
SMALLINT

The following example illustrates numeric type conversion:

   EXEC SQL BEGIN DECLARE SECTION;

       int          Discount;

       int          MaxPurchasePrice;

   .

   .

   .

   EXEC SQL END DECLARE SECTION;

   .

   .

   .

   {

   .

   .

   .

       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 subtraction, 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. In addition, Table 3-2 provides examples of these special elements.

sqlca Array

Every ALLBASE/SQL C 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 C preprocessor parses this command, it inserts the following type definition into the modified source file:

   sqlca_type sqlca;

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.

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.



   sqlformat_type     sqlfmts[MaxFmtArray];



             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.



        char         DataBuffer[MaxDataBuff];



         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. Figure 3-3 provides an example.

Indicator Variables

Each indicator variable field must be declared immediately following the host variable field it describes as shown in Figures 3-2 and 3-3. 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. In Figure 3-4, the host variable is declared to hold a command as large as 2048 bytes.

Figure 3-4 Declaring Host Variables for Dynamic Commands

.

.

.



EXEC SQL BEGIN DECLARE SECTION;

      char          DynamicCommand[2048]  ;

.

.

.

EXEC SQL END DECLARE SECTION;

.

.

.

{

.

.

.

    EXEC SQL PREPARE  CommandOnTheFly

                FROM :DynamicCommand  ;

.

.

.

}

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.

Figure 3-5 Declaring Host Variables for Savepoint Numbers

.

.

.



EXEC SQL BEGIN DECLARE SECTION;

      int           SavePoint1  ;

.

.

.

EXEC SQL END DECLARE SECTION;

.

.

.

{

.

.

.

    EXEC SQL SAVEPOINT :Savepoint1  ;

.

.

.

    EXEC SQL ROLLBACK WORK TO :Savepoint1  ;

.

.

}

Messages from the Message Catalog

The maximum size of a message catalog message is 256 bytes.

Figure 3-6 illustrates how a host variable for holding a message might be declared.

Figure 3-6 Declaring Host Variables for Message Catalog Messages

.

.

.

EXEC SQL BEGIN DECLARE SECTION;

      char         SQLMessage[256]  ;

.

.

.

EXEC SQL END DECLARE SECTION;

.

.

.

{

.

.

.

EXEC SQL SQLEXPLAIN :SQLMessage  ;

printf("%s\n",SQLMessage);

.

.

.

}

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.

Figure 3-7 Declaring Host Variables for DBEnvironment Names

EXEC SQL BEGIN DECLARE SECTION;

char        SomeDBE[128];

.

.

.

EXEC SQL END DECLARE SECTION;

.

.

.

{

printf("\n Enter DBEnvironment name> ");

scanf("%s",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.