PreviousIntroduction Data TypesNext

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:

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

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

* This statement retrieves the employee name whose
* employee id is the same as the contents of the 
* data item "id". The name is returned in
* the data item "name".

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

OpenESQL and DB2
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:


Notes:


2.2 Host Arrays

COBSQL
If you are using COBSQL, 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.

An array is a collection of data items associated with a single variable name. You can define array 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.


Notes:

OpenESQL and DB2

COBSQL


Host arrays are declared in the same way as simple host variables using BEGIN DECLARE SECTION and END DECLARE SECTION, but 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.

A SELECT statement should only be used when you know the maximum number of rows to be selected. When the number of rows to be returned is unknown, the FETCH statement should be used. With the use of 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.

2.2.1 The FOR Clause

COBSQL
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 a Sybase database.

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.

2.2.2 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. For FETCH statements, it records the cumulative sum of rows processed.

DB2
For DB2, SQLERRD(3) contains the following:

DB2
For DB2, SQLERRD(4) contains the following:

DB2
For DB2, SQLERRD(5) contains the following:

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

2.3.1 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

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

2.3.3 Indicator Arrays

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.

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

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

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.
.
.
.
MOVE -1 TO ind-comm.
.
.
.
EXEC SQL
   INSERT INTO SALES (ID, NAME, COMM)
      VALUES (:sales_id, :sales_name, :sales_comm:ind-comm)
END-EXEC


Copyright © 1998 Micro Focus Limited. All rights reserved.
This document and the proprietary marks and names used herein are protected by international law.
PreviousIntroduction Data TypesNext