How to implement a Stored Procedure

Implementing Custom Components in Denodo

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.

What is a Stored Procedure and Why sometimes they are needed

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:

1. Execute a query to get a result set from a final view.
2. Execute some validation over those results based on the imput parameters
3. Execute an insert over a base view of a data source used for logging purposes (storing the result of the validation)
4. Return a summary of the execution to the calling application along with the result set

Of course this logic could be executed by the client application. It could connect to Denodo Platform to execute those queries (getting the data from the first point and inserting in the third point) but... what happen if more applications have to use the same logic? Are you going to implement the same in ALL applications?

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).

 

Implementing a new Stored Procedure

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".

1. First of all, ensure you have done the Basics Tutorial so you have available in your Virtual DataPort the base views client and client_type.
   
   
2. Launch Eclipse, an open our existing Denodo Extension project: my_denodo_extensions.
   
   
3. Right click over the project and select New > Other. In the dialog, select Denodo Extension and click on Next >:
   
4. Choose Denodo VDP Stored Procedure as Extension and give a package (com.denodo.vdp.custom.sp) and class name (GetClients):
   
5. Click Finish and you will see that GetClients.java file gets created in the Package Explorer.
   
   
6. By double clicking on the 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:
   
7. In this tutorial, we are going to modify only the getParameters and doCall methods for modifying the list of input/output paramenters and for implementing the logic.
   
   
8. Parameters: No input paramenters / Output paramenters: name, surname and ssn of the clients:
   
9. Logic: The stored procedure has to execute the VQL query
   SELECT name, surname, ssn FROM client
WHERE client_type = '01';
   and return all the output rows:
   
10. If any error occurs, they will be written to the Virtual DataPort log file (%DENODO_HOME%/logs/vdp/vdp.log) by invoking this.environment.log(LOG_ERROR, e.getMessage());.
    

    You could find the Java class of this GetClients stored procedure in this link.

    TIP

 

 

Deploy our Stored Procedure into Virtual DataPort

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:

1. Right click on the project my_denodo_extensions and select Deploy Extensions.
   
   
2. In the dialog a new option for creating the Stored Procedure will appear (it is also a good idea to modify the value of the version of our extension, as we have added a new component). Finally, click Finish.
   
3. From your Virtual DataPort Administration tool, first click on File > Refresh to load the changes. You will see a new Stored Procedure in the tutorial database!
   
4. To execute the stored procedute you have to double-click and then click on Execute and click Ok.
   
5. Stored procedure will be executed and query results would be displayed:
   

 

You have done an excellent work in completing the tutorial for Denodo Custom components.

Thanks!