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"
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.