Version: SQL Anywhere 16.0.0.2127

Scenario:

  1. 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

  2. The .NET app then switches to another user 'BB'. ConnectionString now has user 'BB'.

  3. 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=dotnet-plat-ext-5.0)

  4. 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()

asked 22 Jan, 11:38

chinmaydixit's gravatar image

chinmaydixit
2516
accept rate: 0%


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...

permanent link

answered 22 Jan, 15:56

Volker%20Barth's gravatar image

Volker Barth
37.2k343510773
accept rate: 34%

converted 26 Jan, 03:14

Thanks for your response Volker. I will try CURRENT_USER and USER_NAME() in the trigger.

(22 Jan, 16:21) chinmaydixit
Replies hidden

Well, I would not expect a different result compared to your query, it's just more efficient and shorter...

What do you need that user name within the trigger? In case you just want to log the user doing the update, the DEFAULT LAST USER can do so without needing a trigger...

(22 Jan, 16:47) Volker Barth

The trigger is used for audit logging (log the user who did the update) and the update is done from the front end .NET app.

(25 Jan, 09:10) chinmaydixit

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 :-)

(25 Jan, 14:02) chinmaydixit
1

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...:)

(26 Jan, 03:16) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×15

question asked: 22 Jan, 11:38

question was seen: 152 times

last updated: 26 Jan, 03:16