FETCH (Embedded SQL Form) | Teradata Vantage - FETCH (Embedded SQL Form) - Advanced SQL Engine - Teradata Database

Purpose

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.

Invocation

Executable.

Embedded SQL only.

Syntax

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

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 n th 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 n th 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 mandatory.

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.

ANSI Compliance

ANSI/ISO SQL:2011-compliant.

Authorization

None.

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.

Rules

• You should define an SQLDA.
• You cannot execute FETCH as a dynamic SQL statement.
• Multistatement 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. In other words, 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 Teradata 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 should 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:

  IF the cursor …	THEN …
  has just been positioned	FETCH returns: Requested data for successful data returning statements. SQLCODE +100 and SQLSTATE ‘02xxx’ for no data. An error for non-rollback inducing SQL < O and SQLSTATE > ‘02xxx’.
  is positioned on or after the last row or does not return data	the cursor is positioned after the last row the system assigns +100 to SQLCODE the system assigns ‘02xxx’ to SQLSTATE the host variables remain unchanged
  is positioned before a row	the cursor is positioned on that row the system assigns values from the row to the host variables you specified in the INTO clause or in the output SQLDA.
  is positioned on a row other than the last row	the cursor is positioned on the row immediately following that row the system assigns values from the new current row to the host variables you specified in the INTO clause or in the 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 or not you specified a corresponding host variable.

  IF a corresponding host variable is …	THEN the system assigns …
  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.

  IF …	THEN the system sets the host indicator value to …
  the column and main host variables are typed CHARACTER and the column value is longer than the main host variable	the length of the column value.
  anything else	0.

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

Related Topics