Hi Robert,
I have been having exactly the same problem and as yet found no solution.
According to Oracles' own documentation and people I spoke to on their world
wide support line, you don't need to use the SELECT for UPDATE to lock a
row.
The conventional wisdom is that if you use a straight SELECT, you get a
cursor (pointer to a result set in memory) and as you navigate through the
result set,
that row is locked and you can do updates if required.
This may be true of PL/SQL, I have worked on another site where I was able
to do what is shown in the PL/SQL type pseudo-code shown below.
for cursor1 in (SELECT ename, rowid FROM emp ....)
loop
<put your code here>
UPDATE
end loop;
However, I am not sure that the implementation is completely fool proof in
Oracle JDBC drivers yet. In my case due to the particular nature of my
problem
(need to write to LOB fields) I don't have any choice, but I think there may
be a kind of work around (inelegant) in your case as follows (assuming you
want to
do updates in the body of the select) .
1) Start off with a straight select
PreparedStatement ps = conn.prepareStatement("SELECT ename, rowid FROM emp
WHERE rowid > ?");
ps.setString(1, "????");
ResultSet rs = ps.executeQuery();
Vector v = new Vector();
while (rs.next())
{
int i = rs.getInt(......); //assuming that rowid is an integer type
v.addElement((Object) i);
}
2) get all of the primary-keys of the rows in the table that you want to
update and save them in a vector.
3) In a separate loop, go through this gotten result set and issue updates
int count = v.size();
for (int i = 0; i < count;i++)
{
PreparedStatement ps = conn.prepareStatement("UPDATE emp SET ....... = ?
where rowid = ?");
ps.setString(1, .....);
ps.setInt(2, (int) v.elementAt(i));
ps.executeUpdate();
}
We can compare notes & discuss this further offline if you prefer.
My email is [EMAIL PROTECTED]
Also if you find out how to get the SELECT for UPDATE working please let me
know.
rgsd
Daniel
> -----Original Message-----
> From: Robert Pimentel
> Sent: Thursday, 30 September 1999 6:33
> To: [EMAIL PROTECTED]
> Subject: Re: ResultSet-Session
>
> Just an extra side not while i'm at it.
>
> When I attempt to replace the following line
>
> ps = conn.prepareStatement("SELECT ename, rowid FROM emp WHERE rowid >
> ?");
>
> with
>
> ps = conn.prepareStatement("SELECT ename, rowid FROM emp WHERE rowid > ?
> FOR UPDATE");
>
> My servlet returns a "ORA-01002 Fetch out of sequence" error. Basically
> I'm trying to lock the records until my select is finished. Any ideas?
>
> Regards,
>
> Robert
>
___________________________________________________________________________
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff SERVLET-INTEREST".
Archives: http://archives.java.sun.com/archives/servlet-interest.html
Resources: http://java.sun.com/products/servlet/external-resources.html
LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
