• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 855
  • Last Modified:

Paradox, TQuery, ExecSQL I lost all data on system crash. Help !

Hi, I am using only TQuery to insert a data. Here's the sample how i use it.

procedure AddRecord;
begin
  with TQuery.Create(nil) do
  try
    DatabaseName := 'MASTER';
    SQL.Add('insert into maindbex (Name, Username, Password)');
    SQL.Add('VALUES (:Name, :Username, :Password)');
    ParamByName('Name').AsString := 'Cenk';
    ParamByName('Username').AsString := 'MyUser';
    ParamByName('Password').AsString := 'MyPAssword';
    ExecSQL;
  finally
    Free;
  end;
end;

this code runs without any problems. If i close program and shutdown pc and reopen it, everything i added into database is there. But, if electric cuts or system crashes then everything i added is gone. If i closed program before system crash then no problem but if if it happens while my program runing then all data added by this procedure goes... I couldn't find a way to fix this. I don't want to use TUpdateObject. Can someone help me?

Regards,
0
Dumani
Asked:
Dumani
  • 7
  • 6
  • 6
  • +4
1 Solution
 
MotazCommented:
Just add FlushBuffers.
Because BDE is using caching techniques to store saved data in memory to speed up saving and retreival operation:

SQL.Add('VALUES (:Name, :Username, :Password)');
   ParamByName('Name').AsString := 'Cenk';
   ParamByName('Username').AsString := 'MyUser';
   ParamByName('Password').AsString := 'MyPAssword';
   ExecSQL;
   FlushBuffers;
 finally
   Free;
 end;
end;

0
 
DumaniAuthor Commented:
Did you try this? Because you can not use FlushBuffers to closed dataset. So I am using TQuery to ExecSQL... I only use SQL and Execute it..
0
 
MotazCommented:
FlushBuffers forces Borland Database Engine (BDE) to write cached-saved data into the harddisk, so that any unexpected termination of your program will not result on losing cached data.

Motaz
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MotazCommented:
I forget, I use it with Tables.
what kind of databases r u using? Paradox?
0
 
DumaniAuthor Commented:
I am using Paradox and TQuery... I don't use table and i can not use Tables after this time. Because i finished my all code.
0
 
MotazCommented:
Try this solution:

add BDE to uses clause:

add this code:

  Check(dbiSaveChanges(DataSet.Handle)

0
 
DumaniAuthor Commented:
As I said, I am using TQuery... I tried that with query before. It didn't work..

dbiSaveChanges(myquery.Handle);

if you know a good way to use this, then i can share
0
 
kretzschmarCommented:
flushbuffers is also supportet by tquery,
and should do it, if the os didn't cash
writeoperations to disk
0
 
kretzschmarCommented:
typo ...
cash->cache
0
 
MotazCommented:
I think bdiSaveChanges is also working with TTAble, so that you can change this function to use Table instead, the remaining code and using of Queries can be the same:

procedure AddRecord;
begin
 with TTable.Create(nil) do
 try
   DatabaseName := 'MASTER';
   TableName:= 'maindbex';
   Append;
   FieldByName('Name').AsString:= 'Cenk';
   FieldByName('Username').AsString := 'MyUser';
   FieldByName('Password').AsString := 'MyPAssword';
   Post;
   FlushBuffers;
 finally
   Free;
 end;
end;

May be you only need to change this function
0
 
oraelbisCommented:
Try transaction:

with TQuery.Create(nil) do
 try
   DatabaseName := 'MASTER';
   SQL.Add('insert into maindbex (Name, Username, Password)');
   SQL.Add('VALUES (:Name, :Username, :Password)');
   ParamByName('Name').AsString := 'Cenk';
   ParamByName('Username').AsString := 'MyUser';
   ParamByName('Password').AsString := 'MyPAssword';
   Database1.StartTransaction;
   try
      ExecSQL;
      Database1.Commit; {on success, commit the changes};
   except
      Database1.Rollback; {on failure, undo the changes};
   raise; {raise the exception to prevent a call to CommitUpdates!}
   end;
   CommitUpdates; {on success, clear the cache}
 finally
   Free;
 end;

0
 
MotazCommented:
Sorry, I forget to open and close the table:

procedure AddRecord;
begin
with TTable.Create(nil) do
try
  DatabaseName := 'MASTER';
  TableName:= 'maindbex';
  Open;
  Append;
  FieldByName('Name').AsString:= 'Cenk';
  FieldByName('Username').AsString := 'MyUser';
  FieldByName('Password').AsString := 'MyPAssword';
  Post;
  FlushBuffers;
  Close;
finally
  Free;
end;
end;

0
 
marcoszorrillaCommented:
You can use FlushBuffers with queries.

MyQuery.FlushBuffers;

This is the Borland information about it:

Posts all changes that have been written to the record buffer.

procedure FlushBuffers;

Description

Call FlushBuffers to cause the dataset to post all pending changes to the database, including any cached updates. Use FlushBuffers instead of CheckBrowseMode if it is important that cached record buffers are posted.

Best Regards.
Marcos.
0
 
CrazyOneCommented:
Yeah you can use FlushBuffers on a TQuery but the problem is the ExecSQL closes the dataset and FlushBuffers only works on an open dataset. Odd though I just tested and forced the app to crash and the data was in the table. In other words I didn't loose any of the data that app added to the table prior to the crash.


The Crazy One
0
 
CrazyOneCommented:
Dumani are you using CachedUpdates?
0
 
DumaniAuthor Commented:
Guys thanks for your helps but, I know how to use FlushBuffers and it doesn't work. It only works with UpdateObject and Motaz I am using TQuery and I will not use TTable.

  Oraelbis your idea is good but I don't use Database. I only use TQuery. What does my program exactly do or BDE does after i close my application? Because if i close the application and unplug electric cord (simulate electric cut) all data there but if i pull it before i close data lost...
0
 
kretzschmarCommented:
thats have nothig to do with the bde or your app,
the os caches writeoperations to disk temporary

meikl ;-)
0
 
