How do I write to a database in an Indy TIdTCPServer, TIdCommandHandler event without thread conflicts?

so you have multiple datasets in a pool, these datasets are connected to ONE (1) IBDatabase (or any other base db connection)
if that's the case then you must either provide a unique connection component for each thread or synchronise the calls
I've done both and both works just fine
I did the synching with SendMessage - like: SendMessage(Handle, WM_HANDLEDBSTUFF, DB_ACTION, Integer(DBObjectInfo));
don't know if it's a FIBPlus issue - I'm using dbExpress

when I used one connection for all datasets without synching I mostly got an "5 - Access denied" error

I am using a DB Connection pool that uses multiple Connection objects. Each object has it's own database connection and dataset (query) components.

I think the problem lmay lie in the fact that I'm obtaining the connection from within the Indy event. Is this not possible due to the Indy's multiple threads? Do I have to use something like SendMessage? If so, could you give a quick example?

something like:

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, IdBaseComponent, IdComponent, IdTCPServer;

const
  WM_HANDLEDB = WM_USER + 123;

type
  TForm1 = class(TForm)
    IdTCPServer1: TIdTCPServer;
    procedure IdTCPServer1Execute(AThread: TIdPeerThread);
  private
    { Private declarations }
  protected
    procedure WMHandleDB(var AMsg: TMessage); message WM_HANDLEDB;
  public
    { Public declarations }
  end;

  PDBRequestInfo = ^TDBRequestInfo;
  TDBRequestInfo = record
    Request: Integer;
    Query: TIBQuery;
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.IdTCPServer1Execute(AThread: TIdPeerThread);
var dbri: TDBRequestInfo;
begin
     // handle the data
     // now get a query
     dbri.Request:=1; // get query
     case SendMessage(Handle, WM_HANDLEDB, 1, Integer(@dbri)) of
       1: begin // if we got a query
         // use that query - in this connections thread
         dbri.Query.SQL:='select * from table';
       end;
       else
       begin
         // unknown result
       end;
     end;
end;

procedure TForm1.WMHandleDB(var AMsg: TMessage);
var dbri: TDBRequestInfo;
begin
     // forms thread - main thread
     case AMsg.WParam of
       1: begin
          dbri:=PDBRequestInfo(AMsg.LParam)^;
          // get our query from the pool
          dbri.Query:=QueryPool.GetQuery;
          // return message result
          AMsg.Result:=Integer(Assigned(dbri.Query));
       end;
     end;
end;

end.

sorry .. missunderstood
when the Query is executed the problems arise
maybe it's a FIBPlus issue .. I didn't experience this with dbExpress
although I'm not using a db pool, I create a connection inside the calling thread (in an Intraweb app - UserSession)

That's right, but I guess it could possibly be something to do with the way in which I retrieve the DB connection.

Can you explain what you actually gain by using a message instead of a simple function call? Sorry it it's a stupid question!

if you call a method of an object that was created in another thread it's still executed in the caller thread
SendMessage call is handled by the receiving window in it's thread
the thread that calls SendMessage waits until the receiving window(thread) handles the message and returns

My equivalent of your "WMHandleDB" procedure is this:

function TDBConnectionPool.GetConnection: TBaseDBConnection;
var
  i: Integer;
begin
  // Get an available connection
  // In a critical section so only one thread at a time can claim a connection.
  AcquireConnection.Acquire;
  try
    Result := nil;

    // Find a free connection
    for i := 0 to pred(FDBConnections.Count) do
      if not TBaseDBConnection(FDBConnections[i]).InUse then
      begin
        Result := TBaseDBConnection(FDBConnections[i]);
        Break;
      end;

    // If no free connections exist, create one
    if not Assigned(Result) then
    begin
      Result := CreateConnection;
      Result.FTempName := IntToStr(i);
      FDBConnections.Add(Result);
    end;

    Result.Acquire;

    // if we got a connection, make sure it's connected
    if Assigned(Result) then
      Result.Connect;
  finally
    AcquireConnection.Release;
  end;
