db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Danny" <danny.gallag...@gainergroup.com>
Subject Select for update, cursors
Date Wed, 28 Dec 2005 16:22:17 GMT
We are moving from a local database application (1 user to 1 database) to an
installation that will support a single database on a network (many users on
1 database).

In looking into select for update, cursors, locking, etc.  I have a few
questions that maybe someone could help me with.
Let me preface this with the fact that I am in no way a database programming
expert.

Here is the scenario within the system:
1. A user chooses a transaction from a list that they want to edit.
2. User clicks edit and all the details of the transaction are displayed in
the gui, the user has the option of editing any of these details.
3. User clicks submit and the transaction is updated in the database.

Here is where I have gotten so far:
1. When the user clicks a transaction from the list and clicks edit, that
transaction is retrieved from the database using a select for update, which
returns a ResultSet with the following settings"
(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE,
ResultSet.HOLD_CURSORS_OVER_COMMIT)
2. Second user clicks the same transaction in the list and clicks edit, that
transaction is retrieved using the same query.
3. First user changes details in transaction and submits, database update
occurs through the ResultSet produced from the select from update.
4. Second user changes details in transaction, (not the new details created
by user 1, but the original details) and submits, database update occurs
through the ResultSet produced from the select from update..

The behavior that I want is:
- User 2 to generate some type of exception that I can catch:
	- Either
	At the time that they select the transaction in the list that User 1
is already editing.
	OR
	At the time that User 2 submits the changes when User 1 performed an
update in the time since User 2 clicked edit to view the details of 	the
transaction.

It seems that what I want is a ResultSet.TYPE_SCROLL_SENSITIVE which (from
what I can gather) derby does not support, although I am not sure if this
would solve my problem.

Anyone run into this sort of thing? Any suggestions? I have found some
commentary that the time honored tradition of comparing the last update
timestamp column is really the best way to go about it, but then why have
updatable ResultSets at all? 

Thanks


Danny Gallagher
The Gainer Group
6525 The Corners Parkway
Suite 215
Norcross Ga, 30092
 
 




Mime
View raw message