Browse Prior Art Database

Using SQL string to store pending changes, display changes, allow cancellation of changes without modifying database until changes are committed

IP.com Disclosure Number: IPCOM000015201D
Original Publication Date: 2002-May-05
Included in the Prior Art Database: 2003-Jun-20
Document File: 3 page(s) / 46K

Publishing Venue

IBM

Abstract

Disclosed is a method and apparatus for using an SQL string to store pending data changes, display data to the user as if the change has occurred, and allow cancellation of individual changes without modifying data in the database until changes are committed.

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

Page 1 of 3

  Using SQL string to store pending changes, display changes, allow cancellation of changes without modifying database until changes are committed

Disclosed is a method and apparatus for using an SQL string to store pending data changes, display data to the user as if the change has occurred, and allow cancellation of individual changes without modifying data in the database until changes are committed.

Given that :
A) The user is given a list of existing database entries in a GUI control.
B) Each entry presented to the user can represent multiple / many rows in the database.
C) The user has the ability to add or delete entries from the GUI control.
D) Prior to committing the changes, the user has the ability to cancel any or all previous selection(s).
E) On commit, all records in the selected recordset will be modified by the users choices to add or delete.
F) The database table in question can consist of millions of rows.
G) The user selected number of rows will be small, but committed changes can result in complex joins on millions of rows.

The problem to be solved is how to store pending changes to the database without the expense of:
1) Making a "working" or temporary copy of the records in question
2) Applying the changes against the temporary copy, and rolling them back as necessary before the user commits the changes
3) Copying the changed records back to their original location (or deleting them) when the user commits the changes

The implementation solves this problem by using a string to store SQL syntax consisting entirely of explicit values unioned together. Using this method, no explicit tables are created to store the changes, changes can be eliminated or added using string functions, and the entire block can be added to an SQL statement and treated as a table (allowing the data to be displayed to the user either as pending changes or as if the data had already been changed). This also allows the actual data to be modified only in the final SQL statement (where the SQL values string is treated as a table) when the changes are committed.

The apparatus that currently implements the invention allows the user to add or delete "forms" from an existing mail "package" entry in a DB2 database, using a GUI interface to display the existing data, and the pending changes the user has chosen. The SQL string used to store the pending changes takes the form of:

SQLString = "VALUES ('E003', 3, 'Add') UNION VALUES ('E004', 0, 'Del') UNION VALUES ..."

1

Page 2 of 3

If the user decides to cancel or add changes, string functions are used to locate the criteria inside the string, and add or remove the criteria from the string before the user decides to commit the changes. For example:

"UNION VALUES('E004', 0, 'Del')" can be found easily inside the larger string "VALUES ('E003', 3, 'Add') UNION VALUES ('E004', 0, 'Del') UNION VALUES ...", and be removed, leaving "VALUES ('E003', 3, 'Add') UNION VALUES ..." Similarly, change strings can be appended o...