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

Re: My MS-Access problem keeps getting weirder and

From: "Peter Bense" <Ptbense(at)gwm(dot)sc(dot)edu>
To: <greg(dot)campbell(at)us(dot)michelin(dot)com>
Cc: <pgsql-odbc(at)postgresql(dot)org>
Subject: Re: My MS-Access problem keeps getting weirder and
Date: 2005-05-18 17:29:09
Message-ID: (view raw or whole thread)
Lists: pgsql-odbc
This is what I have been reflecting on for awhile.  I spoke to some individuals in #postgresql about the possibility of using a trigger, stored procedure, or something like that.  [Which honestly I have no experience in implementing.]

The rules method seems to be the most straightfoward implementation, however * but perhaps that's a false presumption?  As I understand it a rule is a type of procedure in and of itself.  Is this understanding correct?  If so, what would prevent me from being able to execute more than one INSERT per rule?


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

>>> "Greg Campbell" <greg(dot)campbell(at)us(dot)michelin(dot)com> 5/17/2005 5:24:14 PM >>>
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;
> 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.
> 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.


pgsql-odbc by date

Next:From: Peter BenseDate: 2005-05-18 18:56:34
Subject: I hate MS-Access & ODBC.
Previous:From: Zoltan BoszormenyiDate: 2005-05-17 22:26:48
Subject: Re: PsqlODBC problem with complex query

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