Skip to Content
0

SAP HANA XSA Web IDE: HDI Container - CURRENT_USER

Oct 06, 2017 at 11:39 AM

174

avatar image

Hi,

we want to trigger our application. If we use CURRENT_USER than I get the technical user. Is there a way to get the application or client user?

Thanks!

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Lucia Subatin
Oct 06, 2017 at 02:25 PM
1

Have you tried this? SELECT SESSION_CONTEXT('APPLICATIONUSER') "APPLICATION_USER" FROM "DUMMY";

Show 6 Share
10 |10000 characters needed characters left characters exceeded

Lucia, very nice! Thank you very much.

The only one problem is, while I use it within a trigger, I get this error:

Error: com.sap.hana.di.trigger: "SYS"."DUMMY": the reference has to be schema-local in "TRIGGER"

or if I don't use the Schema:

Error: The file requires "db://DUMMY" which is not provided by any file

Any ideas how to fix it?

TRIGGER "TBASE.db.trigger::AnamneseI"
AFTER INSERT ON "TBASE.db.data::tbase.cds.Anamnese" 
REFERENCING NEW ROW mynewrow   
 FOR EACH ROW 
BEGIN 
--DECLARE user NVARCHAR;
--SELECT SESSION_CONTEXT('APPLICATIONUSER') "APPLICATION_USER" into user FROM "SYS"."DUMMY";
INSERT INTO "TBASE.db.data::tbase_history.cds.Anamnese" VALUES(:mynewrow."AnamneseID",:mynewrow."PatientID",:mynewrow."Wert",:mynewrow."Bezeichnung", 
(SELECT SESSION_CONTEXT('APPLICATIONUSER') "APPLICATION_USER" into user FROM "DUMMY"), CURRENT_UTCTIMESTAMP, '1' );
END

0

Hey, you need to add a synonym (newfile.hdbsynonym) for DUMMY in schema SYS.

Cheers,

Lucia.

1

While it can be very handy to have access to a DUMMY table in your application (note that you could also create your very own version of DUMMY, e.g. one that lives in the column store) it is not necessary here.

The SESSION_CONTEXT() function can be called right in your insert statement:

TRIGGER "TBASE.db.trigger::AnamneseI"
AFTER INSERT ON "TBASE.db.data::tbase.cds.Anamnese" 
REFERENCING NEW ROW mynewrow   
 FOR EACH ROW 
BEGIN 
INSERT INTO "TBASE.db.data::tbase_history.cds.Anamnese" 
        ("AnamneseID"
       , "PatientID" 
       , "Wert"
       , "Bezeichnung"
       , "APPLICATION_USER"
       , "Timestamp"
       , "SomeFlagNobodyCanTellWhatItDoes")
VALUES (:mynewrow."AnamneseID"
       ,:mynewrow."PatientID"
       ,:mynewrow."Wert"
,:mynewrow."Bezeichnung" , SESSION_CONTEXT('APPLICATIONUSER') , CURRENT_UTCTIMESTAMP , '1'); END

That way the statement is clearer and uses one SQL command less per inserted row.

By the way: this history table looks a lot like what audit logging gives you in the HANA database. If you haven't considered it, you might save yourself time developing boring boilerplate-logging code here. What's also surprising is that you decided to store the new values instead of the old values. So, once your trigger is finished, you have the new values stored twice (base table and logging table) but the old values only potentially exist in former log entries.

0

Hi,

I appreciate your advice very much and will check for your nicer statement solution asap. (Yes, is much more clearer!)

We do have considered to use the sap audit-logging, but this is still not finished yet and we think it will take one more patch if it will be ready to use for it. To bridge this times we decided to do it by "easier" trigger logging. But of course npm audit-logging is our favored solution, because its said we will be able to log also Select requests and this is one of our requirements we need to solve.

About your last hint I will think about it an check for it, if we do have there any conceptual gaps.

Thank you very much!

0

SESSION_CONTEXT('APPLICATIONUSER') works like a charm. :)

But still only one point of this trigger statement annoys me. Setting each column separately would be very time consuming. I looked also for a solution where I can only work with tables. May be one of you has an idea.

But at this moment I'm glad that it works so far.

Thanks!

0

Thank you Lucia, now it works!

0