Hi there,

First of all i'm using the postgresql 8.0rc2 on windows

I have a problem updating views and seem to be stuck.
My front-end to postgresql is ms-access and i have the following code in
my VBA

strSql = "CREATE VIEW bondetailtmp AS SELECT * FROM bondetail WHERE
subbonnr=" & Forms!bonnentest!bonnr & ";"
cn.Execute strSql
strSql = "CREATE RULE bondetailtmpupd AS ON UPDATE TO bondetailtmp " & _
"DO INSTEAD NOTHING;"
cn.Execute strSql
strSql = "CREATE RULE bondetailtmpupd2 AS ON UPDATE TO bondetailtmp " & _
"WHERE OLD.subbonbeschrijving <> NEW.subbonbeschrijving " & _
"DO (UPDATE bondetail " & _
"SET subbonbeschrijving=NEW.subbonbeschrijving);"
cn.Execute strSql
Forms!bonnentest.bondetailtmp.Form.RecordSource = "bondetailtmp"

So the recordsource for the form is set through a link I made to the
bondetailtmp view.
Now when I try to edit the date in the form and move to another record
then I get :
476 2005-01-06 18:46:34ERROR: cannot update a view

476 2005-01-06 18:46:34HINT: You need an unconditional ON UPDATE DO
INSTEAD rule.

476 2005-01-06 18:46:34STATEMENT: BEGIN;UPDATE "public"."bondetailtmp"
SET "subbonbeschrijving"='Test 1000a' WHERE "id" = 2417 AND "subbonnr"
= '4'::float4 AND "subbonlijnnr" = '410'::float4 AND "subbongroep" = ' '
AND "subbonartikelnr1" = ' 15' AND "subbonartikelnr2" = ' ' AND
"subboneenheid1" = 'st' AND "subboneenheid2" = ' ' AND "subbonaantal" =
'10'::float4 AND "subbonaantaltot" = '10'::float4 AND "subbonaantal2" =
'1'::float4 AND "subbonaantal3" = '1'::float4 AND "kleurcode" IS NULL
AND "subbonprijsbf" = '110'::float4 AND "subbonprijseuro" = '0'::float4
AND "subbontotaalbf" = '990'::float4 AND "subbontotaaleuro" =
'0'::float4 AND "berekend" IS NULL AND "berekeningen" IS NULL AND
"munt1" = 'Euro' AND "munt2" IS NULL AND "subtotaaldetail" = 410 AND
"subtotaal" = 0 AND "subtot" = 1 AND "btw" = '21'::float4 AND "tekening"
= ' ' AND "stock" = ' ' AND "produktnr" = '0'::float4 AND "produktid" =
0 AND "voorraadid" = '0'::float4 AND "rendementuur" = '0'::float4 AND
"totaaluren" = '0'::float4 AND "uur" = '0'::float4 AND "mat" =
'0'::float4 AND "matuur" = '0'::float4 AND "subbonprijsbfaankoopuur" =
'0'::float4 AND "subbonprijsbfaankoopmat" = '100'::float4 AND
"subbonprijsbfaankoopmet" = '0'::float4 AND "subbonprijsbfaankoopond" =
'0'::float4 AND "subbonprijsbfaankoopdir" = '0'::float4 AND
"subbonprijsbfaankoopbouw" = '0'::float4 AND "subbonprijsbfaankoopb1" =
'0'::float4 AND "subbonprijsbfaankoopb2" = '0'::float4 AND
"subbonprijsbfaankoopb3" = '0'::float4 AND "margeuur" = '10'::float4 AND
"margemat" = '10'::float4 AND "margemet" = '0'::float4 AND "margeond" =
'0'::float4 AND "margedir" = '0'::float4 AND "margebouw" = '0'::float4
AND "margeb1" = '0'::float4 AND "margeb2" = '0'::float4 AND "margeb3" =
'0'::float4 AND "aankoopprijs" = '1' AND "verkoopprijs" = '0' AND
"subbontotaalbfaankoop" = '1000'::float4 AND "korting" = '10'::float4
AND "datum1" IS NULL AND "aantal1" = '0'::float4 AND "datum2" IS NULL
AND "aantal2" = '0'::float4 AND "checkaantal2" = '0' AND "datum3" IS
NULL AND "aantal3" = '0'::float4 AND "checkaantal3" = '0' AND "datum4"
IS NULL AND "aantal4" = '0'::float4 AND "totaalgeleverd" = '0'::float4
AND "nogteleveren" = '0'::float4 AND "aantalgefaktureerd" = '0'::float4
AND "referentiegefaktureerd" IS NULL AND "onderaannemer" IS NULL AND
"onderaannemerh" IS NULL AND "vinkje" = '0' AND "toegewezen" = ' ' AND
"afdrukr" = '0' AND "barcode" IS NULL AND "begindatum" IS NULL AND
"einddatum" IS NULL AND "kleurprijs1" = '0'::float4 AND "kleurprijs2" =
'0'::float4 AND "kleurprijs3" = '0'::float4 AND "kleurprijs4" =
'0'::float4 AND "kleurprijs5" = '0'::float4 AND "kleurprijs6" =
'0'::float4 AND "kleurprijs7" = '0'::float4 AND "kleurprijs8" =
'0'::float4 AND "kleurprijs9" = '0'::float4 AND "kleurprijs10" =
'0'::float4 AND "stockprodukt" = '0' AND "lengte" = '1'::float4 AND
"breedte" = '1'::float4 AND "hoogte" = '1'::float4 AND "lettersb" IS
NULL AND "lettersf" IS NULL AND "zuiverekost" = '1000'::float4 AND
"afdrukuurtabel" = '1' AND "werfref" = '2410' AND "productieref" = ' '
AND "uwref" = ' ' AND "aanbesteding01" = '0'::float4 AND
"aanbesteding02" = '0'::float4 AND "aanbesteding03" = '0'::float4 AND
"aanbesteding04" = '0'::float4 AND "aanbesteding05" = '0'::float4 AND
"aanbestedingmarge" = '0'::float4 AND "aanbestedingvp" = '0'::float4 AND
"aanbestedingep" = '0'::float4 AND "aanbestedingvpa" = '0'::float4 AND
"aanbestedingepa" = '0'::float4 AND "aanbestedingmargea" = '0'::float4
AND "onderaannemeraanbesteding" IS NULL AND "bevatdetailstaat" = '0' AND
"groepselectie" = '0' AND "doorrekenenbk" = '1' AND "doorrekenenu" = '1'
AND "zuiverkostminuren" = '1000'::float4 AND "merk" IS NULL AND
"leverancier" = 0 AND "transferred" = '0' AND "nietmeerekenen" = '0

