SQL data codes and EGL host variables

The property SQL data code identifies the SQL data type to associate with the EGL host variable. The data code is used by the database management system at declaration time, validation time, or generated-program run time.

You may want to vary the SQL data code for a host variable that is of primitive type CHAR, DBCHAR, HEX, or UNICODE. For a host variable of one of the other primitive types, however, SQL data codes are fixed.

If EGL retrieved a column definition from the database management system, do not modify the SQL data code that was retrieved, if any.

Variable and fixed-length columns

To indicate that a table column is variable length or fixed length, set the SQL data code for the corresponding host variable to the appropriate value, as shown in the next table.

EGL primitive type	SQL data type	Variable or fixed	SQL data code
CHAR	CHAR (the default)	Fixed	453
	VARCHAR, length < 255	Variable	449
	VARCHAR, length > 254	Variable	457
DBCHAR, UNICODE	GRAPHIC (the default)	Fixed	469
	VARGRAPHIC, length < 128	Variable	465
	VARGRAPHIC, length > 127	Variable	473

Note: A SQL data type may require the use of null indicators, but this requirement has no effect on how you code an EGL program. For details on nulls, see SQL support.

Compatibility of SQL data types and EGL primitive types

An EGL host variable and the corresponding SQL table column are compatible in any of the following situations:

The SQL column is any form of character data, and the EGL host variable is of type CHAR with a length less than or equal to the length of the SQL column.
The SQL column is any form of DBCHAR data, and the EGL host variable is of type DBCHAR with a length less than or equal to the length of the SQL column.
The SQL column is any form of number and the EGL host variable is of either of these types:
- BIN, with 2 or 4 bytes and no decimal places.
- DECIMAL, with a maximum length of 18 digits, including decimal places. The number of digits for a DECIMAL variable should be the same for the EGL host variable and for the column.
- SMALLINT.
The SQL column is of any data type, the EGL host variable is of type HEX, and the column and host variable contain the same number of bytes. No data conversion occurs during data transfer.
EGL host variables of type HEX support access to any SQL column of a data type that does not correspond to an EGL primitive type.

If character data is read from an SQL table column into a shorter host variable, content is truncated on the right. To test for truncation, use the reserved word trunc in an EGL if statement.

If numeric data is read from an SQL table column into a shorter host variable, leading zeros are truncated on the left. If the number still does not fit into the host variable, fractional parts of the number (in decimal) are deleted on the right, with no indication of error. If the number still does not fit, a negative SQL code is returned to indicate an overflow condition.

The next table shows the EGL host variable characteristics that are assigned when the retrieve feature of the EGL editor extracts information from a database management system.

SQL data type	EGL host variable characteristics			SQL data code (SQLTYPE)
	Primitive type	Length	Number of bytes
BIGINT	HEX	16	8	493
CHAR	CHAR	1–32767	1–32767	453
DATE	CHAR	10	10	453
DECIMAL	DECIMAL	1-18	1–10	485
DOUBLE	HEX	16	8	481
FLOAT	HEX	16	8	481
GRAPHIC	DBCHAR	1–16383	2–32766	469
INTEGER	BIN	9	4	497
LONG VARBINARY	HEX	65534	32767	481
LONG VARCHAR	CHAR	>4000	>4000	457
LONG VARGRAPHIC	DBCHAR	>2000	>4000	473
NUMERIC	DECIMAL	1-18	1–10	485
REAL	HEX	8	4	481
SMALLINT	BIN	4	2	501
TIME	CHAR	8	8	453
TIMESTAMP	CHAR	26	26	453
VARBINARY	HEX	2–65534	1–32767	481
VARCHAR	CHAR	≤4000	≤4000	449
VARGRAPHIC	DBCHAR	≤2000	≤4000	465

VARCHAR, VARGRAPHIC, and the related LONG data types

The definition of an SQL table column of type VARCHAR or VARGRAPHIC includes a maximum length, and the retrieve command uses that maximum to assign a length to the EGL host variable. The definition of an SQL table column of type LONG VARCHAR or VARGRAPHIC, however, does not include a maximum length, and the retrieve command uses the SQL-data-type maximum to assign a length.

DATE, TIME, and TIMESTAMP

Make sure that the format used for the EGL system default long Gregorian format is the same as the date format specified for the SQL database manager. For details on how the EGL format is set, see sysVar.currentFormattedDate.

You want the two formats to match so that the dates provided by the system variable sysVar.currentFormattedDate are in the format expected by the SQL database manager.

Related concepts
SQL support