>
> 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
