Declaring host variables in ILE RPG applications that use SQL

The SQL ILE RPG precompiler only recognizes a subset of valid ILE RPG declarations as valid host variable declarations.

Most variables defined in ILE RPG can be used in SQL statements. A partial listing of variables that are not supported includes the following:

  • Unsigned integers
  • Pointer
  • Tables
  • UDATE
  • UDAY
  • UMONTH
  • UYEAR
  • Look-ahead fields
  • Named constants
  • Multiple dimension arrays
  • Definitions requiring the resolution of %SIZE or %ELEM
  • Start of changeDefinitions requiring the resolution of constants unless the constant is used in OCCURS, DIM, Start of changeOVERLAY, or POSEnd of change and the constant is declared before it is used in the OCCURS, DIM, Start of changeOVERLAY, or POSEnd of change.End of change

Fields used as host variables are passed to SQL using the CALL/PARM functions of ILE RPG. If a field cannot be used in the result field of the PARM, it cannot be used as a host variable.

Date and time host variables are always assigned to corresponding date and time subfields in the structures generated by the SQL precompiler. The generated date and time subfields are declared using the format and separator specified by the DATFMT, DATSEP, TIMFMT, and TIMSEP parameters on the CRTSQLRPGI command or with the SET OPTION statement. Conversion from the user declared host variable format to the precompile specified format occurs on assignment to and from the SQL generated structure. If the DATFMT parameter value is a system format (*MDY, *YMD, *DMY, or *JUL), then all input and output host variables must contain date values within the range 1940-2039. If any date value is outside of this range, then the DATFMT on the precompile must be specified as one of the IBM® SQL formats of *ISO, *USA, *EUR, or *JIS.

The SQL precompiler accepts variables defined as VARYING(2), but does not support VARYING(4). The LEN keyword is recognized, but only lengths up to the SQL limit for the data type are accepted.

Graphic host variables will use the RPG CCSID value if one is specified. An SQL DECLARE VARIABLE statement cannot be used to change the CCSID of a host variable whose CCSID has been defined in RPG, or a host variable that is defined as UCS-2 or UTF-16.

The precompiler will generate an RPG logical (indicator) variable as a character of length 1. This type can be used wherever SQL allows a character host variable. It cannot be used as an SQL indicator variable. It is up to the user to make sure that only values of 1 or 0 are assigned to it.

The precompiler supports EXTNAME, but does not support EXTNAME(filename : fmtname : fieldtype), where fieldtype is *ALL, *INPUT, *OUTPUT, or *KEY.

The precompiler supports LIKEREC(intrecname), but does not support the optional second parameter.

The precompiler supports EXTDESC(literal), but does not support EXTDESC(constant).

Parent topic: Using host variables in ILE RPG applications that use SQL