Using host structures in ILE RPG applications that use SQL

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.

DBIGCHR           DS           642
 

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.

DPEMPL            DS
D EMPNO                  01     06A
D FIRSTN                 07     18A
D MIDINT                 19     19A
D LASTNA                 20     34A
D DEPTNO                 35     37A
 
…
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, 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:

Dfststruct       DS
D sub1                            4B 0
D sub2                            9B 0
D sub3                           20I 0
D sub4                            9B 0

Dsecstruct       DS                      QUALIFIED
D sub1                            4A
D sub2                           12A
D sub3                           20I 0
D myvar                           5A
D sub5                           20A

D myvar          S               10I 0 

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.