Thread: Bug Attached Table Ms Access97 with Boolean
Hello!
Sorry for my English.
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:
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;
"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;
"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););
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_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";);
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;
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
"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"();
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;
$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;