[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2002-06-07
28
Medium Priority
?
849 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
LVL 44

Accepted Solution

by:
CrazyOne earned 800 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

656 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