DumaniAuthor Commented:
there's a solutions. Becuase when you use dbisavechanges() to a table then it writes all data to disk. And it's not OS thing. It is BDE that's why it's called ENGINE.

Thanks
0
 
kretzschmarCommented:
the bde just tells the os to save,
if the os does it, is out of control of the bde,

therefore be happy enough that you only lost the last
inserted records on a crash, the db-file can also be corrupted in this case

and this is not only for bde and paradox, also
sql-servers, if the hardware crashes, may have then a corrupted database

thats why it is recommended to do a daily save-copy

flushbuffers (which internal calls dbisavechanges)
is the only thing you can do

just my two cents about this theme,
because there is no garantee to hold the data
intact in any application, if the hardware or something else causes a crash

meikl ;-)
0
 
kretzschmarCommented:
btw. you have many open questions,
what about to close these threads
0
 
CrazyOneCommented:
>>>And it's not OS thing

Dumani I think it might be because I cut the power on my Win2000 machine and I didn't lose any of the newly inserted records. I also think it could have something to do with the Hard Drives "Write cache Enabled" and this is what MS has to say about that.

"Select to allow the drive to do write-back caching. This may improve write performance, but it will increase the chance of disk corruption in the event the system is not shut down properly."

Personally if you want to guarantee the data gets saved I think your going to need to change your approach to adding records and not use ExecSQL. Either use a TTable like in Motaz's example or stick with the TQuery but use

with TQuery do begin
     Open;
     Insert;
     AddRecord;
     Post;
     FlushBuffer;
     Close;
end;
0
 
kretzschmarCommented:
>And it's not OS thing

>"Select to allow the drive to do write-back caching. This
>may improve write performance, but it will
>increase the chance of disk corruption in the event the
>system is not shut down properly."

looks like a contradiction

;-)

0
 
CrazyOneCommented:
Well that's Microsoft for you. They themselves are a contradiction. hehehehe :>)

I suspect the problem has something to do in part with what version of Windows is being used and if the database is being networked as well. I did my forced crash test on Win2000 with database being local and I did not encounter the problem Dumani is having.
0
 
kretzschmarCommented:
>They themselves are a contradiction
hehe :-))

couldn't also reproduce the problem on my win2000-system,
seems to be a better os
0
 
DumaniAuthor Commented:
Guys i am not going to use Open; Insert; I am going to use ExecSql; I know how to force write to database. I am asking how to force bde write to harddisk when i ExecSql. )I know all other ways and i will not use non of them.
0
 
CrazyOneCommented:
Dumani we understand that. What you don't seem to see is that with ExecSql this may not be possible. So you are probably going to have to live with loosing the data or change your approach. The ExecSql should be writing directly to the disk but for some reason in your situation it isn't. BTW what version of the BDE are you using and what version of Windows? Also for testing purposes instead of using a BDE alias why not try in the DatabaseName pointing to the directory the table is in. Something else you could try is Application.ProcessMessages. I doubt if this will have any affect but what the heck.
0
 
pnh73Commented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Accept answer from CrazyOne

Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
Paul (pnh73)
EE Cleanup Volunteer
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 6
  • 6
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now