Browse Prior Art Database

Support of Multiple Rows on SQL Fetch Statement

IP.com Disclosure Number: IPCOM000110081D
Original Publication Date: 1992-Oct-01
Included in the Prior Art Database: 2005-Mar-25
Document File: 4 page(s) / 108K

Publishing Venue

IBM

Related People

Boykin, JR: AUTHOR [+2]

Abstract

Disclosed is a functional description to support access to multiple rows on an SQL FETCH request.

This text was extracted from an ASCII text file.
This is the abbreviated version, containing approximately 52% of the total text.

Support of Multiple Rows on SQL Fetch Statement

       Disclosed is a functional description to support access
to multiple rows on an SQL FETCH request.

      The current SQL FETCH statement is limited to retrieve only one
row per invocation.  A blocked-fetch is supported in the Remote Data
Services sense.  However, the amount of rows fetched in the block is
determined by the Database Manager and this method of blocked-fetch
causes a cursor to be read-only (i.e., positioned UPDATE and DELETE
statements cannot reference the blocked cursor).  To support a
user-defined method of block-fetching for an SQL cursor, and to
support an updatable, blocked cursor, the FETCH statement is enhanced
with a "repeated fetch clause."
INVENTION

                            (Image Omitted)

single fetch clause:
repeated fetch clause:
Functional Description of Extensions for Repeated Fetch Clause
     FOR ... ROWS
          Evaluates <host variable> or <integer constant> to an
integral value k. If k <= 0 or is NULL, an error is raised.
Otherwise, the cursor is positioned on the next row and that row is
fetched.  Then, the next k - 1 rows are fetched (moving forward in
the table), until the end of the cursor is reached or until an error
results.  The values from each individual fetch are placed in data
areas described in the INTO clause.
     INTO DESCRIPTOR (repeated fetch clause)
          Identifies one or more SQLDA structures; each must contain
a valid description of zero or more host variables.  There must be a
SQLDA for each row returned or an error is raised.  Extra SQLDAs are
ignored.
          The length of each SQLDA, as shown by SQLDABC, must be
sufficient to describe the number of variables indicated by SQLD.
The first value for a row corresponds to the first variable described
by the SQLDA, the second value corresponds to the second variable,
and so on.
      INTO ARRAY OF DESCRIPTORS
          Identifies an array of SQLDA structures.  <integer
constant> must not be less than the number of rows returned or an
error is raised.
          The SQLDA array, <da array>, may be declared in any way
which  provides for contiguous storage of SQLDAs of the required size
(as fixed by the number of variables being fetched).  The length of
each SQLDA in the array, as shown by SQLDABC, must be sufficient to
describe the number of variables indicated by SQLD.  The first row
retrieved corresponds to the first SQLDA group in the array, the
second row corresponds to the...