Thread: Updating views : cannot figure out what goes wrong
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
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.
--- 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