The FETCH statement positions a cursor on a row of the result table. It can return zero, one, or multiple rows, and it assigns the values of the rows returned to host variables.
This statement can only be embedded in an application program. It is an executable statement that cannot be dynamically prepared. Multiple row fetch is not allowed in a REXX procedure.
See "DECLARE CURSOR" for an explanation of the authorization required to use a cursor.
>>-FETCH----+-----------------------------+--------------------->
+-NEXT------------------------+
+-PRIOR-----------------------+
+-FIRST-----------------------+
+-LAST------------------------+
| (1) |
+-BEFORE----------------------+
| (2) |
+-AFTER-----------------------+
+-CURRENT---------------------+
'-RELATIVE--+-host-variable-+-'
'-integer-------'
.-FROM-.
>----+------+--cursor-name----+--------------------+-----------><
+-single-fetch-------+
'-multiple-row-fetch-'
single-fetch
.-,----------------.
V |
|---+-INTO-----host-variable---+-------------+------------------|
| (3) |
'-INTO DESCRIPTOR-------descriptor-name--'
multiple-row-fetch
|---FOR----+-host-variable-+--ROWS------------------------------>
'-integer-------'
>-----+-INTO--host-structure-array---------------------------------+>
'-USING DESCRIPTOR--descriptor-name--INTO--row-storage-area--'
>---------------------------------------------------------------|
row-storage-area
|---:--host-identifier-1--+--------------------------------------+->
| .-INDICATOR-. |
'-+-----------+--:--host-identifier-2--'
>---------------------------------------------------------------|
Notes:
The following keywords specify a new position for the cursor: NEXT, PRIOR, FIRST, LAST, BEFORE, AFTER, CURRENT, and RELATIVE. Of those keywords, only NEXT may be used for cursors that have not been declared SCROLL.
Table 25. Synonymous Scroll Specifications
| Specification | Alternative |
|---|---|
| RELATIVE +1 | NEXT |
| RELATIVE -1 | PRIOR |
| RELATIVE 0 | CURRENT |
If a single- or multiple-row fetch clause is not specified, no data is returned to the user. However, the cursor is positioned and a record lock may be acquired. For more information on locking, see "Isolation Level".
Before the FETCH statement is processed, the user must set the following fields in the SQLDA. (The rules for REXX are different. For more information see the SQL Programming with Host Languages book.)
The SQLDA must have enough storage to contain all SQLVAR occurrences. Therefore, the value in SQLDABC must be greater than or equal to 16 + SQLN*(80), where 80 is the length of an SQLVAR occurrence.
SQLD must be set to a value greater than or equal to zero and less than or equal to SQLN. For more information, see Appendix C, SQL Descriptor Area (SQLDA).
For example, "FETCH PRIOR FROM C1 FOR 3 ROWS" causes the previous row, the current row, and the next row to be returned, in that order. The cursor is positioned on the next row. "FETCH RELATIVE -1 FROM C1 FOR 3 ROWS" returns the same result. "FETCH FIRST FROM C1 FOR :x ROWS" returns the first x rows, and leaves the cursor positioned on row number x.
When a multiple-row-fetch is successfully executed, three variables are set in the SQLCA:
The first structure in the array corresponds to the first row, the second structure in the array corresponds to the second row, and so on. In addition, the first value in the row corresponds to the first item in the structure, the second value in the row corresponds to the second item in the structure, and so on. The number of rows to be fetched must be less than or equal to the dimension of the host structure array.
Before the FETCH statement is processed, the user must set the following fields in the SQLDA:
The values of the other fields of the SQLDA (such as SQLNAME) may not be defined after the FETCH statement is executed and should not be used.
The SQLDA must have enough storage to contain all SQLVAR occurrences. Therefore, the value in SQLDABC must be greater than or equal to 16 + SQLN*(80), where 80 is the length of an SQLVAR occurrence. If LOBs or distinct types are specified, there must be two SQLVAR entries for each parameter marker and SQLN must be set to two times the number of parameter markers.
SQLD must be set to a value greater than or equal to zero and less than or equal to SQLN. For more information, see Appendix C, SQL Descriptor Area (SQLDA).
On completion of the FETCH, the SQLDATA pointer in the first SQLVAR entry addresses the returned value for the first column in the allocated storage in the first row, the SQLDATA pointer in the second SQLVAR entry addresses the returned value for the second column in the allocated storage in the first row, and so on. The SQLIND pointer in the first nullable SQLVAR entry addresses the first indicator value, the SQLIND pointer in the second nullable SQLVAR entry addresses the second indicator value, and so on. The SQLDA must be allocated on a 16-byte boundary.
host-identifier-2 identifies the optional indicator area. It must be specified if the SQLTYPE of any SQLVAR occurrence is nullable. The indicators are returned as small integers. host-identifier-2 must be large enough to contain an indicator for each nullable value for each row to be returned.
The nth host variable identified by the INTO clause or described in the SQLDA corresponds to the nth column of the result table of the cursor. The data type of each host variable must be compatible with its corresponding column.
Each assignment to a variable is made according to the rules described in Language Elements. If the number of variables is less than the number of values in the row, the SQLWARN3 field of the SQLCA is set to 'W'. Note that there is no warning if there are more variables than the number of result columns. If the value is null, an indicator variable must be provided. If an assignment error occurs, the value is not assigned to the variable, and no more values are assigned to variables. Any values that have already been assigned to variables remain assigned.
If an error occurs as the result of an arithmetic expression in the SELECT list of an outer SELECT statement (division by zero, or overflow) or a character conversion error occurs, the result is the null value. As in any other case of a null value, an indicator variable must be provided. The value of the host variable is undefined. In this case, however, the indicator variable is set to -2. Processing of the statement continues as if the error had not occurred. (However, this error causes a positive SQLCODE.) If you do not provide an indicator variable, a negative value is returned in the SQLCODE field of the SQLCA. It is possible that some values have already been assigned to host variables and will remain assigned when the error occurs.
An open cursor has three possible positions:
If a cursor is positioned on a row, that row is called the current row of the cursor. A cursor referenced in an UPDATE or DELETE statement must be positioned on a row. A cursor can only be positioned on a row as a result of a FETCH statement.
It is possible for an error to occur that makes the state of the cursor unpredictable.
If the specified host variable is character and is not large enough to contain the result, 'W' is assigned to SQLWARN1 in the SQLCA. The actual length of the result is returned in the indicator variable associated with the host-variable, if an indicator variable is provided.
If the specified host variable is a C NUL-terminated host variable and is not large enough to contain the result and the NUL-terminator:
Two tables, FORUM and ARCHIVE, each have the following columns:
| Name: | FORUM | RECEIVED | SOURCE | TOPIC | ENTRY_TEXT |
|---|---|---|---|---|---|
| Type: |
char(8) not null |
timestamp not null |
char(8) not null |
char(64) not null |
varchar(4000) not null |
| Description: | Forum name | Date and time entry received | User ID of person adding entry | Topic within the forum | The text added in this entry table |
The FORUM table contains a number of named forums. Each forum contains one or more topics and each topic contains one or more entries. When a topic is no longer current, its entries are either deleted or moved to the ARCHIVE table.
The following PL/I program is used to perform maintenance on the forum table. A user can invoke the program with one of three commands. Each command is accompanied by a string of text that can be found within the TOPIC column of the entries for a given topic (this need not be the entire TOPIC value). The three commands are:
CLEANUP: PROC OPTIONS(MAIN);
DCL NOT_END BIT(1);
EXEC SQL BEGIN DECLARE SECTION;
DCL ACTION BINARY FIXED(15); /* 1=chg-topic 2=archive 3=delete */
DCL SRCH_FORUM CHAR(8);
DCL SRCH_TOPIC CHAR(66) VARYING;
DCL NEW_TOPIC CHAR(64) VARYING;
DCL FORUM CHAR(8);
DCL TSTMP CHAR(26);
DCL PERSON CHAR(8);
DCL TOPIC CHAR(64) VARYING;
DCL TXT CHAR(2000) VARYING;
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
EXEC SQL WHENEVER NOT FOUND CONTINUE;
EXEC SQL WHENEVER SQLWARNING CONTINUE;
EXEC SQL WHENEVER SQLERROR GOTO ERRCHK;
EXEC SQL CONNECT TO TOROLAB3;
GET LIST (ACTION, SRCH_FORUM, SRCH_TOPIC, NEW_TOPIC);
SRCH_TOPIC = '%' || SRCH_TOPIC || '%';
EXEC SQL DECLARE CUR CURSOR FOR
SELECT * FROM FORUM
WHERE FORUM = :SRCH_FORUM AND TOPIC LIKE :SRCH_TOPIC
FOR UPDATE OF TOPIC;
EXEC SQL OPEN CUR;
NOT_END = '1'B;
DO WHILE (NOT_END);
EXEC SQL FETCH CUR INTO :FORUM, :TSTMP, :PERSON, :TOPIC, :TXT;
IF SQLSTATE = '02000' THEN
NOT_END = '0'B;
ELSE DO;
SELECT;
WHEN (ACTION = 1) /* change topic value */
EXEC SQL UPDATE FORUM
SET TOPIC = :NEW_TOPIC
WHERE CURRENT OF CUR;
WHEN (ACTION = 2) /* archive entry to another table */
DO;
EXEC SQL INSERT INTO ARCHIVE
VALUES (:FORUM, :TSTMP, :PERSON, :TOPIC, :TXT);
EXEC SQL DELETE FROM FORUM WHERE CURRENT OF CUR;
END;
WHEN (ACTION = 3) /* delete topic */
EXEC SQL DELETE FROM FORUM WHERE CURRENT OF CUR;
END; /* select */
END; /* else do */
END; /* do while */
FINISHED:
EXEC SQL CLOSE CUR;
EXEC SQL COMMIT WORK;
RETURN;
ERRCHK:
DISPLAY ('Unexpected Error -changes will be backed out');
PUT SKIP LIST (SQLCA);
EXEC SQL WHENEVER SQLERROR CONTINUE; /* continue if error on rollback */
EXEC SQL ROLLBACK WORK;
RETURN;
END; /* CLEANUP */