Lightning Admin Community Forums
May 27, 2011, 09:12:28 PM
Welcome, Guest. Please login or register.

Login with username, password and session length
News: Version 1.3.4 Build 20 released on January 16th, 2007
 
   Home   Help Login Register  
Pages: [1]
  Reply  |  Print  
Author Topic: MS Access and updatable views  (Read 3075 times)
Support Administrator
Administrator
PGLA Level3

Posts: 214


« on: August 02, 2006, 11:28:05 AM »
Quote

Creates a function that allows MS users to update their views without getting the error "Another user is trying to modify the data..". This error usually occurs when Rules are used to update Views.

Lets suppose we have two talbles: ONE and TWO.
ONE: A int(with sequence from autonumber), B varchar(50), C int
TWO: C int, D varchar(20), E varchar(30)

We create a view on this table:

CREATE VIEW testvw AS SELECT * FROM ONE,TWO WHERE ONE.A = TWO.A ORDER BY ONE.A;

We will have to create a function for each action that can be done on the view (INSERT,UPDATE,DELETE).

So first we create the funtion:

CREATE FUNCTION tview_in(int,varchar,int,int,varchar,varchar) /*the parameters passed to the function correspond to each column that both tables have. You have to put them all here..*/
RETURNS int AS '
DECLARE
BEGIN
INSERT INTO ONE(A,B,C) VALUES ($1,$2,$3);
INSERT INTO TWO(C,D,E) VALUES ($4,$5,%6);
RETURN 1;
END; '
LANGUAGE 'plpgsql';

Now lets create the rule that calls this function whenever we try to insert from MS Access:

CREATE RULE testvw_ins AS ON INSERT TO testvw
DO INSTEAD
SELECT tview_in('',new.n,new.c,new.d,new.e);

With this workaround MS Access will not complain about the users trying ecc....

You will have to write 3 functions and 3 rules tha call the respective functions (INSERT UPDATE DELETE) for every view that you want to make updateable.

Obviously for more complex view you will need to figure out well what you're doing atherwise you may experience some serious problems.
Logged
[email protected]
Guest
« Reply #1 on: August 22, 2007, 05:33:20 PM »
Quote

I gave it a try and it indeed works.  There is one minor bug that occurs during inserts.  When inserting the new record return displays #DELETED.  Refreshing the screen redisplays the new record. Other than that every thing is fine.

Here is the test case I used:

Code:
/************************************************

        TABLE and SEQUENCE DEFINITTIONS

*************************************************/
CREATE SEQUENCE Public.Foo_seq
   INCREMENT BY 1
       MINVALUE 0
     START WITH 0;

CREATE TABLE Public.Foo (
   id        INTEGER PRIMARY KEY
             DEFAULT nextval('public.foo_seq'),
   foo_name  TEXT
);

ALTER SEQUENCE Public.Foo_seq
      OWNED BY public.foo.id;

CREATE TABLE Public.Bar (
   id        INTEGER PRIMARY KEY
             REFERENCES public.foo( id )
     ON DELETE CASCADE,
   bar_name  text
);

/************************************************

        VIEW DEFINITTION

*************************************************/

CREATE VIEW Public.Foobar ( id, foo, bar )
         AS SELECT F.id, F.foo_name, B.bar_name
              FROM Public.Foo AS F
        INNER JOIN Public.Bar AS B
                ON F.id = B.id;

/************************************************

        Function and Rule DEFINITTIONS

*************************************************/

CREATE FUNCTION insert_Foobar( INTEGER, TEXT, TEXT )
RETURNS int AS $$
     DECLARE
       BEGIN
            INSERT INTO Public.Foo( id, foo_name )
         VALUES ( DEFAULT, $2 );
            INSERT INTO Public.Bar( id, bar_name )
         VALUES ( currval( 'Public.Foo_seq' ), $3 );
            RETURN 1;
       END; $$
LANGUAGE 'plpgsql';

    CREATE RULE insert_Foobar
AS ON INSERT TO Public.Foobar
     DO INSTEAD SELECT insert_Foobar( null, NEW.foo, NEW.bar );


CREATE FUNCTION update_Foobar( INTEGER, TEXT, TEXT )
RETURNS int AS $$
     DECLARE
       BEGIN
            UPDATE Public.Foo
       SET foo_name = $2
             WHERE id = $1;
            UPDATE Public.Bar
       SET bar_name = $3
     WHERE id = $1;
            RETURN 1;
       END; $$
LANGUAGE 'plpgsql';

    CREATE RULE update_Foobar
AS ON UPDATE TO Public.Foobar
     DO INSTEAD SELECT update_Foobar( OLD.id, NEW.foo, NEW.bar );

    CREATE RULE delete_Foobar
AS ON DELETE TO Public.Foobar
     DO INSTEAD DELETE FROM Public.Foo
              WHERE id = OLD.id;
Logged
Support Administrator
Administrator
PGLA Level3

Posts: 214


« Reply #2 on: August 22, 2007, 07:00:47 PM »
Quote

Richard,
We had the deleted problem and I believe it has something to do with the type of Primary key used in the table/view.
Access it seems does not like ODBC connections to use anthing other than a integer for the key.
What we did was added a serial rec_id for the primary key, then added unique contraints instead of doing a compound key as the primary.

After we did that the #deleted stuff went away.
Logged
Addrienne
Guest


« Reply #3 on: May 20, 2011, 11:52:03 PM »
Quote

At last! Someone who understands! Thkans for posting!
Logged
Minnie
Guest


« Reply #4 on: May 21, 2011, 01:32:24 PM »
Quote

Thatís raelly shrewd! Good to see the logic set out so well.
Logged
Pages: [1]
  Reply  |  Print  
 
Jump to:  

Powered by SMF 1.1.4 | SMF © 2006-2007, Simple Machines LLC