Thread: About rules on views and M$ Access
Hi,
I've some problems releted to updating columns of a view, from M$ Access
through ODBC. Well let me explain.
I've a view that takes data from two different tables and have some
calculated columns.
I simply want to modify data of some of the non-calculated columns, so I
wrote a rule that on the UPDATE
event on the view performs another UPDATE on the table I want to modify.
This is the view:
CREATE VIEW ordini_emessi_view AS
SELECT "Dettagli Ordini Emessi"."Ordine numero",
"Dettagli Ordini Emessi"."IDArticolo",
"Dettagli Ordini Emessi"."Quantità",
"Dettagli Ordini Emessi"."QuantitàEvasa",
"Anagrafica Articoli Tab"."CodiceAcquisto",
("Costo" * (1 - ("Sconto" / 100))) AS "CostoEffettivo",
"Anagrafica Articoli Tab"."MinimoAcquisto",
"Anagrafica Articoli Tab"."Collegamento",
"Descrizione degli articoli"."Descrizione",
"Dettagli Ordini Emessi"."PrezzoUnit",
"Descrizione degli articoli"."Unità di Fatturazione"
AS "UDM"
FROM (("Anagrafica Articoli Tab" JOIN "Dettagli Ordini Emessi"
ON (("Anagrafica Articoli
Tab"."IDArticolo" = "Dettagli Ordini Emessi"."IDArticolo")))
JOIN "Descrizione degli articoli"
ON (("Anagrafica Articoli
Tab"."Collegamento" = "Descrizione degli articoli"."IDART")));
I'd want to modify only the columns of "Dettagli Ordini Emessi" so the
rule is:
CREATE OR REPLACE RULE ordini_emessi_update AS ON UPDATE TO
ordini_emessi_view
DO INSTEAD
UPDATE "Dettagli Ordini Emessi" SET "Ordine numero"=NEW."Ordine numero",
"IDArticolo"=NEW."IDArticolo", "Quantità"=NEW."Quantità",
"QuantitàEvasa"=NEW."QuantitàEvasa", "PrezzoUnit"=NEW."PrezzoUnit"
WHERE "Ordine numero"=OLD."Ordine numero" AND
"IDArticolo"=OLD."IDArticolo";
It all works fine if I do updates in this form:
UPDATE ordini_emessi_view SET field=something, ...
WHERE "Ordine numero"=something AND "IDArticolo"=something;
I've problems using M$ Access because it sends UPDATES with a lot of
fields in the WHERE clause. It always includes the
calculated column "CostoEffettivo" that makes PostgreSQL updating nothing:
UPDATE "ordini_emessi_view" SET "Quantità"='360'::float4
WHERE "Ordine numero" = 620 AND "IDArticolo" = 3170
AND "Quantità" = '350'::float4 AND "QuantitàEvasa" = '360'::float4
AND "CostoEffettivo" = '1.44232004547119'::double precision --This
line makes problems!
AND "MinimoAcquisto" = '18'::float4 AND "Collegamento" = 2793
AND "PrezzoUnit" = '1.4423'::float8 AND "UDM" = 'mq';
Please note that if I comment that line and send the query manually to
PG it works perfectly.
I think that when PG reads this SQL it looks in the view for a row
satisfacting the WHERE conditions, then
if it founds it performs the update, elsewhere it does nothing.
But it simply doesn't find any row with
"CostoEffettivo"='1.44232004547119' and this is probably due to internal
handling of double precision numbers, I don't know exactly why. The
query successes if I cast the column "CostoEffettivo" as text.
Remember anyway that I can't change the queries the Access sends to ODBC!
Please, if you have suggestions about solving this problem, tell me!
Regards, Romaz
> > Please note that if I comment that line and send the query > manually to > PG it works perfectly. > I think that when PG reads this SQL it looks in the view for a row > satisfacting the WHERE conditions, then > if it founds it performs the update, elsewhere it does nothing. > But it simply doesn't find any row with > "CostoEffettivo"='1.44232004547119' and this is probably due > to internal > handling of double precision numbers, I don't know exactly why. The > query successes if I cast the column "CostoEffettivo" as text. > Remember anyway that I can't change the queries the Access > sends to ODBC! > > Please, if you have suggestions about solving this problem, tell me! > This is because Access uses so called "optimistic locking". It remembers the last values of record and updates the recordONLY when noone hasn't changed it. I use similar approach in my application and I also have this problem. Actually, this wouldn't be problem, if the view had primary key. Because generally only those fields get added to WHERE clause,that have been changed (you never change "CostoEffettivo"?). But in case of view, all fields are added, because thisis the only possible way to identify the changed record. If you try the same thing on ordinary table, you see that onlyprimary key and old value of changed field are in where clause: primary key to identify the record and old value to implementoptimistic locking. I'm aware of only one solution to this problem - create table with the same fields as view and make sure it has a primarykey. Then create SELECT and UPDATE rules on that table with appropriate SELECT and UPDATE queries. This tricks Accessto think that the view has primary key. You cannot just add primary key to existing view. As you see, this is quite ugly and incompatible solution. But only alternative I know is to change database design. Tambet
--- Davide Romanini <romaz@libero.it> wrote: (snipped) > But it simply doesn't find any row with > "CostoEffettivo"='1.44232004547119' and this is > probably due to internal > handling of double precision numbers, I don't know > exactly why. The > query successes if I cast the column > "CostoEffettivo" as text. > Remember anyway that I can't change the queries the > Access sends to ODBC! > Not quite true: you could always use unbound forms etc, though that lets you in for a lot of extra work ;-) Your queries suggest that "CostoEffettivo" is a display field, i.e. it is not used in computations and will not be updated directly. If so, try casting it to text in your view definition, and have it displayed as text in your form. At least the value should agree that way, so your updates would succeed. As for controlling the way Access composes its SQL: I don't know whether that is possible, perhaps you could post the question to an Access NG/List. __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com
Jeff Eckermann ha scritto: > Not quite true: you could always use unbound forms > etc, though that lets you in for a lot of extra work > ;-) > Your queries suggest that "CostoEffettivo" is a > display field, i.e. it is not used in computations and > will not be updated directly. If so, try casting it > to text in your view definition, and have it displayed > as text in your form. At least the value should agree > that way, so your updates would succeed. > As for controlling the way Access composes its SQL: I > don't know whether that is possible, perhaps you could > post the question to an Access NG/List. > Thanks I've solved in this way. The field "CostoEffettivo" is used in the form only to do a calcul in VBA, I only had to place a Val() in that piece of code. Some note about the very stupid behaviour of Access. When I link an ODBC table, if it has not a unique identifier such a primary key, Access asks me for that. If I don't choose any field Access will refuse to make any update or insert in the table. So, I think, if I choose the primary key fields for the table, Access will use only that fields to do updates. But, NO, Access will use practically all the fields of the table (really not all the fields, don't know what criteria it uses to choose them), so I ask: why it needs to know a unique identifier when it link tables? It could simply use *always* all the fields of the table as unique identifier! Anyway, thanks a lot for your answers. Bye, Romaz