on 2021 Jan 22 11:38 AM
Version: SQL Anywhere 16.0.0.2127
Scenario:
A .NET app connects to the db with a user 'AA'. ConnectionString has user 'AA'. Connection Pooling: ON Provider: iAnywhere.Data.SQLAnywhere.v4.5.dll
The .NET app then switches to another user 'BB'. ConnectionString now has user 'BB'.
There are no new connections in the db with user 'AA'. Verified this by calling ClearAllPools method. (https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.clearallpools?view=d...
The .NET app then UPDATEs a record in a table with user 'BB'. This fires the BEFORE UPDATE trigger of the table.
Issue:
A call to function User_Id() inside this trigger is returning the previous user 'AA' even though user 'BB' made the UPDATE. This seems like a SQL Anywhere defect to me.
Query inside the trigger:
SELECT user_name FROM sysuser WHERE user_id = User_Id()
Request clarification before answering.
Just a hint: The name of the current user is available without a query, just use the user_name() builtin function or the "current user" special value...
That being said, you are running a quite old v16 build. There have been several fixes to connection pooling, see here. So a newer build might be worthwhile to try...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Using USER_NAME() in place of USER_Id() actually worked !!!
USER_NAME() returns the correct user inside the trigger in the scenario where the user id was switched. I am wondering how the implementation would be different within Sybase.
Thank you so much Volker !!! You made my day. You are a rock star 🙂
Well, I would not expect a different result compared to your query, it's just more efficient and shorter...
Using USER_NAME() in place of USER_Id() actually worked !!!
LOL, I'm glad my expectation was wrong –even if I don't understand why it does make a difference...:)
User | Count |
---|---|
82 | |
29 | |
9 | |
8 | |
7 | |
7 | |
7 | |
6 | |
6 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.