TMSChangeNotification in Delphi TService
TMSChangeNotification in Delphi TService
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?
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?
Re: TMSChangeNotification in Delphi TService
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:
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)
Code: Select all
ALTER DATABASE [Database_Name] SET ENABLE_BROKER
Supported SELECT Statements are described in MSDN article:
https://docs.microsoft.com/en-us/previo ... v=sql.105)
Re: TMSChangeNotification in Delphi TService
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.
I think the problem lies elsewhere.
Re: TMSChangeNotification in Delphi TService
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.
Re: TMSChangeNotification in Delphi TService
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.
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.
Re: TMSChangeNotification in Delphi TService
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.
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.
-
- Posts: 1
- Joined: Mon 20 Jan 2020 05:37
- Contact:
Re: TMSChangeNotification in Delphi TService
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 ?
Re: TMSChangeNotification in Delphi TService
The dbGo components were developed by Embarcadero. You may refer to the official documentation or look for information on specialized forums.