Rational Developer for System z
Enterprise PL/I for z/OS, Version 4.2, Programming Guide

Coding SQL statements in PL/I applications

Defining the SQL communications area
Defining SQL descriptor areas
Embedding SQL statements
Using host variables
Using host structures
Using indicator variables
Host structure example

You can code SQL statements in your PL/I applications using the language defined in DB2 UDB for z/OS SQL Reference. 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 either an SQLCODE variable (if the STDSQL(86) preprocessor option is used) or an SQL communications area (SQLCA). As shown in Figure 5, part of an SQLCA consists of an SQLCODE variable and an SQLSTATE variable.

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

  exec sql include sqlca;

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

Figure 5. 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 sqlerrmc     char(70) var,      /* Error message tokens      */
      2 sqlerrp      char(8),           /* Diagnostic information    */
      2 sqlerrd(0:5) 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 EXEC SQL INCLUDE statement:

  exec sql include sqlda;

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

Figure 6. 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
    1 Sqlda2 based(Sqldaptr),
      2 sqldaid2     char(8),          /* Eye catcher = 'SQLDA   ' */
      2 sqldabc2     fixed binary(31), /* SQLDA size in bytes=16+44*SQLN*/
      2 sqln2        fixed binary(15), /* Number of SQLVAR elements*/
      2 sqld2        fixed binary(15), /* # of used SQLVAR elements*/
      2 sqlvar2(Sqlsize refer(sqln2)), /* Variable Description     */
        3 sqlbiglen,
          4 sqllongl fixed binary(31),
          4 sqlrsvdl fixed binary(31),
        3 sqldatal   pointer,
        3 sqltname   char(30) var;

  dcl Sqlsize    fixed binary(15);     /* number of sqlvars (sqln) */
  dcl Sqldaptr   pointer;
  dcl Sqltripled char(1) value('3');
  dcl Sqldoubled char(1) value('2');
  dcl Sqlsingled char(1) value(' ');

Embedding SQL statements

The first statement of your 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 DSN8710.DEPT
           set   Mgrno  = :Mgr_Num
           where Deptno = :Int_Dept;
Comments

In addition to SQL statements, 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 in the source code. Place it at the point 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) compiler option.

Names

Any valid PL/I variable name can be used for a host variable. The length of a host variable name must not exceed the value n specified in the LIMITS(NAME(n)) compiler option.

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 in the following two ways:

All such arrays 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);
Determining equivalent SQL and PL/I data types

The base SQLTYPE and SQLLEN of host variables are determined according to Table 8 and Table 9. If a host variable appears with an indicator variable, the SQLTYPE is the base SQLTYPE plus one.

To determine the PL/I data type that is equivalent to a given SQL data type, you can use Table 10 and Table 11.

Table 8. SQL data types generated from PL/I declarations
    PL/I Data Type SQLTYPE of Host Variable SQLLEN of Host Variable SQL Data Type
BIN FIXED(p), 7 < p <= 15 500 2 SMALLINT
BIN FIXED(p), 15 < p <= 31 496 4 INTEGER
BIN FIXED(p), 31 < p <= 63 492 8 BIGINT
DEC FIXED(p,s)   0<=p<=15
and 0<=s<=p
484
p (byte 1)
s (byte 2)
DECIMAL(p,s)
BIN FLOAT(p), 1 <= p <= 21 480 4 REAL or FLOAT(n) 1<=n<=21
BIN FLOAT(p), 22 <= p <= 53 480 8
DOUBLE PRECISION or
FLOAT(n) 22<=n<=53
Under FLOAT(NODFP):
    DEC FLOAT(p), 1 <= p <= 6 480 4 FLOAT (single precision)
    DEC FLOAT(p), 7 <= p <= 16 480 8 FLOAT (double precision)
Under FLOAT(DFP):
    DEC FLOAT(p), 1 <= p <= 7 996 4 DECFLOAT (single precision)
    DEC FLOAT(p), >7 <= p <= 16 996 8 DECFLOAT (double precision)
    DEC FLOAT(p), >16 <= p <= 34 996 16 DECFLOAT (extended decimal)
