CA Datacom/DB SQL Preprocessors › Embedding SQL Statements in Host Programs › Coding Embedded SQL in PL/I › Distinguishing Storage for Use as Host-Variables in PL/I

Distinguishing Storage for Use as Host-Variables in PL/I

The information in this section is divided into three parts:

• ANSI and DB2A86 modes
• Modes other than ANSI and DB2A86
• All modes

For ANSI and DB2A86 Modes

For ANSI and DB2A86 SQL modes, all storage that is referenced by the statement must be delimited by starting and ending statements. For example:

 EXEC SQL BEGIN DECLARE SECTION ;
     storage declarations of host-variables.
 EXEC SQL END DECLARE SECTION ;

In the previous example, the EXEC SQL lines (through and including the semicolon) are reserved. They may appear on one or more lines but should not be mixed with code not related to SQL since the Preprocessor comments the lines. You can, if desired, place the semicolon immediately following SECTION (that is to say, without a space between the N and the semicolon). All other reserved words must be separated by a space.

You may have more than one SQL declare section. All references to storage must follow the declaration in the source.

You must comply with the following requirements when you code host-variable declarations:

• All variables must be declared at the elementary level. You may declare more than one occurrence of the same variable type in a declare.
• All declarations within the DECLARE SECTION must conform as either a potential host or indicator.

For Modes Other Than ANSI and DB2A86

For modes other than ANSI and DB2A86, you must comply with the following requirements when you code host-variable declarations:

• You may declare structures of fields. These may be referenced as direct or indirect.

  direct

  A direct reference specifies a field within a structure or substructure that can be used as a host-variable. A reference in this form may be the name or a qualified name of two levels.

  indirect

  Indirect reference specifies a structure or substructure that can be expanded in its entirety into fields that are host-variables. A reference to a structure is its name. A reference to a substructure is its name which must be unique or a unique two-level qualified name.

  For example, consider the following abstract structures. Assume elementary fields except X are eligible to be host fields.

         Structure 1
                 DCL 1   A,
                      2  B  ...,
                      2  C  ...;
         Structure 2
                 DCL 1   D,
                      2  E,
                       3 F  ...,
                       3 G  ...,
                      2  H  ...;
         Structure 3
                 DCL 1   M,
                      2  N  ...,
                      2  O  ...,
                      2  X  ...;
  

  Structure 1 can be referenced directly by specifying B, C, A.B or A.C. It can be referenced indirectly by specifying A which is expanded into its components B and C to match the SQL statement.

  Structure 2 can be referenced directly by specifying F, G, or H. Structure D cannot be referenced indirectly since it is more than two levels. Substructure E can be referenced indirectly expanding into fields F and G.

  Structure 3 can be referenced directly by specifying N or O. Remember that X is invalid as a host-variable. Although structure M is two levels, indirect reference is not possible since X is a component.

  An array of indicator variables may be specified with an indirect reference. Each element field is paired with an indicator variable until all fields have indicators or there are no more indicators remaining in the array.

• Declarations used as host-variables or indicators may appear after a statement in the source. The use of EXEC SQL BEGIN and END DECLARE SECTION markers is optional.

For All Modes

For all modes, you must comply with the following requirements when you code host-variable declarations:

• Indicator variables must be declared as fixed binary(15) or binary fixed(15). As with host-variables, declarations of indicator variables may contain multiple occurrences.
• Declarations may be defined in PL/I INCLUDE directives that have been expanded by the Preprocessor.
• Declarations may be defined in SQL INCLUDE directives.