TMSChangeNotification in Delphi TService

[JPMi]

I'm trying to create a Windows service based on Vcl.SvcMgr.TService in Delphi. I am hoping to get notifications about data updates from SQL server, using TMSChangeNotification.

I have no problems doing this in a regular application based on Vcl.Forms.TForm.
I'm using the same SDAC components for my service, and I made sure that the properties are the same.

The database connection is enabled in TService.OnStart event, and the TMSChangeNotification.OnChange handler is just supposed to write a message to the log and do a Dataset.Refresh. The connected dataset is a TMSQuery.

dbMonitor shows that the database connection is successful, and that a WAITFOR is executed and is pending.
When I change the data in SQL Server Manager, the first WAITFOR is triggered/completed and a new one is executed and pending.
The TMSChangeNotification.OnChange event in my service is NOT triggered, so no message in the log and the Dataset is not refreshed.

Have anyone tried this, and made it work? Any ideas?

[Stellar]

TMSChangeNotification work requires the presence of an active SQL Server Service Broker that provides built-in message exchange support. To activate SQL Server Service Broker, you can run the script:

Code: Select all

ALTER DATABASE [Database_Name] SET ENABLE_BROKER

Also please make sure that you're executing an SQL statement that is compatible with notifications.
Supported SELECT Statements are described in MSDN article:
https://docs.microsoft.com/en-us/previo ... v=sql.105)

[JPMi]

Thanks, but the same database and the same query with the same options works in a Vcl Forms application.

I think the problem lies elsewhere.

[Stellar]

An example of using TMSChangeNotification in Windows service:

Code: Select all

unit Unit1;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.SvcMgr, Vcl.Dialogs, ActiveX, Data.DB, MemDS, DBAccess,
  Vcl.ExtCtrls, MSAccess, MSClasses;

type
  TService1 = class(TService)
    DelayTimer: TTimer;
    MSConnection1: TMSConnection;
    MSQuery1: TMSQuery;
    MSChangeNotification1: TMSChangeNotification;
    procedure ServiceStart(Sender: TService; var Started: Boolean);
    procedure DelayTimerTimer(Sender: TObject);
    procedure MSChangeNotification1Change(Sender: TObject;
      DataSet: TCustomMSDataSet; NotificationInfo: TMSNotificationInfo;
      NotificationSource: TMSNotificationSource;
      NotificationType: TMSNotificationType);
  private
    function AddToLog(const Value: string);
  public
    function GetServiceController: TServiceController; override;
  end;

var
  Service1: TService1;

implementation

{$R *.dfm}

//CREATE TABLE [dbo].[Dept](
//	[DEPTNO] [int] IDENTITY(1,1) NOT NULL,
//	[DNAME] [varchar](128) NULL,
//	[LOC] [varchar](64) NULL
//) ON [PRIMARY]

function TService1.AddToLog(const Value: string);
begin

end;

procedure ServiceController(CtrlCode: DWord); stdcall;
begin
  Service1.Controller(CtrlCode);
end;

function TService1.GetServiceController: TServiceController;
begin
  Result := ServiceController;
end;

procedure TService1.ServiceStart(Sender: TService; var Started: Boolean);
begin
  AddToLog('ServiceStart()');

  try
    CoInitialize(nil);
    DelayTimer.Enabled := True;
  except
    on E: Exception do
    begin
      AddToLog('ServiceStart(): ' + E.Message);
    end;
  end;
end;

