Multiple-row FETCH using a row storage area

Before using a multiple-row FETCH statement with the row storage area, the application must define a row storage area and an associated description area.

The row storage area is a host variable defined in the application. The row storage area contains the results of the multiple-row FETCH statement. A row storage area can be a character variable with enough bytes to hold all of the rows that are requested on the multiple-row FETCH statement.

An SQLDA that contains the SQLTYPE and SQLLEN for each returned column is defined by the associated descriptor used on the row storage area form of the multiple-row FETCH. The information provided in the descriptor determines the data mapping from the database to the row storage area. To maximize performance, the attribute information in the descriptor should match the attributes of the columns retrieved.

Consider the following PL/I example:

Note: By using the code examples, you agree to the terms of the Code license and disclaimer information.

       *....+....1....+....2....+....3....+....4....+....5....+....6....+....7...*
     EXEC SQL INCLUDE SQLCA;
     EXEC SQL INCLUDE SQLDA;
 
...
 
    DCL DEPTPTR PTR;
    DCL 1 DEPT(20) BASED(DEPTPTR),
        3 EMPNO CHAR(6),
        3 LASTNAME CHAR(15) VARYING,
        3 WORKDEPT CHAR(3),
        3 JOB CHAR(8);
    DCL I BIN(31) FIXED;
    DEC J BIN(31) FIXED;
    DCL ROWAREA CHAR(2000);
 
...
 
    ALLOCATE SQLDA SET(SQLDAPTR);
    EXEC SQL
     DECLARE D11 CURSOR FOR
     SELECT EMPNO, LASTNAME, WORKDEPT, JOB
     FROM CORPDATA.EMPLOYEE
     WHERE WORKDEPT = 'D11';

...
 
    EXEC SQL
      OPEN D11;
    /* SET UP THE DESCRIPTOR FOR THE MULTIPLE-ROW FETCH */
    /* 4 COLUMNS ARE BEING FETCHED                 */
    SQLD = 4;
    SQLN = 4;
    SQLDABC = 366;
    SQLTYPE(1) = 452; /* FIXED LENGTH CHARACTER -  */
                      /* NOT NULLABLE              */
    SQLLEN(1) = 6;
    SQLTYPE(2) = 456; /*VARYING LENGTH CHARACTER   */
                      /* NOT NULLABLE              */
    SQLLEN(2) = 15;
    SQLTYPE(3) = 452; /* FIXED LENGTH CHARACTER -  */
    SQLLEN(3) = 3;
    SQLTYPE(4) = 452; /* FIXED LENGTH CHARACTER -  */
                      /* NOT NULLABLE              */
    SQLLEN(4) = 8;
    /*ISSUE THE MULTIPLE-ROW FETCH STATEMENT TO RETRIEVE*/
    /*THE DATA INTO THE DEPT ROW STORAGE AREA      */
    /*USE A HOST VARIABLE TO CONTAIN THE COUNT OF  */
    /*ROWS TO BE RETURNED ON THE MULTIPLE-ROW FETCH     */
 
    J = 20;        /*REQUESTS 20 ROWS ON THE FETCH */
   ...
    EXEC SQL
      WHENEVER NOT FOUND
      GOTO FINISHED;
    EXEC SQL
      WHENEVER SQLERROR
      GOTO FINISHED;
    EXEC SQL
      FETCH D11 FOR :J ROWS
      USING DESCRIPTOR :SQLDA INTO :ROWAREA;
    /* ADDRESS THE ROWS RETURNED                   */
    DEPTPTR = ADDR(ROWAREA);
    /*PROCESS EACH ROW RETURNED IN THE ROW STORAGE */
    /*AREA BASED ON THE COUNT OF RECORDS RETURNED  */
    /*IN SQLERRD3.                                 */
    DO I = 1 TO SQLERRD(3);
     IF EMPNO(I) = '000170'  THEN
       DO;
      :
       END;
    END;
    IF SQLERRD(5) = 100 THEN
      DO;
        /* PROCESS END OF FILE  */
      END;
    FINISHED:

In this example, a cursor has been defined for the CORPDATA.EMPLOYEE table to select all rows where the WORKDEPT column equal 'D11'. The sample EMPLOYEE table in the Sample Tables shows the result table contains multiple rows. The DECLARE CURSOR and OPEN statements do not have special syntax when they are used with a multiple-row FETCH statement. Another FETCH statement that returns a single row against the same cursor can be coded elsewhere in the program. The multiple-row FETCH statement is used to retrieve all rows in the result table. Following the FETCH, the cursor position remains on the final row in the block.

The row area, ROWAREA, is defined as a character array. The data from the result table is placed in the host variable. In this example, a pointer variable is assigned to the address of ROWAREA. Each item in the rows that are returned is examined and used with the based structure DEPT.

The attributes (type and length) of the items in the descriptor match the columns that are retrieved. In this case, no indicator area is provided.

After the FETCH statement is completed, the ROWAREA contains all of the rows that equal 'D11', in this case 11 rows. The SQLCA that is returned to the application contains the following:

SQLCODE contains 0
SQLSTATE contains '00000'
SQLERRD3 contains 11, the number of rows returned
SQLERRD4 contains 34, for the length of the row fetched
SQLERRD5 contains +100, indicating the last row in the result table was fetched

In this example, the application has taken advantage of the fact that SQLERRD5 contains an indication of the end of the file being reached. As a result, the application does not need to call SQL again to attempt to retrieve more rows. If the cursor has immediate sensitivity to inserts, you should call SQL in case any records were added. Cursors have immediate sensitivity when the commitment control level is something other than *RR.