Rational Developer for System z
PL/I for Windows, Version 8.0, Programming Guide

Coding SQL statements in PL/I applications

Defining the SQL communications area
Defining SQL descriptor areas
Embedding SQL statements
Using host variables
Determining equivalent SQL and PL/I data types
Determining compatibility of SQL and PL/I data types
Using host structures
Using indicator variables
Host structure example
Using statements and compile-time options

You can code SQL statements in your PL/I applications using the language defined in SQL Reference, Volume 1 and Volume 2 (SBOF-8923). Specific requirements for your SQL code are described in the sections that follow.

Defining the SQL communications area

A PL/I program that contains SQL statements must include an SQL communications area (SQLCA) As shown in Figure 1 part of an SQLCA consists of an SQLCODE variable and an SQLSTATE variable.

The SQLCA should be included by using the SQL INCLUDE statement:

  exec sql include sqlca;

The SQLCA must not be defined within an SQL declare section. The scope of the SQLCODE and SQLSTATE declaration must include the scope of all SQL statements in the program.

Figure 1. The PL/I declaration of SQLCA
  Dcl
    1 Sqlca,
      2 sqlcaid     char(8),           /* Eyecatcher = 'SQLCA   '*/
      2 sqlcabc     fixed binary(31),  /* SQLCA size in bytes = 136 */
      2 sqlcode     fixed binary(31),  /* SQL return code */
      2 sqlerrm     char(70) var,      /* Error message tokens */
      2 sqlerrp     char(8),           /* Diagnostic information */
      2 sqlerrd(6)  fixed binary(31),  /* Diagnostic information */
      2 sqlwarn,                       /* Warning flags */
        3 sqlwarn0  char(1),
        3 sqlwarn1  char(1),
        3 sqlwarn2  char(1),
        3 sqlwarn3  char(1),
        3 sqlwarn4  char(1),
        3 sqlwarn5  char(1),
        3 sqlwarn6  char(1),
        3 sqlwarn7  char(1),
      2 sqlext,
        3 sqlwarn8  char(1),
        3 sqlwarn9  char(1),
        3 sqlwarna  char(1),
        3 sqlstate  char(5);       /* State corresponding to SQLCODE */

Defining SQL descriptor areas

The following statements require an SQLDA:

Unlike the SQLCA, there can be more than one SQLDA in a program, and an SQLDA can have any valid name. An SQLDA should be included by using the SQL INCLUDE statement:

  exec sql include sqlda;

The SQLDA must not be defined within an SQL declare section.

Figure 2. The PL/I declaration of an SQL descriptor area
  Dcl
    1 Sqlda based(Sqldaptr),
      2 sqldaid     char(8),          /* Eye catcher = 'SQLDA   ' */
      2 sqldabc     fixed binary(31), /* SQLDA size in bytes=16+44*SQLN*/
      2 sqln        fixed binary(15), /* Number of SQLVAR elements*/
      2 sqld        fixed binary(15), /* # of used SQLVAR elements*/
      2 sqlvar(Sqlsize refer(sqln)),  /* Variable Description     */
        3 sqltype   fixed binary(15), /* Variable data type       */
        3 sqllen    fixed binary(15), /* Variable data length     */
        3 sqldata   pointer,          /* Pointer to variable data value*/
        3 sqlind    pointer,          /* Pointer to Null indicator*/
        3 sqlname   char(30) var ;    /* Variable Name            */
  dcl Sqlsize   fixed binary(15);     /* number of sqlvars (sqln) */
  dcl Sqldaptr  pointer;

Embedding SQL statements

The first statement of your PL/I program must be a PROCEDURE or a PACKAGE statement. You can add SQL statements to your program wherever executable statements can appear. Each SQL statement must begin with EXEC (or EXECUTE) SQL and end with a semicolon (;).

For example, an UPDATE statement might be coded as follows:

  exec sql update Department
           export   Mgrno  = :Mgr_Num
           where    Deptno = :Int_Dept;
Comments

In addition to SQL statements, PL/I comments can be included in embedded SQL statements wherever a blank is allowed.

SQL style comments ('--') are supported when embedded in SQL statements.

Continuation for SQL statements

The line continuation rules for SQL statements are the same as those for other PL/I statements.

Including code

SQL statements or PL/I host variable declaration statements can be included by placing the following SQL statement at the point in the source code where the statements are to be embedded:

  exec sql include member;
Margins

SQL statements must be coded in columns m through n where m and n are specified in the MARGINS(m,n) compile-time option.

Names

Any valid PL/I variable name can be used for a host variable and is subject to the following restriction: Do not use host variable names, external entry names, or access plan names that begin with 'SQL', 'DSN', or 'IBM'. These names are reserved for the database manager or PL/I. The length of a host variable name must not exceed 100 characters.

Statement labels

