Data processing: database and file management or data structures – Database design – Data structure types
Reexamination Certificate
2001-07-26
2004-06-22
Mizrahi, Diane D. (Department: 2175)
Data processing: database and file management or data structures
Database design
Data structure types
C707S793000
Reexamination Certificate
active
06754653
ABSTRACT:
BACKGROUND OF THE INVENTION
1. Field of the Invention
The present invention relates to a method, system, and program for implementing cursors in a database and, in particular, updateable scrollable cursors.
2. Description of the Related Art
Prior art database programs include a feature referred to as cursors. A cursor is a named control structure used by an application program to point to a row of interest within some set of rows and to retrieve rows from the set, possibly making updates and deletions. A cursor points to rows from a database table that satisfy a structured query language (SQL) query against the table. The rows in the table that satisfy the SQL query comprise a result table of data. The SQL query includes an SQL SELECT statement and a WHERE clause to qualify rows according to a predicate. An application can then access data on a row-by-row basis from the result table.
If the result table is static and not updateable, then the result table may be materialized in a workfile. Alternatively, the cursor may point directly to the rows in the base table. In such case, the result table is not materialized in a workfile and the cursor is updateable when the base table is updated.
When a cursor is opened or initialized, the current row position of the cursor is the first record in the result table. The application program may then issue fetch commands to move the current row position and fetch forward or backward by one or more rows or from the first or last row by one or more rows. In current implementations, if the cursor is static, then the result table cannot be updated such that once it is created no rows will be added or removed, and no values in any rows will change. Further, a static cursor is not affected by other applications accessing or updating the data records in the underlying database table from which the result set was generated. Still further, in current static cursor implementations, the cursor is also read-only so that it is not possible for the application to change any values in the cursor result table.
Open DataBase Connectivity (ODBC) is a standard database access method to allow applications to access data in a database management system (DBMS). An ODBC driver translates the application's queries into commands that the DBMS understands. The ODBC standards describe scrollable, keyset driven, static and dynamic cursors. The ODBC standards mention that cursors may be updateable or nonupdateable. Cursors are defined as updateable if the application is capable of modifying the data in the cursor result table. As discussed, the result table may be implemented in a work file or comprise the rows pointed to by the cursor in the base table. The ODBC also mentions that when positioned on a row in an updateable cursor, the application can perform position updates or delete operations that target the base table rows used to build the current row in the cursor.
The ODBC defines the following types of cursors:
scrollable cursor: allows the application to fetch forward or backward from the current position, i.e., from anywhere, in the result set. With a scrollable cursor, your application can request by position the data presented in the current row. Typical scrolling requests include moving one row forward, one row back, to the beginning, or to the end of the result set. With a scrollable cursor, the application can request that a certain row of data be made the current row more than once.
forward-only cursor: allows the application to fetch forward serially from the start to end of the result set.
keyset cursor: the rows in the result table are identified by the value present in a designated column.
static cursors only contain data that was placed in the cursor when it was created. A static cursor does not display new rows inserted in the database after the cursor was opened, even if they match the search conditions of the cursor SELECT statement. If rows in the result table are updated by means other than through the cursor defining the result table, then the new data values are not displayed in the static cursor. The static cursor may display rows deleted from the database after the cursor was opened if they were deleted by a positioned delete through the cursor.
dynamic cursors: Dynamic cursors reflect all changes made to the rows in their result table when scrolling through the cursor. The data values, order, and membership of the rows in the result table can change on each fetch. All UPDATE, INSERT, and DELETE statements made by all users are visible through the cursor. Updates are visible immediately if they are made through the cursor. Updates made outside the cursor are not visible until they are committed, unless the cursor transaction isolation level is set to read uncommitted. Updates made outside the cursor by the same transaction as that which defines the cursor are immediately visible.
Cursors may be categorized as forward-only or scrollable. If the cursor is scrollable then they can be either static, keyset or dynamic.
Although the ODBC defines the above types of scrollable and updateable cursors, such cursor features are not typically implemented in database programs. Thus, there is a need in the art to provide an implementation of scrollable cursors in a DBMS.
SUMMARY OF THE PREFERRED EMBODIMENTS
To address the shortcomings in the prior art discussed above, preferred embodiments disclose a method, system, program, and data structures for making data available to an application program. A result table is generated including rows corresponding to a subset of rows in a base table having data in one or more columns that satisfy a query predicate clause. The result table includes, for each row, a location identifier of the corresponding row in the base table and a column for each column in a query select list. The result table is accessed to return requested data from the base table to the application program.
In further embodiments, the query select list is provided from a structured language query (SQL) SELECT statement and the query predicate clause is provided from an SQL WHERE statement.
Still further, the result table may comprise a database table.
In additional embodiments, a database INSERT command is generated to insert data from columns in base table rows that satisfy the query predicate clause into corresponding columns in result table rows. In such case, an insert list is generated including an entry for each column in the query select list. An insert statement is generated to insert data from the base table columns listed in the query select list into the corresponding result table columns in the insert list. The query predicate clause is generated into the insert statement. The insert statement is then executed to insert data from the base table columns in the query select list into the corresponding result table columns in the insert list. In this way, data is inserted from those base table rows having data that satisfies the query predicate clause in the insert statement.
Preferred embodiments provide a technique for implementing a result table and, in particular, for result tables used with static scrollable cursors. Preferred embodiments include control information with the result table and control blocks used to form the result table that allow for an implementation of static scrollable cursors where the result table is sensitive to changes in the base table and where updates to the result table entries are reflected in the base table.
REFERENCES:
patent: 4190835 (1980-02-01), Buynak
patent: 4221003 (1980-09-01), Chang et al.
patent: 4434475 (1984-02-01), McCaskill et al.
patent: 5097408 (1992-03-01), Huber
patent: 5241648 (1993-08-01), Cheng et al.
patent: 5276870 (1994-01-01), Shan et al.
patent: 5396623 (1995-03-01), McCall et al.
patent: 5410693 (1995-04-01), Yu et al.
patent: 5412805 (1995-05-01), Jordan, II et al.
patent: 5446858 (1995-08-01), Copeland et al.
patent: 5450581 (1995-09-01), Bergen et al.
patent: 5546576 (1996-08-01), Cochrane et al.
patent: 5579515 (1996-11-01), Hintz et al.
patent: 5581275 (1996-12-
Bonner Charles Roy
Phoenix San Yu
Shyam Kalpana
Watts Julie Ann
Konrad Raynes & Victor & Mann LLP
Mizrahi Diane D.
Victor David W.
Wu Yicun
LandOfFree
Method, system, and program for processing a fetch request... does not yet have a rating. At this time, there are no reviews or comments for this patent.
If you have personal experience with Method, system, and program for processing a fetch request..., we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Method, system, and program for processing a fetch request... will most certainly appreciate the feedback.
Profile ID: LFUS-PAI-O-3330956