Host variables and datatypes

Host variables

A host variable is a COBOL data item used in an SQL statement, prefixed by a colon. It is an input variable when its value is sent to the database (WHERE, VALUES, SET clauses), and an output variable when it receives a value from the database (INTO clauses).

           EXEC SQL
               SELECT ENAME, SAL INTO :EMP-NAME, :SALARY
                 FROM EMP WHERE EMPNO = :EMP-NUMBER
           END-EXEC.

Host variable names are case-insensitive and may be qualified by their group, as in COBOL (:EMPNO OF EMP-REC).

NoteTo be verified

Qualification (OF/IN) and the use of a group item as a list of host variables (INTO :EMP-REC).

Datatype conversion

The runtime converts the COBOL data to and from the database types, according to the PICTURE and USAGE of the host variable:

COBOL declaration Sent to the database as
PIC X(n) character string (see Fixed-length character strings)
PIC 9(n), PIC S9(n)V9(m) (DISPLAY) number
PIC S9(n)V9(m) COMP-3 number
PIC S9(n) COMP, COMP-5, BINARY integer
COMP-1, COMP-2 floating point
PIC N(n) national string
NoteTo be completed

Complete this table from CobolVarType (runtime/libgixsql/SqlVar.h) and document the conversion of dates.

Fixed-length character strings

The precompiler option -P / --picx-as chooses how PIC X items are sent to the database:

char (default), charf
as fixed-length strings, trailing spaces included;
varchar
as variable-length strings, trailing spaces removed.

This option does not apply to VARYING items, whose length is always given by their length field.

Variable-length strings: VARYING

A VARYING item has a length and a data part:

       01  ENAME  PIC X(20) VARYING.

The precompiler replaces it with a group of two level-49 items:

       01  ENAME.
           49 ENAME-LEN  PIC 9(8) COMP-5.
           49 ENAME-ARR  PIC X(20).

On output, the runtime sets ENAME-LEN to the length of the value; on input, only the first ENAME-LEN characters are sent.

  • The suffixes -LEN and -ARR can be changed with -Y / --varying (for example --varying=LEN,TEXT).
  • The length field is 4 bytes by default. Pro*COBOL uses a 2-byte length (PIC S9(4) COMP): use -N 2 / --varying-length-size 2. The generated code tells the runtime the size of the length fields.

Group items with level-49 items written by hand are also recognized as variable-length strings, as in Pro*COBOL. Their length field must have the size given by -N.

NoteTo be verified

Detection of hand-written level-49 groups, and PIC X(n) VARYING vs USAGE VARRAW.

SQL TYPE IS

The SQL TYPE IS clause declares a host variable with an SQL type:

       01  BUFFER  SQL TYPE IS VARBINARY(100).
       01  STMT    SQL TYPE IS VARCHAR(500).

VARCHAR and VARBINARY generate a VARYING group as above.

EXEC SQL VAR

The VAR statement gives an SQL type to an existing data item (datatype equivalencing, in Pro*COBOL terms):

       01  VARD  PIC X(120).
       01  NUM3  COMP-2.
       ...
           EXEC SQL VAR VARD IS VARCHAR(120) END-EXEC.
           EXEC SQL VAR NUM3 IS FLOAT END-EXEC.

The supported types are CHAR, CHARF, VARCHAR, VARCHAR2 (same as VARCHAR), VARBINARY, INTEGER, FLOAT, DECIMAL and LONG RAW.

Indicator variables

An indicator variable is associated with a host variable to handle NULL values. It must be declared PIC S9(4) COMP, and is written just after the host variable, optionally preceded by INDICATOR:

       01  COMM      PIC S9(5)V99 COMP-3.
       01  COMM-IND  PIC S9(4) COMP.
       ...
           EXEC SQL
               SELECT COMM INTO :COMM:COMM-IND
                 FROM EMP WHERE EMPNO = :EMP-NUMBER
           END-EXEC.
           IF COMM-IND = -1
               DISPLAY "No commission"
           END-IF.
Value On output (SELECT, FETCH) On input (INSERT, UPDATE)
-1 the column is NULL NULL is sent, the host variable is ignored
0 the value was stored the host variable is sent

Truncation (positive indicator values) is not reported.

NoteTo be verified

INDICATOR keyword, indicator tables for group items.

Host tables (arrays)

GixSQL does not support host tables: a host variable cannot be a table (OCCURS) to fetch or insert several rows in a single statement. Use a cursor and a loop instead. See Feature comparison.