You may need to declare an SQLDA structure to execute dynamically defined SQL statements. You can have the system include the structure automatically by specifying:
EXEC SQL INCLUDE SQLDA;
in your source code, or by directly coding the structure as shown in Figure 128.
Figure 128. SQLDA Structure (in PL/I)
DCL 1 SQLDA BASED(SQLDAPTR),
2 SQLDAID CHAR(8),
2 SQLDABC BIN FIXED(31),
2 SQLN BIN FIXED(15),
2 SQLD BIN FIXED(15),
2 SQLVAR(SQLSIZE REFER(SQLN)),
3 SQLTYPE BIN FIXED(15),
3 SQLLEN BIN FIXED(15),
3 SQLDATA PTR,
3 SQLIND PTR,
3 SQLNAME CHAR(30) VAR;
DCL SQLSIZE BIN FIXED(15);
DCL SQLDAPTR PTR;
|
The SQLDA must not be declared within the SQL declare section. See the DB2 Server for VSE & VM SQL Reference manual for more information on the individual fields within the SQLDA.
In addition to the structure above, you should declare an additional
mapping for the same area. The SQLPRCSN and SQLSCALE fields of the
second mapping are used when decimal data is used. Figure 129 shows this mapping.
Figure 129. SQLDAX Structure (in PL/I)
DCL 1 SQLDAX BASED(SQLDAPTR),
2 SQLDAIDX CHAR(8),
2 SQLDABCX BIN FIXED(31),
2 SQLNX BIN FIXED(15),
2 SQLDX BIN FIXED(15),
2 SQLVARX(SQLSIZE REFER(SQLNX)),
3 SQLTYPEX BIN FIXED(15),
3 SQLPRCSN format 1 or format 2,
3 SQLSCALE format 1 or format 2,
3 SQLDATAX PTR,
3 SQLINDX PTR,
3 SQLNAMEX CHAR(30) VAR;
The SQLPRCSN and SQLSCALE fields can be declared in one of two
formats.
|
Format 1: 3 SQLPRCSN BIT(8),
3 SQLSCALE BIT(8),
The fields must be set by bit 8 strings. For example, for a precision of 5 and scale of 2, the following assignments are required: SQLDAPTR->SQLPRCSN = '00000101'B SQLDAPTR->SQLSCALE = '00000010'B |
Format 2: 3 SQLPRCSN CHAR(1),
3 SQLSCALE CHAR(1),
This format requires the declaration of additional variables.
These are a CHAR(2) variable and a BASED FIXED BIN(15) variable for both
precision and scale. For example:
DCL PRCSNC CHAR(2); DCL PRCSNN FIXED BIN(15) BASED (ADDR(PRCSNC)); DCL SCALEC CHAR(2); DCL SCALEN FIXED BIN(15) BASED (ADDR(SCALEC));The SQLDAX fields for a precision of 5 and scale of 2 would be: PRCSNN = 5; SCALEN = 2; SQLDAPTR->SQLPRCSN = SUBSTR(PRCSNC,2,1); SQLDAPTR->SQLSCALE = SUBSTR(SCALEC,2,1);Format 2, although more complex, allows PL/I manipulation of the precision and scale fields. For example, the value of the SQLPRCSN field can be determined simply by reversing the substring operation above. That is: SUBSTR(PRCSNC,2,1) = SQLDAPTR->SQLPRCSN;Such an operation cannot be done using format 1. |
Because the PL/I SQLDA is declared as a based structure, your program can dynamically allocate an SQLDA of adequate size for use with each EXECUTE statement. For example, the code fragment below allocates an SQLDA adequate for five fields and uses it in an EXECUTE of statement S3:
SQLSIZE=5; ALLOCATE SQLDA SET(SQLDAPTR); /* Add code to set values and pointers in the SQLDA */ EXEC SQL EXECUTE S3 USING DESCRIPTOR SQLDA;
The statement SQLSIZE=5 determines the size of the SQLDA to be allocated by means of the PL/I REFER feature. The ALLOCATE statement allocates an SQLDA of the size desired, and sets SQLDAPTR to point to it. (Before an EXECUTE statement is issued using this SQLDA, your program must fill in its contents.)
You can use a similar technique to allocate an SQLDA for use with a DESCRIBE statement. The following program fragment illustrates the use of SQLDA with DESCRIBE for three fields and a "prepared" statement S1:
EXEC SQL DECLARE C1 CURSOR FOR S1;
SQLSIZE = 3;
ALLOCATE SQLDA SET(SQLDAPTR);
EXEC SQL DESCRIBE S1 INTO SQLDA;
IF SQLD > SQLN THEN
- get a bigger one;
Set SQLDATA and SQLIND;
EXEC SQL OPEN C1;
EXEC SQL FETCH C1 USING DESCRIPTOR SQLDA;