The base SQLTYPE and SQLLEN of host variables are determined according to the following table. If a host variable appears with an indicator variable, the SQLTYPE is the base SQLTYPE plus one.
| PL/I Data Type | SQLTYPE of Host Variable | SQLLEN of Host Variable | SQL Data Type |
|---|---|---|---|
| BIN FIXED(n), n < 16 | 500 | 2 | SMALLINT |
| BIN FIXED(n), n ranges from 16 to 31 | 496 | 4 | INTEGER |
| DEC FIXED(p,s) | 484 | p (byte 1) s (byte 2) | DECIMAL(p,s) |
| BIN FLOAT(p), 22 <= p <= 53 | 480 | 8 | FLOAT |
| DEC FLOAT(m), 7 <= m <= 16 | 480 | 8 | FLOAT |
| CHAR(n), 1 <= n <= 254 | 452 | n | CHAR(n) |
| CHAR(n) VARYING, 1 <= n <= 4000 | 448 | n | VARCHAR(n) |
| CHAR(n) VARYING, n > 4000 | 456 | n | LONG VARCHAR |
| 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 |
Since SQL does not have single or extended precision floating-point data type, if a single or extended precision floating-point host variable is used to insert data, it is converted to a double precision floating-point temporary and the value in the temporary is inserted into the database. If the single or extended precision floating-point host variable is used to retrieve data, a double precision floating-point temporary is used to retrieve data from the database and the result in the temporary variable is assigned to the host variable.
The following table can be used to determine the PL/I data type that is equivalent to a given SQL data type.
| SQL Data Type | PL/I Equivalent | Notes |
|---|---|---|
| SMALLINT | BIN FIXED(15) | |
| INTEGER | BIN FIXED(31) | |
| DECIMAL(p,s) | DEC FIXED(p) or DEC FIXED(p,s) | p = precision and s = scale; 1 <= p <= 31 and 0 <= s <= p |
| FLOAT | BIN FLOAT(p) or DEC FLOAT(m) | 22 <= p <= 53 7 <= m <= 16 |
| CHAR(n) | CHAR(n) | 1 <= n <= 254 |
| VARCHAR(n) | CHAR(n) VAR | 1 <= n <= 4000 |
| LONG VARCHAR | CHAR(n) VAR | n > 4000 |
| 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 |
PL/I host variables in SQL statements must be type compatible with the columns which use them:
Graphic data types are compatible with each other. A GRAPHIC or VARGRAPHIC column is compatible with a fixed-length or varying-length PL/I graphic character host variable.
When necessary, the Database Manager automatically converts a fixed-length character string to a varying-length string or a varying-length string to a fixed-length character string.
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:
An indicator variable is a 2-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;
The following example shows the declaration of a host structure and an indicator array followed by an SQL statements 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;
This topic describes the way the preprocessor handles statements and compile-time options with SQL statements.
You can use a host variable to represent the database name you want your application to connect to, for example:
exec sql connect to :dbase;
If a host variable is specified:
dcl dbase char (10); dbase = 'SAMPLE'; /* blanks are padded automatically */ exec sql connect to :dbase;
IBM1214I W xxx.x A dummy argument is created for argument
number 6 in entry reference SQLESTRD_APIThe preprocessor ignores all DECLARE TABLE statements.
The preprocessor ignores all DECLARE STATEMENT statements.
If you specify the compile-time option DFT(EBCDIC) and you use a varying string host variable as input to the database, you must initialize the host variable or you might get a protection exception during the execution of your program.
If you use an uninitialized varying string on mainframe DB2, your program would be in error and might also get a protection exception.