|Subject||system table behaviour|
I am writing a small application for our support staff which includes the ability to see who is connected to a database using the query:
SELECT M.MON$ATTACHMENT_ID, M.MON$USER, M.MON$REMOTE_ADDRESS, M.MON$TIMESTAMP, M.MON$REMOTE_PROCESS FROM MON$ATTACHMENTS M
When the application first runs, it gets the correct number of records.
If a user connects to the database and, without closing the application, I re-run the query, it doesn’t update the result. That is, the list of connected users does not show the new connection. Similarly if someone disconnects, the list is not updated. Closing the application and running it again updates the result.
Using Flamerobin, the results are as expected.
I tried using a stored procedure to give me the list (hoping that moving the query closer to the database might yield more timely results) but that didn’t work either. I have also tried opening and closing the view and file. Other user tables seem to work as you would expect.
Do the system tables cache results or do they behave differently to user tables? I have been scratching my head for a while now and can’t figure it out.