How to use TSession Class of BDE in Delphi?

Question

I am using mutiple connection using ODBC. In whole project I am using the same connection, but create, use and destory TQuery object. Now I am going to use connection in threads and came to know Delphi BDE provides TSession Class for that. I want to know how to use TSession for concurrent operation, please provide code sample for that if possible.

Answer 1

While I agree that the BDE is old, it is possible to create thread-safe access to the database using the BDE and TSessions.

Consider this. When two copies of the same application are running at the same time, the database engine or database server distinguishes between the two instances for the purpose of record and table locking. This distinction is possible because each application uses a separate connection, or in the case of the BDE, session.

The session is represented by a TSession instance. In single threaded projects the TSession is created for you. If you want to connect to the BDE with two or more threads, each should have its own TSession.

Using multiple TSessions is demonstrated here, in this really old code example that I dug up (it is old, and I would do it differently today, but you asked for it). The trick is that each session needs to have the same network directory and have a unique private directory. Here is the TThread descendant:

 type
  TWriteData = class(TThread)
  private
    FSQL: String;
    FFileName: String;
  protected
    procedure Execute; override;
  public
    constructor Create(CreateSuspended: Boolean; const SQL: String;
      const FileName: String); override; overload;
  end;

Here is the overridden constructor:

constructor TWriteData.Create(CreateSuspended: Boolean; 
 const SQL: String; const FileName: String);
begin
  inherited Create(True);
  FSQL := SQL;
  FFileName := String;
end;

And here is the execute method. Importantly, the TSession.PrivateDir is set to a unique directory name (based on the ThreadID). Could also use a GUID, or some other value, as long as it is unique. Note also that Session1 is a TSession component on the data module, and Query1 is a TQuery that uses a TDatabase (Database1), which in turn uses Session1. Session is a variable declared in the Bde.DBTables unit. This variable refers to the default TSession that the BDE creates for the BDE TDataSets that are active in the primary thread of execution.

procedure TWriteData.Execute;
var
  DataMod: TDataModule1;
  AppDir: String;
begin
  AppDir := ExtractFilePath(Application.ExeName);
  DataMod := TDataModule1.Create(nil);
  try
    with DataMod do
      begin
        //All sessions need a unique private directory
        Session1.PrivateDir := AppDir + IntToStr(Self.ThreadID);
        //All sessions share a common network control file
        Session1.NetFileDir := Session.NetFileDir;
        ForceDirectories(Session1.PrivateDir);
        try
          Query1.SQL.Text := FSQL;
          ClientDataSet1.Open;
          ClientDataSet1.SaveToFile(AppDir + FFileName);
          ClientDataSet1.Close;
        finally
          SysUtils.RemoveDir(Session1.PrivateDir);
        end; //try
      end; //begin
  finally
    DataMod.Free;
  end;
end;

I hope this helps.