-
Marco Meoni
- PowerServer
- Wednesday, 2 June 2021 11:16 AM UTC
Hello,
I am wondering how to rely on database context variables with PS 2021. We're using Oracle but the question applies to any DB.
In a c/s PB app, there are DB variables whose value lives for the life of the connection. The PB app can fetch these vars in DW SQL or eSQL.
After migration to PS 2021, login-related DB context is not accessible anymore, which requires the change of hundreds DWs (e.g. with one additional login retrieval argument) because the connection pooling makes each DB call open&close the connection and context vars do not persist.
In more details:
we store current user in Oracle database context, which we later get in PL/SQL as such:
vs_User := SYS_CONTEXT('VG_SECURITY_CONTEXT', 'USERID');
We also store the currently logged in user location (SITE1, SITE2, SITE3, etc) which is selected by the user at application start, in an Oracle *temporary* table. This table is accessible from DB for the whole connection time, and accessed via getter function so that we don't need to pass it explicitly in each function/procedure call.
This was implemented in this way to minimize the impact of the changes which needed to be done
to introduce a concept of "user location" in our application.
Most of the usage boils down to an additional condition applied in WHERE clause in SQL queries:
WHERE user_site = f_GetCurrentUserSite()
SQL conditions like above fail because context is lost.
With SnapDevelop REST API project I could manually control multiple calls to use same DB connection via data context.CurrentConnection.Open() and Close(), but with PS 2021 I just have PB DWs and ask PS to make to magic.
Is there any solution? Thanks,
.m
P.S.
In PS 2020 an acceptable workaround could be AEM -> Resources -> Data Source -> Dynamic Database Connection.
Find Questions by Tag
Helpful?
If a reply or comment is helpful for you, please don’t hesitate to click the Helpful button. This action is further confirmation of their invaluable contribution to the Appeon Community.