RE: [GENERAL] Updating a view - Mailing list pgsql-odbc

From Ryan C. Bonham
Subject RE: [GENERAL] Updating a view
Date
Msg-id 19AB8F9FA07FB0409732402B4817D75A038A6B@FILESERVER.SRF.srfarms.com
Whole thread Raw
List pgsql-odbc
Ok, I understand that, i think. :)  Why i was wiating for a resonce i
decided to link the VB form to a select statment on a table.. I get the same
error message.. Ex.

Given

 table foo (
 a    integer NOT NULL,
 name    varchar(50) NOT NULL
 comment varchac(50) NOT NULL
 );

Select * from name = 'Joe'

If i try to switch records after making chages to comment i get the error
(-2147217842).. Only other thing that might effect this is that i have other
tables/views open in subforms that are related to this table.. Do i possible
need to close thoose connections first..(I don't see why i would, as they
should requery when i move the the next record anyways)...

Thanks for the help.


> -----Original Message-----
> From: Pete Leonard [mailto:pete@hero.com]
> Sent: Wednesday, August 15, 2001 12:22 PM
> To: Ryan C. Bonham
> Cc: pgsql-odbc@postgresql.org; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Updating a view
>
>
>
> Ryan,
>
> Have you created a rule for updating the view?
>
> In short - because views are derived from an underlying set
> of tables, the
> database cannot safely insert/update/delete through a view until rules
> defining exactly what to do are created.
>
> Given:
>
> table foo (
> a    integer NOT NULL,
> name    varchar(50) NOT NULL
> );
>
> table bar (
> b    integer NOT NULL,
> nameb    varchar(50) NOT NULL
> );
>
> create view foobar as
> select f.name, b.nameb, f.a from foo f, bar b where f.a = b.b;
>
> the rule to handle an update would be something like:
>
> create rule update_foobar as on update to foobar
> do instead (
> update foo set name=NEW.name where a = NEW.a;
> update bar set nameb=NEW.nameb where b = NEW.b;
> );
>
>
> Check out the postgres documentation for rules & views -
> it'll cover all
> of this.
>
>     --pete
>
>
>
> On Wed, 15 Aug 2001, Ryan C. Bonham wrote:
>
> > Hi,
> >
> > Ok i know this is something stupid, somebody mind
> explaining it to me? Also
> > sorry if this gets posted twice, i got a error back the first time..
> >
> > I have a Visual Basic Project that access a PostgreSQL
> Datbase. One Form
> > pulls information from a View in postgres. If teh user
> changes anything on
> > the form the update fails. I tried running hte dame update
> from PGAdmin and
> > it also fails with the following message. I just dont
> understand what i need
> > to create i guess..  Thanks for the help..
> >
> > Ryan
> >
> > *******************************************************
> > * pgAdmin v7.1.0: Error - 2001-07-19 11:31:42
> > *******************************************************
> >
> > Error Details
> > *************
> > Error Number: -2147217842
> > Error Description: Operation was canceled.
> > Error Source: Microsoft OLE DB Provider for ODBC Drivers
> > Subroutine of Function: frmODBCLogon, cmdOK_Click
> >
> > System Details
> > **************
> > Operating System: Windows NT v5.0 Build 2195
> > Additional Info: Service Pack 2
> >
> >
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> >
>
>

pgsql-odbc by date:

Previous
From: "Ryan C. Bonham"
Date:
Subject: Updating a view
Next
From: Cedar Cox
Date:
Subject: Re: Changing ODBC users with Access doesn't work correctly for me