Question
Appriss
US
Last activity: 5 Aug 2019 22:31 EDT
How to set a values for a connection to an external Oracle database connection
My application is working with an external Oracle database as it's SOR. We have defined the connection to this database in a Database rule, using a JDBC Connection Pool. The connection string defines a user created specifically for our Pega application for the connection. So, any connections that are made made from the pool appear to be from this user.
We now have an auditing requirement to know the identity of the logged in user that executed the CRUD operations that we do to this database. There is a way to do this by calling a procedure when the connection is initiated according to this article: https://oracle-base.com/articles/misc/dbms_session
We have very few Connect-SQL rules defined in our app, and are instead using the Data classes that map to the external database. Does Pega provide any hooks or options into the connection process on call this stored procedure to set the client information for the connection? This seems like this would be needed by people who deal with connection pools and auditing for an external database.
Regards,
Brian
Accepted Solution
Ernst & Young LLP
MT
3. If Declare Triggers are viable, can they be setup in a parent class so it will run for any of it's children? - Yes, assuming your data class structure is similar to the following:
Org-Div-FW-YourAppFW-Data
Org-Div-FW-YourAppFW-Data-ExtDB
Org-Div-FW-YourAppFW-Data-ExtDB-Table1
Org-Div-FW-YourAppFW-Data-ExtDB-TableN
Create your trigger rule (one for committed save, and one for committed delete) in Org-Div-FW-YourAppFW-Data-ExtDB class, and it would get triggered for all sub data classes of it.
PEG
GB
You can add a trigger on the Oracle user, so this will be called when they log in.
But if you're using pooled connections I believe this would be when a new connection was taken by the pool. Which may not be when a connection was requested by the Pega Engine from the application server.
Is this what you were asking?
Appriss
US
Not exactly.
We are required to create audit records in our external database whenever a user makes a change (insert, update, delete) to the tables contained there. Since the connection pool is controlled by a single system user, we do not currently know who is making these changes.
Ideally, what we'd like to do is be able to run some code just prior to or after a save / update / delete to run some code to allow us to call an Oracle stored procedure to populate the connection with the current Pega users ID. In prior frameworks I've used, there were sometimes hooks available called PreSave / PostSave to do things like this. I see some references to a PreSave activity in @baseclass that indicates it may be an extension point, but it doesn't really describe how to use it. Does the PreSave get ran for both inserts and updates? What about deletes?
I've looked at using Declare Triggers to attempt to do this, but the concern I have with this route is having to create on for every class we need to do it on. So, for example if I have 2 data classes defined as MyOrg-MyDiv-Data-MyTable1 and MyOrg-MyDIv-Data-MyTable2 is there a way for us to create a Declare Trigger on MyOrg-MyDiv-Data that will run whenever data is saved / deleted on class that inherits from it?
Boiling all of that down into 3 questions:
1. Does Pega provide any out of box "hook" that run during the save / delete process?
Not exactly.
We are required to create audit records in our external database whenever a user makes a change (insert, update, delete) to the tables contained there. Since the connection pool is controlled by a single system user, we do not currently know who is making these changes.
Ideally, what we'd like to do is be able to run some code just prior to or after a save / update / delete to run some code to allow us to call an Oracle stored procedure to populate the connection with the current Pega users ID. In prior frameworks I've used, there were sometimes hooks available called PreSave / PostSave to do things like this. I see some references to a PreSave activity in @baseclass that indicates it may be an extension point, but it doesn't really describe how to use it. Does the PreSave get ran for both inserts and updates? What about deletes?
I've looked at using Declare Triggers to attempt to do this, but the concern I have with this route is having to create on for every class we need to do it on. So, for example if I have 2 data classes defined as MyOrg-MyDiv-Data-MyTable1 and MyOrg-MyDIv-Data-MyTable2 is there a way for us to create a Declare Trigger on MyOrg-MyDiv-Data that will run whenever data is saved / deleted on class that inherits from it?
Boiling all of that down into 3 questions:
1. Does Pega provide any out of box "hook" that run during the save / delete process?
2. Can the PreSave activity be used here to do what we need? If so, what about Deletes?
3. If Declare Triggers are viable, can they be setup in a parent class so it will run for any of it's children?
Regards,
Brian
Accepted Solution
Ernst & Young LLP
MT
3. If Declare Triggers are viable, can they be setup in a parent class so it will run for any of it's children? - Yes, assuming your data class structure is similar to the following:
Org-Div-FW-YourAppFW-Data
Org-Div-FW-YourAppFW-Data-ExtDB
Org-Div-FW-YourAppFW-Data-ExtDB-Table1
Org-Div-FW-YourAppFW-Data-ExtDB-TableN
Create your trigger rule (one for committed save, and one for committed delete) in Org-Div-FW-YourAppFW-Data-ExtDB class, and it would get triggered for all sub data classes of it.
Appriss
US
Thank you, that is exactly what I was looking for. I have implemented this as described with a single Committed-Save and Commited-Delete at the Parent level, and it is triggering for it's children. I was able to place the logic in these triggers to make a call to the stored procedure as I needed to do.
Regards,
Brian