Solved

Progress Bar While SQL Stored Procedure Executing

Posted on 2000-03-01
6
2,008 Views
Last Modified: 2010-04-04
I am using D5 and MSSQL7 and have some database update stored procedures which take a little while to execute.  To give the user a progress bar, I figure I could maintain a small table in the database which has a progress count (or percent).  To access this progress table, the application could perhaps use a timer to fire off a query to read progress information (to feed to the progress bar).  Is seems that I would have to establish a seperate Thread in which the query to read progress is executed.  Is this the correct approach?  Has anyone done this who could assist?
0
Comment
Question by:anthonyfaunt
[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
  • 3
  • 2
6 Comments
 
LVL 9

Expert Comment

by:ITugay
ID: 2576049
You may do it by timer or in thread (looping in Execute) but be carefull!

The TQuery may not work properly in your thread. I don't know why, but a lot of exceptions I get when trying to run TQuery in the different thread.

Now I do what I need by timer event.

But in both cases you will spend lot of system time to send and receive packets to/from server.

You trying to solve very difficult task. It look like impossible to do without creating serious network traffic. EventAlerter componet for MSSQL will be very usefull. May be somebody know about it?

Igor.
0
 

Author Comment

by:anthonyfaunt
ID: 2576187
Hi Igor,
Thanks for your comments.
When you use a timer event are you using it to periodically fire off a query while a stored procedure is running?
I did not think this could be done without doing it in a thread.
Tony
0
 
LVL 9

Expert Comment

by:ITugay
ID: 2576277
Hi Tony,

This code the same as in my unit.  Timer interval 1000 msec. Decreasing interval will freeze application. It work :).

const InTimer : boolean = false;

procedure TCheckForm.Timer1Timer(Sender: TObject);
begin
   if InTimer then exit;
   InTimer := true; // prevent recursivelly call
   try
     CheckQuery.Open;
     if CheckQuery.Fields[0].AsInteger = 1 then
     begin
       // code here
     end;
     CheckQuery.Close;
   finally
     InTimer := false;
   end;
end;


And I would like to ask you: How application will see changing of information in maintain table if you make changes under transaction?

Cheers,
Igor.


0
Industry Leaders: 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!

 

Author Comment

by:anthonyfaunt
ID: 2577752
Good morning Igor,

Thanks for your code.

In answer to your question:

Any database changes made by a stored procedure that are contained in a single transaction will not be visible to a query from the application until committed.

It is too risky not to use transaction processing and so there must be another way.

I am following up on your suggestion about the MSSQL EventAlerter.  MSSQL7 Books On Line has a lot of information under the heading of Events and I am checking this out.

Regards,
Tony

0
 
LVL 9

Expert Comment

by:ITugay
ID: 2579116
Dear Tony,

In the last question I hinted at impossibility to display the information by timer or in thread if you are under transaction. I'm glad that you agree with me. And now you on a right way;)

Best regards,
Igor.
0
 

Accepted Solution

by:
dhnkley earned 200 total points
ID: 2583692
Tony,
I have done some testing, and found the Following.
1. That if you fire off a stored procedure in the main Thread that the Application does not service any other thread.
2. Because of 1 a Timer does not work not even in it's on thread.
3. By Placing the Stored Procedure in its own thread, other threads can be serviced. Found Best Results when you set this thread to the lowest priority as the code does nothing once ExecProc command is started.
4. You could not use two stored procedures in seperate threads to query the database as they seem to be mutualy exclusive.

I got it to work by Using Two threads one for the Stored Procedure which does all the work. A Second Thread which uses a query to pole a database table to get the percent complete of the stored procedure. This second Thread I made Increase and decrease the polling so as to reduce network traffic to a minimum.

I Also found I had to use a Tsession with two database controls, and fire the storedprocedure and the query at different database sessions.

Below is the Code I used to create a Process Form:

I have not tested as Igor has Suggested if this works under a Transaction.
unit Progress;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  ComCtrls, ExtCtrls, StdCtrls, Db, DBTables;

Type
  TProgressThread = class(TThread)
  protected
    iProgress: Integer;
    iTime : Integer;
    bCompleted: Boolean;
    procedure UpdateProgress;
    Procedure Execute; Override;
  Public
    StartTime  : TDateTime;
    ProgressBar: TProgressBar;
    StoredProc : TStoredProc;
end;
Type
  TFireThread = class(TThread)
  protected
    Procedure Execute; Override;
  Public
    StoredProc : TStoredProc;
