Browse Prior Art Database

UPDATE/DELETE WHERE CURRENT of CURSOR When SELECT Is Dynamic

IP.com Disclosure Number: IPCOM000036768D
Original Publication Date: 1989-Oct-01
Included in the Prior Art Database: 2005-Jan-29
Document File: 5 page(s) / 19K

Publishing Venue

IBM

Related People

Horn, GR: AUTHOR [+2]

Abstract

Disclosed is a method within a database management system (DBMS) for detecting error conditions during SQL statement compilation and execution for UPDATE and DELETE statements that are based on a positioned cursor. The SQL statement DECLARE ... CURSOR FOR SELECT ... FOR UPDATE OF ... defines a cursor that can be used to fetch records, and then update or delete a specific record using the SQL statements UPDATE ... WHERE CURRENT OF ... DELETE ... WHERE CURRENT OF ...

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 33% of the total text.

Page 1 of 5

UPDATE/DELETE WHERE CURRENT of CURSOR When SELECT Is Dynamic

Disclosed is a method within a database management system (DBMS) for detecting error conditions during SQL statement compilation and execution for UPDATE and DELETE statements that are based on a positioned cursor. The SQL statement

DECLARE ... CURSOR FOR SELECT ... FOR UPDATE OF ... defines a cursor that can be used to fetch records, and then update or delete a specific record using the SQL statements UPDATE ... WHERE CURRENT OF ...

DELETE ... WHERE CURRENT OF ...

For the UPDATE or DELETE statement to be valid, three conditions must hold true:
1. The table/view name in the UPDATE/DELETE statement must

match the table/view name in the SELECT statement.
2. The table/view in the SELECT statement must not be

read-only for the cursor. A cursor is read-only if the

SELECT statement contains any of the following:

a set operator (i.e., UNION, INTERSECT or EXCEPT)

the DISTINCT keyword

a column function in the select list

GROUP BY, HAVING, or ORDER BY clause

a FROM clause that identifies either

more than one table or view

a read-only view

a table or view that is also identified in

the FROM

clause of a subquery of the SELECT statement.
3. The columns being updated in the UPDATE statement must

each be specified in the FOR UPDATE clause of the

SELECT statement.

This can easily be checked at Precompile time if both the SELECT statement and the UPDATE/DELETE statement are static. It is a relatively simple matter to validate a dynamic UPDATE or DELETE statement when it is prepared if the SELECT statement is static. The only requirement is that the information saved during compilation contains the table name and updatable column information (bit encoded) and a flag indicating if the cursor is read-only. The SQL compiler can perform the same checks as if the UPDATE/DELETE statement were static.

If the SELECT statement is dynamic, a whole different set of problems arises. It does not matter if the UPDATE/DELETE statement is static or dynamic. In effect, the UPDATE/DELETE statement cannot be validated when it is compiled or prepared. If the UPDATE/DELETE statement is static, this is obvious since the SELECT statement is non- existent at compile time. If the UPDATE/DELETE statement is dynamic, it is also true since the SELECT statement may not yet be prepared. Restrictions that would have to be enforced to require the SELECT

1

Page 2 of 5

statement to be prepared when the UPDATE/DELETE statement is prepared would be too cumbersome for the user to consider.

It is thus apparent that the validation of the UPDATE/DELETE statement must be done at execution time. In OS/2* Extended Edition Database Services, SQL statements are compiled in an Access Section, which is composed in part of data objects and code threads. The data objects contain information about the data being operated on, and the threads contain operators that direct the execution upon the data. This type of compilation is referred to...