CHAR(n), 452 n CHAR(n)
CHAR(n) VARYING, 1 <= n <= 255 448 n VARCHAR(n)
CHAR(n) VARYING, n > 255 456 n VARCHAR(n)
GRAPHIC(n), 1 <= n <= 127 468 n GRAPHIC(n)
GRAPHIC(n) VARYING, 1 <= n <= 2000 464 n VARGRAPHIC(n)
GRAPHIC(n) VARYING, n > 2000 472 n LONG VARGRAPHIC
Table 9. SQL data types generated from SQL TYPE declarations
    PL/I Data Type SQLTYPE of Host Variable SQLLEN of Host Variable SQL Data Type
SQL TYPE IS BLOB(n) 1<n<2147483647 404 n BLOB(n)
SQL TYPE IS CLOB(n) 1<n<2147483647 408 n CLOB(n)
SQL TYPE IS DBCLOB(n) 1<n<1073741823 (2) 412 n DBCLOB(n) (2)
SQL TYPE IS ROWID 904 40 ROWID
SQL TYPE IS VARBINARY(n) 1<n<32704 908 n VARBINARY(n)
SQL TYPE IS BINARY(n) 1<n<255 912 n BINARY(n)
SQL TYPE IS BLOB_FILE 916 267 BLOB File Reference (1)
SQL TYPE IS CLOB_FILE 920 267 CLOB File Reference (1)
SQL TYPE IS DBCLOB_FILE 924 267 DBCLOB File Reference (1)
SQL TYPE IS BLOB_LOCATOR 960 4 BLOB Locator (1)
SQL TYPE IS CLOB_LOCATOR 964 4 CLOB Locator (1)
SQL TYPE IS DBCLOB_LOCATOR 968 4 DBCLOB Locator (1)
SQL TYPE IS RESULT_SET_LOCATOR 972 4 Result Set Locator
SQL TYPE IS TABLE LIKE table-name AS LOCATOR 976 4 Table Locator (1)
Note:
  1. Do not use this data type as a column type.
  2. n is the number of double-byte characters.
Table 10. SQL data types mapped to PL/I declarations
SQL Data Type PL/I Equivalent Notes
SMALLINT BIN FIXED(15)
INTEGER BIN FIXED(31)
BIGINT BIN FIXED(63)
DECIMAL(p,s)
DEC FIXED(p) or
DEC FIXED(p,s)
p = precision and s = scale;
1 <= p <= 31 and 0 <= s <= p
Under FLOAT(NODFP):
   REAL or FLOAT(n)
BIN FLOAT(p) or
DEC FLOAT(m)
1 <= p <= 21
and 1 <= m <= 6
   DOUBLE PRECISION,
   DOUBLE, or FLOAT(n)
BIN FLOAT(p) or
DEC FLOAT(m)
22 <= p <= 53
and 7 <= m <= 16
Under FLOAT(DFP):
   DECFLOAT DEC FLOAT(m)
Short Decimal Float
1 <= m <= 7
   DECFLOAT DEC FLOAT(m)
Long Decimal Float
7 <= m <= 16
   DECFLOAT DEC FLOAT(m)
