ADVANCE

The ADVANCE statement is a procedure cursor manipulation statement. It is used in conjunction with procedures having one or more multiple row result sets to advance the position of an opened procedure cursor to the first or next query result set and to initialize information in the associated sqlda_type and sqlformat_type data structures.

Scope

Application Programs Only

SQL Syntax

  ADVANCE CursorName [USING [SQL]DESCRIPTOR {SQLDA
                                             AreaName}]

Parameters

CursorName: identifies a procedure cursor. The procedure cursor's current active query result set, the procedure's statements, and the values of any procedure input parameters, determine the format information to be returned by each successive ADVANCE statement.
USING [SQL] DESCRIPTOR: defines where to place the data format information of a query result for an EXECUTE PROCEDURE statement on which a procedure cursor has been defined. Specify a location that does not conflict with that of another SQL statement such as OPEN, CLOSE, DESCRIBE, EXECUTE, or any FETCH that is not associated with this ADVANCE statement.
SQLDA: specifies that a data structure of sqlda_type named SQLDA is to be used to pass information about the next result set between the application and ALLBASE/SQL.
AreaName: specifies the user defined name of a data structure of sqlda_type that is to be used to pass information about the next result set between the application and ALLBASE/SQL.

Description

The query result set to which the procedure cursor points is called the active result set. You use the information in the associated sqlda_type and sqlformat_type data structures to process the query result set via FETCH statements.
For a procedure that returns multiple row results of a single format, if the procedure was created with the WITH RESULT clause, it is unnecessary to issue an ADVANCE statement to get format information for each result set, since the format is already known from the DESCRIBE RESULT statement.
The ADVANCE statement cancels any current, active query result set. It can be used as an efficient way to throw away any unread rows resulting from the most recently executed multiple row result set SELECT statement in the procedure. The execution of the procedure continues with the next statement. Control returns to the application when the next multiple row result set statement is executed, or when procedure execution terminates.
Refer to the ALLBASE/SQL Advanced Application Programming Guide for further explanation and examples of how to use the ADVANCE statement.

Authorization

You do not need authorization to use the ADVANCE statement.

Example

Refer to the ALLBASE/SQL Advanced Application Programming Guide for a pseudocode example of procedure cursor usage.