Threaded Example Help

[Bill Gage]

I am unconfident with my ability to deal with threading, and I need some help making sure I do this right.

I am using the Threading Example as a base for what I need to do.

What I want to be able to do it specify a MSConnection and the SQL to execute within the thread.

Is there a way to pass this information to the thread during its creation?

Bill

Here is the code I am using with TODOs in the place where I need to put the appropriate information.

function TdmMain.RunThread(ObjectPtr: pointer; Method: pointer; EndMethod: pointer): THandle;
var
ThreadId: DWORD;
MethodDesc: PMethodDesc;
begin
New(MethodDesc);

MethodDesc.ObjectPtr := ObjectPtr;
MethodDesc.Method := Method;
MethodDesc.EndMethod := EndMethod;
MethodDesc.hWindow := frmMain.Handle;

InterlockedIncrement(ThreadCount);
result := CreateThread(nil, 1000, @ThreadProc, MethodDesc, 0, ThreadId);
end;

procedure TdmMain.Execute;
var
Data: TdmThread;
ThreadNum: integer;
i: integer;
begin
i := CoInitializeEx(nil, COINIT_APARTMENTTHREADED);
if i S_OK then
raise Exception.Create('err - ' + IntToStr(i));

EnterCriticalSection(hCountSec);
Inc(ThreadNumber);
ThreadNum := ThreadNumber;
LeaveCriticalSection(hCountSec);

Data := TdmThread.Create(nil);
try
try
with Data do
begin
MSConnection.Assign({MY CONN});//todo: need to assign the appropriate connection
MSConnection.LoginPrompt := False;
MSConnection.Connect;
//todo: assign the desired SQL Statement to execute
MSSQL.Execute;
MSConnection.Disconnect;
end;
except
on E:Exception do
ThreadLogIt(IntToStr(ThreadNum) + ' ' + IntToStr(ThreadCount) + ' Exception ' + E.Message, True);
end;
finally
Data.Free;
CoUninitialize;
end;
end;

[Jackson]

If you create an application that uses multiple threads to perform
database operations using SDAC, you must create one additional connection
for each thread. If you want to use single TMSConnection component common for
all threads, you need to synchronize your threads, because multiple TMSQuery
components can't use one TMSConnection component at the same time.
If lifetime of your threads is minimal, like in following example, it is
desirable to set TCustomDAConnection.Pooling = True. TCustomDAConnection has
software pool, which stores open connections with identical parameters. For
more information about TCustomDAConnection.Pooling please see SDAC help.
Following example demonstrates working with SDAC in threads.

Code: Select all

...
type
  TMyThread = class(TThread)
  private
    FMSConnection: TMSConnection;
    FMSQuery: TMSQuery;
  protected
    procedure Execute; override;
  public
    constructor Create(QueryText: string);
    destructor Destroy; override;
  end;
...

constructor TMyThread.Create(QueryText: string);
begin
  inherited Create(True);
  FMSConnection := TMSConnection.Create(nil);
  FMSConnection.Pooling := True;
  FMSConnection.Username := 'sa';
  FMSConnection.Password := '';
  FMSConnection.Server := 'server';
  FMSConnection.Database := 'Test';

  FMSQuery := TMSQuery.Create(nil);
  FMSQuery.Connection := FMSConnection;
  FMSQuery.SQL.Text := QueryText;
  Resume;
end;

destructor TMyThread.Destroy;
begin
  FMSQuery.Free;
  FMSConnection.Free;
  inherited Destroy;
end;

procedure TMyThread.Execute;
var
  hr: HRESULT;
begin
  hr := CoInitializeEx(nil, COINIT_APARTMENTTHREADED);
  if hr  S_OK then
    Exception.Create('Error occurs: ' + IntToHex(hr, 2));
  try
    FMSConnection.Connect;
    FMSQuery.Execute;
  finally
    CoUninitialize;
  end;
end;

procedure TfmMain.Button1Click(Sender: TObject);
var
  i: integer;
begin
  for i := 0 to 99 do
    TMyThread.Create('insert into SimpleTable2(Name) values(''New Value'')');
end;

[Bill Gage]

>>If you create an application that uses multiple threads to perform
>>database operations using SDAC, you must create one additional >>connection for each thread.

I got that from the Threads example. I like the example in that it uses a copy of a datamodule for the thread. I do not need to use a single TMSConnection component for all threads. I like how the Threads example takes the datamodule TMSConnection and assigns the properties of another connection to it.

I also like the Threads example because it shows the use of critical sections.

So I guess what I am trying to say is that I would like to stick with that methodology if I can. I simply want to specify the SQL and the TMSConnection to assign to the components on the Data datamodule, but I want it to be thread safe. I do not want the a thread grabbing the datamodule with the wrong settings.

When is the appropriate time to identify which TMSConnection and SQL to use in the thread? Before calling RunThread by assigning them to global variables? Adding two parameters to the RunThread method and setting the connection and sql in that method? Or is there a way to pass additional information to the CreateThread method?

[Bill Gage]

What if I used the following. I set a global variable before I run the thread to pause executing additional threads until I have the sql and connection set.

procedure TdmMain.RunSQLMultithreaded(dbIndex: TDBIndex; sqltext: array of String);
var
i: Integer;
begin
for i := 0 to Length(sqltext) - 1 do
begin
while ThreadWait and (ThreadCount >= MAX_THREADS) do
Application.ProcessMessages;
ThreadWait := True;//<<<Set the wait
ThreadDB := DBConnection(dbIndex);//<<<Identify the connection to use
ThreadSQL := sqltext);//<<< Identify the sql to use
RunThread(Self, @TdmMain.Execute, @TdmMain.EndExecute);
end;
end;

Then in the thread execute I do the following...

Data := TdmThread.Create(nil);
try
try
with Data do
begin
MSConnection.Assign(ThreadDB);//<<< set my connection
MSConnection.LoginPrompt := False;
MSConnection.Connect;
MSSQL.SQL.Text := ThreadSQL;//<<< set my sql
ThreadWait := False;//<<< release the wait

// Run the SQL
MSSQL.Execute;
....

Will the above work?

[Bill Gage]

Well it didn't work, so I am back to the drawing board.

[Bill Gage]

For some reason in my app the EndExecute method for the thread never gets fired.

Does this have anything to do with the code being in a datamodule?

Bill

[Bill Gage]

Well I tried moving all the threading code from the datamodule to my main form and by gosh IT WORKES!

Don't know why or how, but I think it is working.

Bill