Procedures

Overview

An overview of how to enable, view and execute database stored procedures from within Servoy.

Get Started

When enabled for a database server the stored procedures are shown in the solution explorer. To do this find and open the database server that you need. Under the advanced server settings you will see the option to Enable procedures, check the box.

Make sure to Restart the Developer after doing this. Once you've restarted the developer, If your database has stored procedures they should show up when the Procedures node of the database server is selected.

Calling a Stored Procedure

Once you have enabled stored procedures, you can use them in the scripting context. You can use code completion under datasources.sp.servername.storedprocedurename. The method in this case will return a JSDataset for further use.

Create In-Memory Datasource

If your stored procedure returns a dataset you can generate the schema for an inmemory datasource (temp table) in Servoy. Simply right-click the stored procedure and select "Create inmem table from procedure". In the example below a datasource is created and prefilled with two columns which are of type text. The stored procedure must declare these two out parameters as arguments.

Working with in-memory datasources

In-memory datasources work the same way as any other datasource with the exception that they have no data to begin with. At the end of a client session the data in a datasource is deleted and the table is dropped. Each session has their own copy of the datasource and it is not shared.

We are able to manipulate it just as we can with any other table. All foundset API's will work the same way. It also has it's own entity scope to deal with business logic. For example we can use the onLoad event initialize and load data when it's first accessed.

Remarks

Note that the logic for stored procedures is coded and dependent on the database itself. If there are issues with the logic of the stored procedure, it should be resolved within the database first. Servoy is simply used to find and execute the procedure.