?
Solved

AutoUpdate - ExecSql Freeze

Posted on 2012-09-04
5
Medium Priority
?
1,243 Views
Last Modified: 2012-09-05
Hello,

My application freeze at ExecSql function. Script have 17.000 lines.

First step:
  mmAtu.clear;
  for I := gRhParameOri.versbd+1 to gVersaoBDAtual do
  begin
    mmLer.Clear;
    xArq := gPathAtuVer+'ver'+inttostr(I)+'.txt';
    if FileExists(xArq) then
      mmLer.Lines.LoadFromFile(xArq);
    mmAtu.text := mmAtu.text + mmLer.text;
  end;

Open in new window


And after:

  dmParam.qyAtuVer.Close;
  dmParam.qyAtuVer.SQL.Clear;
  dmParam.qyAtuVer.Sql.Text := mmAtu.text;
  dmParam.qyAtuVer.ExecSQL;

Open in new window


I'll need separate in more files ? Is there any way to check the progress of execution ?

Thanks
0
Comment
Question by:powerlog
  • 3
5 Comments
 
LVL 19

Expert Comment

by:Thommy
ID: 38366744
Try using TSQLMonitor to monitor sql communication with your database server...
0
 
LVL 38

Accepted Solution

by:
Geert Gruwez earned 2000 total points
ID: 38366760
monitoring ?
some possibilities
1: divide your script into separate pieces and execute 1 piece at a time
  >> adding a marker to indicate the start of the next piece in the script helps
2: add update statements in your script to indicate the progress
  >> the update statements will have to be autonomous transactions
  >> execute the script inside a thread
  >> start a second thread to read the updated values and give feedback to the mainthread
  >> in the main thread display the feedback

is this difficult ? yes, threading is very difficult to get everything working seamlessly together

check here for the ultimate threading library:
http://otl.17slon.com/tutorials.htm

or read my articles on threading
here is one for timing a query inside a thread:
http://www.experts-exchange.com/Programming/Languages/Pascal/Delphi/A_10043-Timing-queries-with-threads.html
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 38366766
>>Script have 17.000 lines.

most database like sql server, oracle, interbase .... etc
have something like stored procedures

with such a long script, you'll need items contained in transactions to have consistent data
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 38366824
why are loading the query text twice ?
and why are u using a memo as in between >> this is slow !

here is your code, but optimized a little

var List: TStringList;

begin
  List := TStringList.Create;
  try
    dmParam.qyAtuVer.Close;
    dmParam.qyAtuVer.SQL.Clear;
    dmParam.qyAtuVer.SQL.BeginUpdate;
    try
      for I := gRhParameOri.versbd+1 to gVersaoBDAtual do
      begin
        xArq := gPathAtuVer+'ver'+inttostr(I)+'.txt';
        if FileExists(xArq) then
        begin
          List.LoadFromFile(xArq);
          dmParam.qyAtuVer.SQL.AddLines(List);
        end;
      end;
    finally
      dmParam.qyAtuVer.SQL.EndUpdate;
    end;
  finally
    List.Free;
  end;
  dmParam.qyAtuVer.ExecSQL;

Open in new window


lol >>> divide the script >>> why execute the separate pieces and indicate progress ?
you could even count the total steps with a procedure like filecount
and show the progress with showprogress

var List: TStringList;
  step, totalsteps: integer;
  error: string;
begin
  step := 1;
  dmParam.qyAtuVer.Close;
  try
    TotalSteps := FileCount(gPathAtuVer, 'ver*.txt');
    for I := gRhParameOri.versbd+1 to gVersaoBDAtual do
    begin
      xArq := gPathAtuVer+'ver'+inttostr(I)+'.txt';
      if FileExists(xArq) then
      try
        dmParam.qyAtuVer.SQL.LoadFromFile(xArq);
        dmParam.qyAtuVer.ExecSQL;
        Step := Step + 1;
        ShowProgress(Step, TotalSteps, '');
      except
        on E: exception do 
          Raise Exception.Create('Failed at step ' + IntToStr(Step) + #13#10 + 
            'Error message: ' + E.Message);
      end;
    end;
  except
    on E: Exception do 
    begin
      ShowProgress(Step, TotalSteps, E.Message);
      Raise;
    end;
  end;

Open in new window


ShowProgress would be something like

procedure TForm1.ShowProgress(Step, Total: integer; Msg: string);
begin
  // show the message in a label
  lblProgress.Caption := Msg;
  lblProgress.Update;
  // show the progress in a progressbar
  // pgbProgress.Min := 0;
  // pgbProgress.Max := 100;
  pgbProgress.Position := Round(Step/Total*100);
  pgbProgress.Update;
end;

filecount is a count using the TSearchRec:
(somewhat stolen from systan in this thread:
http://www.experts-exchange.com/Programming/Languages/Pascal/Delphi/Q_27513571.html)

function FileCount(const AFolder, AFilter: string): integer;
var
  vFindHandle: THandle;
  vFilter    : String;
  vFindData  : WIN32_FIND_DATA;
  AList: TStringList;
begin
  Result := 0;
  AList := TStringlist.Create;
  try
    AList.Clear;
    vFilter := AFolder + '\' + AFilter;
    vFindHandle := FindFirstFile(PChar(vFilter), vFindData);
    if vFindHandle = INVALID_HANDLE_VALUE then
      Exit;

    repeat
      AList.Add(ChangeFileExt(vFindData.cFileName, ''));
    until not FindNextFile(vFindHandle, vFindData);

    Windows.FindClose(vFindHandle);
    if AList.count > 0 then
      Result := AList.Count;
  finally
    AList.Free;
  end;
end;
0
 
LVL 28

Expert Comment

by:Sinisa Vuk
ID: 38366830
I would put prograss bar on form and use your first source lines...

...
  ProgressBar1.Max := gVersaoBDAtual;
  ProgressBar1.Min := gRhParameOri.versbd+1;
  ProgressBar1.Position := ProgressBar1.Min;
  dmParam.qyAtuVer.SQLConnection.StartTransaction(..);
  try
    for I := gRhParameOri.versbd+1 to gVersaoBDAtual do
    begin
      xArq := gPathAtuVer+'ver'+inttostr(I)+'.txt';
      if FileExists(xArq) then
      begin
        mmLer.Lines.LoadFromFile(xArq);
        dmParam.qyAtuVer.Sql.Text := mmLer.text;
        dmParam.qyAtuVer.ExecSQL;
      end;
      ProgressBar1.StepIt;
    end;
    dmParam.qyAtuVer.SQLConnection.Commit(...);
  except
    dmParam.qyAtuVer.SQLConnection.Rollback(...);
  end;
...

Open in new window

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

809 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