Question
· Jan 22, 2023

System Process and Session Information in Triggers

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

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

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?

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.     

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

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.