I did change the field 'subbonbeschrijving' from Test1000 into
Test1000a, so the sql query access sends seems to be ok.
What am I doing wrong. I looked in the manuel but I can't seem to find
what goes wrong.

Thanks

Dirk Cleenwerck


--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.6.9 - Release Date: 06-01-2005


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

On Fri, 7 Jan 2005, Dirk Cleenwerck wrote:

> First of all i'm using the postgresql 8.0rc2 on windows
>
> I have a problem updating views and seem to be stuck.
> My front-end to postgresql is ms-access and i have the following code in
> my VBA
>
> strSql = "CREATE VIEW bondetailtmp AS SELECT * FROM bondetail WHERE
> subbonnr=" & Forms!bonnentest!bonnr & ";"
> cn.Execute strSql
> strSql = "CREATE RULE bondetailtmpupd AS ON UPDATE TO bondetailtmp " & _
> "DO INSTEAD NOTHING;"
> cn.Execute strSql
> strSql = "CREATE RULE bondetailtmpupd2 AS ON UPDATE TO bondetailtmp " & _
> "WHERE OLD.subbonbeschrijving <> NEW.subbonbeschrijving " & _
> "DO (UPDATE bondetail " & _
> "SET subbonbeschrijving=NEW.subbonbeschrijving);"

I think you'll need to give more details or a standalone example, because
making up a simple bondetail (holding just the columns necessary for
the view and rule to work) and making the view and rules above did not
error on an update.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

--- Dirk Cleenwerck <dirk.cleenwerck@useitgroup.com>
escribió:
> Hi there,
>
> strSql = "CREATE VIEW bondetailtmp AS SELECT * FROM
> bondetail WHERE
> subbonnr=" & Forms!bonnentest!bonnr & ";"
> cn.Execute strSql
> strSql = "CREATE RULE bondetailtmpupd AS ON UPDATE
> TO bondetailtmp " & _
> "DO INSTEAD NOTHING;"
> cn.Execute strSql
> strSql = "CREATE RULE bondetailtmpupd2 AS ON UPDATE
> TO bondetailtmp " & _
> "WHERE OLD.subbonbeschrijving <>
> NEW.subbonbeschrijving " & _
> "DO (UPDATE bondetail " & _
> "SET
> subbonbeschrijving=NEW.subbonbeschrijving);"
> cn.Execute strSql
> Forms!bonnentest.bondetailtmp.Form.RecordSource =
> "bondetailtmp"
>
> 476 2005-01-06 18:46:34ERROR: cannot update a view
>
> 476 2005-01-06 18:46:34HINT: You need an
> unconditional ON UPDATE DO
> INSTEAD rule.
>
Just like the message says to you: yo have to use
CREATE RULE ... DO INSTEAD not just DO.
If you just does CREAT RULE ... DO it will defaults to
ALSO so it will try to update the view itself the DO
INSTEAD says not to touch the view but instead of that
do this stmnt.

regards,
Jaime Casanova

__________________________________________________ _______
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)