Querying Mon$Attachments table

[bzwirs]

What is the proper way to query a Firebird Mon$ table. I run the following function to determine how many active users are attached to a database:

Code: Select all

function TForm1.Get_UserCount: integer;
var
  iUsers  : integer;
begin
  with MiscSQL do
  begin
    Close;
    with SQL do
    begin
      Clear;
      Add('SELECT COUNT(MON$ATTACHMENT_ID)');
      Add('FROM MON$ATTACHMENTS');
      Add('where MON$ATTACHMENT_ID <> CURRENT_CONNECTION and');
      Add('      MON$SYSTEM_FLAG = 0 and');
      Add('      MON$STATE = 1')
    end;
    ExecSQL;
    iUsers := Fields[0].AsInteger;
  end;
  Result := 1 + iUsers;
end;

This function fires every 2 minutes (using timer component) and updates the figure on the task bar of the application. Even though I know the users logged in changes often the result is always the same. What am I doing wrong?

Any help would be appreciated.

regards

Bill Zwirs

[ViktorV]

The similar question has already been discussed on our forum. Follow the link viewtopic.php?f=24&t=31244 for details. To solve the issue, try to execute the TIBCTransaction.Commit method before opening the dataset. Note, if you are using a default transaction in the TIBCQuery component, then after calling the Commit method, all the datasets using this transaction will be closed. Therefore, to avoid this behavior, it is better to use a separate transaction for the TIBCQuery component, in which you execute a query to the MON$ATTACHMENTS system table.

[bzwirs]

Thanks Viktor. Using a separate Transaction component and executing the commit method before opening the dataset did the trick.

regards

Bill Zwirs

[ViktorV]

It is good to see that the problem has been solved.
Feel free to contact us if you have any further questions about IBDAC.