Using external file descriptions in ILE RPG applications that use SQL

Field definitions for externally described files, including renaming of fields, are recognized by the SQL precompiler. The external definition form of the data structure can be used to obtain a copy of the column names to be used as host variables.

How date and time field definition are retrieved and processed by the SQL precompiler depends on whether *NOCVTDT or *CVTDT is specified on the OPTION parameter of the CRTSQLRPGI command. If *NOCVTDT is specified, then date and time field definitions are retrieved including the format and separator. If *CVTDT is specified, then the format and separator are ignored when date and time field definitions are retrieved, and the precompiler assumes that the variable declarations are date/time host variables in character format. *CVTDT is a compatibility option for the ILE RPG precompiler.

If the GRAPHIC or VARGRAPHIC column has a UCS-2 CCSID, the generated host variable will have the UCS-2 CCSID assigned to it. If the GRAPHIC or VARGRAPHIC column has a UTF-16 CCSID, the generated host variable will have the UTF-16 CCSID assigned to it.

CLOB, BLOB, and DBCLOB columns in the external file are ignored. No host variable definition will be generated in the host structure for these types.

In the following example, the sample table DEPARTMENT is used as a file in an ILE RPG program. The SQL precompiler retrieves the field (column) definitions for DEPARTMENT for use as host variables.

DCL-F DEPARTMENT DISK(*EXT) USAGE(*INPUT) RENAME(ORIGREC:DEPTREC);

Note: Code an F-spec for a file in your ILE RPG program only if you use ILE RPG statements to do I/O operations to the file. If you use only SQL statements to do I/O operations to the file, you can include the external definition of the file (table) by using an external data structure.

In the following example, the sample table is specified as an external data structure. The SQL precompiler retrieves the field (column) definitions as subfields of the data structure. Subfield names can be used as host variable names, and the data structure name TDEPT can be used as a host structure name. The example shows that the field names can be renamed if required by the program.

DCL-DS TDEPT EXT EXTNAME('DEPARTMENT');
  DEPTN EXTFLD('DEPTNAME')
  ADMRD EXTFLD('ADMRDEPT')
END-DS;