I am evaluating Servoy for potential use with a very large database with many views and stored procedures.
I have seen how I can write methods to populate forms from stored procedures, but that is extremely time consuming.
I would really like to use views and stored procedures with the ease with which tables are used.
How do I do this? I would really like to use this product, but that is a deal-breaker for my developers.
Can I take this silence to mean that this feature is not available?
Hi,
It is currently not possible to base a form on a view or stored procedure.
Sorry.
Why is it so difficult for your developers to base the forms on a table rather than a view? By basing them on tables - and then using tabpanels - you can maintain editability all the way through the chain - unlike with views or stored procs.
There are several reasons that we want to use stored procedures, including security, speed, etc. Several of our queries involve multiple tables and we would like to show all of the data in one grid.
Our stored procedures also enforce security. For example, if you don’t have a certain subscription level, you get an entirely different query and results.
I wish our database were so simple that we could just query a table and be done with it. To be honest, I am very surprised that there is anyone with any appreciable size database that does not have queries than span multiple tables.
It seems that it would be simple to allow read-only queries based on views/procs.
Thank you very much for your response.
I believe that the ability to create forms from stored procs and views (or anything else that will generate column names and a PK ID) is on the planning list for the next major rev of the product.
However, in the meantime, if all you want is a readonly view - why not just use a HTML grid to show the data? You can have hyperlinks in that view that can trigger Servoy methods to create a “drill down” of certain data.
It’s very flexible and easy to do.
Hope this helps.
I understand that tab panels / portals are ways to view ONE to MANY and that relationships allow me to join to tables ONE level above or below, so there is no way to show the results of a VERY normalized database with 2 levels above or below the table for the FORM on (1) line.
An example would be
Orders Relate to Customers
Customers relate to Regions
I want to show on 1 line, all Orders, the customer and the region name.
(Yes, I know I can have a portal or Tab but this limits the UI flexibility!)
Bruce
Hi Bruce,
I know what you’re saying - but that’s NOT TRUE.
Assuming you have two relations:
orders_to_customers
customers_to_regions
Assume the field you want to display is “region” from the regions table related to the customer related to the order.
Place a LABEL with the text below and DO CHECK “displayTags” property:
%%orders_to_customers.customers_to_regions.region%%
Done.
Wow, I did not know that you can use the dot notation to go more than 1 level up or below. Is this mentioned anywhere in the manuals or am I blind?
thanks
blomasky:
Wow, I did not know that you can use the dot notation to go more than 1 level up or below. Is this mentioned anywhere in the manuals or am I blind?thanks
That’s standard behaviour of object oriented environments.
jaleman:
blomasky:
Wow, I did not know that you can use the dot notation to go more than 1 level up or below. Is this mentioned anywhere in the manuals or am I blind?thanks
That’s standard behaviour of object oriented environments.
Jan & Bob:
I would also like to see support for views, so please add my name to the list. Materialized views in Oracle are very powerful, and many views can be operated on in more or less the same way as a single flat table. There are many reasons for views instead of tables, including security, filtering, and basic ease of reporting. I understand there may some limitations in editing certain types of tables in Servoy, but that would be a reasonable limitation.
Here’s an example where I would like to use a View… perhaps you have an ingenious way around it. I basically need to have fast reference to a set of unique values from a large table. I tried referencing it as a value list, but servoy seems to build it every time the value list might need to be referenced. As a result the initial load of the Form its used on is slow, and when attempting to select values from the list, it never seems to build. It has about 3000 values in it (building from a table of approx 800,000 records). In addition, I need to be able to view and report on this list very quickly.
I can easily construct a view, and this “table” is always up to date and is very quickly accessible in Oracle. But Servoy will not allow me to use this view in a form directly (I know you’ve mentioned some tricks, but I have not learned them yet). I ended up building a seperate table to do this, but, I have to maintain it through triggers on the DB, which is a great deal of work that would otherwise not be required.
Lastly, we need to integrate with other systems in our firm and outside our firm, and in some cases, we are only given access to the data through tightly controlled views. Direct table access is never allowed. Again, we could scan and update “real” tables on some periodic basis, but this is a great deal of extra work, and if substantial changes are made in between updates, we may miss critical data at certain times. Views are truly an integral part of most SQL oriented environments.
We can certainly work around the limitation, but it would be a great time saver if it where not a requirement.
Thanks for listening! I’m looking forward to the training in Phoenix, so I can work my way out of the Servoy “pin head” realm.
Regards,
Lee
leesnover:
I would also like to see support for views, so please add my name to the list. Materialized views in Oracle are very powerful, …
Materialized views work. I’ve used them quite some times. They’re not really views but tables which is why they work. And yes, they are very powerful, and yes you can use it today in Servoy.
I maintain materialized view with triggers in PostgreSQL, but I’m careful to query them using raw SQL, since Servoy may not cache the latest verson.
The query on the original sample data took 3 secs, with my materialized view it now takes 0,00001 secs or something like that 