Extended Decimal Float
16 <= m <= 34
CHAR(n) CHAR(n) 1 <= n <= 32767
VARCHAR(n) CHAR(n) VAR  
GRAPHIC(n) GRAPHIC(n) n is a positive integer from 1 to 127 that refers to the number of double-byte characters, not to the number of bytes
VARGRAPHIC(n) GRAPHIC(n) VAR n is a positive integer that refers to the number of double-byte characters, not to the number of bytes; 1 <= n <= 2000
LONG VARGRAPHIC GRAPHIC(n) VAR n > 2000
DATE CHAR(n) n must be at least 10
TIME CHAR(n) n must be at least 8
TIMESTAMP CHAR(n) n must be at least 26
Table 11. SQL data types mapped to SQL TYPE declarations
SQL Data Type PL/I Equivalent Notes
Result set locator SQL TYPE IS RESULT_SET_LOCATOR Use this data type only for receiving result sets. Do not use this data type as a column type.
Table locator SQL TYPE IS TABLE LIKE table-name AS LOCATOR Use this data type only in a user-defined function or stored procedure to receive rows of a transition table. Do not use this data type as a column type.
BLOB locator SQL TYPE IS BLOB_LOCATOR Use this data type only to manipulate data in BLOB columns. Do not use this data type as a column type.
CLOB locator SQL TYPE IS CLOB_LOCATOR Use this data type only to manipulate data in CLOB columns. Do not use this data type as a column type.
DBCLOB locator SQL TYPE IS DBCLOB_LOCATOR Use this data type only to manipulate data in DBCLOB columns. Do not use this data type as a column type.
BLOB file reference SQL TYPE IS BLOB_FILE Use this data type only as a reference to a BLOB file. Do not use this data type as a column type.
CLOB file reference SQL TYPE IS CLOB_FILE Use this data type only as a reference to a CLOB file. Do not use this data type as a column type.
DBCLOB file reference SQL TYPE IS DBCLOB_FILE Use this data type only as a reference to a DBCLOB file. Do not use this data type as a column type.
BLOB(n) SQL TYPE IS BLOB(n) 1<n<2147483647
CLOB(n) SQL TYPE IS CLOB(n) 1<n<2147483647
DBCLOB(n) SQL TYPE IS DBCLOB(n) n is the number of double-byte characters. 1<n<1073741823
ROWID SQL TYPE IS ROWID  
XML AS SQL TYPE IS XML AS ... Used to describe an XML version of a BLOB, CLOB, DBCLOB, BLOB_FILE, CLOB_FILE, or DBCLOB_FILE 
Determining compatibility of SQL and PL/I data types

PL/I host variables in SQL statements must be type compatible with the columns which use them:

Using host structures

A PL/I host structure name can be a structure name with members that are not structures or unions. For example:

  dcl 1 A,
        2 B,
          3 C1 char(...),
          3 C2 char(...);

In this example, B is the name of a host structure consisting of the scalars C1 and C2.

Host structures are limited to two levels. A host structure can be thought of as a named collection of host variables.

Each leaf element of a host structure must have one of the following valid host data attributes as discussed in Declaring host variables:

Using indicator variables

An indicator variable is a two-byte integer (BIN FIXED(15)). On retrieval, an indicator variable is used to show whether its associated host variable has been assigned a null value. On assignment to a column, a negative indicator variable is used to indicate that a null value should be assigned.

Indicator variables are declared in the same way as host variables and the declarations of the two can be mixed in any way that seems appropriate to the programmer.

The SQL Preprocessor does not require that indicator arrays have a lower bound of one.

An indicator variable must have the attribute REAL NATIVE SIGNED FIXED BIN(15).

Given the statement:

  exec sql fetch Cls_Cursor into :Cls_Cd,
                                 :Day :Day_Ind,
                                 :Bgn :Bgn_Ind,
                                 :End :End_Ind;

Variables can be declared as follows:

  exec sql begin declare section;
  dcl Cls_Cd    char(7);
  dcl Day       bin fixed(15);
  dcl Bgn       char(8);
  dcl End       char(8);
  dcl (Day_Ind, Bgn_Ind, End_Ind)   bin fixed(15);
  exec sql end declare section;

Host structure example

The following example shows the declaration of a host structure and an indicator array followed by an SQL statement that could be used to retrieve the data into the host structure:

  dcl 1 games,
        5 sunday,
          10 opponents char(30),
          10 gtime     char(10),
          10 tv        char(6),
          10 comments  char(120) var;
  dcl indicator(4) fixed bin (15);
 

  exec sql
       fetch cursor_a
       into :games.sunday:indicator;

Terms of use | Feedback

This information center is powered by Eclipse technology. (http://www.eclipse.org)