wood burning stoves*
The moose likes JDBC and the fly likes Update the table rows through cursor Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Update the table rows through cursor" Watch "Update the table rows through cursor" New topic
Author

Update the table rows through cursor

sudha swami
Ranch Hand

Joined: Apr 24, 2007
Posts: 179
Hi,
I want to update the table rows by using cursors. Which one is the better way to use and why?
1)Select for Update
2)Where current Of
I am totally confused on this. Can you please help me out?

Thanks
Sudha
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1786
    
  16

SELECT ... FOR UPDATE locks the rows specified in the WHERE clause of your SELECT. The lock is released when you COMMIT or ROLLBACK the current transaction. This can be useful e.g. if you want to make sure nobody tries to update the same rows at the same time. But it can also be risky, because it is easy to lock a lot of records unnecessarily e.g. by selecting all the rows even if you only want to update one, or lock other people out by doing the SELECT and then forgetting to commit/rollback the transaction. So you should be careful how you use this.

UPDATE ... WHERE CURRENT OF [cursor] does exactly what it says: it updates the current record in your cursor. This works when your cursor is doing SELECT ... FOR UPDATE i.e. you have locked the record ready for updating it, which should stop anybody else updating it between the time you read it and the time you update it.

But I'm no Java expert, so you should get advice on how to manage this transaction and read consistency stuff within your Java application.


No more Blub for me, thank you, Vicar.
 
Consider Paul's rocket mass heater.
 
subject: Update the table rows through cursor