Solved

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

Posted on 2002-06-07
28
799 Views
Last Modified: 2007-11-27
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
Comment
Question by:Dumani
  • 7
  • 6
  • 6
  • +4
28 Comments
 
LVL 7

Expert Comment

by:Motaz
ID: 7062412
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
 
LVL 1

Author Comment

by:Dumani
ID: 7062421
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
 
LVL 7

Expert Comment

by:Motaz
ID: 7062422
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
 
LVL 7

Expert Comment

by:Motaz
ID: 7062437
I forget, I use it with Tables.
what kind of databases r u using? Paradox?
0
 
LVL 1

Author Comment

by:Dumani
ID: 7062442
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
 
LVL 7

Expert Comment

by:Motaz
ID: 7062451
Try this solution:

add BDE to uses clause:

add this code:

  Check(dbiSaveChanges(DataSet.Handle)

0
 
LVL 1

Author Comment

by:Dumani
ID: 7062480
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7062485
flushbuffers is also supportet by tquery,
and should do it, if the os didn't cash
writeoperations to disk
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7062486
typo ...
cash->cache
0
 
LVL 7

Expert Comment

by:Motaz
ID: 7062487
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
 
LVL 3

Expert Comment

by:oraelbis
ID: 7062543
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
 
LVL 7

Expert Comment

by:Motaz
ID: 7062544
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
 
LVL 3

Expert Comment

by:marcoszorrilla
ID: 7063325
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 44

Accepted Solution

by:
CrazyOne earned 200 total points
ID: 7063835
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
 
LVL 44

Expert Comment

by:CrazyOne
ID: 7063843
Dumani are you using CachedUpdates?
0
 
LVL 1

Author Comment

by:Dumani
ID: 7064058
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7064067
thats have nothig to do with the bde or your app,
the os caches writeoperations to disk temporary

meikl ;-)
0
 
LVL 1

Author Comment

by:Dumani
ID: 7064142
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7064316
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7064327
btw. you have many open questions,
what about to close these threads
0
 
LVL 44

Expert Comment

by:CrazyOne
ID: 7064505
>>>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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7064510
>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
 
LVL 44

Expert Comment

by:CrazyOne
ID: 7064517
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7064536
>They themselves are a contradiction
hehe :-))

couldn't also reproduce the problem on my win2000-system,
seems to be a better os
0
 
LVL 1

Author Comment

by:Dumani
ID: 7064570
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
 
LVL 44

Expert Comment

by:CrazyOne
ID: 7064588
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
 
LVL 1

Expert Comment

by:pnh73
ID: 9010484
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now