Concurent insert into database

Hello experts!

My application reads data sent through the Internet.

I use IdTCPServer to read the data.

Sometimes it is lots of data and easy approach like:

onExecute:
 with DataModule2.ZQuery1 do
     begin
       Close;
       Params[0].AsInteger := 1;
       Params[1].AsDateTime := Today;
       Params[2].AsString := lineOfData;
       ExecSQL;
       ApplyUpdates;

     end;

works fine but is not stable, after some time it crashes.

What approach would be appropriate here?

Thank you

panJames
panJamesAsked:
Who is Participating?
 
Sinisa VukConnect With a Mentor Commented:
Seem that you have few concurrent request for write to db with globally defined query.

you can two ways:
- create query component each time data comes or

- useing critical sections ... so define global variable like :

var
  LockDB: TRtlCriticalSection;

Open in new window


... on botton of unit before 'end.' add....

...
initialization
  InitializeCriticalSection(LockDB);

finalization
  DeleteCriticalSection(LockDB);

Open in new window


... and on Execute :

...
  EnterCriticalSection(LockDB);
  try
    with DataModule2.ZQuery1 do
     begin
       Close;
       Params[0].AsInteger := 1;
       Params[1].AsDateTime := Today;
       Params[2].AsString := lineOfData;
       ExecSQL;
     end;
  finally
    LeaveCriticalSection(LockDB);
  end;

...

Open in new window

0
 
Geert GOracle dbaCommented:
the approach depends on a some considerations
> what type of database ? oracle, mssql, interbase ?

you are adding 1 line at a time or is this 1 line the whole text file ?
what is this lineofdata ?

for your delphi approach there are several considerations
are you catching all the errors ? and handling them appropriately ?
what do you do when the database is down ? eg for maintenance
what happens if you lose connection ?

for the database > is there space enough, are you archiving/deleting old data ?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.