FETCH statement Format 2 (Fetch data)

Function

Fetches one or more rows of the search results into the area specified in the SQL descriptor area.

Privileges

None.

Format 2: Reading one or more lines of retrieval results into specified receive areas in the SQL descriptor area

FETCH {cursor-name | extended-cursor-name}
    USING DESCRIPTOR [:]SQL-descriptor-area-name
         [CHARACTER_SET[:]character-set-descriptor-area-name]

    [BY : embedded-variable [ROWS]]

Operands

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.

Specifies the name of the SQL descriptor area to be used in order for the UAP to receive the retrieval results.

Specifies the name of the character set descriptor area to be used to specify the character set name of the retrieval results.

By using an array that is set in the SQL descriptor area, specifies the embedded variable in which the size of the FETCH area is set in terms of the number of elements. A SMALLINT data type embedded variable with a value in the range 1 to 30000 should be specified. An error may result if 0 or a negative value is set. The behavior of the FETCH statement cannot be guaranteed if a client library older than Version 05-03 is used.

Common rules

  1. 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.
  2. The information needed by the UAP for the execution of the FETCH statement should be assigned to the SQL descriptor area specified in SQL-descriptor-area-name. For details about the SQL descriptor area, see the HiRDB Version 9 UAP Development Guide.
  3. If there are no rows to be fetched, the system returns the following return codes:
    • Return code 100 to SQLCODE in the SQL Communications Area
    • Return code 100 to the SQLCODE variable
    • Return code '02000' to the SQLSTATE variable
    However if a row that existed when the list was created during a search using the list or the value of an attribute is deleted or updated, codes 110, 110, and 'R2000' will be set, respectively.
  4. The data type of a receive area in the specified SQL descriptor area must be either the data type of the corresponding retrieval item or a data type that can be converted into that data type.
  5. If the retrieval result is character data and the character set of the retrieval result is different from the character set specified in the character set descriptor area, the retrieval results are automatically converted into the character set specified in the character set descriptor area.
  6. When BY : embedded-variable [ROWS] is specified, a value that corresponds with the data type in the SQLSYS area of the SQL descriptor area should be set.
    • Variable-length character string type (VARCHAR, NVARCHAR, or MVARCHAR):
      Set an area that stores the length of the character string and a value equivalent to one element, including any gap between elements that is necessitated by boundary alignment.
      For example, in the case of the following array variable of VARCHAR data type, the value set in SQLSYS will be sizeof(vchr[0]):

    struct {
       short  len;
       char   str[257];
    } vchr[128];

    • Other data types:
      Set the value 0.

Note

The cursor name, similar to an embedded variable name, is effective only within a compile-unit module. Multiple SQLs relative to the same cursor cannot be used in multiple files.

Example

Use a cursor (CR2) to read retrieval results from a stock table into receive areas in a specified SQL descriptor area:

FETCH CR2
 USING DESCRIPTOR SQLDA