Browse Prior Art Database

Orderable, Updatable, Scrollable Structured Query Language Cursors

IP.com Disclosure Number: IPCOM000116001D
Original Publication Date: 1995-Jul-01
Included in the Prior Art Database: 2005-Mar-30
Document File: 2 page(s) / 71K

Publishing Venue

IBM

Related People

Boykin, JR: AUTHOR [+2]

Abstract

Disclosed is a method of supporting the ANS/ISO scrollable cursor definition with extensions as follows: o A scrollable cursor is not read-only unless the SELECT statement defining the cursor is read-only, e.g., an ORDER BY clause on a SELECT statement defining a scrollable cursor does not make the cursor read-only. o The FETCH statement is extended to support positioning before and after all records defined by a Structured Query Language (SQL) cursor, and a REFRESH option to re-FETCH a previously FETCHed row, validate predicates applicable to the cursor and return the result row values to the user. o Positioned UPDATE and DELETE statements are allowed to reference a scrollable cursor.

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

Orderable, Updatable, Scrollable Structured Query Language Cursors

      Disclosed is a method of supporting the ANS/ISO scrollable
cursor definition with extensions as follows:
  o  A scrollable cursor is not read-only unless the SELECT statement
      defining the cursor is read-only, e.g., an ORDER BY clause on a
      SELECT statement defining a scrollable cursor does not make the
      cursor read-only.
  o  The FETCH statement is extended to support positioning before
and
      after all records defined by a Structured Query Language (SQL)
      cursor, and a REFRESH option to re-FETCH a previously FETCHed
      row, validate predicates applicable to the cursor and return
the
      result row values to the user.
  o  Positioned UPDATE and DELETE statements are allowed to reference
      a scrollable cursor.

      To support arbitrary navigation in a cursor (e.g., fetching a
row of the result based on its absolute position in the result, or
position relative to the current position), a temporary table is
built containing the requested columns, and an internally generated
column containing the absolute position number or each result row.
For good performance, an internal index is also created, defined on
the position number, to allow quick access to a particular result row
based on a requested position.

      To support update and delete through a scrollable cursor, the
temporary table mentioned previously will also contain an internally
generated column containing the record IDs of each row in the target
table.  The values in this column will be used to locate the row in
the target table for the purpose of updating or deleting it, if so
requested by the application program, through a positioned UPDATE SQL
statement.  When a row is deleted through a scrollable cursor,...