Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (5)

Resolved PS 2021 handling of DB context variables

  1. How-to
2
Votes
Undo
  1. Marco Meoni
    Marco Meoni
  2. PowerServer
  3. 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.

 

fake time in mobile app
PowerServer product enquiry
Responses (5)
Logan Liu @Appeon
Logan Liu @Appeon Accepted Answer Pending Moderation
  1. Thursday, 3 June 2021 16:43 PM UTC
  2. PowerServer
  3. # 1

Hi Marco,

In PB 2021 Beta, the database connections are stored in a connection pool, and used only when needed. It is a short-lived connection; it is more secure and less vulnerable to network condition compared to a long-running connection. So it is recommended to redesign the PB code that relies on long-running connections.

There is no workaround for using the long-running connection in PB 2021 Beta. If you want this feature, we suggest that you submit an enhancement ticket on our support portal (at https://www.appeon.com/standardsupport). This way, our product team will analyze this request when they plan to extend PowerServer in the future. 

Regards,

Logan

Comment
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Friday, 4 June 2021 20:00 PM UTC
I think this all is a big change. Powerserver totally bypasses any userid that you pass in into SQLCA when connecting from powerbuilder. This means that if you are using for example Oracle schemas to handle user permissions, this will now totally be gone and you'll have to totally revise your user permissions.

So there goes "migrating in a few days to powerserver" and "being 99.9% compatible" ...

Just my 2cts.

regards.
  1. Helpful
Logan Liu @Appeon
  1. Logan Liu @Appeon
  2. Monday, 7 June 2021 07:19 AM UTC
Hi Miguel,



This topic is about how to keep a long-running database connection in PowerServer 2021 Beta. This feature is not supported at this moment, and we are still doing requirement analysis.



It seems that you are talking about another topic. What you said about connecting SQLCA with different users to handle user permissions is already supported. If you have encountered any issue with your database connection on permissions, please open a ticket in the Appeon Standard Support at https://www.appeon.com/standardsupport/.

Appreciate your understanding.



Regards, Logan
  1. Helpful
There are no comments made yet.
Piotr Sinior
Piotr Sinior Accepted Answer Pending Moderation
  1. Friday, 4 June 2021 13:01 PM UTC
  2. PowerServer
  3. # 2

Hi Logan,

For another project in ASP.NET we have handled a similar case run on Oracle DB, by running additional query:

DBMS_SESSION.SET_CONTEXT('CLIENTCONTEXT', 'USERID', <whoever is the web browser user>)

before each actual business query.

This way we could access the USERID context variable from any function/procedure/sql executed after that.

Since the Snap Develop project comes with compiled .dll libraries, we are not sure if the same approach could be achieved by us, without introducing such additional feature to the framework.

This is why we are looking forward to your feedback on this one.

 

Regards,

Piotr

Comment
mike S
  1. mike S
  2. Friday, 4 June 2021 13:34 PM UTC
other databases such as SQL Server have similar session variable capabilities, so this isn't an oracle only request. Having some capability /option to have PS set a session variable prior to any query would be nice. options on type of dml (select vs update vs delete vs insert, or really just select vs others) that it prepends the session variable setting would be good.

  1. Helpful
There are no comments made yet.
Marco Meoni
Marco Meoni Accepted Answer Pending Moderation
  1. Monday, 7 June 2021 05:01 AM UTC
  2. PowerServer
  3. # 3

Hello,

solution would come from user impersonation, as described by this article:

https://docs.microsoft.com/en-us/powerapps/developer/data-platform/webapi/impersonate-another-user-web-api

but dataContext object in PS 2021 is not accessible as in a REST API project.

Best,

.m

Comment
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Monday, 7 June 2021 13:36 PM UTC
We do support using unique user ID and password to connect to the database. So I don’t see the problem.
  1. Helpful
Marco Meoni
  1. Marco Meoni
  2. Monday, 7 June 2021 14:15 PM UTC
Hello Armeen,

connection is short-lived, i.e. the next request cannot access context variables from previous one.
  1. Helpful
There are no comments made yet.
mike S
mike S Accepted Answer Pending Moderation
  1. Monday, 7 June 2021 14:31 PM UTC
  2. PowerServer
  3. # 4

This would be of huge benefit to allow database triggers in particular to know who the user is.  (stored procedures/functions too, but we do have a work around for those by sending in the user id as a parm). 

It would allow for things like auditing of data changes.   The idea is that since the shared database connections all are shared with the same generic user id, the context variables allow the database know who is making a particular request.  I believe they were added by the major database vendors explicitly for purposes such as this (for web and service requests.)

It would be great to have an option in PS when the PS database connection is made to track the user and have PS add/update  that context variable when a particular PS session runs sql.

 

 

Comment
There are no comments made yet.
Marco Meoni
Marco Meoni Accepted Answer Pending Moderation
  1. Friday, 11 June 2021 08:17 AM UTC
  2. PowerServer
  3. # 5

Hi Logan,
I find Piotr's suggestion a valuable workaround to long-lived connection, if doable.
Would it be possibile to run a custom query before each actual DB request (DW or eSQL)?
That would allow to set the desired context variables.
Maybe this query "extension" is easier than a query "wrapper" based on user impersonation?
Best,
.m

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.