Using host structures in RPG/400 applications that use SQL

The RPG/400® data structure name can be used as a host structure name if subfields exist in the data structure. The use of the data structure name in an SQL statement implies that it is the list of subfield names that make up the data structure.

When subfields are not present for the data structure, then the data structure name is a host variable of character type. This allows character variables larger than 256, because data structures can be up to 9999.

In the following example, BIGCHR is an RPG/400 data structure without subfields. SQL treats any referrals to BIGCHR as a character string with a length of 642.

*...1....+....2....+....3....+....4....+....5....+....6....+....7...*
IBIGCHR      DS                            642

In the next example, PEMPL is the name of the host structure consisting of the subfields EMPNO, FIRSTN, MIDINT, LASTNAME, and DEPTNO. The referral to PEMPL uses the subfields. For example, the first column of EMPLOYEE is placed in EMPNO, the second column is placed in FIRSTN, and so on.

    *...1....+....2....+....3....+....4....+....5....+....6....+....7. ..*
    IPEMPL       DS
    I                                       01  06 EMPNO
    I                                       07  18 FIRSTN
    I                                       19  19 MIDINT
    I                                       20  34 LASTNA
    I                                       35  37 DEPTNO
…
    C                     MOVE  '000220'  EMPNO
…
    C/EXEC SQL
    C+ SELECT *  INTO :PEMPL
    C+ FROM  CORPDATA.EMPLOYEE
    C+ WHERE  EMPNO = :EMPNO
    C/END-EXEC

When writing an SQL statement, referrals to subfields can be qualified. Use the name of the data structure, followed by a period and the name of the subfield. For example, PEMPL.MIDINT is the same as specifying only MIDINT.