Say I want to create a form that displays information from a stored procedure. Can I do this, or must the dataprovider be a table?
Stored procedures aren’t supported.
Can you give us an example on how you would like to handle/implement Stored procedures in Servoy.
In case more requests come from developers we will put it higher on prio list.
Thanks for your input.
I will try to explain in detail. We currently run SQL 7.0 in a small Windows intranet environment (~40 machines). The clients run a front end compiled in VB 6.0 to access and modify data in the database. We are evaluating Servoy because we would like to give our remote sales people access to our database via a web browser.
We control all access to the data tables by using stored procedures. They are used to return the resultsets to the user for viewing, and for updating the tables with the user’s changes. The users do not have direct access to the tables, all the permissions are set on the stored procedure objects themselves.
The main form we use is one that displays our Merchants. When a user wants to see orders for this Merchant, the request is sent to the DB via a stored procedure with parameters that are used as criteria in the WHERE clause that return only orders belonging to that specific Merchant, as well as related data from other tables via a JOIN statement.
How would you suggest going about a redesign of the front end application and back end database to take advantage of the Servoy product?
Thank you,
Scott Shell
National Data Funding Corp.
SShell@ndfc.com
My first impression is to migrate your forms into Servoy without the necessity of stored procedures. All SQL is handled by Servoy, joins can be done via relations between tables, very complex SQL can also be done combining your own manually typed SQL with Servoy’s SQL generator. . Not having all the details about your solution, I can’t oversee what effort it would take migrating to Servoy, but you’ll surely gain a lot of flexibility, having a very intuitive, graphical programming interface.
Servoy has build in security for users, but you may also decide to handle this from your back end database.
There’s no need to leave MS SQL, it works fine with Servoy.
However there are also Free SQL databases (Firebird, Postgres, mySQL) that perform good (or even better). Some members on this forum will be happy to share their experiences with you.
If you would like some more info/advice about migration to Servoy you can also contact us via mail (info@servoy.com)
We’ll be happy to assist you. Thanks for your interest in our product!
Does Servoy support SQL Server views?
With the large amount of data we store, we separate it into discreet tables by month and the view automatically avoids un-necessary table scans because it knows via table constraints not to scan a table containing july data when it’s looking for data in September for example.
I would think that support for stored procedured AND views would be highly desireable. Because we store our business rules mainly in stored procedures, we have been able to solve many an issue with a simple change to a stored procedure without having to re-distribute a new front end to every one of our clients. The use of stored procedures has also enabled us to make changes without breaking the current front end.
Another benefit of stored procedures is they are pre-compiled and optimized ahead of time.
I would think Servoy should consider stored procedure support a priority considering the high number of businesses that use SQL server who similarly benefit by storing business rules inside of stored procedures.
Currently we don’t support views, mainly because views don’t expose a primary key which Servoy needs to be able to operate. We are investigating to support them if you can manually define which column in that particular view can be used as a unique key.
Couple of other things:
How would you envision the integration of stored procedures in Servoy? Keep in mind that Servoy does an awfull lot of work for you when you use SQL based tables, how would you expect data to be hooked up to SP resultsets? How would you hook it back up for inserts, related sets, deletes, updates and sorting?
I don’t agree with your statement on views, other than precompiling the sql statement the view has no performance benefits, Servoy uses precompiled (prepared) statements as well so apart from the initial query there should be no performance difference. It IS possible to create indexes on views (on most databases) that could improve performance in specific areas. To improve performance on large tables first optimize your indexes then choose for table partitioning both are fully compatible with Servoy.
I can see benefits of using Stored Procedures to get the work done but in my opinion there are more disavantages than benefits by using SP’s. I don’t think its a good idea to store your business rules inside the database. Yes it keeps you more in control, yes it does give some speed benefits (in 99% of the cases achievable by other means as well) but it makes you very inflexible to use multiple tools that apply different types of business logic depending on the context.
Bottom line: IF there is a good way to integrate stored procedures into Servoy, there is demand and it is achieveable we will certainly do it. Meanwhile I recommend you to investigate using as little stored procedures as possible. I personally have been involved in projects that used them extensively and projects that avoidded them as much as possible an my personal experience was that things went much smoother if Stored procedures were avoided for business rules.
Hi,
I would like to be able to use a form to accept the required stored procedure input parameters from the user, i.e. a separate input box for each input parameter correctly labeled on the form.
Then it would be very useful if the form could call the stored procedure (submit button) in the connected SQL database and pass the input parameters to the stored procedure.
Also it would be good if the form could receive and display any ouput parameters or return codes from the stored procedure.
This would allow the intelligence to be built into the SQL database and use Servoy as a graphical interface.
If this is not currently possible, please indicate when this very useful functionality will be developed.
Thank you
Roger
With Servoy 2.0 (you can find a beta version on developer.servoy.com in the download section) you can accomplish what you want.
You can retrieve results of a stored procedure as HTML and render that on a label or in a field with display type html area.
You can create your input boxes by putting enough fields on one layout (global variables) to fit the maximum input parameters. Then using a method you can hide/show fields as necessary and dynamically execute stored procedures.