Skip site navigation (1) Skip section navigation (2)

Re: My MS-Access problem keeps getting weirder and weirder...

From: "Greg Campbell" <greg(dot)campbell(at)us(dot)michelin(dot)com>
To: Peter Bense <Ptbense(at)gwm(dot)sc(dot)edu>
Cc: pgsql-odbc(at)postgresql(dot)org
Subject: Re: My MS-Access problem keeps getting weirder and weirder...
Date: 2005-05-17 21:24:14
Message-ID: 428A60FE.8090708@us.michelin.com (view raw or flat)
Thread:
Lists: pgsql-odbc
I think of the activity you described as TRIGGER events, more than rules.
It sound a bit complicated, like you have to avoid a series of foreign 
key violations or loops or FOREIGNS where the PRIMARY side is NOT 
transacted yet.

This situation still sound like a DATA problem with a value exceeding 
the BOUNDS of what a field will hold.

Have you tried DEBUGGING with a simplistic version where each action is 
done as separate steps?  (I suppose if the rule are in place you have to 
redo you database a bit to test this.) .... I guess you have,...that's 
what your message says.

Is it possible to use the client to send each step as a statement, all 
wrapped in a transaction?

Is it possible to write a function (stored procedure) to take care of 
the combination of steps?



Peter Bense wrote:
> I want to thank everyone who has provided suggestions regarding my
> MS-Access / ODBC / Link-tables issue the past couple of days.
> 
> Here's what I've found:
> 
> 1. As my gut instincts had told me, there is no problem with the
> translations of booleans, at least given how I have been using them
> (with foreign-key lookups).
> 
> 2. Inserts into the following view work cleanly:
> 
> 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;
> 
> 3. THINGS BREAK WHEN I APPLY CERTAIN TYPES OF RULES TO THE VIEW.
> 
> The way this view is supposed to work is as follows:
> A) - A data entry person enters participant ID, survey type, date
> received, etc.
> B) - A RULE performs the following insertion:
> 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, 'f', 'f', 'f', now(),
> now());
> 
> C) - A series of checks are conducted to ensure that this data is
> valid.  Basically 4 or 5 updates are run to toggle these boolean fields
> on the PostgreSQL side.  If the participant ID is a valid participant
> ID, that field is toggled.  Once it passes that field, the month and
> year of birth are verified.  If that check is successful, then it checks
> to see whether or not there is an associated tracking record already. 
> If there isn't, it passes the tracking check.
> 
> All of those steps work fine on my test inserts, and the datavalues are
> toggled accordingly.  So far so good.
> 
> WHEN I TRY TO INSERT DATA INTO SOME OTHER TABLE FROM THAT RULE, THINGS
> BREAK!
> 
> As soon as I add an INSERT statement to my rule following the UPDATEs,
> e.g. 
> INSERT INTO tblpis_tracking (ppt_id, pre_rc_date, pre_rc_id,
> pre_is_blank)
> 	SELECT ppt_id, date_received, staff_id, is_blank
> 	FROM tblpis_survey_receipt
> 	WHERE ppt_id=new.ppt_id
> 	AND survey_type=1
> 	AND check_ppt='t'
> 	AND check_dob='t'
> 	AND check_tracking='t';
> );
> 
> ... Things break upon insert.
> 
> Why?
> 
> Can I only perform one insert per AS ON INSERT TO?
> 
> If so, this might be the cause of my problem.
> 
> 
> Help!
> 
> ./Peter
> 
> Peter T. Bense - Teradata Certified Professional
> (ptbense(at)gwm(dot)sc(dot)edu) - 803-777-9476 
> Database Administrator/Webmaster
> Prevention Research Center
> University of South Carolina
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

Attachment: greg.campbell.vcf
Description: text/x-vcard (241 bytes)

In response to

pgsql-odbc by date

Next:From: Zoltan BoszormenyiDate: 2005-05-17 22:04:01
Subject: PsqlODBC problem with complex query
Previous:From: Peter BenseDate: 2005-05-17 19:14:00
Subject: My MS-Access problem keeps getting weirder and weirder...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group