Chapter 2: Host Variables

Host variables are data items defined within a COBOL program. They are used to pass values to and receive values from a database. Host variables can be defined in the File Section, Working-Storage Section, Local-Storage Section or Linkage Section of your COBOL program and have any level number between 1 and 48. Level 49 is reserved for VARCHAR data items.

When a host variable name is used within an embedded SQL statement, the data item name must begin with a colon (:) to enable the Compiler to distinguish between host variables and tables or columns with the same name.

Host variables are used in one of two ways:

Input host variables
These are used to specify data that will be transferred from the COBOL program to the database.
Output host variables
These are used to hold data that is returned to the COBOL program from the database.

For example, in the following statement, :book-id is an input host variable that contains the ID of the book to search for, while :book-title is an output host variable that returns the result of the search:

     EXEC SQL
        SELECT title INTO :book-title FROM titles
           WHERE title_id=:book-id 
     END-EXEC

Declaring Host Variables

Before you can use a host variable in an embedded SQL statement, you must declare it. Host variable declarations should be bracketed by the embedded SQL statements BEGIN DECLARE SECTION and END DECLARE SECTION, for example:

 EXEC SQL
     BEGIN DECLARE SECTION
 END-EXEC
 01 id             pic x(4).
 01 name           pic x(30).
 EXEC SQL
    END DECLARE SECTION
 END-EXEC
    . . .
     display "Type your identification number: "
     accept id.

* The following statement retrieves the name of the
* employee whose ID is the same as the contents of 
* the host variable "id". The name is returned in
* the host variable "name".

     EXEC SQL
         SELECT emp_name INTO :name FROM employees
          WHERE emp_id=:id
     END-EXEC
     display "Hello " name.

Notes:

You can use groups of data items as a single host variable. However, a group item cannot be used in a WHERE clause.
OpenESQL trims trailing spaces from character host variables. If the variable consists entirely of spaces, OpenESQL does not trim the first space character because some servers treat a zero length string as NULL.

OpenESQL and DB2 Preprocessors

You can use data items as host variables even if they have not been declared using BEGIN DECLARE SECTION and END DECLARE SECTION.

When declaring host variables, you should bear the following in mind:

Host variable names must conform to the COBOL rules for data items.
Host variables can be declared anywhere that it is legal to declare COBOL data items.
Underscores (_) are not permitted in host variable names.

Host Arrays

An array is a collection of data items associated with a single variable name. You can define an array of host variables (called host arrays) and operate on them with a single SQL statement.

You can use host arrays as input variables in INSERT, UPDATE and DELETE statements and as output variables in the INTO clause of SELECT and FETCH statements. This means that you can use arrays with SELECT, FETCH, DELETE, INSERT and UPDATE statements to manipulate large volumes of data.

Host arrays are declared in the same way as simple host variables using BEGIN DECLARE SECTION and END DECLARE SECTION. With host arrays, however, you must use the OCCURS clause to dimension the array. For example:

 EXEC SQL
     BEGIN DECLARE SECTION
 END-EXEC
 01 AUTH-REC-TABLES
    05 Auth-id       OCCURS 25 TIMES PIC X(12).
    05 Auth-Lname    OCCURS 25 TIMES PIC X(40).
 EXEC SQL
    END DECLARE SECTION
 END-EXEC.
  . . .

     EXEC SQL
         CONNECT USERID 'user' IDENTIFIED BY 'pwd' 
                 USING 'db_alias'
     END-EXEC
     EXEC SQL
         SELECT au-id, au-lname
          INTO :Auth-id, :Auth-Lname FROM authors
     END-EXEC
     display sqlerrd(3)

In this example, up to 25 rows (the size of the array) can be returned by the SELECT statement. If the SELECT statement could return more than 25 rows, then 25 rows will be returned and SQLCODE will be set to indicate that more rows are available but could not be returned.

Use a SELECT statement only when you know the maximum number of rows to be selected. When the number of rows to be returned is unknown, use the FETCH statement. Using arrays, it is possible to fetch data in batches. This can be useful when creating a scrolling list of information.

If you use multiple host arrays in a single SQL statement, their dimensions must be the same.

Notes:

You cannot mix host arrays and simple host variables within a single SQL statement. If any of the host variables is an array, they must all be arrays.
You must define all host variables within a host array with the same number of occurrences. If one variable has 25 occurrences, all variables in that host array must have 25 occurrences.

This information on host arrays applies in its entirety if you are using an Oracle database. If you are using a Sybase database, however, you can only use host arrays as output variables in either a SELECT or a FETCH statement.
Both Oracle and Sybase allow a simple host variable in the WHERE clause of a SELECT statement. This is the only time that you can mix array host variables and simple host variables.

The FOR Clause

By default, the entire array is processed by an SQL statement but you can use the optional FOR clause to limit the number of array elements processed to just those that you want. This is especially useful in UPDATE, INSERT and DELETE statements where you may not want to use the entire array.

The FOR clause must use an integer host variable, for example:

 EXEC SQL
     BEGIN DECLARE SECTION
 END-EXEC
 01 AUTH-REC-TABLES
    05 Auth-id       OCCURS 25 TIMES PIC X(12).
    05 Auth-Lname    OCCURS 25 TIMES PIC X(40).
 01 maxitems         PIC S9(4) COMP-5 VALUE 10.
 EXEC SQL
     END DECLARE SECTION
 END-EXEC.
   . . .
     EXEC SQL
         CONNECT USERID 'user' IDENTIFIED BY 'pwd' 
                 USING 'db_alias'
     END-EXEC
     EXEC SQL
         FOR :maxitems
          UPDATE   authors
           SET   au_lname = :Auth_Lname
           WHERE au_id  = :Auth_id
     END-EXEC
     display sqlerrd(3)

