You can translate the question and the replies:

StoredProcedureException

Hi, I create a stored procedure for create drive_views, i would like validate if drive view exist before create. I try this: this.environment.executeVqlCommand(select 1 from drive_view) but if it not exist i receive the error by StoredProcedureException and SP stop. How i read the SQLException and if it return view not found, i create it? Thanks
user
08-11-2021 11:24:41 -0500
code

3 Answers

Hi , In order to handle the Exception raised I would use a try/catch block as required. Further, in cases where I need to get information about the views, I would use the [Predefined Stored Procedure](https://community.denodo.com/docs/html/browse/latest/en/vdp/vql/stored_procedures/predefined_stored_procedures/predefined_stored_procedures) GET_ELEMENTS / GET_VIEWS to verify if the view exists or not in the Virtual DataPort Server. For instance : Instead of using ‘select 1 from drive_view’, you could use the VQL query based on the below example format `select 1 from GET_VIEWS() where name='drive_view'` In case if the view is present, output will be 1. This way even if the view is not available, it would not trigger any exceptions. You can take a look at the [GET_VIEWS](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/stored_procedures/predefined_stored_procedures/get_views) section under Virtual DataPort VQL Guide for more information on the Stored Procedure used. Based on the output of the above query you can further enhance the code to perform other operations as per the requirement. For more information, you could check the [Developing Stored Procedure](https://community.denodo.com/docs/html/browse/8.0/en/vdp/developer/developing_extensions/developing_stored_procedures/developing_stored_procedures) section from the Virtual DataPort Developer Guide. Also you can have a look at [How to implement stored procedure](https://community.denodo.com/tutorials/browse/customcomponents/3storedprocedure) tutorial which illustrates a simple example related to implementing Custom Stored Procedures. Hope this helps!
Denodo Team
09-11-2021 07:08:08 -0500
code
Hi, Thanks, but my problem it´s not that, how i check the resultset of the execution? try { for(String select_dv : select_view) { output = this.environment.executeVqlCommand(vdb, select_dv); if (output == null) { flag_existe = true; getProcedureResultSet().addRow(new Object[] {output,out,select_dv,flag_existe}); Insert(temp_view, temp_interface, vdb, flag_existe); } else { flag_existe = false; Thanks
user
09-11-2021 11:48:00 -0500
Hi, In order to check the result set of the execution ,then you could use the functions like [executeQuery()](https://community.denodo.com/docs/html/browse/8.0/en/vdp/javadoc/com/denodo/vdb/engine/storedprocedure/DatabaseEnvironment.html#executeQuery-java.lang.String-), etc. to execute a query against the VDP server which returns the ResultSet object. Further by using the next() function on this resultset object, it is possible to do the condition checks as required. Also in case of getProcedureResultSet, you could use the [nextElement()](https://community.denodo.com/docs/html/browse/8.0/en/vdp/javadoc/com/denodo/vdb/engine/storedprocedure/StoredProcedureResultSet.html#nextElement--) function to get the elements from the result set. You can take a look at [com.denodo.vdp.demo.storedprocedure](https://community.denodo.com/docs/html/browse/8.0/en/vdp/javadoc/index) Java document for the detailed information about all the classes,methods,interfaces in this package used to create the custom Stored Procedures. Hope this helps.!
Denodo Team
10-11-2021 07:19:04 -0500
code
You must sign in to add an answer. If you do not have an account, you can register here