READ RESULT SET - SQL

Common Set Syntax:

READ [(limit)] RESULT SET result-set INTO

VIEW view-name
parameter [, parameter]...

FROM ddm-name
   [GIVING [:] sql-code]
END-RESULT (structured mode only)      
LOOP (reporting mode only)      

Extended Set Syntax:

READ [(limit)] RESULT SET result-set INTO

VIEW view-name
parameter [, parameter]...

FROM ddm-name
    [WITH INSENSITIVE SCROLL [:] scroll-hv]
   [GIVING [:] sql-code]
[WITH ROWSET POSITIONING FOR

[:] row_hv
integer

ROWS]
END-RESULT (structured mode only)      
LOOP (reporting mode only)      

This document covers the following topics:

For an explanation of the symbols used in the syntax diagram, see Syntax Symbols.

Belongs to Function Group: Database Access and Update

See also READ RESULT SET - SQL in the Natural for DB2 part or READ RESULT SET - SQL in the part Natural SQL Gateway of the Database Management System Interfaces documentation.


Function

The SQL statement READ RESULT SET can only be used in conjunction with a CALLDBPROC statement. It is used to read a result set which was created by a stored procedure that was invoked by a previous CALLDBPROC statement.

Restriction

This statement is available only with Natural for DB2 and Natural SQL Gateway.

Syntax Description

Syntax Element Description
limit
Limit Option:

You can limit the number of rows to be read. You can specify the limit either as a numeric constant (0 - 4294967295) or as a variable of format N, P or I.

result-set
Result Set:

As result-set you specify a result-set locator variable filled by a preceding CALLDBPROC statement. result-set has to be a variable of format/length I4.

Note:
If a syncpoint operation takes place between the CALLDBPROC statement and the READ RESULT SET statement, the result sets can no longer be accessed by the READ RESULT SET statement.

When using the Natural SQL Gateway, the READ RESULT SET statement has to be placed into the same Natural program which called the stored procedure producing the result set. The Natural SQL Gateway can only process one result set per stored procedure at any point of time.

INTO
INTO Clause:

The INTO clause is used to specify the target fields in the program which are to be filled with the result set.

The INTO clause can specify either single parameters or one or more views as defined in the DEFINE DATA statement.

VIEW view-name
VIEW Clause:

view-name specifies a view whose fields receive the columns of the result set created by the stored procedure invoked via the CALLDBPROC statement.

The number of columns of the result set must correspond to the number of fields defined in the view (not counting group fields, redefining fields and indicator fields).

parameter
Parameter:

Each parameter specifies a field which receives a column of the result set created by the stored procedure invoked via the CALLDBPROC statement.

FROM ddm-name
DDM Name:

As ddm-name you specify the name of the data definition module (DDM) which is used to "address" the database executing the stored procedure.

For further information, see ddm-name.

WITH INSENSITIVE SCROLL [:] scroll_hv
WITH INSENSITIVE SCROLL Clause:

This clause belongs to the SQL Extended Set. It is available only with Natural for DB2.

Using this clause causes the application to use an insensitive scrollable cursor to access the result set created by the previously invoked stored procedure. In order to use this clause, the stored procedure must have created the result set with a scrollable cursor. scroll_hv has to be an alphanumeric Natural variable which contains the scrolling direction. scroll_hv will be evaluated each time the READ RESULT SET processing loop is executed.

If the GIVING sqlcode option is specified as well, the processing loop will stay open, even if an sqlcode +100 (row not found) is returned from the RDBMS.

The processing will be terminated, if the application issues an ESCAPE statement or if the SQLCODE +100 (row not found) is encountered five times successively without a terminal I/O.

If the GIVING sqlcode option is not specified, the processing loop will be closed, if any SQLCODE other than 0 (success) is returned from the RDBMS.

GIVING sqlcode
GIVING sqlcode Clause:

This clause may be used to obtain the SQLCODE of the SQL "fetch" operation used to process the result set.

If this clause is specified and the SQLCODE of the SQL operation is not 0, no Natural error message will be issued. In this case, the action to be taken in reaction to the SQLCODE value has to be coded in the invoking Natural object.

The sqlcode field has to be a variable of format/length I4.

If the GIVING sqlcode clause is omitted, a Natural error message will be issued if the SQLCODE is not 0.

WITH ROWSET POSITIONING FOR ... ROWS
WITH ROWSET POSITIONING FOR ... ROWS Clause:

This clause belongs to the SQL Extended Set. It is available only with Natural for DB2.

Using this clause causes the application to read multiple rows of data from the result set by the previously invoked stored procedure. The integer or :row_hv variable determines the number of rows fetched.

If the GIVING sqlcode option is specified as well, the processing loop will stay open, even if an SQLCODE +100 (row not found) is returned from the RDBMS.

The processing will be terminated, if the application issues an ESCAPE statement or if the SQLCODE +100 (row not found) is encountered five times successively without a terminal I/O.

If the GIVING sqlcode clause is not specified, the processing loop will be closed if any SQLCODE other than 0 (success) is returned from the RDBMS.

END-RESULT
End of READ RESULT SET Statement:

In structured mode, the Natural reserved keyword END-RESULT must be used to end the READ RESULT SET statement.

In reporting mode, the Natural statement LOOP must be used to end the READ RESULT SET statement.

LOOP

Example

See Example of CALLDBPROC/READ RESULT SET in the section CALLDBPROC - SQL of the Natural for DB2 documentation.