2

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"
Pred
  • 8,789
  • 3
  • 26
  • 46

2 Answers2

0

I think you can achieve what you need by using the scripting capabilities within Exasol - see section 3.5 in the user manual..

You could also handle the parameterisation 'externally' via a shell script

Dave S
  • 71
  • 2
  • Thanks for the comment, I saw it already. My problem is not to check the values or anything like this, but to store them only for the current session and to remove them when the session is terminated. The variable scope in LUA is limited to the function it was defined, therefore I cannot use that variable in a view. Fix me if I am wrong with this. – Pred May 10 '17 at 09:30
0

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.

mucio
  • 7,014
  • 1
  • 21
  • 33
  • Thanks for the input, this is done already. My problem with this approach (as it is in the original post) is that the records are not maintained and I cannot find a way to detect if a session is terminated -> I cannot delete the records, unless I have another script scheduled to run periodically and do some GC job. – Pred May 10 '17 at 14:40
  • yes, that is the way to go with Exasol, I had to do something similar. From what is my experience Exasol is quite bare database, it does few things and it does them pretty well, but it doesn't come with a full suit of tools or functionality like Oracle or SQL Server. I think the best solution is to bring complex logics outside the DB layer with Exasol, what usually is done with PL-SQL or TSQL should be moved outside Exasol, or you have Lua... – mucio May 10 '17 at 14:52
  • Well... I am accepting this answer since this is the only solution what I found with more research in this topic. – Pred May 10 '17 at 15:03