end;
type
  TfrmProgress = class(TForm)
    ProgressBar1: TProgressBar;
    Label1: TLabel;
    Label2: TLabel;
    spTest: TStoredProc;
    Label3: TLabel;
    Animate1: TAnimate;
    procedure FormShow(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frmProgress: TfrmProgress;
  MyThread : TProgressThread;
  MyFire : TFireThread;
  StartTime : TDateTime;
  Running : Boolean;

implementation

uses ThreadedModule, Main, DataModule;

{$R *.DFM}

procedure TfrmProgress.FormShow(Sender: TObject);
begin
 StartTime := Time;
 With Animate1 do
  begin
     Filename := ExtractFilePath(application.exename) + '\FileMove.avi';
     Active := true;
  end;

  Running := true;
  //Create Two threads
  MyThread := TProgressThread.Create(True);
  MyThread.ProgressBar := ProgressBar1;
  MyThread.ProgressBar.Position := 0;
  MyThread.FreeOnTerminate := true;
  MyThread.bCompleted := false;
  MyThread.StartTime := Time;
  MyThread.Priority := tpHighest;
  MyThread.iTime := 0;
  MyThread.resume;
  MyFire := TFireThread.Create(True);
  MyFire.FreeOnTerminate := true;
  MyFire.Priority := tpLowest;
  MyFire.StoredProc := spTest;
  MyFire.resume;

end;



procedure TProgressThread.UpdateProgress;
begin
  ProgressBar.Position := iProgress;
  frmProgress.Label2.Caption := Timetostr(StartTime-time);
  ProgressBar.Refresh;
  frmProgress.Label2.Refresh;
  frmMain.ShowStatus(2,TimetoStr(time));
  Application.ProcessMessages;
end;

Procedure TProgressThread.Execute;
var
  UserName     : String;
  ComputerName : string;
  n            : Integer;
  qryProgress  : TQuery;
  sSQL         : string;
Begin
  UserName := dmData.UserName;
  ComputerName := dmData.GetWComputerName;
  qryProgress := TQuery.create(frmProgress);
  qryProgress.DatabaseName := 'Progress';
  while Running do
  begin
    frmMain.ShowStatus(2,timetostr(time));
    Application.ProcessMessages;
    sSQL := 'Select * from Progress where [User] = ''' + Username  +'''';
    with qryProgress do
    begin
      Close;
      SessionName := 'Session1_1';
      DatabaseName := 'Progress';
      Sql.Clear;
      Sql.Add(sSQL);
      close;
      prepare;
      open;
      first;
      if iProgress = FieldByName('Percent').asinteger then
        iTime := iTime + 2000;
      if iProgress + 1 < FieldByName('Percent').asinteger then
        iTime := iTime - 2000;
      if iTime < 0 then
        iTime := 0;
      iProgress := FieldByName('Percent').asinteger;
      ProgressBar.Position := FieldByName('Percent').asinteger;
      frmProgress.Label2.Caption := FieldByName('Percent').asstring;
      frmProgress.Label3.Caption := formatdatetime('n:ss', StartTime - Time);
    end;
    for n := 1 to 3000 + iTime do
      application.ProcessMessages;
  end;
  QryProgress.free;
  frmProgress.close;
end;



Procedure TFireThread.Execute;
Begin
  application.ProcessMessages;
  frmMain.ShowStatus(1,'Fire Sp');
  with StoredProc do
  begin
    ParamByName('@User').asstring := dmData.UserName;
    ParamByName('@Workstation').asString := dmData.GetWComputerName;
    ExecProc;
  end;
  frmMain.ShowStatus(1,'Fire Sp Done');
  Running := false;
  free;
end;
end.



Stored Procedures:

CREATE PROCEDURE UpdatePercent
@Percent int,
@Completed bit,
@User char(25),
@WorkStation char(25)
AS
IF @Percent > 100
  SET @Percent = 100
IF @Percent < 0
 SET @Percent = 0
IF @Completed = 1
  SET @Percent = 0
IF (SELECT ISNULL(Count(User),0)
FROM Progress
WHERE [User] = @User
and WorkStation = @WorkStation) = 0
BEGIN
  INSERT INTO Progress
    ([Percent],
    Completed,
    [User],
    WorkStation)
  VALUES
   (@Percent,
    @Completed,
    @User,
    @Workstation)
END
ELSE
BEGIN
  UPDATE Progress
  SET [Percent]=@Percent,
   Completed=@Completed,
   [User]=@User,
   Workstation = @Workstation
END


CREATE PROCEDURE GetProgress
@User char(25),
@Workstation char(255),
@Percent int OUTPUT,
@Completed bit OUTPUT
 AS
SELECT @Percent=ISNULL([Percent],0), @Completed=ISNULL(Completed,0)
FROM Progress
WHERE [User]=@User and Workstation=@Workstation
IF @Percent is null
  SET @Percent = 0
IF @Completed is null
  SET @Completed = 0
Return @Percent

Regards
Darren.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Suggested Solutions

Title # Comments Views Activity
Delphi XE10 Round Image 2 227
Multiple image collision 13 108
How to load 2 images in same column in Delphi 2 81
update joined tables 2 74
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
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…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

738 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