Simple Host Variables

Before you can use a host variable in an embedded SQL statement, you must declare it.

Declaring simple host variables

Generally, host variable declarations are coded as data items bracketed by the embedded SQL statements BEGIN DECLARE SECTION and END DECLARE SECTION. The following rules also apply:

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.
For OpenESQL and DB2 ECM only, you can use COBOL data items as host variables even if they have not been declared using BEGIN DECLARE SECTION and END DECLARE SECTION.
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 permitted in host variable names only when you compile using DIALECT"MF" (default setting) or DIALECT"ENTCOBOL".

Referencing simple host variables

You reference host variables from embedded SQL statements. When you code a host variable name into an embedded SQL statement, it must be preceded by a colon (:) to enable the compiler to distinguish between the host variable and tables or columns with the same name.

Example

 EXEC SQL
     BEGIN DECLARE SECTION
 END-EXEC
 01 id             pic x(4).
 01 name           pic x(30).
 01 book-title     pic x(40).
 01 book-id        pic x(5).
 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.

* 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