In the previous section, you have learnt about developing Virtual DataPort custom functions. Denodo Platform also provides an API to develop stored procedures written in Java. The process to create a stored procedure is very similar to the one you followed in the previous section, as you only have to use the Denodo4E plugin.
Basically, a stored procedure is a program containing a logic which is going to be used by several clients.
Typically is composed by a group of VQL queries. Let's see an example, imagine we have this requirement:
In these cases, it makes sense to define the logic inside of a stored procedure so external applications could execute it directly (the execution is centralized in a single point and we obtain other benefits, like for example, reducing the network traffic between the Denodo server and the client).
Now let’s see the development process of a Denodo Stored Procedure. In this tutorial, you are going to create a simple stored procedure
GetClients to return all the Clients of type "Residential".
New > Other. In the dialog, select
Denodo Extensionand click on Next >:
Denodo VDP Stored Procedureas Extension and give a package (
com.denodo.vdp.custom.sp) and class name (
GetClients.javafile gets created in the Package Explorer.
GetClients.java, you could see that a template to implement the stored procedure was created. By default, the template includes a Java code for doubling a number passed as imput parameter:
doCallmethods for modifying the list of input/output paramenters and for implementing the logic.
ssnof the clients:
SELECT name, surname, ssn FROM client
WHERE client_type = '01';
Ok, once the procedure is ready, as you know, it has to be deployed into Virtual DataPort. You have to follow the same steps you did for deploying the custom function:
File > Refreshto load the changes. You will see a new Stored Procedure in the
You have done an excellent work in completing the tutorial for Denodo Custom components.