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:
These are used to specify data that will be transferred from the COBOL program to the database.
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
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.
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:
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.
OpenESQL and DB2 (using
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.
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
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.
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.
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.
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
variable with a companion indicator variable,
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
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
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
Note: 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.
Copyright © 2000 MERANT International Limited. All rights reserved.
This document and the proprietary marks and names used herein are protected by international law.