Method, system, and program for using a fetch request to make data available to an application program

• US 6,643,637 B2
• Filed: 07/26/2001
• Issued: 11/04/2003
• Est. Priority Date: 09/07/2000
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Watts, Julie Ann, Chen, Yao Ching Stephen, Shyam, Kalpana
• Primary Examiner(s): Rones, Charles
• Assistant Examiner(s): Wu, Yicun
• Application Number:
  US09/915,869
• Publication Number:
  US 20020029212A1
• Time in Patent Office: 831 Days
• Field of Search: 707/1, 707/2, 707/3, 707/4, 707/7, 707/8, 707/10, 707/100, 707/101, 707/102, 707/533, 717/11.C, 345/467, 345/160, 345/784, 345/787, 365/15, 358/1.1, 296/031.4
• US Class Current: 1/1
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99938 Concurrency, e.g. lock management in shared database
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99953 Recoverability

Abstract

Provided is 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.

Claims

1. A method for making data available to an application program, comprising:

generating a result table 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, wherein the result table includes, for each row, a location identifier of the corresponding row in the base table;

maintaining a cursor row pointer that points to a current row in the result table;

receiving a fetch request from the application program to reposition the cursor row pointer to a target row in the result table that is different than the current row and return the data at the target row to the application program;

repositioning the cursor row pointer to point to the target row in the result table;

determining whether a delete flag for the target row indicates whether the corresponding row in the base table was deleted, wherein the target row in the result table is no longer available to return in response to the fetch request if the delete flag indicates that the corresponding row in the base table was deleted; and

returning a code to the application program in response to the fetch request indicating that the requested row in the result table is not available.

2. The method of claim 1, wherein if the target row in the result table is available to return in response to the fetch request, then further performing:

using the location identifier for the target row to access the row in the base table corresponding to the target row;

updating the target row in the result table with the accessed row in the base table; and

returning the data from the accessed row in the base table to the application program in response to the fetch request.

3. The method of claim 1, wherein if the delete flag does not indicate that the corresponding row in the base table was deleted, then further performing:

using the location identifier for the target row to access the row in the base table corresponding to the target row;

determining whether the corresponding row in the base table was deleted from the base table, wherein the target row in the result table is no longer available to return in response to the fetch request if the corresponding row in the base table was deleted.

4. The method of claim 3, further comprising:

setting the delete flag to indicate that the corresponding row in the base table was deleted if the corresponding row in the base table is determined to have been deleted.

5. The method of claim 1, wherein if the delete flag does not indicate that the corresponding row in the base table was deleted, then further performing:

using the location identifier for the target row to access the row in the base table corresponding to the target row; and

determining whether the accessed row in the base table qualifies according to the query predicate clause, wherein the target row in the result table is no longer available to return in response to the fetch request if the corresponding row in the base table does not qualify according to the query predicate clause.

6. The method of claim 1, further comprising:

using the location identifier for the target row to access the row in the base table corresponding to the target row; and

determining whether the accessed row in the base table qualifies according to the query predicate clause, wherein the target row in the result table is no longer available to return in response to the fetch request if the corresponding row in the base table does not qualify according to the query predicate clause.

7. The method of claim 6, further comprising:

setting an update flag for the target row indicating that the corresponding row in the base table no longer qualifies according to the query predicate clause if the accessed row is determined to not qualify according to the query predicate clause due to an updating of the corresponding row in the base table.

8. The method of claim 6, wherein if the accessed row in the base table qualifies according to the query predicate clause, then further performing:

using the location identifier for the target row to access the row in the base table corresponding to the target row;

updating the target row in the result table with the accessed row in the base table; and

returning the data from the accessed row in the base table in response to the fetch request.

9. The method of claim 1, wherein 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.

10. The method of claim 1, wherein the result table comprises a database table.

11. The method of claim 1, wherein the location identifier identifies a page location including the record and a unique identifier of the record in the page.

12. The method of claim 1, wherein the fetch request specifies movement of the cursor row pointer one or more rows from the current result table row addressed by the cursor row pointer.

13. A system for making data available to an application program, comprising:

a computer readable medium;

a storage device;

a base table stored in the storage device;

means for generating a result table in the computer readable medium 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, wherein the result table includes, for each row, a location identifier of the corresponding row in the base table;

means for maintaining a cursor row pointer that points to a current row in the result table in the computer readable medium;

means for receiving a fetch request from the application program to reposition the cursor row pointer to a target row in the result table that is different than the current row and return the data at the target row to the application program;

means for repositioning the cursor row pointer to point to the target row in the result table;

means for determining whether a delete flag for the target row indicates whether the corresponding row in the base table was deleted, wherein the target row in the result table is no longer available to return in response to the fetch request if the delete flag indicates that the corresponding row in the base table was deleted; and

means for returning a code to the application program in response to the fetch request indicating that the requested row in the result table is not available.

14. The system of claim 13, further comprising means for performing if the target row in the result table is available to return in response to the fetch request:

using the location identifier for the target row to access the row in the base table corresponding to the target row;

updating the target row in the result table with the accessed row in the base table; and

returning the data from the accessed row in the base table to the application program in response to the fetch request.

15. The system of claim 13, further comprising means for performing if the delete flag does not indicate that the corresponding row in the base table was deleted:

using the location identifier for the target row to access the row in the base table corresponding to the target row;

determining whether the corresponding row in the base table was deleted from the base table, wherein the target row in the result table is no longer available to return in response to the fetch request if the corresponding row in the base table was deleted.

16. The system of claim 15, further comprising:

means for setting the delete flag to indicate that the corresponding row in the base table was deleted if the corresponding row in the base table is determined to have been deleted.

17. The system of claim 13, further comprising means for performing if the delete flag does not indicate that the corresponding row in the base table was deleted:

using the location identifier for the target row to access the row in the base table corresponding to the target row; and

determining whether the accessed row in the base table qualifies according to the query predicate clause, wherein the target row in the result table is no longer available to return in response to the fetch request if the corresponding row in the base table does not qualify according to the query predicate clause.

18. The system of claim 13, further comprising:

means for using the location identifier for the target row to access the row in the base table corresponding to the target row; and

means for determining whether the accessed row in the base table qualifies according to the query predicate clause, wherein the target row in the result table is no longer available to return in response to the fetch request if the corresponding row in the base table does not qualify according to the query predicate clause.

19. The system of claim 18, further comprising:

means for setting an update flag for the target row indicating that the corresponding row in the base table no longer qualifies according to the query predicate clause if the accessed row is determined to not qualify according to the query predicate clause due to an updating of the corresponding row in the base table.

20. The system of claim 18, further comprising means for performing if the accessed row in the base table qualifies according to the query predicate clause:

using the location identifier for the target row to access the row in the base table corresponding to the target row;

updating the target row in the result table with the accessed row in the base table; and

returning the data from the accessed row in the base table in response to the fetch request.

21. The system of claim 13, wherein 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.

22. The system of claim 13, wherein the result table comprises a database table.

23. The system of claim 13, wherein the location identifier identifies a page location including the record and a unique identifier of the record in the page.

24. The system of claim 13, wherein the fetch request specifies movement of the cursor row pointer one or more rows from the current result table row addressed by the cursor row pointer.

25. An article of manufacture including code for making data available to an application program by:

generating a result table 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, wherein the result table includes, for each row, a location identifier of the corresponding row in the base table;

maintaining a cursor row pointer that points to a current row in the result table;

receiving a fetch request from the application program to reposition the cursor row pointer to a target row in the result table that is different than the current row and return the data at the target row to the application program;

repositioning the cursor row pointer to point to the target row in the result table;

determining whether a delete flag for the target row indicates whether the corresponding row in the base table was deleted, wherein the target row in the result table is no longer available to return in response to the fetch request if the delete flag indicates that the corresponding row in the base table was deleted; and

returning a code to the application program in response to the fetch request indicating that the requested row in the result table is not available.

26. The article of manufacture of claim 25, wherein if the target row in the result table is available to return in response to the fetch request, then further performing:

using the location identifier for the target row to access the row in the base table corresponding to the target row;

updating the target row in the result table with the accessed row in the base table; and

returning the data from the accessed row in the base table to the application program in response to the fetch request.

27. The article of manufacture of claim 25, wherein if the delete flag does not indicate that the corresponding row in the base table was deleted, then further performing:

using the location identifier for the target row to access the row in the base table corresponding to the target row;

determining whether the corresponding row in the base table was deleted from the base table, wherein the target row in the result table is no longer available to return in response to the fetch request if the corresponding row in the base table was deleted.

28. The article of manufacture of claim 25, further comprising:

setting the delete flag to indicate that the corresponding row in the base table was deleted if the corresponding row in the base table is determined to have been deleted.

29. The article of manufacture of claim 25, wherein if the delete flag does not indicate that the corresponding row in the base table was deleted, then further performing:

using the location identifier for the target row to access the row in the base table corresponding to the target row; and

determining whether the accessed row in the base table qualifies according to the query predicate clause, wherein the target row in the result table is no longer available to return in response to the fetch request if the corresponding row in the base table does not qualify according to the query predicate clause.

30. The article of manufacture of claim 25, further comprising:

using the location identifier for the target row to access the row in the base table corresponding to the target row; and

determining whether the accessed row in the base table qualifies according to the query predicate clause, wherein the target row in the result table is no longer available to return in response to the fetch request if the corresponding row in the base table does not qualify according to the query predicate clause.

31. The article of manufacture of claim 30, further comprising:

setting an update flag for the target row indicating that the corresponding row in the base table no longer qualifies according to the query predicate clause if the accessed row is determined to not qualify according to the query predicate clause due to an updating of the corresponding row in the base table.

32. The article of manufacture of claim 30, wherein if the accessed row in the base table qualifies according to the query predicate clause, then further performing:

using the location identifier for the target row to access the row in the base table corresponding to the target row;

updating the target row in the result table with the accessed row in the base table; and

returning the data from the accessed row in the base table in response to the fetch request.

33. The article of manufacture of claim 25, wherein 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.

34. The article of manufacture of claim 25, wherein the result table comprises a database table.

35. The article of manufacture of claim 25, wherein the location identifier identifies a page location including the record and a unique identifier of the record in the page.

36. The article of manufacture of claim 25, wherein the fetch request specifies movement of the cursor row pointer one or more rows from the current result table row addressed by the cursor row pointer.