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).
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 |
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
-LENand-ARRcan 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.
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.
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.