You can translate the question and the replies:

Not getting the expected result using a VQL stored procedure

Hi everyone, I have been trying to recreate a view using a stored procedure, like this: ( nomeview IN VARCHAR) AS ( // Procedure variables v_name VARCHAR; database_name VARCHAR; i_name VARCHAR; CURSOR interfaces IS 'SELECT view_name FROM VIEW_DEPENDENCIES() WHERE view_database_name = ''vdb_sample'' and depth = 1 and dependency_name = :v_name'; ) BEGIN // Procedure body v_name := nomeview; //database_name := nome_db; OPEN interfaces PARAMETERS (v_name) VALUES(dependency_name); FETCH interfaces INTO i_name; CLOSE interfaces; EXECUTE 'CREATE OR REPLACE view v1 FOLDER = ''/03 - business entities'' AS SELECT * from :i_name' PARAMETERS ( i_name) VALUES (i_name); EXECUTE 'drop interface view :i_name' PARAMETERS ( i_name) VALUES(i_name); EXECUTE 'ALTER VIEW v1 RENAME :i_name' PARAMETERS ( i_name) VALUES (i_name); END The problem here is that for some reason, the variable i_name is assuming the base view name, instead of the interface name, which is leading to the recreated view being a copy of the base view instead of the interface. I´m I doing something wrong or is it a bug? thanks.
Denodo 8.0 VQL Stored Procedure Error VQL Stored procedure
user
13-12-2023 11:02:25 -0500
code

1 Answer

Hi, If I face this error while executing a VQL stored procedure, I would check the following things in my code: * In the “OPEN interfaces PARAMETERS (v_name) VALUES(dependency_name);”, I couldn’t find the ‘dependency_name’ being declared in the given code. The view name inside the VALUES() parameter should be valid to get the expected result. * If I am trying to find the dependent views of the view given in the ‘nomeview’ parameter, then I would change the line to the below format: ``` OPEN interfaces PARAMETERS (v_name) VALUES(nomeview); ``` * If the above line is changed, then it wouldn't be necessary to assign the value of vname separately. * Also, if I am trying to create an interface view for the obtained view in the `interfaces` cursor, I would use the below syntax for creating an [interface view](https://community.denodo.com/docs/html/browse/latest/en/vdp/vql/defining_a_derived_view/defining_an_interface_view/defining_an_interface_view): ``` CREATE OR REPLACE interface view <view_name> (column_names :data types) set implementation <implementation_view> FOLDER = ''<folder_name>'' ``` * Finally, if I am trying to give the view name in the `EXECUTE` statement with parameters, then I would use two different variable names in the [PARAMTERS() and VALUES()](https://community.denodo.com/docs/html/browse/8.0/en/vdp/developer/developing_extensions/developing_stored_procedures/developing_vql_stored_procedures#:~:text=Example%20of%20VQL%20stored%20procedure%20that%20uses%20the%20EXECUTE%20command%20with%20the%20PARAMETERS%20clause) section to avoid conflict in the execute statement. For further information, you could refer [Developing VQL Stored Procedures](https://community.denodo.com/docs/html/browse/8.0/en/vdp/developer/developing_extensions/developing_stored_procedures/developing_vql_stored_procedures) section of the Virtual DataPort Developer Guide. Furthermore, if you are still facing issues and if you are a valid support user, you can raise a [Support Case](https://support.denodo.com/cases/add), for further assistance from the Support Team. Hope this helps!
Denodo Team
18-12-2023 00:34:12 -0500
code
You must sign in to add an answer. If you do not have an account, you can register here