Thread: Using views and MS access via odbc

Using views and MS access via odbc

From
Ron Snyder
Date:
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;

Re: Using views and MS access via odbc

From
Hiroshi Inoue
Date:
Ron Snyder wrote:
>
> 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).

Are you using 7.2 ?
Your settings probably worked well under 7.1 but
doesn't in 7.2 due to the following change in
tcop/postgres.c.

     /*
      * It is possible that the original query was removed due to
      * a DO INSTEAD rewrite rule.  In that case we will still have
      * the default completion tag, which is fine for most purposes,
      * but it may confuse clients if it's INSERT/UPDATE/DELETE.
      * Clients expect those tags to have counts after them (cf.
      * ProcessQuery).
      */
      if (strcmp(commandTag, "INSERT") == 0)
              commandTag = "INSERT 0 0";
      else if (strcmp(commandTag, "UPDATE") == 0)
              commandTag = "UPDATE 0";
      .
      .

 * UPDATE 0 * means no tuple was updated.

regards,
Hiroshi Inoue
    http://w2422.nsk.ne.jp/~inoue/

Re: Using views and MS access via odbc

From
Tom Lane
Date:
Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> Your settings probably worked well under 7.1 but
> doesn't in 7.2 due to the following change in
> tcop/postgres.c.

AFAIR, there is only a visible change of behavior for
INSERT/UPDATE/DELETE queries, not for SELECTs.  So I don't think
this change explains Ron's complaint.

            regards, tom lane

Re: Using views and MS access via odbc

From
"Hiroshi Inoue"
Date:
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
>
> Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> > Your settings probably worked well under 7.1 but
> > doesn't in 7.2 due to the following change in
> > tcop/postgres.c.
>
> AFAIR, there is only a visible change of behavior for
> INSERT/UPDATE/DELETE queries, not for SELECTs.  So I don't think
> this change explains Ron's complaint.

For a view a_view

  UPDATE a_view set ... where xxxxx;
returns UPDATE 0 in any case in 7.2.

The psqlodbc driver understands that no row was updated
and returns the info to the upper application if requested.
MS access( and I) think there's no such case other than
the row was changed or deleted after it was SELECTed.
Note that MS access doesn't issue any SELECT commands
to check the optimistic concurrency of the row. The where
clause of the UPDATE command contains *a_item = old_value*
for all items to check the optimisitic concurrency at the
same time.

regards,
Hiroshi Inoue

Re: Using views and MS access via odbc

From
"Hiroshi Inoue"
Date:
> -----Original Message-----
> From: Tom Lane
>
> Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> > Your settings probably worked well under 7.1 but
> > doesn't in 7.2 due to the following change in
> > tcop/postgres.c.
>
> AFAIR, there is only a visible change of behavior for
> INSERT/UPDATE/DELETE queries, not for SELECTs.  So I don't think
> this change explains Ron's complaint.

If you'd not like to change the behavior, I would change it, OK ?

regards,
Hiroshi Inoue

Re: Using views and MS access via odbc

From
Tom Lane
Date:
"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> If you'd not like to change the behavior, I would change it, OK ?

To what?  I don't want to simply undo the 7.2 change.

            regards, tom lane

Re: Using views and MS access via odbc

From
"Hiroshi Inoue"
Date:
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
>
> "Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> > If you'd not like to change the behavior, I would change it, OK ?
>
> To what?  I don't want to simply undo the 7.2 change.

What I'm thinking is the following makeshift fix.
I expect it solves Ron's case though I'm not sure.
Returning UPDATE 0 seem to make no one happy.

regards,
Hiroshi Inoue

*** postgres.c.orig    Thu Feb 28 08:17:01 2002
--- postgres.c    Sat May  4 22:53:03 2002
***************
*** 805,811 ****
                      if (DebugLvl > 1)
                          elog(DEBUG, "ProcessQuery");

