ProgressBar + Query

I have big query (20s to open) and i want to run progresbar while opening query?

ADOQuery5.SQL.Clear;
ADOQuery5.SQL.Add('select krupje, sum(skirtumas), sum(viso) from (select krupje, skirtumas, viso from bchipsai where krupje is not null and skirtumas is not null and laikas > trunc(sysdate)+8/24 union all');
ADOQuery5.SQL.Add('select inspektorius, skirtumas, viso from bchipsai where inspektorius is not null and skirtumas is not null and laikas > trunc(sysdate)+8/24 union all');
ADOQuery5.SQL.Add('select chiperis, skirtumas, viso from bchipsai where chiperis is not null and skirtumas is not null and laikas > trunc(sysdate)+8/24) group by krupje');
ADOQuery5.Open;

How to do it?
selasAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
pcsentinelConnect With a Mentor Commented:
The qkey to this is to run the query asynchrously, however the problem you have is the range of the progressbar, i.e. how long will the query take. You will need to make an approximation of this.

I have included a  the code for a small program below.

*****************UNIT CODE******************
unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Db, ADODB, Grids, DBGrids, StdCtrls;

type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    DBGrid1: TDBGrid;
    DataSource1: TDataSource;
    Button1: TButton;
    Label1: TLabel;
    Button2: TButton;
    ADOCommand1: TADOCommand;
    ADODataSet1: TADODataSet;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
  private
    fCancel: boolean;
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}

procedure TForm1.Button1Click(Sender: TObject);
begin
      ADOCommand1.CommandText:='select krupje, sum(skirtumas), sum(viso) from (select krupje, skirtumas, viso from bchipsai where krupje is not null and skirtumas is not null and laikas > trunc(sysdate)+8/24 union all'+
            'select inspektorius, skirtumas, viso from bchipsai where inspektorius is not null and skirtumas is not null and laikas > trunc(sysdate)+8/24 union all'+
            'select chiperis, skirtumas, viso from bchipsai where chiperis is not null and skirtumas is not null and laikas > trunc(sysdate)+8/24) group by krupje';
  fCancel:=false;
  ADODataset1.Recordset:=adocommand1.Execute;
  while stExecuting in adocommand1.States do
  begin
        label1.caption:=IntToStr(StrToInt(label1.caption)+1);
    label1.Repaint;
    Application.ProcessMessages;
  end;
  label1.caption:='0';
  if not fCancel then
        Datasource1.DataSet:=ADODataset1;
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
      fCancel:=true;
      adocommand1.cancel;
end;

end.

***********************FORM CODE*********************
object Form1: TForm1
  Left = 158
  Top = 207
  Width = 696
  Height = 480
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object Label1: TLabel
    Left = 40
    Top = 152
    Width = 6
    Height = 13
    Caption = '0'
  end
  object DBGrid1: TDBGrid
    Left = 324
    Top = 32
    Width = 337
    Height = 373
    DataSource = DataSource1
    TabOrder = 0
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'MS Sans Serif'
    TitleFont.Style = []
  end
  object Button1: TButton
    Left = 124
    Top = 88
    Width = 75
    Height = 25
    Caption = 'Run'
    TabOrder = 1
    OnClick = Button1Click
  end
  object Button2: TButton
    Left = 124
    Top = 116
    Width = 75
    Height = 25
    Caption = 'Cancel'
    TabOrder = 2
    OnClick = Button2Click
  end
  object ADOConnection1: TADOConnection
    Connected = True
    ConnectionString =
      'Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initi' +
      'al Catalog=paultemp;Data Source=CAT;Use Procedure for Prepare=1;' +
      'Auto Translate=True;Packet Size=4096;Workstation ID=PAULSPC;Use ' +
      'Encryption for Data=False;Tag with column collation when possibl' +
      'e=False'
    LoginPrompt = False
    Provider = 'SQLOLEDB.1'
    Left = 20
    Top = 20
  end
  object DataSource1: TDataSource
    Left = 124
    Top = 20
  end
  object ADOCommand1: TADOCommand
    CommandText = 'WAITFOR DELAY '#39'00:00:05'#39' '
    Connection = ADOConnection1
    ExecuteOptions = [eoAsyncExecute, eoAsyncFetch, eoAsyncFetchNonBlocking]
    Parameters = <>
    Left = 56
    Top = 20
  end
  object ADODataSet1: TADODataSet
    Connection = ADOConnection1
    ExecuteOptions = [eoAsyncExecute, eoAsyncFetchNonBlocking]
    Parameters = <>
    Left = 92
    Top = 20
  end