procedure TService1.DelayTimerTimer(Sender: TObject);
begin
  DelayTimer.Enabled := False;

  try
    MSConnection1.Close;
    MSConnection1.Options.Provider := prDirect;
    MSConnection1.Server := 'dbmssql\mssql2008R2';
    MSConnection1.Username := 'sa';
    MSConnection1.Password := '';
    MSConnection1.Database := 'SergeyL';

    try
      MSConnection1.Connect;
    finally
      AddToLog('Connected = ' + BoolToStr(MSConnection1.Connected, True));
    end;
  except
    on E: Exception do
    begin
      AddToLog('Connected(): ' + E.Message);
    end;
  end;

  try
    MSQuery1.SQL.Text := 'SELECT DeptNo, DName, Loc FROM dbo.Dept';
    MSQuery1.ChangeNotification := MSChangeNotification1;
    MSQuery1.ReadOnly := True;
    MSQuery1.Options.ReflectChangeNotify := True;

    try
      MSQuery1.Open;
    finally
      AddToLog('Open = ' + BoolToStr(MSQuery1.Active, True));
    end;
  except
    on E: Exception do
    begin
      AddToLog('Open(): ' + E.Message);
    end;
  end;
end;

function NotificationInfoToString(NotificationInfo: TMSNotificationInfo): string;
begin
  case NotificationInfo of
    niAlter:
      Result := 'Alter';
    niDelete:
      Result := 'Delete';
    niDrop:
      Result := 'Drop';
    niError:
      Result := 'Error';
    niInsert:
      Result := 'Insert';
    niInvalid:
      Result := 'Invalid';
    niIsolation:
      Result := 'Isolation';
    niOptions:
      Result := 'Options';
    niPreviousFire:
      Result := 'PreviousFire';
    niQuery:
      Result := 'Query';
    niResource:
      Result := 'Resource';
    niRestart:
      Result := 'Restart';
    niTemplateLimit:
      Result := 'TemplateLimit';
    niTruncate:
      Result := 'Truncate';
    niUnknown:
      Result := 'Unknown';
    niUpdate:
      Result := 'Update';
    else
      Result := '';
  end;
end;

function NotificationSourceToString(NotificationSource: TMSNotificationSource): string;
begin
  case NotificationSource of
    nsClient:
      Result := 'Client';
    nsData:
      Result := 'Data';
    nsDatabase:
      Result := 'Database';
    nsEnvironment:
      Result := 'Environment';
    nsExecution:
      Result := 'Execution';
    nsObject:
      Result := 'Object';
    nsStatement:
      Result := 'Statement';
    nsSystem:
      Result := 'System';
    nsTimeout:
      Result := 'Timeout';
    nsUnknown:
      Result := 'Unknown';
    else
      Result := '';
  end;
end;

function NotificationTypeToString(NotificationType: TMSNotificationType): string;
begin
  case NotificationType of
    ntChange:
      Result := 'Change';
    ntSubscribe:
      Result := 'Subscribe';
    ntUnknown:
      Result := 'Unknown';
    else
      Result := '';
  end;
end;

procedure TService1.MSChangeNotification1Change(Sender: TObject;
  DataSet: TCustomMSDataSet; NotificationInfo: TMSNotificationInfo;
  NotificationSource: TMSNotificationSource;
  NotificationType: TMSNotificationType);
begin
  AddToLog('');
  AddToLog('New Notification:');
  AddToLog('Notification Info: ' + NotificationInfoToString(NotificationInfo));
  AddToLog('Notification Source: ' + NotificationSourceToString(NotificationSource));
  AddToLog('Notification Type: ' + NotificationTypeToString(NotificationType));
end;

end.

[JPMi]

Thanks Stellar.

While trying to follow your example, I found the real culprit. It was an event handler on the query component which somehow messed up things. I'm not sure why yet. The only thing it did was writing a message to the log.

I don't think the ActiveX unit and CoInitialize are necessary when using prDirect.
I assume that you use the timer to avoid that a slow or failed connect holds back the service start?

At least I receive the notifications now, and can get on with my project.

[Stellar]

Glad to see that the issue was resolved.
You're right, it's not necessary to manually initialize COM when using Direct mode.
Also, the timer was used only for a deferred connection to the server.

[RushabJain]

Is it possible to know when and if the contents of certain tables in a database has changed? How can my SQL Server notify the client applications that the data was changed by another user? How to implement query notifications with dbGo ?

[Stellar]

The dbGo components were developed by Embarcadero. You may refer to the official documentation or look for information on specialized forums.