In this example, 10 rows (the value of :maxitems) are modified by the UPDATE statement.

The number of array elements processed is determined by comparing the dimension of the host array with the FOR clause variable. The lesser value is used.

If the value of the FOR clause variable is less than or equal to zero, no rows are processed.

Note:

COBSQL Preprocessor

If you are using COBSQL, this information on the FOR clause is only applicable if you are using an Oracle database. It does not apply if you are using either a Sybase or an Informix database.

Determining the Number of Rows Processed

The third element of SQLERRD in the SQLCA, SQLERRD(3), records the number of rows processed for INSERT, UPDATE, DELETE and SELECT INTO statements.

OpenESQL Preprocessor

For FETCH statements, SQLERRD(3) always contains the number of rows fetched by the last FETCH statement.

COBSQL and DB2 Preprocessors

For FETCH statements, SQLERRD(3) records the cumulative sum of rows processed.

The DB2 Preprocessor

SQLERRD(3) contains the following:

If PREPARE is invoked and successful, an estimate of the number of rows that will be returned.
After INSERT, UPDATE and DELETE, the actual number of rows affected.
If compound SQL is invoked, a count of the number of successful sub-statements.
If CONNECT is invoked, either 1 if the database can be updated or 2 if the database is read-only.
If an error occurs when processing a host array, the last row that was processed successfully.

SQLERRD(4) contains the following:

If PREPARE is invoked and successful, a relative cost estimate of the resources required to process the statement.
If compound SQL is invoked, a count of the number of successful sub-statements.
If CONNECT is invoked, 0 for a one-phase commit from a down-level client; 1 for a one-phase commit; 2 for a one-phase, read-only commit; 3 for a two-phase commit.

SQLERRD(5) contains the following:

The total number of rows deleted, inserted, or updated as a result of both:
- The enforcement of constraints after a successful delete operation.
- The processing of triggered SQL statements from activated triggers.
If compound SQL is invoked, an accumulation of the number of such rows for all sub-statements. In some cases, when an error is encountered, this field contains a negative value that is an internal error pointer.
If CONNECT is invoked, an authentication type value of 0 for a server authentication; 1 for client authentication; 2 for authentication using DB2 Connect; 3 for DCE security services authentication; 255 for unspecified authentication.

Indicator Variables

Embedded SQL enables you to store and retrieve null values from a database by using indicator variables. Indicator variables are always defined as:

pic S9(4) comp-5.

Null Values

Unlike COBOL, SQL supports variables that can contain null values. A null value means that no entry has been made and usually implies that the value is either unknown or undefined. A null value enables you to distinguish between a deliberate entry of zero (for numerical columns) or a blank (for character columns) and an unknown or inapplicable entry. For example, a null value in a price column does not mean that the item is being given away free, it means that the price is not known or has not been set.

Together, a host variable and its companion indicator variable specify a single SQL value. Both variables must be preceded by a colon (:). When a host variable is null, its indicator variable has the value -1; when a host variable is not null, the indicator variable has a value other than -1.

Within an embedded SQL statement an indicator variable should be placed immediately after its corresponding host variable. For example, the following embedded UPDATE statement uses a saleprice host variable with a companion indicator variable, saleprice-null:

     EXEC SQL
        UPDATE closeoutsale
           SET temp_price = :saleprice:saleprice-null, 
                listprice = :oldprice   
     END-EXEC

In this example, if saleprice-null has a value of -1, when the UPDATE statement executes, the statement is read as:

     EXEC SQL
        UPDATE closeoutsale
           SET temp_price = null, listprice = :oldprice   
     END-EXEC

You cannot use indicator variables in a search condition. To search for null values, use the is null construct. For example, you can use the following:

     if saleprice-null equal -1
         EXEC SQL
             DELETE FROM closeoutsale 
                    WHERE temp_price is null
         END-EXEC
     else
         EXEC SQL
             DELETE FROM closeoutsale 
                    WHERE temp_price = :saleprice
         END-EXEC
     end-if

Data Truncation

Indicator variables serve an additional purpose if truncation occurs when data is retrieved from a database into a host variable. If the host variable is not large enough to hold the data returned from the database, the warning flag sqlwarn1 in the SQLCA data structure is set and the indicator variable is set to the size of the data contained in the database.

Indicator Arrays

Use indicator arrays in the same ways that you can use indicator variables, that is:

To assign null values.
To detect null values.
To detect data truncation.

In the following example, an indicator array is set to -l so that it can be used to insert null values into a column:

 01 ix                       PIC 99 COMP-5.
   . . .
 EXEC SQL
     BEGIN DECLARE SECTION
 END-EXEC
 01 sales-id       OCCURS 25 TIMES PIC X(12).
 01 sales-name     OCCURS 25 TIMES PIC X(40).
 01 sales-comm     OCCURS 25 TIMES PIC S9(9) COMP-5.
 01 ind-comm       OCCURS 25 TIMES PIC S9(4) COMP-5.
 EXEC SQL
     END DECLARE SECTION
 END-EXEC.
  . . .
     PERFORM VARYING iX FROM 1 BY 1 UNTIL ix > 25
         MOVE -1 TO ind-comm (ix)
     END-PERFORM.
      . . .
     EXEC SQL
         INSERT INTO SALES (ID, NAME, COMM)
           VALUES (:sales_id, :sales_name, :sales_comm:ind-comm)
     END-EXEC

COBSQL

If you are using COBSQL, this information on indicator arrays is only applicable if you are using an Oracle database. It does not apply if you are using a Sybase database.