Using external file descriptions in RPG/400 applications that use SQL

The SQL precompiler processes the RPG/400® source in much the same manner as the ILE RPG compiler. This means that the precompiler processes the /COPY statement for definitions of host variables.

Field definitions for externally described files are obtained and renamed, if different names are specified. 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.

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

*...1....+....2....+....3....+....4....+....5....+....6....+....7....*
FTDEPT   IP  E                    DISK
F            TDEPT                             KRENAMEDEPTREC
IDEPTREC
I              DEPTNAME                        DEPTN
I              ADMRDEPT                        ADMRD

Note: Code an F-spec for a file in your RPG program only if you use RPG/400 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 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 field names must be changed because they are greater than six characters.

*...1....+....2....+....3....+....4....+....5....+....6....+....7....*
ITDEPT     E DSDEPARTMENT
I              DEPTNAME                        DEPTN
I              ADMRDEPT                        ADMRD

Note: DATE, TIME, and TIMESTAMP columns will generate host variable definitions that are treated by SQL with the same comparison and assignment rules as a DATE, TIME, and TIMESTAMP column. For example, a date host variable can only be compared against a DATE column or a character string that is a valid representation of a date.

Although varying-length columns generate fixed-length character-host variable definitions, to SQL they are varying-length character variables.

Although GRAPHIC and VARGRAPHIC columns are mapped to character variables in RPG/400, SQL considers these GRAPHIC and VARGRAPHIC variables. 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.