end

*********************************************************

this doesn't use a progressbar but does update a label.

the key to using a progress bar is to first run a quick query that will return the total number of records you need to process. Set your progress bars max value to this and then process the main query, just replace the
    label1.caption:=IntToStr(StrToInt(label1.caption)+1);
    label1.Repaint;

with progressbar.step


hope this helps


regards
0
 
KristaoConnect With a Mentor Commented:
hi

Nice question =) i had this problem too but i was to lazy to create some peace of code in my app so i didn't do it. But this is nice way to test my skills now =).

So we have query wich opens in 20 sec nice query my query opens in 3 min =) of corese that select is on 14 A4 pages =). I love big querys.

So we can create solve this kind of problem with threads, becouse app runs on onley one thread - mainthread.

Job:

1)Create 2 Threads(proceses)
2)1 Thread Opens Query
3)2 Thread Moves Statusbar

Ok with status bar we will have problem. We don't realy know how much time will take the query ok we know it could be 20 sec. Lets make statusbar MAX value to 20 and statusbar step 1. So now lets do some brain trick if the job takes more than 20 sec we will just increase MAX value by 1 if less we wil fill the progressbar to MAX.

Button on Form will run 1 and 2 thread. 1 thread will stop progress thread when query is open, on stop progress bar wil fill up to MAX.

This is the way i would do it. becouse you can't do this in MainThread, MainThread is responsible for redrawing form and ect. things. I hope this will help you, it worked for me the code my neded to change. I didn't free the creaded class so there is memory leaks =).

//--------------------------
unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, DB, ADODB, ComCtrls, Unit2, Unit3;

type
  TForm1 = class(TForm)
    ProgressBar1: TProgressBar;
    ADOQuery1: TADOQuery;
    Button1: TButton;
    ADOConnection1: TADOConnection;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    Status: TStatus;
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
var
  k: TAdoOpen;
begin
  k := TAdoOpen.Create(true);
  k.Resume;
  Status := TStatus.Create;
  Status.Resume;
end;

end.

//--------------------------

unit Unit2;

interface

uses
  Classes, SyncObjs, SysUtils;

type
  TStatus = class(TThread)
  private
    { Private declarations }
  protected
    sStop: boolean;
    cStop: TCriticalSection;
    procedure Execute; override;
    procedure ProgressIT;
    procedure Full;
    function st: boolean;
  public
    constructor Create;
    procedure Stop;
  end;

implementation
uses unit1;

constructor TStatus.Create;
begin
  sStop := false;
  cStop := TCriticalSection.Create;
  inherited Create(true);
end;

procedure TStatus.Execute;
begin
  while st = false do
  begin
    Synchronize(ProgressIT);
    sleep(1000); //sleep 1 sec
  end;
  Synchronize(Full); //fill progressbar fuly
end;

procedure TStatus.Full;
begin
  Form1.ProgressBar1.Position := Form1.ProgressBar1.Max;
end;

procedure TStatus.ProgressIT;
begin
  if Form1.ProgressBar1.Max - Form1.ProgressBar1.Position = 5 then //litle brain trick
  begin
    Form1.ProgressBar1.Max := Form1.ProgressBar1.Max + 1;
  end;
  Form1.ProgressBar1.StepIt;
end;

procedure TStatus.Stop;
begin
  cStop.Acquire;
  try
    sStop := true;
  finally
    cStop.Release;
  end;
end;

function TStatus.st: boolean;
begin
  cStop.Acquire;
  try
    Result := sStop;
  finally
    cStop.Release;
  end;
end;

end.


//--------------------------

unit Unit3;

interface

uses
  Classes;

type
  TAdoOpen = class(TThread)
  private
    { Private declarations }
  protected
    procedure Execute; override;
  end;

implementation
uses unit1;


procedure TAdoOpen.Execute;
begin
  Form1.ADOQuery1.Open;
  Form1.Status.Stop;
end;

end.
0
All Courses

From novice to tech pro — start learning today.