saving tcp data into a table database

Question

I am using a VCL TCPServer components which fires events everytime a data is received on a tcp port. Within the event data is available into text parameter of the procedure. Because I want to save this data into mysql database I am wondering which is the best approach to this problem. Directly use an INSERT SQL command within the procedure for every data received or store the data in a memory (i.e. TStrings) and then calling a function every X (using Timer) minutes excute the INSERT command?

Thanks

Answer 1

Well,

You should not save your data inside the event, because you are breaking the component execution flow when you do that. Instead:

1 - You can buffer all data you want to save, and from time to time you save them (not good, you can lost a lot of data if something happens with your app).

2 - Inside the event, create a thread that will receive the data and save them to the database. This way you are not breaking the event flow execution and you will have all data saved on the fly. Also you will be using one more core of your CPU if you do so. That is what i would do.

Code for solution 2 (untested):

TOnError = procedure(aError : string) of object;

TSaveDataThread = class(TThread)
private
  oError : string;
  oOnError : TOnError;
  oDataToSave : string;
  procedure SaveDataToDataBase;
  procedure CallOnError;
protected
  procedure Execute; override;
  constructor Create(aDataToSave : string); reintroduce;
public
  property OnError : TOnError read oOnError write oOnError;
end;

procedure TSaveDataThread.CallOnError;
begin
  oOnError(oError);
end;

constructor TSaveDataThread.Create(aDataToSave: string);
begin
  inherited Create(True);
  FreeOnTerminate := True;
  oDataToSave := aDataToSave;
end;

procedure TSaveDataThread.Execute;
begin
  inherited;
  Self.SaveDataToDataBase;
end;

procedure TSaveDataThread.SaveDataToDataBase;
begin
  //put here your code to save aDataToSave to the database
  //set error on oError
  oError := 'error';
  Synchronize(CallOnError);
end;

procedure TForm5.OnError(aError: string);
begin
  ShowMessage(aError);
end;

procedure TForm5.Button1Click(Sender: TObject);
var
  vSaveDataThread : TSaveDataThread;
  vDataToSave : string;
begin
  //change this method fot the event you want to use
  vDataToSave := 'this is the data that will be saved to the database';
  vSaveDataThread := TSaveDataThread.Create(vDataToSave);
  vSaveDataThread.OnError := Self.OnError;
  vSaveDataThread.Start;
end;

Check this out if you dont know threads: Multithreading - The Delphi Way