Not able to set session variable in SAP HANA V1.0 SPS12

[former_member565716]

Hello,

I am trying to set the session variable in SAP HANA, as described here:

SET [SESSION] Statement (Session Management)

command: SET 'MY_VAR' = 'abc';

is successful, but when I am trying to retrieve back the value using the command:

SELECT SESSION_CONTEXT('MY_VAR') FROM DUMMY;

It's returning me null value.

[pfefferf]

Can you add some information in which context you are setting and reading the session variable.

And are you sure that both actions are done in the same session?

[lbreddemann]

In order to bring up some more helpful information:

In the session where you run your SELECT command, please also run the following two and post the results:

select current_connection from dummy;
select * from m_session_context;

[former_member565716 · Accepted solution]

Hi, Florian Pfeffer and Lars Breddemann thanks to both of you for your replies.

I got the Idea from your comments, and after researching little more I came to know, each query I was running in the SQL console was getting executed in a new session.

To confirm the commands are working, I created a procedure, containing both set and select commands for a session variable.

CREATE PROCEDURE "SCHEMA_NAME"."CHANGE_SESSION_VAR" (OUT sessionVar NVARCHAR(5))
AS
BEGIN
	SET 'mySessionVar' = 'jmk';
	SELECT SESSION_CONTEXT('mySessionVar') into sessionVar FROM DUMMY;
END

And it worked, as invoking this procedure is executing both the internal queries in the same session.

Thanks again for your comments.😊