Determining equivalent SQL and RPG/400 data types
The precompiler determines the base SQLTYPE and SQLLEN of host variables based on the table. If a host variable appears with an indicator variable, the SQLTYPE is the base SQLTYPE plus one.
| RPG/400® data type | Col 43 | Col 52 | Other RPG/400 coding | SQLTYPE of host variable | SQLLEN of host variable | SQL data type |
|---|---|---|---|---|---|---|
| Data Structure subfield | blank | blank | Length = n where n ≤ 256 | 452 | n | CHAR(n) |
| Data structure (without subfields) | n/a | n/a | Length = n where n ≤ 9999 | 452 | n | CHAR(n) |
| Input field | blank | blank | Length = n where n ≤ 256 | 452 | n | CHAR(n) |
| Calculation result field | n/a | blank | Length = n where n ≤ 256 | 452 | n | CHAR(n) |
| Data Structure subfield | B | 0 | Length = 2 | 500 | 2 | SMALLINT |
| Data Structure subfield | B | 0 | Length = 4 | 496 | 4 | INTEGER |
| Data Structure subfield | B | 1-4 | Length = 2 | 500 | 2 | DECIMAL(4,s) where s=column 52 |
| Data Structure subfield | B | 1-9 | Length = 4 | 496 | 4 | DECIMAL(9,s) where s=column 52 |
| Data Structure subfield | P | 0 to 9 | Length = n where n is 1 to 16 | 484 | p in byte 1, s in byte 2 | DECIMAL(p,s) where p = n*2-1 and s = column 52 |
| Input field | P | 0 to 9 | Length = n where n is 1 to 16 | 484 | p in byte 1, s in byte 2 | DECIMAL(p,s) where p = n*2-1 and s = column 52 |
| Input field | blank | 0 to 9 | Length = n where n is 1 to 30 | 484 | p in byte 1, s in byte 2 | DECIMAL(p,s) where p = n and s = column 52 |
| Input field | B | 0 to 4 if n = 2; 0 to 9 if n = 4 | Length = 2 or 4 | 484 | p in byte 1, s in byte 2 | DECIMAL(p,s) where p=4 if n=2 or 9 if n=4 and s = column 52 |
| Calculation result field | n/a | 0 to 9 | Length = n where n is 1 to 30 | 484 | p in byte 1, s in byte 2 | DECIMAL(p,s) where p = n and s = column 52 |
| Data Structure subfield | blank | 0 to 9 | Length = n where n is 1 to 30 | 488 | p in byte 1, s in byte 2 | NUMERIC(p,s) where p = n and s = column 52 |
Use the information in the following table to determine the RPG/400 data type that is equivalent to a given SQL data type.
| SQL data type | RPG/400 data type | Notes |
|---|---|---|
| SMALLINT | Subfield of a data structure. B in position 43, length must be 2 and 0 in position 52 of the subfield specification. | |
| INTEGER | Subfield of a data structure. B in position 43, length must be 4 and 0 in position 52 of the subfield specification. | |
| BIGINT | No exact equivalent | Use P in position 43 and 0 in position 52 of the subfield specification. |
| DECIMAL | Subfield of a data structure. P in
position 43 and 0 through 9 in position 52 of the subfield specification. OR Defined as numeric and not a subfield of a data structure. |
Maximum length of 16 (precision 30) and maximum scale of 9. |
| NUMERIC | Subfield of the data structure. Blank in position 43 and 0 through 9 in position 52 of the subfield | Maximum length of 30 (precision 30) and maximum scale of 9. |
| DECFLOAT | Not supported | Not supported |
| FLOAT (single precision) | No exact equivalent | Use one of the alternative numeric data types described above. |
| FLOAT (double precision) | No exact equivalent | Use one of the alternative numeric data types described above. |
| CHAR(n) | Subfield of a data structure or input
field. Blank in positions 43 and 52 of the specification. OR Calculation result field defined without decimal places. |
n can be from 1 to 256. |
| CHAR(n) | Data structure name with no subfields in the data structure. | n can be from 1 to 9999. |
| VARCHAR(n) | No exact equivalent | Use a character host variable large enough to contain the largest expected VARCHAR value. |
| CLOB | Not supported | Not supported |
| GRAPHIC(n) | Not supported | Not supported |
| VARGRAPHIC(n) | Not supported | Not supported |
| DBCLOB | Not supported | Not supported |
| BINARY | Not supported | Not supported |
| VARBINARY | Not supported | Not supported |
| BLOB | Not supported | Not supported |
| DATE | Subfield of a data structure. Blank
in position 52 of the subfield specification. OR Field defined without decimal places. |
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 | Subfield of a data structure. Blank
in position 52 of the subfield specification. OR Field defined without decimal places. |
Length must be at least 6; to include seconds, length must be at least 8. |
| TIMESTAMP | Subfield of a data structure. Blank
in position 52 of the subfield specification. OR Field defined without decimal places. |
Length must be at least 19. To include microseconds at full precision, length must be 26. If length is less than 26, truncation occurs on the microseconds part. |
| XML | Not supported | Not supported |
| DATALINK | Not supported | Not supported |
| ROWID | Not supported | Not supported |
| Result set locator | Not supported | Not supported |