Using views and MS access via odbc

From: Ron Snyder <snyder(at)roguewave(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Using views and MS access via odbc
Date: 2002-05-02 02:11:35
Message-ID: F888C30C3021D411B9DA00B0D0209BE8026E3075@cvo-exchange.cvo.roguewave.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

I've got a table, view, and rules as below. The permissions are set up in
such a way that I can use it just fine as myself via psql. When I try to
access the data using an ms access interface via odbc, I get the first
record in the view, but any attempts to go to other records cause ms access
to tell me that they've been deleted (it's lying though, because I can still
see them through the psql interface).

I thought I had seen a mention on one of the web pages that said some
interfaces don't handle views very well, but can't seem to find that page
again (in hopes that it also had some suggestions to get around the
problem).

Here are my questions:
1) Is this a known problem?
2) Am I doing something wrong?
3) Are there work arounds?

-ron

create table log (
id serial not null,
whenentered timestamp,
username name not null default user,
class varchar(10),
entry varchar
);

create view myview as select id,whenentered,class,entry from log where
username=user;

create rule ins_mylog as on insert to myview do instead insert into log
(whenentered,class,entry) values (now(), NEW.class, NEW.entry);

-- create the rule that will actually do an update on the right record
create rule upd_mylog as on update to myview do instead update log set
whenentered=NEW.whenentered, class=NEW.class, entry=NEW.entry where
id=OLD.id;

-- create a rule that satisfies postgres' need for completeness
create rule upd_mylog0 as on update to myview do instead nothing;

create rule del_mylog0 as on delete to myview do instead nothing;
create rule del_mylog as on delete to myview do instead delete from log
where id=OLD.id;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2002-05-02 02:21:48 Re: aggregate on zero rows slow?
Previous Message GB Clark 2002-05-02 00:12:22 Re: [GENERAL] Fwd: Postfix Relay Hub SMTP server: errors from pos

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2002-05-02 02:58:16 Re: insert with multiple targetLists
Previous Message Nigel J. Andrews 2002-05-02 02:05:41 Re: Schemas: status report, call for developers