Re: updateable resultset only working for tables?

On Thu, 11 Mar 2004, Guido Fiala wrote:

> First i tried to update/insert records using an updateable resultset using a
> table as datasource, that works just fine.
>
> My application requires to do updates also to views which are created
> about as this:
>
> create view sometable_update as Select * from sometable;
> create rule sometable_update_rule as on update to sometable_update do instead
> update sometable set ...;
>
> Updating to sometable_update does work if i type an update statement myself.
>
> However, using code like this:
>
> Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
> ResultSet.CONCUR_UPDATABLE);
> ResultSet urs=stmt.executeQuery("SELECT * FROM sometable_update");
>
> results in an SQLException "no primary keys".
>
> Basically i assume, that this happens because the driver is not able to
> detect the primary keys of a view, which is mainly because postgres
> stores views in a completly different way as tables.

Well, views don't have primary keys, just like SELECT statements don't
have primary keys. A view is completely opaque to the JDBC driver, it
doesn't know if the view definition is from multiple tables or involves
calculations, so it can't drill through it and update the base tables.
Now with your example you have provided an on update rule which alleviates
the multiple tables and calculations problems, but we still have no
primary key.

> Is there a way to tell the driver the primary keys manually (as i know
> them at this point in my code) for a certain ResultSet/Statement or
> another solution to this problem?

How would you propose providing this information to the driver? If you
have the primary key columns, you should also have the non-pk columns
making an update simple. You're just going to have to bite the
bullet on this one and either avoid the view or write an update statement
yourself.

Kris Jurka