You can translate the question and the replies:

Pass custom parameters via jdbc and read them as session/env variables

Hello, I have a requirement where I would like to pass a custom parameter via denodo jdbc driver from northbound connectivity. I would also like to read the value of this parameter within Denodo as a session/environment variable and potentially use this as jdbc parameter for southbound connectivity. I have been trying various options like GETVAR, GETSESSION and VQL stored procedure to achieve this but haven’t been successful. Has anyone tried similar use case or any pointer how this can be achieved? Many thanks.
user
28-06-2023 03:06:56 -0400
code

6 Answers

Hi, how are you planning to use of the parameter on the data source? What is you data source? Kind regards
Denodo Team
29-06-2023 05:24:57 -0400
code
Hello, Thank you for your reply. These parameters will be further used to set the protected variable values within underline RDBMS. Which can then be used to filter the data. The data source is relation database DB2. For security reasons these parameters needs to be passed as values at conenction time(to set session context at conenction time) and not to be applied as predicates within "where" caluse of Denodo View/Query. Hope this helps. Thank you
user
03-07-2023 10:01:32 -0400
The list of JDBC parameters supported by Denodo driver is listed here: [JDBC Driver Parameters](https://community.denodo.com/docs/html/browse/8.0/en/vdp/developer/access_through_jdbc/parameters_of_the_jdbc_connection_url/parameters_of_the_jdbc_connection_url). You can pass them as driver properties or inside the connection URL. To retrieve some of them in VDP there's the function [GETSESSION()](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/functions/other_functions/other_functions#getsession). e.g. ``` select GETSESSION('useragent') ``` Currently there's no option to pass custom parameters outside the supported ones. However, if you're trying to achieve some access control for user session, Denodo can handle advanced user/role privileges with access to resources with every level of granularity (up to view, column and row level!). You can read more information at [User and Access Rights in Virtual DataPort](https://community.denodo.com/docs/html/browse/8.0/en/vdp/administration/databases_users_and_access_rights_in_virtual_dataport/user_and_access_right_in_virtual_dataport/user_and_access_right_in_virtual_dataport). Hope this helps
Denodo Team
05-07-2023 03:20:46 -0400
code
Hello, Thank you for your message. I could read the useragent value within GETSESSION('useragent') but this will be available within the VQL. I need to substring all the parameters passed from northbound connection and then pass it to southbound jdbc drivers in Driver URL. I know there is Driver Paramaters option in Advance tab but I cant use denodo SUBSTR function to slect paramaters from useragent or I cant use @USER_AGENT in initial sql sentneces. Is there any functionality I can use where the concatenated parameters can be separataed and then use those values in jdbc driver parameters. Thank you
user
10-07-2023 09:34:29 -0400
Hi again, I created a new property under *Advanced tab > Driver Property* and I am able to use the substring function: `SUBSTR('@user_agent', 1, 10)` Have a look at the bottom of the section [Importing JDBC Sources](https://community.denodo.com/docs/html/browse/8.0/en/vdp/administration/creating_data_sources_and_base_views/jdbc_sources/jdbc_sources#importing-jdbc-sources). Also you should be able to access @user_agent in the Initial SQL statement as explained [here](https://community.denodo.com/docs/html/browse/8.0/en/vdp/administration/creating_data_sources_and_base_views/jdbc_sources/jdbc_sources#initial-sql-statements). The user guide also list all the automatically defined interpolation variables. Finally, if you need more help and have a valid support account, you can open a new case in the [Support Site](https://support.denodo.com/) so that Denodo team can assist you. Hope this helps.
Denodo Team
12-07-2023 05:30:00 -0400
code
Hello, Many thanks for your reply. I also had a check if I can use SUBSTR('@user_agent', 1, 10). What appears to me that test connection works fine but the value of new property is passed as is a "whole" string in this case "SUBSTR('@user_agent', 1, 10)" with @user_agent values resolved. So lets say if I have a parameter ABC the value wont be substring expected but rather whole string "SUBSTR('@user_agent', 1, 10)". In short SUBSTR function is not working in the value context. Please could you confirm this is not the behaviour you are seeing? Thank you,
user
12-07-2023 07:39:00 -0400
You must sign in to add an answer. If you do not have an account, you can register here