Thread: updateable resultset only working for tables?

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.

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?

If possible i would like to avoid creating my own insert/update-Strings and
let it do the driver.

Guido

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

Am Donnerstag, 11. März 2004 14:00 schrieb Kris Jurka:
> > 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

I would tell it this way maybe:

ResultSet.setPrimaryKeys(String[] pks);

After this the driver should trust me and simply go on.

Now how do i know about the primary keys? Well - i can whether define a
certain naming scheme for my application
-or-
am more nice and general solution would be to massage postgres
system-catalogues until i get a view containing the primary keys of a view.

The "algorithm" is easily written, but not as easy to do, i know...

If we would end up with a select-statement which holds all the primary keys of
all tables used in a view (recursively) with their alias-names it would just
be the right thing. Yes? No?

> 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.

So it looks now...

Guido

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

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)

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

Views are updateable with RULES only...

However, maybe i went the completly the wrong way choosing views for this
problem:

I want that certain groups can only update/select certain columns in tables,
and that's what views are for.

Thanks, for your help.
Guido

Am Donnerstag, 11. März 2004 15:29 schrieb Dave Cramer:
> 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
>

Guido, Dave,

This should work for views as well (assuming as is the case here rules
are applied to the view making the view itself updateable).

The logic in the driver first looks for a primary key on the underlying
object of the select statement and if a primary key is found then see if
the select includes the primary key columns.  If it does then use these
columns in building subsequent update statements.

However if either the table doesn't have a primary key, or the primary
key columns are not selected by the query, the driver falls back to
looking for a column in the select named 'oid' and it will use that as
the primary key for subsequent updates.

So as long as the select statement has a selected column named 'oid'
whose datatype is compatable with the oid datatype (i.e. I think integer
would also work, but I doubt varchar would), then the driver should just
go ahead and use this column in building its update statements.

So in the case at hand, I think the driver should work, however the
rules defined on the view will probably need to be different to allow
the view to be updated by the following type of update statement that
the driver would generate:
update <view> set bar = ? where oid = ?

thanks,
--Barry
Dave Cramer wrote:
> 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)
>>