|
|
|
|
|
|
|
|
ALLBASE/SQL COBOL Application Programming Guide: HP 9000 Computer Systems > Chapter 4 Host Variables
Declaring Host Variables |
|
If your program uses host variables in the PROCEDURE DIVISION, you must declare the host variables in the DATA DIVISION:
Host variables must be declared in what is known as a declare section. A declare section consists of the SQL command EXEC SQL BEGIN DECLARE SECTION END-EXEC., one or more variable declarations, and the SQL command EXEC SQL END DECLARE SECTION END-EXEC. (as shown in Figure 4-1). More than one declare section may appear in the WORKING-STORAGE SECTION, the LINKAGE SECTION, and the FILE SECTION. Note that variables which are not host variables may also be declared within a declare section. Each host variable is declared by using a COBOL data description entry. The declaration contains the same components as any COBOL data description entry:
The level number can be from 01 to 49; single-level variables can have level numbers 01 or 77. The data name must be the same as the corresponding host variable name in the PROCEDURE DIVISION. The data clause must satisfy ALLBASE/SQL data type and COBOL preprocessor requirements. Note, data clauses can also contain the optional constructs highlighted below:
Figure 4-1 Host Variable Declarations in the DATA DIVISION
Table 4-1 summarizes the syntax of the data description entries for host variables holding each type of ALLBASE/SQL data. Only the data descriptions shown in Table 4-1 are supported by the COBOL preprocessor. You can insert strings ranging from 1 to 3996 characters into a CHAR column. When ALLBASE/SQL assigns data to a CHAR host variable from 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 the actual length of the string in a four-byte field preceding the string itself. In order to describe both the length and value of a VARCHAR string, you declare VARCHAR data as a group containing two 49-level items:
Table 4-1 Host Variable Data Types
The VENDORREMARKS column in the PURCHDB.VENDORS table is defined as VARCHAR(60). It is therefore declared as follows:
In the above example, VENDORREMARKS is the GROUP-NAME, REMARKSLENGTH is the LENGTH-NAME, and REMARKS is the VALUE-NAME. When using a VARCHAR host variable as an output variable, use the GROUP-NAME:
ALLBASE/SQL places the remarks into the item named REMARKS and the number of characters in the remarks string into the item named REMARKSLENGTH. When using the value in REMARKS, only use the number of characters specified in REMARKSLENGTH or move spaces to REMARKS before filling it. When using a VARCHAR host variable as an input variable, you assign the actual length of the VARCHAR string to the string length variable and assign the value of the string to the string value variable:
When ALLBASE/SQL copies data from REMARKS, it copies as many characters as you have specified in REMARKSLENGTH and stores the value in REMARKSLENGTH in a four-byte field preceding the value in REMARKS. If the value in REMARKSLENGTH is an odd number, ALLBASE/SQL stores the number of characters specified, plus one space on the right; in this case, the value in REMARKSLENGTH is incremented by one and stored in the four-byte field preceding the REMARKS value. Declared as PIC S9(9) COMP, possible values range from -2,147,483,648 to +2,147,483,647. ALLBASE/SQL offers the option of specifying the precision of floating point data. In COBOL, ALLBASE/SQL FLOAT data is declared as DECIMAL. The first part of this discussion relates to the ALLBASE/SQL FLOAT data type. This is followed by a discussion of declaring ALLBASE/SQL FLOAT data as DECIMAL, in COBOL. 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 Table 4-1. See the for complete syntax specifications. 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. Table 4-2 ALLBASE/SQL Floating Point Column Specifications
COBOL DECIMAL data for the ALLBASE/SQL FLOAT data type is defined in terms of a precision and a scale:
When you declare a host variable that will contain a DECIMAL value, the data clause defines the number of digits to the left and the right of the decimal point. The following declaration corresponds to an ALLBASE/SQL column defined as DECIMAL (10,2):
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 for a complete account of how to calculate the precision and scale. 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 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. 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 for detailed syntax and to the chapter in this document titled "Defining and Using Long Columns" for information about using LONG column data.
For BULK record operations, when the precision of a DECIMAL declaration is odd, the COBOL preprocessor generates a filler character. This character, known as a slack byte, ensures that the data is aligned on word boundaries. ALLBASE/SQL requires that data be on word boundaries for certain data manipulation operations. DECIMAL values are padded by one byte on the left, and string values are padded by one byte on the right. Most of the time, odd-byte padding has no effect on an application program. If, however, an odd-byte host variable in an array is used by a non-SQL subprogram, the subprogram needs to declare the variable for the passed value in a data description entry compatible with the way ALLBASE/SQL declares the host variable in the modified source file. Whenever the COBOL preprocessor generates a FILLER declaration in the modified source file, the event is flagged as follows in SQLMSG:
The example in Figure 4-2 highlights the source file and resulting modified source file generated by the COBOL preprocessor for odd-byte columns. Figure 4-2 Data Declarations Generated for Boundary Alignment
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 (unless the DEFAULT value is NULL). If a column not in an insert list does allow a NULL, then a NULL is inserted instead of the default value. Your default specification options are as follows:
Complete syntax for the CREATE TABLE and ALTER TABLE commands as well as definitions of the above options are found in the . 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:
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:
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.
Under the following conditions, ALLBASE/SQL performs data type conversion when executing SQL commands containing host variables:
Data types for which type conversion can be performed are called compatible data types. Table 4-3 “COBOL Data Type Equivalency and Compatibility” summarizes data type-host variable compatibility. It also points out which data type combinations are incompatible and which data type combinations are equivalent, i.e., require no type conversion. E describes an equivalent situation, C a compatible situation, and I an incompatible situation. Table 4-3 COBOL Data Type Equivalency and Compatibility
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:
The example identified as Figure 4-3 is a query that accesses the PURCHDB.ORDERITEMS table. The query produces a single-row query result that consists of two maximum values. The declare section illustrated contains data clauses equivalent to the data types in the PURCHDB.ORDERITEMS table:
Figure 4-3 Declaring Host Variables for Single-Row Query Results
The example in Figure 4-4 is similar to that in Figure 4-3. This query, however, is a BULK query, which may return a multiple-row query result. And it incorporates a HAVING clause.
Figure 4-4 Declaring Host Variables for Multiple-Row Query Results
When ALLBASE/SQL moves string data of one type to a host variable declared as a compatible type, the following occurs:
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:
When the string in column BIG_STRING is too long to fit in host variable LITTLE-STRING, ALLBASE/SQL puts the actual length of the string into indicator variable LITTLE-STRING-IND. If a column is too small to hold a string in an INSERT or an UPDATE operation, the string is truncated and stored. ALLBASE/SQL gives no error or warning message, but SQLWARN1 will contain a W. 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 COBOL have the following precedence, from highest to lowest:
The following example illustrates numeric type conversion:
The query illustrated contains in the select list 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. If the fractional part is zero, no error results. Otherwise, an error condition occurs. Refer to the for additional information on how type conversion can cause truncation and overflow of numeric values. The following section discusses how to declare elements specific to ALLBASE/SQL programs. In addition, Table 4-4 “Program Element Data Description Entries” provides the syntax of these special elements. Table 4-4 Program Element Data Description Entries
Every ALLBASE/SQL COBOL program must have the SQL Communications Area (SQLCA) declared in the working storage section of the DATA DIVISION. You can use the INCLUDE command to declare the SQLCA:
Refer to the chapter, "Runtime Status Checking and the SQLCA," for further information regarding the SQLCA. When you declare an 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 immediately after the host variable field it describes. And if used, the bulk processing indicator variables (starting index and number of rows) are declared as 01 level data descriptions. They must be referenced in order (starting index followed by number of rows) immediately following your array reference. Refer back to Figure 4-4 again for an example. Each indicator variable must be declared immediately following the host variable it describes, as shown in Figures 4-3 and 4-4. (The SQLIND data clause must be complete before column 64.) If a column allows nulls, a null indicator must be declared for it. When the COBOL preprocessor encounters SQLIND, it generates the following declaration in its place in SQLOUT:
The maximum size for the host variable used to hold dynamic commands is 32,762 bytes. Such a host variable can be declared as a CHAR or VARCHAR data type. In Figure 4-5, one host variable is declared to hold a CHAR dynamic command of up to 2048 bytes. The second host variable is declared to hold a VARCHAR dynamic command of 80 bytes or less. Figure 4-5 Declaring Host Variables for Dynamic Commands
The maximum size of a message catalog message is 256 bytes. Figure 4-7 illustrates how a host variable for holding a message might be declared. Figure 4-7 Declaring Host Variables for Message Catalog Messages
The DBEnvironment you specify in the preprocessor command line is the same as the DBECon file name. The maximum length of the complete pathname for a DBEnvironment is 128 bytes. When used in a host variable, the DBEnvironment name can be unquoted or enclosed in single quotation marks. Figure 4-8 Declaring Host Variables for DBEnvironment Names
The host variable can be declared as a CHAR or VARCHAR variable. In this example, it is declared as CHAR. Two instances require that you pass ALLBASE/SQL data structures between calling and called subprograms and/or programs.
For example, in Figure 4-9 the host variable passed is declared in the CallingProgram outside a declare section, because it is not used in an SQL command in that program. The passed host variable is declared in the INSERTsubpgm within a declare section in the LINKAGE SECTION. This is because it is used in an SQL command in the subprogram. Note that USING clauses in both calling and called code name both the SQLCA and the passed host variable. The SQLCA must always be named in this clause in programs and subprograms that contain SQL commands to be executed from the same DBE session. Figure 4-9 Declaring Host Variables Passed Between Subprograms
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||
|
|
|
|||||||||
|
|||||||||
