Re: updateable resultset only working for tables? - Mailing list pgsql-jdbc

Sorry Guido, I misunderstood, I thought you wanted a updateable
statement on a table without a primary key.

views in postgres are not updateable... so until that is done you are
unfortunately beat.

The updateable result set interface specifies that only simple result
sets are updateable, and "simple" varies from db to db.

Dave
On Thu, 2004-03-11 at 09:21, Guido Fiala wrote:
> Am Donnerstag, 11. März 2004 14:37 schrieb Dave Cramer:
> > Actually, Guido, I put a hack in the driver that if you do
> >
> > select oid, * from table it should work.
>
> I tried without success so far -
>
> "create view someview as select oid, * from table;"
>
> i assume?
>
> What version of driver do i need?
>
> Guido
>
> >
> > 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