EGL Reference Guide for iSeries

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.

The next sections cover these topics:

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:

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

Columns with the following SQL data types cannot be accessed in a generated COBOL program because an equivalent COBOL data type does not exist:

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


Related reference
SQL item properties
sysVar.currentFormattedDate


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]