Using host structures in ILE RPG applications that use SQL

The ILE RPG data structure name can be used as a host structure name. The use of the data structure name in an SQL statement implies the specification of the list of subfield names that make up the data structure.

When a data structure contains one or more unnamed subfields, the data structure name cannot be used as a host structure in an SQL statement. The named subfields can be used as host variables.

The following must be considered when using a data structure as a host structure name:

All subfields must be valid host variable declarations.
All subfields must have a name.
No subfield can be defined with the DIM or OCCUR keywords.

Any subfields within a data structure which are defined out of order or that overlap earlier bytes in the data structure will be skipped by the precompiler. This includes bytes that were skipped for alignment or positioning. For example, in the following structure declaration the FIRSTNAME subfield will be skipped since it is defined to use storage prior to the LASTNAME subfield.

DCL-DS PEMPL;
  LASTNAME CHAR(10) POS(11);
  FIRSTNAME CHAR(10) POS(1);
END-DS;

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

DCL-DS BIGCHR LEN(642);
END-DS;

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

DCL-DS PEMPL;
  EMPNO CHAR(6);
  FIRSTN CHAR(12);
  MIDINT CHAR(1);
  LASTNA CHAR(15);
  DEPTNO CHAR(3);
END-DS;
...
EMPNO = '000220';
...
EXEC SQL SELECT * INTO :PEMPL
  FROM CORPDATA.EMPLOYEE
  WHERE EMPNO = :EMPNO;

When writing an SQL statement, references to subfields that are not in a QUALIFIED data structure 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. If the data structure has the QUALIFIED keyword, then the subfield must be referenced using the data structure name to qualify the subfield name.

In this example, there are two data structures, one QUALIFIED and one not QUALIFIED, that contain the same subfield names:

DCL-DS fststruct;
  sub1 INT(5);
  sub2 INT(10);
  sub3 INT(20);
  sub4 INT(10);
END-DS;

DCL-DS secstruct QUALIFIED;
  sub1 CHAR(4);
  sub2 CHAR(12);
  sub3 INT(20);
  myvar CHAR(5);
  sub5 CHAR(20);
END-DS;

DCL-S myvar INT(10);

Referencing secstruct.sub1 as a host variable will be a character variable with a length of 4.

sub1 as a host variable will have an SQL data type of small integer. It picks up its attributes from the data structure that is not QUALIFIED.

A host variable reference to myvar will use the standalone declaration to pick up the data type of integer. If you use secstruct.myvar, the character variable in the QUALIFIED structure will be used.

You cannot refer to sub5 without qualifying it with secstruct because it is in a QUALIFIED data structure.

The precompiler will recognize a host structure defined using the LIKEDS keyword. However, the SQL syntax for a host variable only allows using a single level of qualification in an SQL statement. This means that if a data structure DS has a subfield S1 which is defined like a data structure with a subfield S2, an SQL statement cannot refer to S2 using the fully qualified host variable name of DS.S1.S2. If you use S1.S2 as the host variable reference, the precompiler will recognize it as DS.S1.S2. The following additional restrictions apply:

The top level structure, DS, cannot be an array.
S1.S2 must be unique. That is, there must be no other valid names in the program ending with S1.S2, such as a structure S1 with a subfield S1.S2, or a structure DS3 with a subfield DS3.S0.S1.S2.

Example

DCL-DS CustomerInfo QUALIFIED;
  Name CHAR(20);
  Address CHAR(50);
END-DS;

DCL-DS ProductInfo QUALIFIED;
  Number CHAR(5);
  Description CHAR(20);
  Cost PACKED(9:2);
END-DS;

DCL-DS SalesTransaction QUALIFIED;
  Buyer LIKEDS(CustomerInfo);
  Seller LIKEDS(CustomerInfo);
  NumProducts INT(10);
  Product LIKEDS(ProductInfo) DIM(10);
END-DS;

EXEC SQL
  SELECT * INTO :CustomerInfo.Name, :Buyer.Name FROM MYTABLE;

CustomerInfo.Name will be recognized as a reference to the QUALIFIED structure's variable. Buyer.Name will be defined as SalesTransaction.Buyer.Name.

You cannot use SalesTransaction.Buyer.Name in an SQL statement because only one level of qualification is allowed in SQL syntax. You cannot use Product.Cost in an SQL statement because cost is in a dimensioned array.

If there is a SalesTransaction2 defined like SalesTransaction, then the subfields that are structures cannot be used in SQL statements. Because only one level of qualification is supported by SQL, a reference to Buyer.Name is ambiguous.