Determining equivalent SQL and ILE RPG data types
The precompiler determines the base SQLTYPE and SQLLEN of host variables according to this table. If a host variable appears with an indicator variable, the SQLTYPE is the base SQLTYPE plus one.
RPG data type | RPG coding | SQLTYPE of host variable | SQLLEN of host variable | SQL data type |
---|---|---|---|---|
Data structure (without subfields) | Free-form:
|
452 | n | CHAR(n) |
Zoned data | Free-form:
|
488 | p in byte 1, s in byte 2 | NUMERIC(p, s) where p is the number of digits and s is the number of decimal places |
Packed data | Free-form:
|
484 | p in byte 1, s in byte 2 | DECIMAL(p, s) where p is the number of digits and s is the number of decimal places |
2-byte binary with zero decimal positions | Free-form:
Fixed-form:
|
500 | 2 | SMALLINT |
4-byte binary with zero decimal positions | Free-form:
Fixed-form:
|
496 | 4 | INTEGER |
2-byte integer | Free-form:
|
500 | 2 | SMALLINT |
4-byte integer | Free-form:
|
496 | 4 | INTEGER |
8-byte integer | Free-form:
|
492 | 8 | BIGINT |
short float | Free-form:
|
480 | 4 | FLOAT (single precision) |
long float | Free-form:
|
480 | 8 | FLOAT (double precision) |
Character | Free-form:
|
452 | n | CHAR (n) where n is the length |
Character varying length greater than 254 | Free-form:
|
448 | n | VARCHAR (n) where n is the length |
Character varying length between 1 and 254 | Free-form:
|
456 | n | VARCHAR (n) where n is the length |
graphic | Free-form:
|
468 | m | GRAPHIC(m) where m = n or m = b/2 |
varying graphic | Free-form:
|
464 | m | VARGRAPHIC(m) where m = n or m = (b-2)/2 |
UCS-2 | Free-form:
|
468 | m | GRAPHIC(m) with CCSID 13488 or CCSID 1200 where m = n or m = b/2 |
varying UCS-2 | Free-form:
|
464 | m | VARGRAPHIC(m) with CCSID 13488 or CCSID 1200 where m = n or m = b/2 |
Date | Free-form:
|
384 | DATE DATFMT(f) DATSEP(s)1 | |
Time | Free-form:
|
388 | TIME TIMFMT(f) TIMSEP(s)1 | |
Timestamp | Free-form:
|
392 | 19 when n = 0, otherwise 20+n | TIMESTAMP(n) |
1SQL creates the date/time subfield using the DATE/TIME format specified on the CRTSQLRPGI command. The conversion to the host variable DATE/TIME format occurs when the mapping is done between the host variables and the SQL-generated subfields. |
The following table can be used to determine the RPG data type that is equivalent to a given SQL data type.
SQL data type | RPG data type | Notes |
---|---|---|
SMALLINT | Free-form:
|
|
INTEGER | Free-form:
|
|
BIGINT | Free-form:
|
|
DECIMAL | Free-form:
|
Maximum length of 32 (precision 63) and maximum scale of 63. |
NUMERIC | Free-form:
|
Maximum length of 63 (precision 63) and maximum scale of 63. |
DECFLOAT | Not supported | Not supported |
FLOAT (single precision) | Free-form:
|
|
FLOAT (double precision) | Free-form:
|
|
CHAR(n) | Free-form:
|
n can be from 1 to 32766. |
CHAR(n) | Free-form:
|
n can be from 1 to 32766. |
VARCHAR(n) | Free-form:
|
n can be from 1 to 32740. |
CLOB | Not supported | Use SQLTYPE keyword to declare a CLOB. |
GRAPHIC(n) | Free-form:
|
n can be 1 to 16383. |
VARGRAPHIC(n) | Free-form:
|
n can be from 1 to 16370. |
DBCLOB | Not supported | Use SQLTYPE keyword to declare a DBCLOB. |
BINARY | Not supported | Use SQLTYPE keyword to declare a BINARY. |
VARBINARY | Not supported | Use SQLTYPE keyword to declare a VARBINARY. |
BLOB | Not supported | Use SQLTYPE keyword to declare a BLOB. |
DATE | Free-form:
|
If the format is *USA, *JIS, *EUR, or *ISO, the length must be at least 10. If the format is *YMD, *DMY, or *MDY, the length must be at least 8. If the format is *JUL, the length must be at least 6. |
TIME | Free-form:
|
Length must be at least 6; to include seconds, length must be at least 8. |
TIMESTAMP(n) | Free-form:
|
For a character field, the length must be at least 19; to include fractional seconds, the length can be 21 to 32. If less than 32, some fractional seconds will be truncated. |
XML | Not supported | Use SQLTYPE keyword to declare an XML. |
DATALINK | Not supported | |
ROWID | Not supported | Use SQLTYPE keyword to declare a ROWID. |
Result set locator | Not supported | Use SQLTYPE keyword to declare a result set locator. |