end;

function TDBConnectionPool.CreateConnection: TBaseDBConnection;
begin
  Result := nil;

  // Create a new connection
  {$IFDEF FBCONNECTION}
    if FDatabaseType = dtFirebird then
      Result := TFirebirdDBConnection.Create;
  {$ENDIF}

  // Exception raised if no connection created
  if Assigned(Result) then
  begin
    Result.FDatabase := FDatabase;
    Result.FUsername := FUsername;
    Result.FPassword := FPassword;
  end
  else
    raise Exception.Create('Database Type not implemented');
end;

The DBConnectionPool component is created on the main form of the application. Would the critical section not be the equivalent of calling the method via a Message?

>>Would the critical section not be the equivalent of calling the method via a Message?
nope, a CS merely locks other threads from executing that part of code
the message handler executes in the other thread and blocks the calling thread - and all others ofcourse

your problem is as I suspected - you have one Database
if you want to keep it like this you'll have to synch/protect all the calls to the DB
another option is to create a T(IB)Database for every pooled connection - I prefer this method - you limit the pool size anyway :-)

I am creating a seperate database for every connection. Here's part of the code of a TBaseDBConnection object:

//----------------------------------------------------------------------------------
{ TFirebirdDBConnection }
//----------------------------------------------------------------------------------

constructor TFirebirdDBConnection.Create;
begin
  inherited;

  // Create the database components
  FDatabaseConnection := TpFIBDatabase.Create(nil);
  FTransaction := TpFIBTransaction.Create(nil);
  FQuery := TpFIBDataSet.Create(nil);
  FStoredProcedure := TpFIBStoredProc.Create(nil);
end;

procedure TFirebirdDBConnection.Connect;
begin
  if not FDatabaseConnection.Connected then
  begin
    // Set up the database component
    FDatabaseConnection.DatabaseName := Database;
    FDatabaseConnection.DBParams.Clear;
    FDatabaseConnection.DBParams.Add(format('user_name=%s', [FUsername]));
    FDatabaseConnection.DBParams.Add(format('password=%s', [FPassword]));
    FDatabaseConnection.SQLDialect := 3;

    // Set up the Transaction component
    FTransaction.DefaultDatabase := FDatabaseConnection;
    FDatabaseConnection.DefaultTransaction := FTransaction;

    // Set up the Query component
    FQuery.Database := FDatabaseConnection;
    FQuery.PrepareOptions := FQuery.PrepareOptions + [psAskRecordCount];

    // Set up the Stored Procedure component
    FStoredProcedure.Database := FDatabaseConnection;

    // Open the database connection
    FDatabaseConnection.Open;
  end;
end;

//----------------------------------------------------------------------------------

I can try rewriting the code to utilise Messages. I'll let you know outcome. Any other ideas before I start?

using messages won't help in this case unless you handle the Query execution .. but you can do that with your existing lock and as you said it works
I don't have FIBPlus and can't check .. also don't have the time for that :-/

How do you mean "handle the Query execution"?

Also, if I put the query in a critical section surely only one thread can execute it at a time. This kind of negates the point of using threads. I feel like I'm missing something here! What I'm looking to do is run concurrent database queries. i.e. one thread can be waiting for a long running query to finish while another does a query at the same time.

Also, how would I use the Message code if I'm running it in a TComponent that has no Handle property?

Thanks for your great help so far.

try that DB pooling with dbExpress or some other DB compos and see if they have the same problems
or .. create/destroy all db stuff inside the servers OnConnect/OnDisconnect methods
personaly I'd create a TIdPeerThread descendant with your TBaseDBConnection as a property
you set the servers ThreadClass:=TMyPeerThread;
then in the events you cast the AThread param to your thread class

if you have a stateless system then this might be a bit of an overhead


