System Process and Session Information in Triggers

Question

Question

Stefan Cronje · Jan 22, 2023

Hi all,

I am trying to use some process private variables (percent variables) in Triggers.

I am referring to values from $System.Process, like the ClientIPAddress and CSPSessionID.
These do to not contain values and I suspect it is bacause of scope. I also checked, and the %session variable is not available if the change originated from a CSP request.
I know that in triggers the scope of the variables are to be kept local, that is why NEW is to be used.

Is there a way to access the Process Private Variables in Triggers or get $System.Process to return the correct values?

Thanks.

Product version: IRIS 2022.3

Discussion (10)1

Log in or sign up to continue

Robert Cemper · Jan 22, 2023

You are CORRECT ,
In Caché/Ensemble SMP uses straightforward CSP and you have %session object and %request ...
I verified IRIS 2022.2 and 2022.3:
- SMP runs Queries differently and neither %session nor %request is visible
by variable » %inBackground = 1 it indicates working in background
and once completed the Job is gone, but SMP is still around

0 0

Robert Cemper · Jan 22, 2023

FOUND SOMETHING !

in SMP IRIS you see this header as in Caché / Ensemble

but click to more has a surprise in IRIS

Select foreground and you get all %session, .... that you look for.
Recorded with SPOOL , device 2
%CSPsc=1 %ROWCOUNT=0 %ROWID="" %SNGetQueryStats=1 %SYSLOG=1 %application=<OBJECT REFERENCE>[356@%CSP.UI.Portal.Application] %caller="" %class="%CSP.UI.Portal.SQL.Home" %method="InvokeInstanceMethod" %msg="" %obj=<OBJECT REFERENCE>[362@%ZEN.Component.html] %objlasterror="" %page=<OBJECT REFERENCE>[20@%CSP.UI.Portal.SQL.Home] %request=<OBJECT REFERENCE>[1@%CSP.Request] %response=<OBJECT REFERENCE>[2@%CSP.Response] %sc=1 %session=<OBJECT REFERENCE>[3@%CSP.Session] %sqlcontext=<OBJECT REFERENCE>[433@%Library.ProcedureContext] %tStatement=<OBJECT REFERENCE>[369@%SQL.Statement] %zenContext="method" %zenThreadId=39 SQLCODE=0 rs="" <Private variables> $zp=14828 $io="|TCP|58771"

Reverse engineering of the SMP page uncovered this new checkbox to me.
Could be it is documented somewhere

HTH

0 0

Iain MacDonald · Jan 23, 2023

https://docs.intersystems.com/iris20223/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_smp#GSQL_smp_executequeryopts

The IRIS 2022.3 documentation has this:

Execute Query in the foreground: a check box specifying whether or not to run the query in the foreground. Simple queries run in the foreground are often significantly faster than those run in the background. However, long queries run in the foreground may cause the Management Portal to be unresponsive during query execution. The default is to run all queries in the background.

1 0

score 0 · Answer 1 · 2023-01-22T03:50:47-05:00

As a first step I would try to find out if your Trigger is running in the same process as your CSP
just writing $JOB with a $H into a test global. eg.:

set ^stefan($h,$j)="CSP"  for CSP
set ^stefan($h,$j)="TRIGGER"  for the trigger code
;;; or use
do LOG^%ETN()  ; write to ^ERRORS

score 0 · Answer 2 · 2023-01-22T05:42:12-05:00

For simplicity I am updating the table using the System Management Portal SQL.

I used auditing to get the PID and logged the other in the trigger.
Both are the same value: 95437

The ^ERRORS contains 3472 lines just for this. Do you want to see something specific in it?

There is no reference to %session in it.

score 0 · Answer 3 · 2023-01-22T08:21:31-05:00

Thank you for clarifying. That makes sense.

It would have been nice to still have that information accessible somehow. If you add audit logs and other things based on triggers, you lose that traceability if SMP is used. This does create a bit of a gap in the audit trail if you are keeping one based on trigger events. The Dynamic SQL audit event can be used, but you can't link it directly to a record that has been updated or deleted, if you are using information that is only available in %session.

Can the InBackground behaviour be changed in a configuration?

score 1 · Answer 4 · 2023-01-22T09:35:11-05:00

I could imagine 2 variants:

creating a "clone" of the CSP based "synchronous" Queries we know from C/E to have the unique session
just using xDBC based tools such as Squirrel, DBeaver, WinSQL, ..... which is probably better since SMP mimics ODBC mode, but doesn't use real xDBC connection
external xDBC provides a 1:1 connection as a terminal session would do.

score 0 · Answer 5 · 2023-01-22T09:38:53-05:00

Thank you.

The tools used to run updates is not up to me. The end-customer has very strict security policies, so the only way to run SQL is via the SMP. Only that web port is open to most personnel, and then you are doing this on a remote desktop as well.

score 0 · Answer 6 · 2023-01-22T11:33:09-05:00

Robert Cemper · Jan 22, 2023

I understand this. Have experienced it as well.

0 0

score 0 · Answer 7 · 2023-01-24T03:49:58-05:00

Robert Cemper · Jan 24, 2023

THX for pointing

0 0