Creating Service Application with SQL Server Comutication

[fayyaz]

Hi All

I want to create a service application with Delphi that can connect to SQL DataBase
I can create service application but the ADOQuery don't work inside the service
Is any one that can tel me what should I Do?
Thanks

[Wolfgan]

Are you doing something wrong. I've done, everything works fine. Show your code.

[fayyaz]

Dear Wolfgan
Thank you for your replay
I should better to describe that I have Don this project with text file and also with local access database and it work properly
but when I change connection string of ADOConnection to connect to same database in SQLServer it Doesn't work Properly also when I do this project as a desktop application with SQL Server it has no problem but in service application mode it doesn't work.
here is my code

unit TestServiceU;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, SvcMgr, Dialogs,
  ExtCtrls, DB, ADODB, DBTables;

type
  TTestService = class(TService)
    ADOQuery1: TADOQuery;
    procedure ServiceStart(Sender: TService; var Started: Boolean);
  private
    { Private declarations }
  public
    function GetServiceController: TServiceController; override;
    { Public declarations }
  end;
Type
  TMyThread = Class(TThread)
  private
    procedure doProgress;
  protected
    procedure Execute; override;
  end;
var
  TestService: TTestService;
  T:TMyThread;

implementation

{$R *.DFM}

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

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

procedure TmyThread.doProgress;
Var
    Test:TextFile;
begin
    TestService.ADOQuery1.Parameters[0].Value:='bla bla bla...';
    TestService.ADOQuery1.ExecSQL;

    AssignFile(Test,'C:\Test');
    Rewrite(Test);
    WriteLn(Test,'With Text File Is Ok Only when I remark ADOQuery Statments');
    Close(Test);

    Sleep(1000);
end;


procedure TmyThread.Execute;
begin
  While true Do
  Begin
    Synchronize(DoProgress);
  End;
End;

procedure TTestService.ServiceStart(Sender: TService;
  var Started: Boolean);
Var
    wData : Array[0..32] of Word;
begin
    With TestService.ADOQuery1.Sql Do
    Begin
      Clear;
      Add('Update');
      Add('Adam1_In_Data');
      Add('Set Value=:p');
    End;

  T := TMyThread.Create(True);
  T.FreeOnTerminate := True;
  T.Resume;
end;

end.

[fayyaz]

Excuse me
I should notify that I just tested this project on a local SQL Server and it was Ok
I mean that the problem is only wit SQL Server on the network.
But I don't know why?

[Wolfgan]

You are not quite right uses components. I do so:

private
  ADOConnection: TADOConnection;
  ADOQuery: TADOQuery;

.......

procedure TTestService.ServiceStart(Sender: TService; var Started: Boolean);
begin
  ADOConnection := TADOConnection.Create(nil);
  ADOConnection.ConnectionString := 'bla..bla'; // 
  ADOQuery := TADOQuery. Create(nil);
  ADOQuery.Connection := ADOConnection;
  ADOQuery.SQL.Text := 'bla bla';
end;

I also do not understand why you are using a thread. Service is also a thread. He has the appropriate means.

procedure TESABExchangeService.ServiceExecute(Sender: TService);
begin
   while not Terminated do 
   begin
 
      do something
      
   
      ServiceThread.ProcessRequests(false); // message queue
      Sleep(50);   // It is necessary that would not load the processor
   end;
end;

[fayyaz]

Dear Wolfgan
Thank you very much for your attention and for your comments.
I Wrote a sample Service as same as you wrote in last post hear is the code of that service

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, SvcMgr, Dialogs,
  DB, ADODB;

type
  TADAMform = class(TService)
    ADOConnection1: TADOConnection;
    procedure ServiceStart(Sender: TService; var Started: Boolean);
    procedure ServiceExecute(Sender: TService);
  private
    { Private declarations }
    ADOConnection: TADOConnection;
    ADOQuery: TADOQuery;
  public
    function GetServiceController: TServiceController; override;
    { Public declarations }
  end;

var
  ADAMform: TADAMform;

implementation

{$R *.DFM}

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

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

procedure TADAMform.ServiceStart(Sender: TService; var Started: Boolean);
begin
  ADOconnection:=TADOConnection.Create(Nil);
  ADOQuery:=TADOQuery.Create(Nil);
  ADOQuery.ConnectionString:='Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Data_Transform;Data Source=fayyaz';
  ADOQuery.Connection:=ADOConnection;
  ADOQuery.SQL.Text := 'Update Adam1_In_Data Set Value=1234';
end;

procedure TADAMform.ServiceExecute(Sender: TService);
begin
  While True Do
  Begin
    AdoQuery.ExecSQL;
    ServiceThread.ProcessRequests(false);
    Sleep(1000);
  End;
end;

end.

Is Every thing Ok?

But when I try to start this service I receive an Error
the error message is in attached file
Thank you for your helps.

[Wolfgan]

All true. If the service does nothing - it stops. I've attached an example that works. I checked. I hope you know that the first service must be registered in the system? For this command (Administrator rights are required):
ADAMservice.exe /install

[fayyaz]

Hi WilfGan

You are Really Kind. I learn very much from your Example. and your Comments.

I have some progress to solve my problem but some questions make me crazy.
believe that I do every thing that I know but can not understand the answers of this questions.

1- Your Example Didn't Work On my computer and appear the last error message. so I understand that if I remove ADO commands from OnStart and OnExecute procedures and write another statement instead of ADO Commands; it run normally and does not appear any error.
So What is the deference between simple statements and the statement that work with ADO Components?
2- In last 2 E mail you told me that it is not necessary to Use Thread because the service is already a Tread. I think you are right. it is true but when I want to use ADO components in my service I have to use Tread because if I don't use Tread it will appear error in start time. and I don't understand why?
3- my service Application work Properly with local SQL Server but If I change my connection to connect to another server on my network it doesn't work again. so what is the deference between local Connection and Connection via the network? (I am network administrator and have administrator rights)
my final code that work properly with local SQL Server is attached to this post.

[Wolfgan]

You misunderstand basis. Any program itself contains a message loop. At your service, it is not. When you start your service, it starts a thread. More service, do nothing, nor any instructions there for this reason it completes its work. On this you and inform.
In order for service has not ended, you need to implement event implementation. See the attached example. Just pay attention that the cycle is better to check the property Terminated. This property is true when the service portebuetsya completion. Otherwise you risk to be able to stop him. This also applies to Thread.
Implementation of SQL in the cycle seems unlikely. Obviously you need a single execution of this command. Consequently, the team should be run at the coming of the events (eg onset of a certain time). Therefore, one should check in the loop is not the right time has come. If the time has come - the command (it is quite possible to run n

[fayyaz]

Dear Wolfgan

Thank you for your good Comments.
I have two mistakes on My Project that when I correct them all of the Errors removed from my project

1. Connecting to SQL Server takes a few time and ADO Components in service application before connection raised a timeout exception and this exception cause to terminate service unexpectedly. so I increase the timeout value of ADO Connection and the connection failure removed.

2. I used Windows Authentication for connecting to SQL Server and Windows authentication Is not Known by Service Application. so I Use an existing User account for Connecting to SQL Server Instead of Windows Authentication. By this change The Problem of Connecting to Remote SQL Server On the network solved.

Thank you for spending your time for me.
Thank you for your Good Comments.
Thank you for useful Examples.
And Thank you for every thing.

Mohammad Hadi Fayyaz

[Wolfgan]

Glad that helped. Good luck.