Introduction

Data Types

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

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:

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.

Notes:

Host variable names that are 27 characters or longer may cause problems. It is therefore recommended that you restrict the names of data items used as host variables to 26 characters or less.
You can also use groups of data items as a single host variable.

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

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.

COBSQL

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.

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:

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.

DB2
For DB2, 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.

DB2
For DB2, 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.

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:

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:

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

Introduction

Data Types