I hate MS-Access & ODBC. - Mailing list pgsql-odbc

Scenario 1:

Inserting via ODBC in MS-Access on the following view FAILS:

afl=# \d vi_tblpis_survey_receipt
View "public.vi_tblpis_survey_receipt"
    Column     |   Type   | Modifiers
---------------+----------+-----------
 ppt_id        | integer  |
 date_received | date     |
 staff_id      | integer  |
 survey_type   | smallint |
 is_blank      | boolean  |
 birth_month   | smallint |
 birth_year    | smallint |
View definition:
 SELECT tblpis_survey_receipt.ppt_id,
tblpis_survey_receipt.date_received, tblpis_survey_receipt.staff_id,
tblpis_survey_receipt.survey_type, tblpis_survey_receipt.is_blank,
tblpis_survey_receipt.birth_month, tblpis_survey_receipt.birth_year
   FROM tblpis_survey_receipt
  ORDER BY tblpis_survey_receipt.insertion;
Rules:
 tblpis_survey_receipt_in AS
    ON INSERT TO vi_tblpis_survey_receipt DO INSTEAD ( INSERT INTO
tblpis_survey_receipt (ppt_id, date_received, staff_id, survey_type,
is_blank, birth_month, birth_year, check_ppt, check_dob, check_tracking,
date_inserted, date_modified)
  VALUES (new.ppt_id, new.date_received, new.staff_id, new.survey_type,
new.is_blank, new.birth_month, new.birth_year, false, false, false,
now(), now());
 UPDATE tblpis_survey_receipt SET check_ppt = true
  WHERE (tblpis_survey_receipt.ppt_id IN ( SELECT
tblpis_participant.ppt_id
           FROM tblpis_participant));
 UPDATE tblpis_survey_receipt SET check_dob = true
  WHERE tblpis_survey_receipt.ppt_id = new.ppt_id AND
tblpis_survey_receipt.check_ppt = true AND (tblpis_survey_receipt.ppt_id
IN ( SELECT tblpis_participant.ppt_id
           FROM tblpis_participant
          WHERE tblpis_survey_receipt.ppt_id =
tblpis_participant.ppt_id AND tblpis_survey_receipt.birth_month =
tblpis_participant.birth_month AND tblpis_survey_receipt.birth_year =
tblpis_participant.birth_year));
 UPDATE tblpis_survey_receipt SET check_tracking = true
  WHERE NOT (tblpis_survey_receipt.ppt_id IN ( SELECT
tblpis_tracking.ppt_id
           FROM tblpis_tracking));
 UPDATE tblpis_survey_receipt SET check_tracking = true
  WHERE tblpis_survey_receipt.survey_type = 1::smallint AND NOT
(tblpis_survey_receipt.ppt_id IN ( SELECT tblpis_tracking.ppt_id
           FROM tblpis_tracking
          WHERE tblpis_tracking.pre_rc_date IS NOT NULL));
 UPDATE tblpis_survey_receipt SET check_tracking = true
  WHERE tblpis_survey_receipt.survey_type = 2::smallint AND NOT
(tblpis_survey_receipt.ppt_id IN ( SELECT tblpis_tracking.ppt_id
           FROM tblpis_tracking
          WHERE tblpis_tracking.post_rc_date IS NOT NULL));
 INSERT INTO tblpis_tracking (ppt_id, pre_rc_date, pre_rc_id,
pre_is_blank)  SELECT tblpis_survey_receipt.ppt_id,
tblpis_survey_receipt.date_received, tblpis_survey_receipt.staff_id,
tblpis_survey_receipt.is_blank
           FROM tblpis_survey_receipt
          WHERE tblpis_survey_receipt.ppt_id = new.ppt_id AND
tblpis_survey_receipt.survey_type = 1 AND
tblpis_survey_receipt.check_ppt = true AND
tblpis_survey_receipt.check_dob = true AND
tblpis_survey_receipt.check_tracking = true;
 INSERT INTO tblpis_tracking (ppt_id, post_rc_date, post_rc_id,
post_is_blank)  SELECT tblpis_survey_receipt.ppt_id,
tblpis_survey_receipt.date_received, tblpis_survey_receipt.staff_id,
tblpis_survey_receipt.is_blank
           FROM tblpis_survey_receipt
          WHERE tblpis_survey_receipt.ppt_id = new.ppt_id AND
tblpis_survey_receipt.survey_type = 2 AND
tblpis_survey_receipt.check_ppt = true AND
tblpis_survey_receipt.check_dob = true AND
tblpis_survey_receipt.check_tracking = true AND NOT (new.ppt_id IN (
SELECT tblpis_tracking.ppt_id
                   FROM tblpis_tracking));
 UPDATE tblpis_tracking SET post_rc_date = new.date_received,
post_rc_id = new.staff_id, post_is_blank = new.is_blank
  WHERE (new.ppt_id IN ( SELECT tblpis_survey_receipt.ppt_id
           FROM tblpis_survey_receipt
          WHERE tblpis_survey_receipt.ppt_id = new.ppt_id AND
tblpis_survey_receipt.survey_type = 2 AND
tblpis_survey_receipt.check_ppt = true AND
tblpis_survey_receipt.check_dob = true AND
tblpis_survey_receipt.check_tracking = true));
)
=============================================================================
NOTE THE ERROR FROM MY psqlodbc log (why the hell does it send ROLLBACK
query types??

conn=141965240, query='INSERT INTO  "public"."vi_tblpis_survey_receipt"

("ppt_id","date_received","staff_id","survey_type","is_blank","birth_month","birth_year")
VALUES (10001,'1942-12-29'::date,2,1::int2,'0',12::int2,1977::int2)'
conn=141965240, query='ROLLBACK'
conn=141965240, query='INSERT INTO  "public"."vi_tblpis_survey_receipt"

("ppt_id","date_received","staff_id","survey_type","is_blank","birth_month","birth_year")
VALUES (10001,'1942-12-29'::date,2,1::int2,'0',12::int2,1977::int2)'
conn=141965240, query='ROLLBACK'
conn=141965240, query='INSERT INTO  "public"."vi_tblpis_survey_receipt"

("ppt_id","date_received","staff_id","survey_type","is_blank","birth_month","birth_year")
VALUES (10001,'1942-12-29'::date,2,1::int2,'0',12::int2,1977::int2)'
conn=141965240, query='ROLLBACK'
conn=141965240, query='INSERT INTO  "public"."vi_tblpis_survey_receipt"

("ppt_id","date_received","staff_id","survey_type","is_blank","birth_month","birth_year")
VALUES (10002,'2005-01-01'::date,2,2::int2,'0',8::int2,1194::int2)'
conn=141965240, query='ROLLBACK'

-- NOTE HOWEVER, a similar insert WORKS FINE FROM PSQL:

afl=# INSERT INTO  "public"."vi_tblpis_survey_receipt"
("ppt_id","date_received","staff_id","survey_type","is_blank","birth_month","birth_year")
VALUES (10006,'2005-01-01'::date,2,2::int2,'0',8::int2,1194::int2);
INSERT 0 0
afl=# select * from vi_tblpis_survey_receipt ;
 ppt_id | date_received | staff_id | survey_type | is_blank |
birth_month | birth_year
--------+---------------+----------+-------------+----------+-------------+------------
      1 | 1977-12-29    |        2 |           2 | f        |
12 |       1977
      2 | 2004-05-24    |        2 |           2 | f        |
12 |       1969
     99 | 2005-05-16    |        2 |           1 | f        |
12 |       1944
 100001 | 2005-05-18    |        2 |           1 | f        |
8 |       1938
 100002 | 2005-05-18    |        2 |           1 | f        |
10 |       1922
 100002 | 2005-05-19    |        2 |           1 | f        |
10 |       1922
     99 | 1977-12-29    |        2 |           1 | f        |
12 |         77
   1000 | 1977-12-29    |        2 |           1 | f        |
12 |         77
 100001 | 1977-12-29    |        2 |           2 | f        |
8 |       1938
  10001 | 1977-12-29    |        2 |           2 | f        |
8 |       1938
  10002 | 2005-01-01    |        2 |           2 | f        |
8 |       1194
  10005 | 2005-01-01    |        2 |           2 | f        |
8 |       1194
  10006 | 2005-01-01    |        2 |           2 | f        |
8 |       1194
(13 rows)

afl=#
=============================================================================

Scenario 2:

HOWEVER, if I change the behavior of the RULE slightly [namely, remove
the last 3 steps -- the INSERT INTO, INSERT INTO , UPDATE), it works
fine.  Just to verify, here's how the rule looks with changes applied:

afl=# \d vi_tblpis_survey_receipt
View "public.vi_tblpis_survey_receipt"
    Column     |   Type   | Modifiers
---------------+----------+-----------
 ppt_id        | integer  |
 date_received | date     |
 staff_id      | integer  |
 survey_type   | smallint |
 is_blank      | boolean  |
 birth_month   | smallint |
 birth_year    | smallint |

View definition:
 SELECT tblpis_survey_receipt.ppt_id,
tblpis_survey_receipt.date_received, tblpis_survey_receipt.staff_id,
tblpis_survey_receipt.survey_type, tblpis_survey_receipt.is_blank,
tblpis_survey_receipt.birth_month, tblpis_survey_receipt.birth_year
   FROM tblpis_survey_receipt
  ORDER BY tblpis_survey_receipt.insertion;
Rules:
 tblpis_survey_receipt_in AS
    ON INSERT TO vi_tblpis_survey_receipt DO INSTEAD ( INSERT INTO
tblpis_survey_receipt (ppt_id, date_received, staff_id, survey_type,
is_blank, birth_month, birth_year, check_ppt, check_dob, check_tracking,
date_inserted, date_modified)
  VALUES (new.ppt_id, new.date_received, new.staff_id, new.survey_type,
new.is_blank, new.birth_month, new.birth_year, false, false, false,
now(), now());
 UPDATE tblpis_survey_receipt SET check_ppt = true
  WHERE (tblpis_survey_receipt.ppt_id IN ( SELECT
tblpis_participant.ppt_id
           FROM tblpis_participant));
 UPDATE tblpis_survey_receipt SET check_dob = true
  WHERE tblpis_survey_receipt.ppt_id = new.ppt_id AND
tblpis_survey_receipt.check_ppt = true AND (tblpis_survey_receipt.ppt_id
IN ( SELECT tblpis_participant.ppt_id
           FROM tblpis_participant
          WHERE tblpis_survey_receipt.ppt_id =
tblpis_participant.ppt_id AND tblpis_survey_receipt.birth_month =
tblpis_participant.birth_month AND tblpis_survey_receipt.birth_year =
tblpis_participant.birth_year));
 UPDATE tblpis_survey_receipt SET check_tracking = true
  WHERE NOT (tblpis_survey_receipt.ppt_id IN ( SELECT
tblpis_tracking.ppt_id
           FROM tblpis_tracking));
 UPDATE tblpis_survey_receipt SET check_tracking = true
  WHERE tblpis_survey_receipt.survey_type = 1::smallint AND NOT
(tblpis_survey_receipt.ppt_id IN ( SELECT tblpis_tracking.ppt_id
           FROM tblpis_tracking
          WHERE tblpis_tracking.pre_rc_date IS NOT NULL));
 UPDATE tblpis_survey_receipt SET check_tracking = true
  WHERE tblpis_survey_receipt.survey_type = 2::smallint AND NOT
(tblpis_survey_receipt.ppt_id IN ( SELECT tblpis_tracking.ppt_id
           FROM tblpis_tracking
          WHERE tblpis_tracking.post_rc_date IS NOT NULL));
)

... THINGS INSERT CLEANLY!  WTF? WTF?

conn=141965240, query='INSERT INTO  "public"."vi_tblpis_survey_receipt"

("ppt_id","date_received","staff_id","survey_type","is_blank","birth_month","birth_year")
VALUES (10002,'2005-01-01'::date,2,2::int2,'0',8::int2,1194::int2)'
conn=141965240, query='COMMIT'
conn=141965240, query='SELECT
"ppt_id","date_received","staff_id","survey_type","is_blank","birth_month","birth_year"
 FROM "public"."vi_tblpis_survey_receipt"  WHERE "ppt_id" = 10002 AND
"date_received" = '2005-01-01'::date AND "survey_type" = 2::int2'
    [ fetched 1 rows ]
conn=141965240, query='SELECT
"ppt_id","date_received","staff_id","survey_type","is_blank","birth_month","birth_year"
 FROM "public"."vi_tblpis_survey_receipt"  WHERE "ppt_id" = 10002 AND
"date_received" = '2005-01-01'::date AND "survey_type" = 2::int2'
    [ fetched 1 rows ]

Peter T. Bense - Teradata Certified Professional
(ptbense@gwm.sc.edu) - 803-777-9476
Database Administrator/Webmaster
Prevention Research Center
University of South Carolina