can I suggest you check out RemObject - www.remobjects.com
I've been using the V2 for over half a year now and it works great .. a real must have for any serious communication
V3 has lots of new goodies .. which I still have to check out :)


about messages
you create a window and a WndProc method like:

//create
var FHandle: Cardinal = AllocateHWnd(WndProc);
// free
DeallocateHWnd(FHandle);

procedure TMyClass.WndProc(var AMsg: TMessage);
begin
  case AMsg.Msg of
    ...
    else AMsg.Result:=DefWindowProc(FHandle, AMsg.Msg, AMsg.WParam, AMsg.LParam);
  end;
end;

Hi Lee,

I've now done the following:

- Created a new TIdPeerThread descendant that contains a DB Connection and Query.
- Removed FIBPlus and replaced it with DBExpress.

Unfortunately, I am still experiencing exactly the same problem.

In order to try to sort this out, I've written a very small application that demonstrates the problem with all the clutter removed. It's only 500K including the executables. Would it be possible for you (or anybody else that wants the points) to download it and try to figure out where the problem lies. I'm pulling my hair out here!!! :o(

You can download the application source here: http:\\www.helion-studios.com\download\ThreadTest.zip

To compile the application you need Delphi 7 (Indy 9 and DBExpress). You will also need either Firebird or Interbase installed although it would be very simple to convert it to another database as it's now using DBExpress. There is a Firebird database included in the directory. It has one table: "ALERTS". The table contains one field: "ALERT_NAME" String(30). That's it. Just complile and run.

When compiled, start a single server application then start a single client application. In the client application, click "Connect" (it will try to connect to the server on the local [127.0.0.1] machine). Click "Continuous Send". You will see a lot of "Data Sent", "Data Received" messages scrolling up the memo. Now, try opening another client and do exactly the same thing. After a few moments both clients will lock up as they are waiting for a response from the server that never arrives. Why?

Thanks for your help with this very frustrating problem.

/Paul

Don't have time to do anything now (23:50 here in Norway), but I'll take a look at work tomorrow.

Thanks for all your effort :o) Hopefully this will fix the problem.

Hi Lee,

I've tried the code above (and turned off CommandHandlersEnabled). It certainly runs better. With two clients it runs for a long time, but with three or four it locks up pretty quick.

I'm still using the same version of Indy however. I'm going to try the latest version, but need the Delphi 7 CD to uninstall the old version. I'm having this delivered to me at 12 today. When I get it I'll try it with the latest version of Indy.

During the meanwhilst, would it be possible to email the executables to me at "norgepaul at hotmail dot com"? This way I can double check that the executable that work on your machine work on mine too.

Cheers

sure, when I get home

Here's something a little strange:

I tried running the sample application with Embedded Firebird and got the same results as you i.e. 4 clients running happily for as long as I left them!!!

Looks like this could be a Firebird C/S problem. Can't believe that somebody's not found it before now though.

I thought of that as well but didn't test it with FB Server
try with new Indy and see if that makes a difference
maybe even try with Indy10, it's very stable now .. not to mention it performs 4x better (with SuperCore enabled) than Indy 9 ;-)

here are the binaries using my method: http://files.delphi-si.com/lee/ThreadTestBin.zip
the db should be in the same dir

Hi Lee,

Looks like the mystery is solved. The problem is to do with the way in which Frirebird connects to the database. If I use the connection string:

"c:\Database.fdb"

then the locking problem occurs. If however the string is:

"127.0.0.1:c:\Database.fdb"

the deadlock goes away. Apparently it is to do with a problem in Firebirds "Local" connectivity.

Thanks for all your help and patience. You certainly pointed me in the right direction and showed me a thing or two about threads.

You get the points. Go crazy :o)

Cheers,
Paul

!! I should've thought of that -- had a similar problem
yes .. using TCP/IP with the server solves that, otherwise it must be singlethreaded - that's even documented :)