Thread: Using views and MS access via odbc
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;
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/
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
> -----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
> -----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
"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
> -----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);
"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
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); >
> -----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
> -----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
> > 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