In an IBO and FB program I want to run a stored procedure when the first user logs into the DB each day. It could be the procedure takes a while. I wanted to do this without delaying the users start-up time. More generally I have other programs where it would be nice to run a stored procedure and not wait for its return before allowing the user to do other things. Rob martin suggested I run the SP in a separate thread and gave some indicative code. The code by Delphi is in class TThread.

The OTL manual discusses its use for DBs. This library looks nice.

Omni Thread Library is here: http://www.omnithreadlibrary.com/

Its use with databass is discussed here: book:howto:databases [OmniThreadLibrary book]

			book:howto:databases [OmniThreadLibrary book] Table of Contents OmniThreadLibrary and Databases Database Model Frame and Worker Connecting to the Database Retrieving the Data Main Program Omn...
			View on otl.17slon.com	Preview by Yahoo

The code here is in a datamodule with no components. It should be self explaining. I have tested it by running it 101 times in a loop with no errors. However I do not understand well the architecture of Windows Threading so am not sure how to make a stress test for this code. 

unit dmExtThreadpas;

interface

uses

  forms,

  SysUtils, Classes, IB_Components, IB_Session, dialogs,

  utils01, {for sayDiagnosticsLog} globals {for gbcdConnectionDetails}; 

  type Tbcd2ndThread = class(TThread)

    public

      procToRun: string;

      constructor create(aProcToRun: string; aConnectionDetails: TbcdConnectionDetails);

      // the TbcdConnectionDetails is a record with four fields, see below

    protected

      connectionDetails : TbcdConnectionDetails;

      Procedure Execute; override;

  end; // bcd2ndThread

  type

  TdmExtThread = class(TDataModule)

    procedure DataModuleCreate(sender:tobject);

  private

    { Private declarations }

    my2ndThread : Tbcd2ndThread;

  public

    { Public declarations }

    function runProcedure(aProcedure : String):Boolean;

  end; // TdmExtThread

var

  dmExtThread: TdmExtThread;

  my2ndThread : Tbcd2ndThread;

implementation

{$R *.dfm}

procedure TdmExtThread.dataModulecreate(sender:Tobject);

begin

  ;

end; // dataModuleCreate

function TdmExtThread.runProcedure(aProcedure : String):Boolean;

begin

  SayDiagnosticsLog('runProcedure('+aProcedure+') called'); // in utils01

  // gbcdConnectionDetails is in globals

  my2ndThread := Tbcd2ndThread.create(aProcedure, gbcdConnectionDetails); // ctor

  with my2ndThread.connectionDetails do

  begin

    protocol := gbcdConnectionDetails.protocol;  // TIB_PROTOCOL

    password := gbcdConnectionDetails.password; // string

    username := gbcdConnectionDetails.username; // string

    databaseName := gbcdConnectionDetails.databasename; // string

  end; // with

  my2ndThread.FreeOnTerminate := true;

  my2ndThread.start; // start the TThread

end; // runProcedure

constructor Tbcd2ndThread.create(aProcToRun:String; aConnectionDetails: TbcdConnectionDetails);

begin

  inherited create(true); // "true" to start the TThread suspended

  procToRun := aProcToRun;

  connectionDetails := aConnectionDetails;

end;

procedure Tbcd2ndThread.execute;

var

    ssnExtThread: TIB_Session;

    cnExtThread: TIB_Connection;

    trExtThread: TIB_Transaction;

    dsqlExtThread: TIB_DSQL;

  // local scope so destroyed when Execute goes out of scope

begin

  // the TIB_Session for this thread

  ssnExtThread := TIB_Session.create(nil);

  ssnExtThread.UseCursor := false;

  ssnExtThread.DefaultConnection := cnExtThread;

  // the TIB_Connection for this thread

  cnExtThread := TIB_Connection.CreateForSession(nil,ssnExtThread);

  cnExtThread.DefaultTransaction := trExtThread;

  cnExtThread.IB_Session := ssnExtThread;

  // The TIB_Transaction for this thread

  trExtThread := TIB_Transaction.CreateForSession(nil,ssnExtThread);

  trExtThread.IB_Session := ssnExtThread;

  trExtThread.IB_Connection := cnExtThread;

  trExtThread.Isolation := tiCommitted;

  // the TIB_DSQL used in this thread

  dsqlExtThread := TIB_DSQL.CreateForSession(nil,ssnExtThread);

  dsqlExtThread.IB_Connection := cnExtThread;

  dsqlExtThread.IB_Transaction := trExtThread;

  cnExtThread.database := connectionDetails.databaseName;

  cnExtThread.Username := connectionDetails.Username;

  cnExtThread.password := connectionDetails.password;

  cnExtThread.protocol := connectionDetails.protocol;

  cnExtThread.connect;

  if cnExtThread.Connected then

  with dsqlExtThread do

  begin

    sql.text := 'execute Procedure '+procToRun;

    prepare;

    Execute;

  end;  // if connected

  Terminate; // this thread has done its work

end; // Execute

end.