Skip site navigation (1) Skip section navigation (2)

Re: updateable resultset only working for tables?

From: Guido Fiala <guido(dot)fiala(at)dka-gmbh(dot)de>
To: pg(at)fastcrypt(dot)com
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: updateable resultset only working for tables?
Date: 2004-03-11 14:21:36
Message-ID: 200403111521.36122.guido.fiala@dka-gmbh.de (view raw or flat)
Thread:
Lists: pgsql-jdbc
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(at)postgresql(dot)org)


In response to

Responses

pgsql-jdbc by date

Next:From: Dave CramerDate: 2004-03-11 14:29:13
Subject: Re: updateable resultset only working for tables?
Previous:From: Dave CramerDate: 2004-03-11 13:37:29
Subject: Re: updateable resultset only working for tables?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group