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