SOAP Server and DB connection

Question

probably this thread is not only specific to Database...

I wrote a project where a SOAP Server called by a web interface runs some operations on a db and then answer to the client. I'm using Delphi 2007 and MSSQL.

I'm testing this project on windows XP and I try to deploy my SOAP Server either internally in IIS (like ISAPI dll or also like CGI exe) or like a winnt service.

In any case sometimes I have problems with db connection, I say "sometimes" because the error is not constant but occasional. The exception is exactly in the opening of db connection.

To open/close the db I use this simplecode (that I already used in many applications with no problems):

procedure TDataMod1.OpenDB;
begin
  try
    if not ADOConnection1.Connected then
    begin
      ADOConnection1.Open;
    end;
  except
    on E:Exception do
    begin
      raise Exception.Create('Exception opening DB: '+E.Message)
    end;
  end;
end;

Mostly the eexception is an horrible "Access violation at address 01173E02 in module 'WebService.dll'. Read of address 00000058", otherwise also a strange "Operation is not allowed when the object is open" even if I tested the Connected value (I try also with ADOConnection1.State=[ADODB.stClosed].

When this error occurs then I receive many exceptions and it gets worse to the unavailability of SOAP Server.

A particular feature is that making some tests on Windows 2003 (so IIS 6) it is more stable and there are no errors connecting to the db.

Could you give me any advice on this problem?

And also a hint: : which is the best provider to connect toMSSQL? SQL Native Client or OLE DB Provider?

Thanks Bye

score 0 · Answer 1 · edited May 23 '17 at 11:57

We are doing this for a legacy Delphi application and we had a similar issue.

I suspect your problem might be that the SOAP server is receiving several requests at the same time and you are using ONE ADO connection to service them all. The SOAP components spawn a thread per request. Thus if two requests hits your server at more or less the same time you are trying to use a component that is already being used or is already open.

We had to create our own connection pooling mechanism by hand and then assign a thread to a unused connection component.

Delphi is unfortunately way behind Java and C# in this area so you will have to develop some boiler plate code here. See this link How do I make connection pooling work in DBX? for some more information.

Good Luck!

SOAP Server and DB connection

1 Answers1