PowerBuilder classic, currently using PB 2017
I have a large old PowerBuilder application + satellite applications. Like many large old PowerBuilder applications it uses SQLCA for 99% of its transaction handling. It also uses stored procedures, datawindows, and embedded SQL to update data.
We want to add auding via database triggers, so we can track anything that changes data in the database no matter who write the application in question. We'd like to know what application and what window was being used when the update took place, when the updates come from our applications
As far as I can tell I either have to pass that information in with every SQL statement, something that's not going to be possible, or use the SQL Server SET CONTEXT_INFO command to store 128 bytes of useful information that the triggers can read. However since a user could be using multiple windows at the same time(ish, I mean it is a single transaction, and that could get confusing) that not really going to cover it.
So I think I need to change every window to have its own transaction, and that's a lot of quite difficult work that I'm not relishing.
Anyone have any ideas as to the best way to go about this?
Note: The trigger generator is written and working, and in use, are is all the SET CONTEXT_INFO stuff, it's just a matter of updating the large behemoth applications to use the triggers (currently turned off via a single SET CONTEXT_INFO call)
Some things I am thinking of:
* Bah, don't worry about two windows a tthe same time, it's not as if PowerBuilder / our application can really handle that, I mean wouldn't commits and rollbacks get all confused already??? (Is that true?)
* Use the SQLPreview event to call SET CONTEXT_INFO everywhere, perhaps reading the call stack to get the required information (see fastfuncs for more details https://github.com/lakeman/fastfuncs)
* Shut up and get on with creating a single transaction for each window
Thanks, any thoughts are welcome :)