MVakili Posted May 6, 2023 Share Posted May 6, 2023 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 2 Quote Link to comment Share on other sites More sharing options...
Oliver Morsch Posted May 7, 2023 Share Posted May 7, 2023 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 Quote Link to comment Share on other sites More sharing options...
MVakili Posted May 7, 2023 Author Share Posted May 7, 2023 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. Quote Link to comment Share on other sites More sharing options...
MVakili Posted May 7, 2023 Author Share Posted May 7, 2023 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; Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.