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
called GetClients
to return all the Clients of type "Residential".
client
and client_type
.my_denodo_extensions
.New > Other
. In the dialog, select Denodo Extension
and click on Next >:Denodo VDP Stored Procedure
as Extension and give a package (com.denodo.vdp.custom.sp
)
and class name (GetClients
):GetClients.java
file 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:getParameters
and doCall
methods
for modifying the list of input/output paramenters and for implementing the logic.name
, surname
and
ssn
of the clients:SELECT name, surname, ssn FROM client
WHERE client_type = '01';
this.environment.log(LOG_ERROR, e.getMessage());
.
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:
my_denodo_extensions
and select Deploy Extensions
.File > Refresh
to load the changes. You will
see a new Stored Procedure in the tutorial
database!Execute
and click Ok
.
You have done an excellent work in completing the tutorial for Denodo Custom components.
Thanks!