Обсуждение: JDBC - update count

I have a relation with snapshots of rows from another relation. I want to
update the original relation to new values only if it is still equal to its
snapshot. For that, I send an update in a PreparedStatement, along these
lines:

UPDATE rel
SET col1=?, col2=?, col3=?, col4=?
FROM snapshot
WHERE snapshot.id = ?
    AND rel.col1 = snapshot.col1
    AND rel.col2 = snapshot.col2
    AND rel.col3 = snapshot.col3
    AND rel.col4 = snapshot.col4

Well, I assume that if the row in rel is different from the snapshot, I
will get a row count of zero returned from executeUpdate(). Instead, it
returns 1 - whether or not the update has actually affected any row.

I checked - the original row is not updated. No other has the new values,
so no other has been affected. And yet, it returns 1 for both a successful
and an unsuccessful update.

Is this my problem (misinterpretation of the executeUpdate API)? Or maybe
it's a bug in the JDBC driver? Or maybe it's a PostgreSQL bug?

Any alternative to doing the above? I'd really hate to break it into two
statements like:

SELECT rel.oid
FROM snapshot, rel
WHERE .....

and then

UPDATE rel
SET .....
WHERE oid = ?

Because I don't want to use oids if I can avoid it (there's talk about
their removal, and they are too system-specific anyway), and I'm not even
sure that this form of update won't give me the same result.

Herouth

On Mon, 12 Jan 1998, Herouth Maoz wrote:

> I have a relation with snapshots of rows from another relation. I want to
> update the original relation to new values only if it is still equal to its
> snapshot. For that, I send an update in a PreparedStatement, along these
> lines:
>
> UPDATE rel
> SET col1=?, col2=?, col3=?, col4=?
> FROM snapshot
> WHERE snapshot.id = ?
>     AND rel.col1 = snapshot.col1
>     AND rel.col2 = snapshot.col2
>     AND rel.col3 = snapshot.col3
>     AND rel.col4 = snapshot.col4
>
> Well, I assume that if the row in rel is different from the snapshot, I
> will get a row count of zero returned from executeUpdate(). Instead, it
> returns 1 - whether or not the update has actually affected any row.

I think it should return 0 also.

> I checked - the original row is not updated. No other has the new values,
> so no other has been affected. And yet, it returns 1 for both a successful
> and an unsuccessful update.
>
> Is this my problem (misinterpretation of the executeUpdate API)? Or maybe
> it's a bug in the JDBC driver? Or maybe it's a PostgreSQL bug?

I'll look into it, and get back to you.

--
Peter T Mount  petermount@earthling.net or pmount@maidast.demon.co.uk
Main Homepage: http://www.demon.co.uk/finder
Work Homepage: http://www.maidstone.gov.uk Work EMail: peter@maidstone.gov.uk