OK great minds think alike…! 8-[]
Yes context variable is a way to do it as you suggest. My feeling is that the overhead of the context set would likely be small in comparison to all the other selects and updates that you would typically do within that request. So likely a workable solution. I also write audit records, but I tend to stay away from triggers and SPs. I use the DB largely for storage only and of course for it’s SQL engine. So all my stuff happens in code in my framework classes (.NET). Louis From: [email protected] [mailto:[email protected]] Sent: 23 September 2015 01:40 PM To: [email protected] Subject: [firebird-support] Re: RDB$Set_Context v GTT v Disk writes Hi Louis, Thanks for your reply. My application architecture is the same as yours. The user logs onto the middle tier. A session key is generated and stored and all further use from that user is associated with that session key and user id. So we are both in the position of knowing exactly what user is talking to our Middle tier. Now to take it a step further, lets say that a user calls a method which will update a record in the database. That record will fire an update trigger which inserts a row into an audit table. The audit table needs to know the user ID. The most obvious way to do this is to pass in the user id with whatever SQL is performing the update. That is fine but in my case this will require an update to a *lot* of pre existing SQL So I'm looking for a 'cheap' way to perform the following process. 1.) User calls some function on middle tier. 2.) Middle tier grabs a connection from the pool 3.) Middle tier sets the user ID context on the connection with RDB$SET_CONTEXT (or some other method..) 4.) Middle tier performs the database update required by the user 5.) The table update trigger fires and reads the user_id from the context variable. 5.) Operation finished, the connection is returned to the pool. The issue here is that the current method, whether using RDB$SET_CONTEXT or GTT's requires an extra transaction for every user interaction with the server. i.e this is an appreciable amount of overhead. The idea I'm experimenting with at the moment is to use a UDF to effectively call back into the middle tier application to fetch the user id as required. [Non-text portions of this message have been removed]
