sqlsessionexasolution

EXASol set a custom session variable


In SQL Server (2016) we have the SESSION_CONTEXT() and sp_set_session_context to retrieve/store custom variables in a key-value store. These values are available only in the session and their lifetime ends when the session is terminated. (Or in earlier versions the good old CONTEXT_INFO to store some data in a varbinary).

I am looking for a similar solution in EXASol (6.0).

An obvious one would be to create a table and store this info there, however this requires scheduled cleanup script and more error prone than a built-in solution. This is the fallback plan, however I'd like to be sure that there is no other options.

Another option could be to create individual users in the database and configure them, but just because of the amount of users to be added, this was ruled out.

The use-case is the following: An application has several users, each user have some values to be used in each queries. The application have access only to some views.

This works wonderfully in SQL Server, but we want to test EXASol as an alternative with the same functionality.

I cannot find anything related in the EXASol Manual but it is possible, that I just missed something.

Here is a simplified sample code in SQL Server 2016

sp_set_session_context @key='filter', @value='asd', @read_only=1;

CREATE VIEW FilteredMyTable AS
SELECT Col1, Col2, Col3 FROM MyTable
WHERE MyFilterCol = CONVERT(VARCHAR(32), SESSION_CONTEXT('filter'))

I've tried an obviously no-go solution, just to test if it works (it does not).

ALTER SESSION SET X_MY_CUSTOM_FILTER = "asd"

Solution

  • You cannot really set a session parameter in EXASOL, the only way to achieve something similar is to store the values that you need in a table with a structure like:

    SESSION_ID   KEY     VALUE   READ_ONLY
    8347387      filter  asd     1
    

    With LUA you could create a script that will make easier for you to manage these "session" variables.