Thread: problem with update rules on a view (ODBC)
Dear list,
I am trying to create a small applications using a PostgreSQL 7.2 database,
updated through ODBC from an OpenOffice form.
I have to work on a small (about 300 rows) subset of a larger (about 10000
rows) ser of records, encompassing many tables. in the subset, one has to
make updates to a boolean field, using (informally : this has to be decided
by a human (a physician, in fact) after reading the information).
I created a view defining the subset of interest, which has two column : an
identifier (primary key of the underlying table), and the boolean variable
of interest. The main OpenOffice form is based on this view, and uses
subforms to display in a convenient manner the records of the other tables
associated with the record of the main table under examination.
I created two update rules on the view : the first one is conditional : its
WHERE clause tests for a change of the boolean variable
(new.value!=old.value) and, if so, updates INSTEAD the underlying table
WHERE primarykey=new.primarykey ; the second rule, unconditional, does
INSTEAD NOTHING. The first rule uses typecasts (OpenOffice insists to use
'0' and '1' as a representation of booleans).
The form works OK, displays the correct set of rows and correctly
associates subrecords with the main record. However, I cannot update
through the form. OpenOffice detects an error during the update.
I tried my rules "by hand" in psql : the update works as advertised in the
docs. However, I noticed that updating the *real" table gives a message :
UPDATE 1
after updating the table, while updating the view gives the message
UPDATE 0
after "updating" the view (i. e. updating the underlying table).
The same thing happens when I create a single non-conditional INSTEAD rule
(reporting the condition in the where clause of the update query, which is
possible in this case) : the record gets updated, and I get an "UPDATE 0"
message.
This might be the source of my problems : if ODBC returns something to the
effect of "zero records updated", OpenOffice has reasons to report an error.
What do I do wrong ?
Is that a known problem ?
Is there a workaround ?
Sincerely,
Emmanuel Charpentier
PS : Culd you please Cc: me your answers : I'm following the list (from
time to time) through the Newsgroup interface ... E. C.
--
Emmanuel Charpentier
Emmanuel Charpentier <charpent@bacbuc.dyndns.org> writes: > I am trying to create a small applications using a PostgreSQL 7.2 database, > updated through ODBC from an OpenOffice form. > ... > I tried my rules "by hand" in psql : the update works as advertised in the > docs. However, I noticed that updating the *real" table gives a message : > UPDATE 1 > after updating the table, while updating the view gives the message > UPDATE 0 > after "updating" the view (i. e. updating the underlying table). You can control this in 7.3 by adjusting the rule firing order (which you do by choosing the names you give to the rules); see http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/rules-status.html I am not sure there is any good workaround in 7.2. regards, tom lane
Tom Lane wrote: > Emmanuel Charpentier <charpent@bacbuc.dyndns.org> writes: > >>I am trying to create a small applications using a PostgreSQL 7.2 database, >>updated through ODBC from an OpenOffice form. >>... >>I tried my rules "by hand" in psql : the update works as advertised in the >>docs. However, I noticed that updating the *real" table gives a message : >>UPDATE 1 >>after updating the table, while updating the view gives the message >>UPDATE 0 >>after "updating" the view (i. e. updating the underlying table). > > > You can control this in 7.3 by adjusting the rule firing order (which > you do by choosing the names you give to the rules); see > http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/rules-status.html Thanks a lot ! I will give it a shot. However, I'm a bit reluctant : my test machine is Debian stable but with some unstable packages : it may accept the (unstable) Postgres 7.3 package. My production machine, however is Debian stable and has to remain so (therefore, no "official" 7.3 packages) ... Word going around is that the new libc6 (2.3.x) has some nasty problems (I've encountered some of them myself : some software suddenly broken by unmet dynamic linking dependencies ...). > I am not sure there is any good workaround in 7.2. Aaaahhhh ... Now, I'll just have to nag Oliver Elphick into making his Debian-stable-Woody PostgreSQL repository apt-get-able ... :-). Anyway, a big "Thank you" ! The "Open source" software, and especially the Postgres team, proves abgain itself much more usable than commercial solutions when it comes to support.
On Sun, 12 Jan 2003, Emmanuel Charpentier wrote: > Tom Lane wrote: > > Emmanuel Charpentier <charpent@bacbuc.dyndns.org> writes: > > > >>I am trying to create a small applications using a PostgreSQL 7.2 database, > >>updated through ODBC from an OpenOffice form. > >>... > >>I tried my rules "by hand" in psql : the update works as advertised in the > >>docs. However, I noticed that updating the *real" table gives a message : > >>UPDATE 1 > >>after updating the table, while updating the view gives the message > >>UPDATE 0 > >>after "updating" the view (i. e. updating the underlying table). > > > > > > You can control this in 7.3 by adjusting the rule firing order (which > > you do by choosing the names you give to the rules); see > > http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/rules-status.html > > Thanks a lot ! I will give it a shot. However, I'm a bit reluctant : my > test machine is Debian stable but with some unstable packages : it may > accept the (unstable) Postgres 7.3 package. My production machine, however > is Debian stable and has to remain so (therefore, no "official" 7.3 > packages) ... Word going around is that the new libc6 (2.3.x) has some > nasty problems (I've encountered some of them myself : some software > suddenly broken by unmet dynamic linking dependencies ...). Compiles fine on a server I did last week, and I even compiled it in the middle of a huge panic about building other software with interdependencies on the same box. Tests past, although I must admit to skipping the bigcheck due to the postgres install becoming the critical path on one of the things I was doing. > > > I am not sure there is any good workaround in 7.2. > > Aaaahhhh ... Now, I'll just have to nag Oliver Elphick into making his > Debian-stable-Woody PostgreSQL repository apt-get-able ... :-). I'm thinking now you are only looking for binary packages. Is building from source unacceptable or impossible? > Anyway, a big "Thank you" ! The "Open source" software, and especially the > Postgres team, proves abgain itself much more usable than commercial > solutions when it comes to support. -- Nigel J. Andrews
Nigel J. Andrews wrote:
[ ... ]
>I'm thinking now you are only looking for binary packages. Is building from
>source unacceptable or impossible?
>
On Debian, it's sometimes ... mmm ... intricate. When all is fine, it's
a breeze. But when you start to have a mix of packages from various
releases, things tend to be a bit harder. Furthermore, when you need
non-standard tools (and this is the case with Postgres, IIRC), it's
often *much* better to use the (usually marvellous) work of Debian
maintainers. After all, I'm maintaining a databas to use it, not for the
sake of maintaining it's DBMS : I use Postgres and Debian because they
are the right tool for my purposes, not for love of the tools themselves.
Sincerely,
Emmanuel Charpentier