Solved

Progress Bar While SQL Stored Procedure Executing

Posted on 2000-03-01
6
2,039 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
Technology Partners: 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

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…
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…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Suggested Courses
Course of the Month10 days, 8 hours left to enroll

628 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