Previous Topic: Defining Host VariablesNext Topic: Using INCLUDE TABLE

Requirements and Options for Host Languages › Using SQL in a PL/I Application Program › Defining Host Variables › Using PL/I Declarations

Using PL/I Declarations

Equivalent Column Data Types

This table shows data types of PL/I host variables that are valid in an SQL declare section and equivalent to CA IDMS table column data types:

Equivalent PL/I data type

CA IDMS data type

CHAR (n)

CHAR(n)

CHAR (n) VAR

VARCHAR(n)

FIXED DECIMAL (p,s)

DECIMAL(p,s)

FLOAT BINARY (n)

where n <= 24

where n > 24

 

REAL

DOUBLE PRECISION

FLOAT DECIMAL (n)

where n <= 6

where n > 6

 

REAL

DOUBLE PRECISION

FIXED BINARY (15)

SMALLINT

FIXED BINARY (31)

INTEGER

CHAR (n)

BINARY(n)

GRAPHIC (n)

GRAPHIC(n)1

GRAPHIC (n) VAR

VARGRAPHIC(n)1

CHAR (10)

DATE

CHAR (8)

TIME

CHAR (26)

TIMESTAMP

SQLBIN (n)

BINARY(n)

CHAR(8)

TID1

Note: 1 This data type is a CA IDMS extension of the SQL standard. For more information about CA IDMS data types, see the CA IDMS SQL Reference Guide.

Data Types Not Supported

The following table shows CA IDMS data types for which there are no equivalent data types in PL/I that are valid in an SQL declare section. The table shows compatible PL/I data types that are valid in host variable declarations; however, accessing a column that has no equivalent data type may result in an error if a data value is not convertible between the two data types.

Compatible PL/I data type

CA IDMS data type

FIXED BINARY (31)

LONGINT or BIGINT

FIXED DECIMAL (p,s)

NUMERIC(p,s)

FIXED DECIMAL (p,s)

UNSIGNED NUMERIC(p,s)

FIXED DECIMAL (p,s)

UNSIGNED DECIMAL(p,s)

Host Variable Declaration Example

In this example, the SQL declare section defines host variables, including one indicator variable, using standard PL/I data declarations. The example is annotated to show the equivalent column data type for each variable and to identify an indicator variable:

WORKING-STORAGE SECTION.
 .
 .
 .
EXEC SQL BEGIN DECLARE SECTION ;
DECLARE 1  EMP_ID           FIXED BINARY (31) ;       ◄─ INTEGER
DECLARE 1  EMP_LNAME        CHAR (20) ;               ◄─ CHARACTER
DECLARE 1  SALARY_AMOUNT    FIXED DECIMAL (6,2) ;     ◄─ DECIMAL
DECLARE 1  PROMO_DATE       CHAR (10) ;               ◄─ DATE
DECLARE 1  PROMO_DATE_I     FIXED BINARY (31) ;       ◄─ Indicator variable
EXEC SQL END DECLARE SECTION ;

Declaring an Indicator Variable

An indicator variable must be either FIXED BINARY (15) or FIXED BINARY (31) data type. In the example above, PROMO_DATE_I is an indicator variable for PROMO_DATE.

SQLIND Data Type

You can declare an indicator variable with the data type SQLIND:

DECLARE 1  PROMO_DATE       CHAR (10) ;               ◄─ DATE
DECLARE 1  PROMO_DATE_I     SQLIND ;                  ◄─ Indicator variable

The precompiler will substitute a FIXED BINARY (31) in the output source.

Note: The SQLIND data type is primarily for use within bulk structure definitions. In other cases its use is optional.

Allowable Host Variable Definitions

A host variable definition must contain a data type declaration and may contain an occurrence count. No other declarations are supported.