JDBC: problems using FETCH FIRST and FOR UPDATE clause at the same time.

[Acupuncture]

Hi,
I am developing a JDBC application and I encountered this problem (DB2
for ISeries).
I want to do a select for update and also use the fetch first rows clause.
This is my sql statement:

SELECT UsrNbr, UsrPwd, UsrPwdChgD, UsrEmail, UsrChgUsrI, UsrChgDte FROM
USERS WHERE UsrNbr = ? FETCH FIRST 1 ROWS ONLY FOR UPDATE

This runs fine (the cursor gets this name 'P00022'), but when I execute the
update where current of:

UPDATE USERS SET UsrPwd=?, UsrPwdChgD=?, UsrEmail=?, UsrChgUsrI=?,
UsrChgDte=? WHERE CURRENT OF P00022

It throws the following error:
'Cursor P00022 for file GUSBRO1 read-only.'

If I take off the 'FETCH FIRST 1 ROWS' from the SELECT CLAUSE the update
works fine.

Is there a way to use both the FETCH FIRST 1 ROWS ONLY and the FOR UPDATE
clauses and still do the update ?

My DB version:
DB2 UDB for AS/400 version 05.01.0000 V5R1m0
My JDBC driver:
AS/400 Toolbox for Java JDBC Driver version 5.0

Regards,
Gustavo

[Bernd Hohmann]

Acupuncture wrote:

SELECT UsrNbr, UsrPwd, UsrPwdChgD, UsrEmail, UsrChgUsrI, UsrChgDte FROM
USERS WHERE UsrNbr = ? FETCH FIRST 1 ROWS ONLY FOR UPDATE
[...]
It throws the following error:
'Cursor P00022 for file GUSBRO1 read-only.'

From the iSeries UDB reference:

"Specification of the fetch-first-clause in a select-statement makes the
result table read-only. A read-only result table must not be referred to
in an UPDATE or DELETE statement. The fetch-first-clause cannot appear
in a statement containing an UPDATE clause."

I found an answer but I don't know a solution for you :-(

Bernd
--
"Ja, alles meine Herren" sprach Fürst Lichnowsky. "Ooch det roochen?"
"Ja, auch das Rauchen." "Ooch im Tiergarten?" "Ja, auch im Tiergarten
darf geraucht werden, meine Herren." Und so endeten die Barrikadenkämpfe
des 18. März in Berlin

[Eugene F]

Instead of FETCH FIRST in the cursor, try using ROW_NUMBER() OVER()
like

SELECT UsrNbr, UsrPwd, UsrPwdChgD, UsrEmail, UsrChgUsrI, UsrChgDte
FROM (
SELECT UsrNbr, UsrPwd, UsrPwdChgD, UsrEmail, UsrChgUsrI, UsrChgDte,
ROW_NUMBER() OVER() rn
FROM USERS WHERE UsrNbr = ?
) x WHERE rn = 1 FOR UPDATE

I am not sure though if iSerires supports that.

Regards,
-Eugene

[Serge Rielau]

Bernd Hohmann wrote:

Acupuncture wrote:

SELECT UsrNbr, UsrPwd, UsrPwdChgD, UsrEmail, UsrChgUsrI, UsrChgDte
FROM USERS WHERE UsrNbr = ? FETCH FIRST 1 ROWS ONLY FOR UPDATE

[...]

It throws the following error:
'Cursor P00022 for file GUSBRO1 read-only.'

From the iSeries UDB reference:

"Specification of the fetch-first-clause in a select-statement makes the
result table read-only. A read-only result table must not be referred to
in an UPDATE or DELETE statement. The fetch-first-clause cannot appear
in a statement containing an UPDATE clause."

I found an answer but I don't know a solution for you :-(

The only thought I have on this is to use a searched update instead of
a cursor update.
Or you leave away the FETCH FIRST 1 ROW and simply close the cursor
after the single update.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[Eugene F]

Serge is right, you could try something like this (update a view) in
one single statement:

UPDATE (
SELECT UsrNbr, UsrPwd, UsrPwdChgD, UsrEmail, UsrChgUsrI, UsrChgDte
FROM
USERS WHERE UsrNbr = ? FETCH FIRST 1 ROWS ONLY
) x
SET x.UsrPwd=?, x.UsrPwdChgD=?, x.UsrEmail=?, x.UsrChgUsrI=?,
x.UsrChgDte=?

-Eugene

[Serge Rielau]

Eugene F wrote:

Serge is right, you could try something like this (update a view) in
one single statement:

UPDATE (
SELECT UsrNbr, UsrPwd, UsrPwdChgD, UsrEmail, UsrChgUsrI, UsrChgDte
FROM
USERS WHERE UsrNbr = ? FETCH FIRST 1 ROWS ONLY
) x
SET x.UsrPwd=?, x.UsrPwdChgD=?, x.UsrEmail=?, x.UsrChgUsrI=?,
x.UsrChgDte=?

-Eugene

Uh.. that's not what I meant... I'm quite certian that this does not
work in DB2 iSeries.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[doug.partch]

No for the "for update statement". It won't work because the "fetch
first" turns it into a read only statement.

One question I have is, what is the primary key to the table and can
the where statement be quailified with this key. If so you will always
only get back one row which you can use the "for update statement on".

[Eugene F]

This one is working fine on V8.2.3 LUW:

db2 => create table t(c1 int, c2 varchar(10))
DB20000I The SQL command completed successfully.
db2 =>
db2 => insert into t values(1, '111'), (2, '222'), (4, '444'), (3,
'333')
DB20000I The SQL command completed successfully.
db2 =>
DB20000I The SQL command completed successfully.
db2 => select * from t

C1 C2
----------- ----------
1 111
2 222
4 444
3 333

4 record(s) selected.

db2 => update (select c2 from t where c1 > 2 fetch first 1 row only) x
set x.c2 = '000'
DB20000I The SQL command completed successfully.
db2 =>
db2 => select * from t

C1 C2
----------- ----------
1 111
2 222
4 000 <---- fetch first 1 row only
3 333

4 record(s) selected.

So it allowed to do the searched update with fetch first rows. Don't
see why it shouldn't in a proc.

-Eugene

[Eugene F]

Oh... sorry... it, of course, may not work on iSeries, wich I can't
test but maybe someone could run that tiny test on iSeries and let us
know :-))

Regards,
-Eugene

[Serge Rielau]

Eugene F wrote:

Oh... sorry... it, of course, may not work on iSeries, wich I can't
test but maybe someone could run that tiny test on iSeries and let us
know :-))

Regards,
-Eugene

I am quite confident thsi test will not work on iSeries.
In DB2 for LUW was introduced only in DB2 V8.1.4.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab