Hi Team,
How should I protect the following code in the event the script execution inside the thread fails?

{sql}
 //
 Thread := TThread.CreateAnonymousThread(
   procedure
   begin
     //
     DBScriptLineItems.ParamByName('EndDate').AsDateTime := dArchiveDate;
     //
     DBScriptLineItems.Prepare;
     //
     if DBScriptLineItems.Prepared then
       DBScriptLineItems.ExecScript;
     //
   end);
 try
   Thread.FreeOnTerminate := False;
   H := Thread.Handle;
   Thread.Start;
   while MsgWaitForMultipleObjects(1, H, False, INFINITE, QS_ALLINPUT) = (WAIT_OBJECT_0 + 1) do
     Application.ProcessMessages;
 finally
   FreeAndNil(Thread);
 end;
 //
{sql}

Regards & TIA,
Ian

Ian


Firstly

     if DBScriptLineItems.Prepared then
       DBScriptLineItems.ExecScript;

Unless you've screwed the sql in the script up then it'is going to be prepared since you do it in the line above.

Next, without seeing what the script does it might be hard to say how to protect it but I'd recommend use of a transaction and EXCEPTION in the script

Thirdly: Because I'm still happily on D2007 things may have changed but with a thread I think you need to look at isolation of the ElevateDB elements - session, database


Roy Lambert

Hi Roy,

>> Unless you've screwed the sql in the script up then it's going to be prepared since you do it in the line above.

Yes.  Well we both know about my SQL skills.  

>>  Next, without seeing what the script does it might be hard to say how to protect it but I'd recommend use of a transaction and EXCEPTION in the script

Oh!  Hadn't thought of in there but I would still need to 'export' the success, or otherwise.

Ian

Ian


>>> Unless you've screwed the sql in the script up then it's going to be prepared since you do it in the line above.
>
>Yes. Well we both know about my SQL skills.

Did you know Tim has been really generous and provided free of charge a wonderful tool to test SQL (including scripts) called EDBManager? <VBG>

>>> Next, without seeing what the script does it might be hard to say how to protect it but I'd recommend use of a transaction and EXCEPTION in the script
>
>Oh! Hadn't thought of in there but I would still need to 'export' the success, or otherwise.

In that case have a try..except block in the thread procedure. As I said I'm not up to date with threads and parallel processing in the newer Delphis so can't advise on structure of the thread code but to me it looks weird.

On 9/18/2021 5:03 PM, Ian Branch wrote:

> How should I protect the following code in the event the script execution inside the thread fails?

>        DBScriptLineItems.ParamByName('EndDate').AsDateTime := dArchiveDate;

Roy mentioned it also but this does not appear thread safe. See:

https://www.elevatesoft.com/manual?action=viewtopic&id=edb2&product=rsdelphiwin64&version=11&topic=Multi_Threaded_Applications

>        DBScriptLineItems.Prepare;

Why not catch exceptions inside the thread procedure itself since this
can throw something already if script has and error.

This way your thread will run to completion and you could use something
as simple as ReturnValue (with Waitfor) to get the execute result code.


>        if DBScriptLineItems.Prepared then
>          DBScriptLineItems.ExecScript;

This can be just ExecScript withing exception handler like Roy said



>      Thread.FreeOnTerminate := False;
>      H := Thread.Handle;
>      Thread.Start;
>      while MsgWaitForMultipleObjects(1, H, False, INFINITE, QS_ALLINPUT) = (WAIT_OBJECT_0 + 1) do
>        Application.ProcessMessages;

Thread.Waitfor might be easier here and you'd get access to ReturnValue

Raul