Thread: Bug Attached Table Ms Access97 with Boolean

Bug Attached Table Ms Access97 with Boolean

From
Ярослав Якубовский
Date:
Hello!
Sorry for my English.
 
Test on server PG 8.3.0.1 and ODBC driver 8.3.100. MS Acces 97 + service pack 2.
 
I has trouble: with BOOLEAN when INSERT INTO VIEW - after INSERT state affected records is locked and don't edit by manually. It's edit only by SQL. It's appear if there is a Boolean field in VIEW and SQL don't include it's.
By Examle:
 
CREATE TABLE "public"."firm" (
  "id" INTEGER NOT NULL,
  "is_active" BOOLEAN DEFAULT false,
  "firm" VARCHAR(150),
  CONSTRAINT "firms_idx_pkey" PRIMARY KEY("id")
) WITH OIDS;
 
------------------------
 
CREATE VIEW "public"."vw_firm_bool" (
    "id",
    "is_active",
    "firm")
AS
SELECT f."id", f."is_active", f."firm
FROM firm f;
 
CREATE RULE "vw_firm_bool_rl" AS ON INSERT TO "public"."vw_firm_bool"
DO INSTEAD (INSERT INTO firm ("id", "is_active", "firm") VALUES (new."id", new."is_active", new."firm););
 
/* working version
CREATE RULE "vw_firm_bool_rl" AS ON INSERT TO "public"."vw_firm_bool"
DO INSTEAD (INSERT INTO firm ("id", "is_active", "firm") VALUES (new."id", COALESCE(new."is_active", false), new."firm););
*/
 
CREATE RULE "vw_firm_bool_rl1" AS ON UPDATE TO "public"."vw_firm_bool"
DO INSTEAD (UPDATE firm SET "is_active" = new."is_active", "firm" = new."firm" WHERE firm."id" = new."id";);
 
CREATE RULE "vw_firm_bool_rl2" AS ON DELETE TO "public"."vw_firm_bool"
DO NOTHING;
 
-----------------
 
If I attached view "vw_firm_bool_rl" in MS Access 97 as linked table "vw_firm_bool_rl" and execute SQL query:
"INSERT INTO vw_firm_bool_rl (id, firm) VALUES (15, 'new firms')"
(explicit pair: "is_active - false" is apsent) then is_active in table = NULL  (not false, not true) and record is locked by opening in table mode in MS Access "vw_firm_bool_rl" by manually
 
WorkAround:
1. Explicit list all Boolean fields in SQL Query "Insert" on side attached table
2. Pg - Rule on view INSERT - COALESCE(new . "<boolean>", false)
3. CREATE TRIGGER "table1_tr" BEFORE INSERT
ON "public"."table1" FOR EACH ROW
EXECUTE PROCEDURE "public"."table1_tr_date"();
 
CREATE OR REPLACE FUNCTION "public"."table1_tr_date" () RETURNS trigger AS
$body$
BEGIN
  /* New function body */
       -- Remember when changed
        NEW.tms := current_timestamp;
        NEW."is_active" := COALESCE(NEW."is_active",  false); 
        -- wanted DEFAULT  VALUE this field, i.e. COALESCE(NEW."is_active", <DEFAULT>, false)
        RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
 

Yaroslav Yakubovskij
E-mail: quick_yak@21centure.ru
ICQ: 327185501