!                     if (querytree->originalQuery)
                      {
                          /* original stmt can override default tag string */
                          ProcessQuery(querytree, plan, dest, completionTag);
--- 805,811 ----
                      if (DebugLvl > 1)
                          elog(DEBUG, "ProcessQuery");

!                     if (querytree->originalQuery || length(querytree_list) == 1)
                      {
                          /* original stmt can override default tag string */
                          ProcessQuery(querytree, plan, dest, completionTag);


Re: Using views and MS access via odbc

From
Tom Lane
Date:
"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> If you'd not like to change the behavior, I would change it, OK ?
>>
>> To what?  I don't want to simply undo the 7.2 change.

> What I'm thinking is the following makeshift fix.
> I expect it solves Ron's case though I'm not sure.
> Returning UPDATE 0 seem to make no one happy.

Agreed, that doesn't seem like it's going over well.  Let's see, you
propose returning the tag if there is only one replacement query, ie,
we had just one DO INSTEAD rule.  [ thinks... ]  I guess the only thing
that bothers me about this is the prospect that the returned tag is
completely different from what the client expects.  For example,
consider a rule like ON UPDATE DO INSTEAD INSERT INTO history_table...
With your patch, this would return an "INSERT nnn nnn" tag, which'd
confuse a client that expects an "UPDATE nnn" response.  (This is one
of the issues that prompted changing the behavior to begin with.)

Would it be reasonable to allow the rewritten query to return a tag
only if (a) it's the only query, per your patch AND (b) it's the same
query type as the original, unrewritten query?

            regards, tom lane

Re: Using views and MS access via odbc

From
Ron Snyder
Date:
Although I can't tell for sure, I really don't think it's the output of the
UPDATE 0 that is causing the problem. I configured the server to log all
queries last night, and it looks to me like it (MS Access) is doing stupid
stuff. (Like issuing a select on all fields (but not *), and then issuing a
giant select to make sure that all the records are still there.) When I

At this point, I'm suspecting that the problem may be more related to my
inexperience with Access than with postgres.  Tom had thought that the
problem might be related to the lack of a column that access recognized as
the unique identifier, and I finally found a page last night that discusses
the exact behavior that I'm seeing. (Although I still haven't figured out
how to fix it.)

http://joelburton.com/resources/pgaccess/faq.html  has some mention of the
problem and a link to the MS knowledge base, but I'm seeing behavior from
the MS client that leads me to believe the problem is closer to the user. :)
(I tried to set up the view so that the user couldn't change the ID or set
the timestamp of the record (each record is a work journal entry, so there's
an ID as well as a timestamp.))

-ron "who's off to sub to pgsql-odbc now"


> -----Original Message-----
> From: Hiroshi Inoue [mailto:Inoue@tpf.co.jp]
> Sent: Saturday, May 04, 2002 7:09 AM
> To: Tom Lane
> Cc: Ron Snyder; pgsql-general@postgresql.org; pgsql-hackers
> Subject: RE: [GENERAL] Using views and MS access via odbc
>
>
> > -----Original Message-----
> > From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> >
> > "Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> > > If you'd not like to change the behavior, I would change it, OK ?
> >
> > To what?  I don't want to simply undo the 7.2 change.
>
> What I'm thinking is the following makeshift fix.
> I expect it solves Ron's case though I'm not sure.
> Returning UPDATE 0 seem to make no one happy.
>
> regards,
> Hiroshi Inoue
>
> *** postgres.c.orig    Thu Feb 28 08:17:01 2002
> --- postgres.c    Sat May  4 22:53:03 2002
> ***************
> *** 805,811 ****
>                       if (DebugLvl > 1)
>                           elog(DEBUG,
> "ProcessQuery");
>
> !                     if (querytree->originalQuery)
>                       {
>                           /* original
> stmt can override default tag string */
>
> ProcessQuery(querytree, plan, dest, completionTag);
> --- 805,811 ----
>                       if (DebugLvl > 1)
>                           elog(DEBUG,
> "ProcessQuery");
>
> !                     if
> (querytree->originalQuery || length(querytree_list) == 1)
>                       {
>                           /* original
> stmt can override default tag string */
>
> ProcessQuery(querytree, plan, dest, completionTag);
>

Re: Using views and MS access via odbc

From
"Hiroshi Inoue"
Date:
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
>
> "Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> > If you'd not like to change the behavior, I would change it, OK ?
> >>
> >> To what?  I don't want to simply undo the 7.2 change.
>
> > What I'm thinking is the following makeshift fix.
> > I expect it solves Ron's case though I'm not sure.
> > Returning UPDATE 0 seem to make no one happy.
>
> Agreed, that doesn't seem like it's going over well.  Let's see, you
> propose returning the tag if there is only one replacement query, ie,
> we had just one DO INSTEAD rule.  [ thinks... ]  I guess the only thing
> that bothers me about this is the prospect that the returned tag is
> completely different from what the client expects.  For example,
> consider a rule like ON UPDATE DO INSTEAD INSERT INTO history_table...
> With your patch, this would return an "INSERT nnn nnn" tag, which'd
> confuse a client that expects an "UPDATE nnn" response.

Is it worse than returning "UPDATE 0" ?
Unfortunately "UPDATE 0" never means the result is unknown
but clearly means no rows were affected. It can never be safe
to return "UPDATE 0".

regards,
Hiroshi Inoue

Re: Using views and MS access via odbc

From
"Hiroshi Inoue"
Date:
> -----Original Message-----
> From: Ron Snyder [mailto:snyder@roguewave.com]
>
> Although I can't tell for sure, I really don't think it's the
> output of the UPDATE 0 that is causing the problem.

You may have other problems.
However you can't get expected results anyway
as long as you are using ordinary updatable views
in 7.2.

regards,
Hiroshi Inoue

Re: Using views and MS access via odbc

From
Ron Snyder
Date:
> > Although I can't tell for sure, I really don't think it's the
> > output of the UPDATE 0 that is causing the problem.
>
> You may have other problems.
> However you can't get expected results anyway
> as long as you are using ordinary updatable views
> in 7.2.

You're right.  When I finally got through _my_ problem (I wasn't paying
attention to the MS access dialog box-- it was asking me to identify the
unique ID, and I was thinking it was asking me to select the columns I
wanted to see from the linked table), I hit the problems that I think you
are referring to (and the ms access timestamp(3) limitation).

-ron