About rules on views and M$ Access

Поиск
Список
Период
Сортировка
От Davide Romanini
Тема About rules on views and M$ Access
Дата
Msg-id 3E28114D.4050203@libero.it
обсуждение исходный текст
Ответы Re: About rules on views and M$ Access  (Jeff Eckermann <jeff_eckermann@yahoo.com>)
Список pgsql-odbc
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




В списке pgsql-odbc по дате отправления:

Предыдущее
От: Bruce Momjian
Дата:
Сообщение: Re: Linux IDE's
Следующее
От: "Tambet Matiisen"
Дата:
Сообщение: Re: About rules on views and M$ Access