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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Cramer | 2004-03-11 14:29:13 | Re: updateable resultset only working for tables? |
Previous Message | Dave Cramer | 2004-03-11 13:37:29 | Re: updateable resultset only working for tables? |