With the exception of the END DECLARE SECTION statement, and the INCLUDE text-file-name statement, executable SQL statements, like PL/I statements, can have a label prefix.

WHENEVER statement

The target for the GOTO clause in an SQL WHENEVER statement must be a label in the PL/I source code and must be within the scope of any SQL statements affected by the WHENEVER statement.

Using host variables

All host variables used in SQL statements must be explicitly declared, and all host variables within an SQL statement must be preceded by a colon (:).

Subscripts must not be used in host variable references.

Using arrays as host variables

You can use an array as a host variable only when it is an array of indicator variables for a host structure. That array must be one-dimensional, have the CONNECTED attribute, and have constant bounds.

All other use of arrays as host variables is invalid.

Declaring host variables

Host variable declarations can be made at the same place as regular PL/I variable declarations.

Only a subset of valid PL/I declarations are recognized as valid host variable declarations. The preprocessor does not use the data attribute defaults specified in the PL/I DEFAULT statement. If the declaration for a variable is not recognized, any statement that references the variable might result in the message :

'The host variable token ID is not valid'

Only the names and data attributes of the variables are used by the preprocessor; the alignment, scope, and storage attributes are ignored.

Declaring scalar host variables

You must declare a scalar host variable with one of the following data attributes:

CHARACTER, GRAPHIC, or WIDECHAR
Host variables that are declared with the CHARACTER, GRAPHIC, or WIDECHAR attribute are called string host variables. The following restrictions apply to them:
FIXED BIN, FIXED DEC, or FLOAT
Host variables that are declared with the FIXED BIN, FIXED DEC, or FLOAT attribute are called numeric host variables. The following restrictions apply to them:
SQL TYPE
Host variables that are declared with the SQL TYPE attribute are called SQL TYPE host variables. The attribute specification must conform to one of the following syntax diagrams:
BINARY
Read syntax diagramSkip visual syntax diagram>>-SQL TYPE IS-BINARY-(--length--)-----------------------------><
 
VARBINARY
Read syntax diagramSkip visual syntax diagram>>-SQL TYPE IS-VARBINARY-(--length--)--------------------------><
 
Result set locator
Read syntax diagramSkip visual syntax diagram>>-SQL TYPE IS-RESULT_SET_LOCATOR------------------------------><
 
ROWID
Read syntax diagramSkip visual syntax diagram>>-SQL TYPE IS-ROWID-------------------------------------------><
 
Table locator
Read syntax diagramSkip visual syntax diagram>>-SQL TYPE IS-TABLE LIKE--table-name--AS LOCATOR--------------><
 
LOB file reference
Read syntax diagramSkip visual syntax diagram>>-SQL TYPE IS-+-BLOB_FILE---+---------------------------------><
               +-CLOB_FILE---+
               '-DBCLOB_FILE-'
 
LOB locator
Read syntax diagramSkip visual syntax diagram>>-SQL TYPE IS--+-BLOB_LOCATOR---+-----------------------------><
                +-CLOB_LOCATOR---+
                '-DBCLOB_LOCATOR-'
 
LOB variable
Read syntax diagramSkip visual syntax diagram>>-SQL TYPE IS-+-BLOB---+-(--length--+---+--)------------------><
               +-CLOB---+            +-K-+
               '-DBCLOB-'            +-M-+
                                     '-G-'
 
BLOB
You can also use BINARY LARGE OBJECT as an alternative for BLOB.
CLOB
You can also use either CHARACTER LARGE OBJECT or CHAR LARGE OBJECT as an alternative for CLOB.
XML LOB variable
Read syntax diagramSkip visual syntax diagram>>-SQL TYPE IS XML AS-+-BLOB---+-(--length--+---+--)-----------><
                      +-CLOB---+            +-K-+
                      '-DBCLOB-'            +-M-+
                                            '-G-'
 
BLOB
You can also use BINARY LARGE OBJECT as an alternative for BLOB.
CLOB
You can also use either CHARACTER LARGE OBJECT or CHAR LARGE OBJECT as an alternative for CLOB.
XML file reference
Read syntax diagramSkip visual syntax diagram>>-SQL TYPE IS XML AS---+-BLOB_FILE---+------------------------><
                        +-CLOB_FILE---+
                        '-DBCLOB_FILE-'
 

The following constant declarations are generated by the SQL preprocessor. You can use them to set the file option variable when you use the file reference host variables:

DCL SQL_FILE_READ      FIXED BIN(31) VALUE(2);
DCL SQL_FILE_CREATE    FIXED BIN(31) VALUE(8);
DCL SQL_FILE_OVERWRITE FIXED BIN(31) VALUE(16);
DCL SQL_FILE_APPEND    FIXED BIN(31) VALUE(32);
Rational Developer for System z
PL/I for Windows, Version 8.0, Programming Guide