FETCH (Embedded SQL Form) | VantageCloud Lake - FETCH (Embedded SQL Form) - Teradata Vantage

Positions a cursor on the next row (default) or any specified row of a response set, and assigns the values in that row to host variables.

ANSI Compliance

ANSI/ISO SQL:2011-compliant.

Required Privileges

None.

Invocation

Executable.

Embedded SQL only.

Syntax

FETCH [
  NEXT |
  PRIOR |
  FIRST |
  LAST |
  ABSOLUTE n |
  RELATIVE n
] cursor_name
[
  INTO   [,...] |
  USING DESCRIPTOR [:] descriptor_area
]

Syntax Elements

into_spec

[:] host_variable_name [ [INDICATOR] :host_indicator_name ]

NEXT

Fetches the next row from the response set relative to the current cursor position.

NEXT is the default.

PRIOR

Fetches the prior row from the response set relative to the current cursor position.

FIRST

Fetches the first row of the response set.

LAST

Fetches the last row of the response set.

ABSOLUTE n

Fetches the nth row of the response set relative to:

• The first row of the set, if n is a positive number.
• The last row of the set, if n is a negative number.

n can be a host_variable_name or an integer_constant.

The data types for the host variable can be any 8-byte numeric data type with zero scale.

An integer_constant can be up to 31 digits.

RELATIVE n

Fetches the nth row of the response set:

• Forward by the value of n, if n is a positive number,
• Backward by the value of n, if n is a negative number,

relative to the current cursor position.

n can be a host_variable_name or an integer_constant.

The data types for the host variable can be any 8-byte numeric data type with zero scale.

An integer_constant can be up to 31 digits.

cursor_name

The name of an open selection cursor from which one or more rows are to be fetched.

host_variable_name

The variable to which the current row column value is to be assigned.

The colon character preceding the name is optional.

host_indicator_variable

The indicator variable.

The colon character preceding the name is required.

descriptor_area

An SQL Descriptor Area (SQLDA).

You can specify descriptor_area in a C program as a name or as a pointer reference (*sqldaname) when the SQLDA structure is declared as a pointer.

Usage Notes

• Scrollable Cursors

  When you open a scrollable cursor, the cursor is positioned before the first row of the response set. You can fetch using one of the FETCH orientation keywords.

  You can open scrollable cursors in a multisession connection to enhance performance for access. When an application does not access rows sequentially, you may achieve better performance by setting the response buffer size equal to the fetch row size. You can try different response buffer sizes to achieve the best performance.

• Define an SQLDA.
• You cannot run FETCH as a dynamic SQL statement.
• Multiple-statement requests are not allowed in scrollable cursor FETCH.
• Scrollable cursor FETCH is not allowed in PP2 COMMITTED mode.
• You must previously declare the cursor identified by cursor_name.
• Use the INTO clause with cursors that you declared with either static or dynamic SQL statements.

  The USING DESCRIPTOR clause is intended for use with selection cursors that you declared with dynamic SQL statements.

• The number of columns the request returns must match the number of host variable specifications or the number of elements in the SQLVAR array of the SQLDA. That is, the number of columns returned in the result set must equal the value of the SQLD field.
• The main host variable you specified by a host variable specification or in the SQLVAR array of the SQLDA, and the corresponding column in the returned data must be of the same data type group.

  The only valid exception is if the main host variable data type is approximate numeric, in which case the spool table column data type can be either approximate numeric or exact numeric.

• If you specify the USING DESCRIPTOR clause, verify that the SQLDATA and SQLIND pointer fields in SQLDA are set to point to the appropriate host variables.

  Because the COBOL language provides no support for setting pointer values, the database supplies a service routine that can be called to do this task.

  The IBM dialect VS COBOL II provides a variant of the SET statement to set pointer values. Programmers coding in this COBOL dialect must consider this feature where appropriate.

• The cursor identified by cursor_name must be open.
• The cursor identified by cursor_name is positioned on the next row and values are assigned to host variables according to the following rules:

  Cursor	Action
  Positioned	FETCH returns: Requested data for successful data returning statements. SQLCODE +100 and SQLSTATE '02xxx' for no data. Error for non-rollback inducing SQL < O and SQLSTATE > '02xxx'.
  One of the following: Positioned on or after last row Does not return data	Cursor is positioned after last row. System assigns +100 to SQLCODE. System assigns '02xxx' to SQLSTATE. Host variables remain unchanged.
  Positioned before a row	Cursor is positioned on that row. System assigns values from the row to the host variables specified in INTO clause or output SQLDA.
  Positioned on a row other than the last	Cursor is positioned on row immediately following that row. System assigns values from new current row to host variables specified in INTO clause or output SQLDA.

• The system assigns values to the host variables you specified in the INTO clause, or in the SQLVAR array in the SQLDA in the order in which you specified the host variables. The system assigns a value to SQLSTATE and SQLCODE last.
• If an error occurs in assigning a value to a host variable, the system stops assigning values to host variables, and assigns one of the following values to the result code variables.

  SQLCODE	SQLSTATE
  -303	‘22509’
  -304	‘22003’
  -413	‘22003’

• The following table indicates what the system assigns if a field in the returned data is NULL, depending on whether you specified a corresponding host variable.

  Corresponding Host Variable	System Assignments
  Specified	-1 to the host indicator variable.
  Not specified	-305 to SQLCODE '22002' to SQLSTATE

  In either case, the host variables remain unchanged.

  • The following table indicates the host indicator value the system sets if a column value in the temporary table row is NOT NULL and you specified a corresponding indicator host variable.

  Situation	Value System Assigns to Host Indicator
  Column and main host variables have data type CHARACTER and column value is longer than main host variable.	Column value length
  Other	0

• The system sets column values in the corresponding main host variables according to the rules for host variables.

Related Information

• descriptor_area, see Teradata® Preprocessor2 for Embedded SQL Programmer Guide, B035-2446.
• Using a FETCH orientation other than NEXT, you must have declared a scrollable cursor. See DECLARE CURSOR (Selection Form).
• Scrollable cursors, see SET BUFFERSIZE.