I have begun to use Servoy since january, and i am still testing the solutions this product can give. I use postgres like database and i have seen that if i define views in my database, Servoy see these views, but can i use them like normal tables, or is it different ? I have made a form and when i have saved data, i have received a message error “cannot save data for views” or something similar…
Has someone worked with views ?
I dont know what Servoy does with views, but I know that not with all views data can be saved! Views with Aggregations are not able to save data in the db. The SQL of the view would help to say if it is a problem of the view.
Regards, Stef
Hi Michele, Servoy makes no difference between views and tables but you need to be sure that your view is updatable. In postgres you can use rules for that, here’s a quick example:
-- Rule: delete_rule ON notass1l
CREATE OR REPLACE RULE delete_rule AS
ON DELETE TO notass1l DO INSTEAD DELETE FROM notass0f
WHERE notass0f.cdass = old.cdass::bpchar;
-- Rule: insert_rule ON notass1l
CREATE OR REPLACE RULE insert_rule AS
ON INSERT TO notass1l DO INSTEAD INSERT INTO notass0f (catass, cdass, descr, categ, keywrd, cuser, cdate, muser, mdate, grupnt, flpbbl)
VALUES (new.catass, new.cdass, new.descr, new.categ, new.keywrd, new.cuser, new.cdate, new.muser, new.mdate, new.grupnt, new.flpbbl);
-- Rule: update_rule ON notass1l
CREATE OR REPLACE RULE update_rule AS
ON UPDATE TO notass1l DO INSTEAD UPDATE notass0f SET catass = new.catass, cdass = new.cdass, descr = new.descr, categ = new.categ, keywrd = new.keywrd, cuser = new.cuser, cdate = new.cdate, muser = new.muser, mdate = new.mdate, grupnt = new.grupnt, flpbbl = new.flpbbl
WHERE notass0f.cdass = old.cdass::bpchar;
But keep in mind that as already pointed out by Stef not all views are updatable.
In the previous example the view is used to get rid of annoying CHARS columns so we always have a 1-1 match against the original table and that allows to update/delete/insert easily:
CREATE OR REPLACE VIEW notass1l AS
SELECT rtrim(notass0f.catass::text)::character varying(1) AS catass, rtrim(notass0f.cdass::text)::character varying(15) AS cdass, rtrim(notass0f.descr::text)::character varying(50) AS descr, rtrim(notass0f.categ::text)::character varying(50) AS categ, rtrim(notass0f.keywrd::text)::character varying(50) AS keywrd, notass0f.cuser, notass0f.cdate, notass0f.muser, notass0f.mdate, rtrim(notass0f.grupnt::text)::character varying(15) AS grupnt, notass0f.flpbbl
FROM notass0f;
Servoy makes no difference between views and tables
Although it pays to mention that views are cached after the first time they are loaded (unless something has changed in recent versions of Servoy)?
What do you mean by “cached”? Views behave the same way that tables do as far as I know.
ngervasi:
What do you mean by “cached”? Views behave the same way that tables do as far as I know.
Please see: Views are Cached! - Classic Servoy - Servoy Community
That’s always been the case also for tables if they get updated from outside Servoy but if you update the views in Servoy everything works perfectly without the need of any refresh, also databroadcast works like a breeze.
The point is why do you need to use views instead of tables? We do to get rid of all the hassles that char columns generate and we are very happy with the result.
ngervasi:
That’s always been the case also for tables if they get updated from outside Servoy but if you update the views in Servoy everything works perfectly without the need of any refresh, also databroadcast works like a breeze.
The point is why do you need to use views instead of tables? We do to get rid of all the hassles that char columns generate and we are very happy with the result.
Whether the data is updated from within or external to Servoy, I do not believe views broadcast at all.
See Paul’s comment from the thread I referenced earlier:
Due to the fact that JDBC through which servoy connects to the database does not allow you to retrieve the underlying SQL statement of the view and because vieuws can display data from multiple tables in aggregated and grouped format, it is not possible for Servoy to know with column in which record in the view maps to what record in which table in the database.
Therefor, it’s not possible for servoy to take care of databroadcasting/keeping the datacache in sync.
Johan/Paul/Jan maybe something has changed?
jbader:
Whether the data is updated from within or external to Servoy, I do not believe views broadcast at all
Confirmed. We use some views based on data from different tables.
Those tables are updated from withing Servoy, however you need to fetch the foundset from the server again to have the view updated.
Guys, maybe I was not clear enough: I’m not updating the underlying tables, I’m directly updating the view and databroadcast works perfectly. My development environment is using Postgres, the production and test servers are using DB2/400, the solution is in production from june and now we have an average of 60 connected users from Italy, France, Russia, Switzerland and Dubai, trust me, if it didn’t work you would have seen my dead body hangin’…
It’s obvious that if the view is based on some tables and you update those tables directly, even from inside servoy, you need to refresh the data to see the changes, it’s happening to me as well for one view based on a UNION ALL query between two tables, but that’s not the case if you update the view directly. You only need some database rules (postgres) or triggers (DB2/400) that takes care of updating the underlying tables properly.
I posted some examples at the beginning of this thread, you can try and see yourself.