Thread

[MVakili]

Sometimes, we want to run a store procedure in the server, which takes a lot of time, and in this case, the page even gets disconnected.
To solve this problem, it is enough to create a Thread in the program and only monitor its execution
Here I present the solution I created for myself and I will be happy if you help to improve it

1- create a temp table for list of process 

    DMt.ProccessTable:='_X_'+DMt.CurrentPreName;
    DMt.Exec_SQLClient('If Not Object_Id('+QuotedStr(DMt.ProccessTable)+') is Null Drop Table '+DMt.ProccessTable);
    DMt.Exec_SQLClient(' Create Table '+DMt.ProccessTable+' ([StartDT] datetime DEFAULT (getdate()),[PName] NChar(50) ,[PID] int )');

2- define type

Type
  TSQLQueryThread = class(TThread)
  private
    FID: Integer;
    FQuery: string;
    FTempTable : String;
    FConnection: TMSConnection;
  protected
    procedure Execute; override;
  public
    constructor Create(const AID: Integer; const AQuery: string; AConnection: TMSConnection;ATempTable:String);
    property ID: Integer read FID;
  end;

2- functions for Thread

procedure TSQLQueryThread.Execute;
var
  Query: TMSQuery;
begin
  Query := TMSQuery.Create(nil);
  try
    Query.Connection := FConnection;
    Query.SQL.Text := FQuery;
    Query.Execute;
  finally
    Query.Free;
  end;
end;

constructor TSQLQueryThread.Create(const AID: Integer; const AQuery: string; AConnection: TMSConnection;ATempTable:String);
begin
  inherited Create(False);
  FID := AID;
  FQuery := AQuery;
  FConnection := AConnection;
  FTempTable:=ATempTable;
end;

procedure TDMT.TH_QueryThreadTerminated(Sender: TObject);
Var
   FinalQ : TMSQuery;
begin
    FinalQ := TMSQuery.Create(nil);
    try
      FinalQ.Connection := TSQLQueryThread(Sender).FConnection;
      FinalQ.SQL.Text := 'Delete From '+TSQLQueryThread(Sender).FTempTable+' Where PID='+TSQLQueryThread(Sender).FID.ToString;
      FinalQ.Execute;
    finally
      FinalQ.Free;
    end;
end;

4-main procedure for call

Procedure TDMT.TH_Execute(Titr,SQLCMD:String);
var
  SQLQueryThread: TSQLQueryThread;
begin
  Inc(FQueryCounter);
  DMt.Exec_SQLClient('Insert Into '+Dmt.ProccessTable+'(PName,PId) Values ('+QuotedStr(Titr)+','+FQueryCounter.ToString+')');
  SQLQueryThread := TSQLQueryThread.Create(FQueryCounter, SQLCMD, ClientConnection,DMt.ProccessTable);
  SQLQueryThread.OnTerminate := TH_QueryThreadTerminated;
  SQLQueryThread.FreeOnTerminate := True;
end;

5- Now We are ready to call it

DMT.TH_Execute(' any title ','Exec ..........');

6- You can use the following command to display the list of running processes and their duration

SQL.Text:='SELECT PID,Pname,   Trim(CONVERT(CHAR,DATEDIFF(mi, StartDT, GETDATE())))+'':''+CONVERT(CHAR,DATEDIFF(ss, StartDT, GETDATE())% 60) Dis FROM '+DMt.ProccessTable;

 

and finally we have controller for our program

[Oliver Morsch]

see https://docs.devart.com/sdac/faq.htm:

Are the SDAC connection components thread-safe?

Yes, SDAC is thread-safe but there is a restriction. But the same TCustomMSConnection object descendant cannot be used in several threads. So if you have a multithreaded application, you should have a TCustomMSConnection object descendant for each thread that uses SDAC

[MVakili]

9 hours ago, Oliver Morsch said:

see https://docs.devart.com/sdac/faq.htm:

Are the SDAC connection components thread-safe?

Yes, SDAC is thread-safe but there is a restriction. But the same TCustomMSConnection object descendant cannot be used in several threads. So if you have a multithreaded application, you should have a TCustomMSConnection object descendant for each thread that uses SDAC

Thank you for your explanation
The purpose of sharing was that, in addition to novice friends having an easy start, professional friends could also check its flaws.

[MVakili]

And as an explanation, I have to say, we used the virtual table as a powerful and highly flexible memory table in the software.
I will put an example of the used functions for your review.

1- These sub-programs are used to save and retrieve virtual tables in SQL Server tables, so you can define any field of the table as a NVarChar(max) and store a virtual table in it.

//--------- Wide string to TStringStream
procedure  TDMT.WS2Mem(S: string; Var K : TMemoryStream);
var
  StringStream: TStringStream;
begin
   StringStream:=TStringStream.Create(S);
   K:=TMemoryStream.Create;
   K.Position := 0;
   K.CopyFrom(StringStream,StringStream.Size);
end;
//--------- TStringStream to WideString

function  TDMT.Mem2WS(MemoryStream : TMemoryStream): string;
var
  StringStream: TStringStream;
begin
  Result:='';

    StringStream:= TStringStream.Create('');
  try
    MemoryStream.Position := 0;
    StringStream.CopyFrom(MemoryStream, MemoryStream.Size);

    Result:= StringStream.DataString;
    Result := Result;
  finally
    FreeAndNil(StringStream);
  end;

end;

2- Example of using 

       VarTable:=TVirtualTable.Create(Self);
       with VarTable Do
          Begin
             Close;
             if DMT.Mem2WS(InVarStr)<>'' then
                LoadFromStream(InVarStr);
             Else
                Begin
                   AddField('Fname',ftString,20);
                   AddField('FVal',ftWideString,20);
                End;

 

Var
   VarStr : TMemoryStream;
begin

   VarStr:=TMemoryStream.Create;
   DMT.WS2Mem(DMT.QAskAnswers.FieldByName('SavedValues').AsString,VarStr );

 

             Try
                MemParams.Close;
                MS:=TMemoryStream.Create;
                WS2Mem(DMT.QAskAnswers.FieldByName('RepParameters').AsString,MS);
                if Mem2WS(MS) <> '' then
                   MemParams.LoadFromStream(MS);
             Finally
                FreeAndNil(MS);
                MemParams.open;
             End;