Denodo community Q&A RSS feed Latest Denodo community questions Why can't a user drop a wrapper ? We have a group of users that have the permissions to drop and create their own views on their own virtual database. In order to facilitate automation, we provided the users with a base view that generates the VQL to create or replace the view by supplying a table_name parameter. This allows a hard coded area to put the replaced or newly created view in the same virtual database every time. The base view uses a datasource that is in a virtual database outside the user's control. Here's the base view that generates the script: CREATE OR REPLACE WRAPPER JDBC bv_createview DATASOURCENAME=admin.ds_denodo SQLSENTENCE='select creation_vql from GENERATE_VQL_TO_CREATE_JDBC_BASE_VIEW () where data_source_name =''old_view'' and catalog_name = ''UserViews'' and schema_name = ''dbo'' and folder = ''/BaseViews'' and database_name = ''PowerUserTeam'' and table_name = ''@table_name'' and base_view_name =''@table_name'' ' OUTPUTSCHEMA ( creation_vql = 'creation_vql' :'java.lang.String' (sourcetypedecimals='0', sourcetypesize='65536', sourcetypeid='12', sourcetypename='VARCHAR') NOT NULL SORTABLE NOT UPDATEABLE, table_name = 'TABLE_NAME' :'java.lang.String' (OBL) (DEFAULTVALUE='VW_AA') EXTERN SORTABLE ); CREATE OR REPLACE TABLE bv_createview I18N us_est ( creation_vql:text (notnull, sourcetypeid = '12', sourcetypedecimals = '0', sourcetypesize = '65536'), table_name:text (extern) ) CACHE OFF TIMETOLIVEINCACHE DEFAULT ADD SEARCHMETHOD bv_createview( I18N us_est CONSTRAINTS ( ADD creation_vql NOS ZERO () ADD table_name (=) OBL ONE ) OUTPUTLIST (creation_vql ) WRAPPER (jdbc bv_createview) ); The ds_denodo datasource is used by the stored procedure GENERATE_VQL_TO_CREATE_JDBC_BASE_VIEW () to find the VQL to create or replace the views: CREATE DATASOURCE JDBC ds_denodo FOLDER = '/ds_admins' DRIVERCLASSNAME = 'com.denodo.vdp.jdbc.Driver' DATABASEURI = '<URI>' USERNAME = 'admin' USERPASSWORD = '<password>' ENCRYPTED CLASSPATH = 'vdp-8.0' DATABASENAME = 'vdp' DATABASEVERSION = '80' FETCHSIZE = 50000 VALIDATIONQUERY = 'select 1' INITIALSIZE = 4 MAXIDLE = -1 MINIDLE = 0 MAXACTIVE = 20 EXHAUSTEDACTION = 1 TESTONBORROW = true TESTONRETURN = false TESTWHILEIDLE = false TIMEBETWEENEVICTION = -1 NUMTESTPEREVICTION = 3 MINEVICTABLETIME = 1800000 POOLPREPAREDSTATEMENTS = false MAXOPENPREPAREDSTATEMENTS = -1 PROPERTIES ('ssl'='true', 'sslTrustServerCertificate'='true', 'loggerLevel'='debug') KERBEROSPROPERTIES ('renewTGT'='true', 'useTicketCache'='true', 'useKerberos'='true') OAUTHPROPERTIES ('useOAuth2'='true') DATA_LOAD_CONFIGURATION ( BATCHINSERTSIZE = 25000 ) DESCRIPTION = 'JDBC to DENODO itself being used by StroredProcedures ' SOURCECONFIGURATION ( delegateaggregatefunctionslist = (avg, count, max, median, min, stdev, stdevp, sum, var, varp), delegatescalarfunctionslist = (abs, acos, addday, addhour, addminute, addmonth, addsecond, addweek, addyear, ascii, asin, atan, atan2, case, cast, ceil, char, coalesce, concat, convert_timezone(evaluate_literal), cos, cot, current_date, degrees, div, exp, firstdayofmonth, firstdayofweek, floor, formatdate, getday, getdayofweek, getdayofyear, getdaysbetween, gethour, getmicrosecond, getmillisecond, getminute, getmonth, getmonthsbetween, getnanosecond, getquarter, getsecond, gettimeinmillis, getweek, getyear, hash, instr, lastdayofmonth, lastdayofweek, len, ln, localtimestamp(evaluate_literal), log, lower, ltrim, max, min, mod, mult, nextweekday, now, nullif, pi, position, pow, power, previousweekday, radians, rand, regexp, removeaccents, repeat, replace, round, rownum, rtrim, sign, similarity, sin, sql_trim, sqrt, substr, substring, subtract, sum, tan, textcat, textconstant, to_date, to_interval_day_second, to_interval_year_month, to_localdate, to_time, to_timestamp, to_timestamptz, trim, trunc, upper, xmlquery, xpath) ); On occasion, the users need to remove a base view by using DROP commands: DROP TABLE IF EXISTS old_view CASCADE; DROP WRAPPER JDBC IF EXISTS old_view CASCADE; The second statement fails with "the user does not have access privileges on jdbc.old_view" Why can't a user drop a wrapper in JDBC when they can drop the table? They can CREATE OR REPLACE, but not drop. What privilege would they need in Role Management to be able to drop their wrapper? Or, alternatively how can we alter the above bv_createview to force the wrapper creation into the user's virtual database PowerUserTeam ? Wed, 24 Apr 2024 11:29:29 GMT 2024-04-24T11:29:29Z Issue with rolling back transaction from exception in VQL Stored procedure I am creating a VQL stored procedure, but it is not rolling back when I catch an exception. I am following the example provided here: Here is the specific example I am following: CREATE OR REPLACE VQL PROCEDURE testTransactionsExceptionsHandling(paramName IN INTEGER) AS ( // Procedure variables varName VARCHAR; ) BEGIN // Procedure body BEGIN_TRANSACTION; // If insert fails (for whatever reason), EXCEPTION part will be executed INSERT INTO testnvarchar_1 (id,charfield) VALUES (3, 'Test'); COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; END; For my test I set the database up to fail on insert to test the rollback. In this example the one of the fields is too small, forcing an insert failure. Here is some simplified code that throws the exception: CREATE OR REPLACE VQL PROCEDURE sp_simple_rollback_exeption FOLDER = '/stored procedures'(varout out varchar) --output parameter isn't used. AS ( lvar varchar; -- exc1 exception; ) BEGIN BEGIN_TRANSACTION; delete from bv_targetexc; Execute 'insert into bv_targetexc select * from bv_source4'; commit; EXCEPTION WHEN others THEN -- when exc1 then return row (varout) values ('Error on insert'); ROLLBACK; END; Exception: SP_SIMPLE_ROLLBACK_EXEPTION [STORED PROCEDURE] [ERROR] Received exception with message 'Cannot rollback if transaction is not started.' Received exception with message 'java.lang.NullPointerException' I also set up a second version to catch the exception differently. This creates and exception variable instead of using "others". VQL SP Code CREATE OR REPLACE VQL PROCEDURE sp_simple_rollback_exeption FOLDER = '/stored procedures'(varout out varchar) --output parameter isn't used. AS ( lvar varchar; exc1 exception; ) BEGIN BEGIN_TRANSACTION; delete from bv_targetexc; Execute 'insert into bv_targetexc select * from bv_source4'; commit; EXCEPTION -- WHEN others THEN when exc1 then return row (varout) values ('Error on insert'); ROLLBACK; END; Exception: SP_SIMPLE_ROLLBACK_EXEPTION [STORED PROCEDURE] [ERROR] Received exception with message 'Error executing data movement from view _p_17d113c7-c86f-4faa-968e-1f646ee9e86d_2943d3b8-6968-4db9-947e-3b1cef9291b8 to source ds_pg_target Exception of plan _p_17d113c7-c86f-4faa-968e-1f646ee9e86d_2943d3b8-6968-4db9-947e-3b1cef9291b8: com.denodo.vdb.util.tablemanagement.TableManagerException: There was an error during a batch insertion: Batch entry 0 INSERT INTO "public".targetexc(customer_name, customer_address, phone_number, customer_code, job_title) VALUES ('Laura Gutierrez', '7751 Larry Junction', '356-(361)254-1886', 'qHzxXA0S', 'Web Designer IV') was aborted: ERROR: value too long for type character(10) Call getNextException to see other errors in the batch.' Note: The transaction will eventually roll back in both versions after it times out. This creates the following message in the logs [JotmBatch] ERROR 2024-04-23T13:49:38.562 com.denodo.vdb.engine.session.DBSession [] - executing rollback due inactivity timeout on session #395, transaction bb14:38:0:018c8f4f655f4db6ab...0a109e: I am looking to get the VQL Stored procedured to rollback immediately upon catching the exception and to return a graceful message to the caller. Tue, 23 Apr 2024 19:52:28 GMT 2024-04-23T19:52:28Z Migrating from SSIS to Denodo Hi Team, Please note that i am part of Denodo migration from SSIS. Kindly need some support on create a denodo package based on SSIS package. I am starting my new package in production, need some assistance. Tue, 23 Apr 2024 07:18:46 GMT 2024-04-23T07:18:46Z Unable to establish connection : IO Error: The Network Adapter could not establish the connection hi team (Unable to establish connection: IO Error: The Network Adapter could not establish the connection)it shows the previous error when trying to test connection in the training database it is not loading the data from oracle,the database url is jdbc:oracle:thin:@data-server:1521:xe which is a correct format Mon, 22 Apr 2024 23:27:08 GMT 2024-04-22T23:27:08Z Denodo server is not starting Hi team I am unable to start most of the servers it says stopped when i start it(Data catalog and web tool servers) ,how can i fix this issue? Mon, 22 Apr 2024 10:28:15 GMT 2024-04-22T10:28:15Z Check for other data sources type Hello, This is very interesting but unfortunately it doesn't cover all Denodo data sources types. Would it be possible to check other sources such as: JSON WS XML CUSTOM (this includes Excel data sources). DF (delimited files) WWW Mon, 22 Apr 2024 10:01:39 GMT 2024-04-22T10:01:39Z Denodo Solution manager roles Hi Denodo experts, Scenario below: In the Denodo Dev and Stage environments we have a developer called db_1_admin who is an admin on a specific Denodo db called db_1. In the Solution manager, we have provided the stage promotion admin role. Because of this , the db_1_admin user is able to deploy VQL to the Stage environment to any database. That means, if the CONNECT DB db_1 command is missing from the VQL, then the solman automatically executes the VQL on the 1st available db in Stage , which this user should not have access to. Is there any role in Sol man that will allow a user to deploy only to their own dbs and not to other Denodo Dbs? Thanks. Sat, 20 Apr 2024 03:41:19 GMT 2024-04-20T03:41:19Z Hiding columns that users cannot execute I have implemented column level security on a view. As expected when the restricted uers executes select * from the view they get an error message that says that they do not have privileges on certain columns. However, the user can see the columns that they do not have access to. Is there a way to prevent the user from seeing the columns that they do not have access to? Fri, 19 Apr 2024 16:24:35 GMT 2024-04-19T16:24:35Z Returning a Select Statement in a VQL Stored Procedure Hi everyone. Simple question here. I want to create a VQL Stored Procedure that returns a Select Statement, how do I do it? I have searched through the Denodo documentation for this but haven't found any reference about it. Thanks in advance Fri, 19 Apr 2024 16:01:32 GMT 2024-04-19T16:01:32Z Vdp-queries log generation Hello Team, We are aware that Denodo logs are produced in two directories: DENODO_HOME/logs/vdp and DENODO_HOME/tools/monitor/denodo-monitor/logs (when Denodo Monitor is running). The file "vdp-queries.log" exists in both locations mentioned above. However, vdp-requests.log is only generated in the Denodo Monitor directory and not in DENODO_HOME/logs/vdp. Please advise on in which case entries are generated in DENODO_HOME/logs/vdp/vdp-queries.log as well. Additionally, could you confirm if there is a log4j2.xml file for logs in DENODO_HOME/tools/monitor/denodo-monitor/logs? Fri, 19 Apr 2024 09:18:03 GMT 2024-04-19T09:18:03Z