FETCH statement Format 3 (Fetch data)
Function
Fetches multiple rows of the search results into the embedded variable specified in the INTO clause.
Privileges
Format 3: Reading more line of retrieval results all at once into embedded variables specified in the INTO clause
FETCH {cursor-name | extended-cursor-name} INTO : embedded-variable-array[:indicator-variable-array]
[,:embedded-variable-array[:indicator-variable-array]]...
Operands
- {cursor-name | extended-cursor-name}
- cursor-name
- Specifies the name of the cursor being used to fetch the retrieval results.
- extended-cursor-name
- Specifies the extended cursor name of the cursor into which retrieval results are fetched.
- For extended cursor names, see 2.27 Extended cursor name.
- : embedded-variable-array[:indicator-variable-array]
[,:embedded-variable-array[:indicator-variable-array]]...
- embedded-variable-array
- Specifies the array variables (embedded variables declared in array format) into which the values of columns that do not contain the null value are to be fetched. To receive values from columns that contain the null value, both embedded variables and indicator variables must be specified.
- indicator-variable-array
- Specifies the indicator variables (indicator variables declared in array format) to which the values indicating whether or not the values of the columns to be read into the embedded variables contain the null value are returned.
Common rules
- Unless allocated by the ALLOCATE CURSOR statement Format 2 (allocate a result set cursor), the cursor specified in the FETCH statement should be opened using the OPEN statement.
- An embedded variables array and its paired indicator variables array must have the same number of elements. If they do not, the number of rows fetched will equal the number of elements in the smaller of the specified arrays.
- The cumulative number of rows actually fetched is set in the SQL Communications Area's SQLERRD2 in the case of the C language and in SQLERRD(3) in the case of COBOL.
- A FETCH statement that uses arrays cannot be used in a procedure.
- A FETCH statement that uses arrays cannot handle LOB data.
- Block transfer cannot be specified in a FETCH statement that uses arrays.
- If there are no more rows to be fetched, the system returns the following return codes; in such a case, the data in the preceding rows is returned:
- Return code 100 to SQLCODE in the SQL Communications Area
- Return code 100 to the SQLCODE variable
- Return code '02000' to the SQLSTATE variable
If a row that was present at the time the list was created during a retrieval through a list is deleted, or if an attribute value is deleted or updated, a return code indicating that fact is not set. In this case, HiRDB continues to perform the retrieval, ignoring the event.
- If an event requiring a warning occurs in any of the fetched rows, the warning information is set in SQLWARN of the SQL Communications Area.
- If an error occurs in any of the fetched rows, the data in the rows up to that row is returned.
- A FETCH statement that uses an array cannot be used for searching unsubscripted repetition columns.
Notes
- If the value in a column of the retrieval results is the null value, the value in the element of the corresponding embedded variable array cannot be guaranteed.
- Array-type and non-array-type variables cannot be mixed.
- SQL descriptor area cannot be specified.
- The cursor is positioned at the last fetched row. If an updating operation using the cursor is then executed, the last fetched row is updated.
Example
Use a cursor (CR3) to read retrieval results from an inventory table into an array variable in C language:
EXEC SQL BEGIN DECLARE SECTION;
long XPCODE[50];
short IPCODE[50];
char XPNAME[50][17];
short IPNAME[50];
EXEC SQL END DECLARE SECTION;
EXEC SQL FETCH CR3
INTO :XPCODE :IPCODE,
:XPNAME :INAME;