Re: updateable resultset only working for tables?

Actually, Guido, I put a hack in the driver that if you do

select oid, * from table it should work.

Dave
On Thu, 2004-03-11 at 08:00, Kris Jurka wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
--
Dave Cramer
519 939 0336
ICQ # 14675561