FETCH statement Format 1 (Fetch data)

Function

The FETCH statement advances to the next row the cursor that indicates the row to be fetched and reads the column values in that row into the embedded variables specified in the INTO clause.

Privileges

None.

Format 1: Reading one line of retrieval results into variables

FETCH {cursor-name | extended-cursor-name} INTO
  {:embedded-variable[:indicator-variable]
   |[statement-label.]SQL-variable-name
   |[[authorization-identifier.]routine-identifier.]SQL-parameter-name
   |[statement-label.]SQL-variable-name..attribute-name
             [..attribute-name]...
   |[[authorization-identifier.]routine-identifier.] SQL-parameter-name
             ..attribute-name[..attribute-name]...}
      [, {:embedded-variable[:indicator-variable]
          |[statement-label.]SQL-variable-name
          |[[authorization-identifier.]routine-identifier.]
             SQL-parameter-name
          |[statement-label.]SQL-variable-name..attribute-name
               [..attribute-name]...
          |[[authorization-identifier.]routine-identifier.]
             SQL-parameter-name
               ..attribute-name[..attribute-name]...} ]...

Operands

cursor-name
Specifies the name of the cursor being used to fetch 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
Specifies an embedded variable into which a non-null column value is to be read.
To receive a column value that is the null value, an embedded variable and an indicator variable must both be specified.
indicator-variable
Specifies the indicator variable that is to receive the value returned by the system indicating whether or not the column value read into the paired embedded variable is the null value.
[statement-label.]SQL-variable-name
[[authorization-identifier.]routine-identifier.] SQL-parameter-name
Specifies an SQL variable or an SQL parameter that is to receive the value of a column in the SQL procedure.
[statement-label.] SQL-variable-name..attribute-name [..attribute-name]
[[authorization-identifier.] routine-identifier.]SQL-parameter-name..attribute-name[..attribute-name]
Specify these operands in order to receive the value of an attribute in a column within the SQL procedure.

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 number of retrieval result columns (the number of selection expressions specified in the SELECT statement specified in either a cursor declaration or an ALLOCATE CURSOR statement) must be equal to the number of embedded variables, SQL variables, or SQL parameters specified in the INTO clause of the FETCH statement. If they are not, there will be fewer column values to be read into the embedded variables. In such a case, a warning flag (W) is set in SQLWARN3 in the SQL Communications Area.
    However, in the case of remote database access, no warning flag (W) is set in the event of an SQL error.
  3. The data type of each embedded variable specified in the INTO clause must be either the same as the data type of the corresponding retrieval item or a data type that can be converted into that data type.
  4. If data fetched into the embedded variable of a fixed-length character string (including a national character string or a mixed character string) is shorter than the length of the retrieval item, the data is left-justified in the embedded variable and trailing blanks are added.
  5. 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.

Notes

  1. If a retrieval result column contains the null value, the value of the corresponding embedded variable is unpredictable.
  2. 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.

Examples

Use a cursor (CR1) to read retrieval results from a stock table (STOCK) into the embedded variables and indicator variables associated with the product code (PCODE), product name (PNAME), color (COLOR), unit price (PRICE), and quantity-in-stock (SQTY) columns:

  1. With embedded variables specified:

    FETCH CR1
     INTO :XPCODE,:XPNAME,:XCOLOR,
          :XPRICE,:XSQTY

  2. With embedded variables and indicator variables specified:

    FETCH CR1
     INTO :XPCODE:IPCODE,
          :XPNAME:IPNAME,
          :XCOLOR:ICOLOR,
          :XPRICE:IPRICE,
          :XSQTY:ISQTY