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

Resolved PS 2021 updating last update user

  1. How-to
0
Votes
Undo
  1. Kevin Ridley
    Kevin Ridley
  2. PowerServer
  3. Monday, 28 February 2022 14:25 PM UTC

I have a client who is moving forward with a conversion to PowerServer 2021 from PB 2017.  Client is up and running with a test version but has a few things to resolve before they can make a final decision.  The most important is they have many tables with audit columns (created by user and timestamp / last updated by user and timestamp.  Obviously the timestamp will work the same, but how are others getting around the create user and last update user?  Obviously we could change every datawindow (probably 2000) to trigger on the itemchanged etc, but that's a very large undertaking.  Currently in PB2017 db connections are per user, and there is a trigger that takes the user from the db connection, but that isn't going to work with the connection cache.

 

Thanks,

Kevin

Cloud Application
Datawindow fails update on PowerServer
Responses (2)
Ronnie Po
Ronnie Po Accepted Answer Pending Moderation
  1. Monday, 28 February 2022 17:44 PM UTC
  2. PowerServer
  3. # 1

Hey Kevin,

Here's one possibility that requires less re-coding but an additional round trip to the database on each insert and update:

1. Change the app's login process to pass along the session id along with the user id and password. If the login is successful, save the user id and session id values in a "live sessions" table in the DB.

2. In the DW or DS ancestor code, right before calling resetUpdate(), use the app's session ID, the DW's update table, and the "live sessions" table to issue your audit SQL statements (or call a custom DB function that takes session ID and table name as arguments).

You'll also need to delete from the "live sessions" table at log out, plus some clean up routine to handle sessions that ended abnormally (without logging out).

Comment
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Monday, 28 February 2022 15:07 PM UTC
  2. PowerServer
  3. # 2

Hi Kevin;

  While the UserID & Password can be passed through to PS2021 if you use a "Dynamic Connection" in the Database section of the PS Project's Web API tab page - the PS2021 server itself will only use that for the DBMS login. It will not know what column(s) to also apply this information to (specially the UserID).

  The idea of handling this in the itemchanged event of the DC/DS is the best idea. Especially if you have one common ancestor DC/DS. If not then yes, that would be a very tedious code refactoring  exercise.

Regards .. Chris

Comment
Load more comments
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Monday, 28 February 2022 15:54 PM UTC
PS: There are "Tons" of PB Apps that do this "Add audit info" to the updated table and/or post updates to related audit tables. I can remember working on dozens of these type of PB Apps when I was an independent consultant that perform this type of audit column(s) updating. Some PB Apps though could be even harder to adapt as the auditing column(s) are not in the DWO's result set. They are hidden from the App and then updated "auto-magically" by either SP's, SF's and/or Triggers. So that would even be a much larger challenge to convert to PS2021.
  1. Helpful 1
Kevin Ridley
  1. Kevin Ridley
  2. Monday, 28 February 2022 17:00 PM UTC
Exactly. This client uses triggers. As you mentioned, the audit columns are not even in the dw SQL. I see you mentioned the dynamic connections. I know we will lose some performance using those, but would the triggers still work? If this is an option, I think they'd go that direction so they don't have to refactor all the dw's and client code.
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Monday, 28 February 2022 18:25 PM UTC
I think that the Trigger approach will work when using PS's "Dynamic Connection" feature as the UserID should be passed into the DBMS thread. Then the trigger(s) can pick up the UID to populate the balance of the DB table's audit related columns. Note: I have not personally tried this yet ... but, in theory. ;-)
  1. Helpful
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.