Solved

Progress Bar While SQL Stored Procedure Executing

Posted on 2000-03-01
6
1,904 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
  • 3
  • 2
6 Comments
 
LVL 9

Expert Comment

by:ITugay
Comment Utility
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
Comment Utility
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
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:anthonyfaunt
Comment Utility
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
Comment Utility
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
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now