How to use OnProgress in TMSQuery

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
brace
Posts: 213
Joined: Wed 14 Feb 2007 08:26

How to use OnProgress in TMSQuery

Post by brace » Mon 17 Jun 2013 07:14

I use a TMSQuery to Execute a Restore Database SQL command.

This can take minutes in case of a very large database.
The prolbem is that my UI (VCL Thread) becomes non responsive during this restore db and moreover I cannot show any progress to the user.

I have asked around and I have been told to use OnProgress if available.

I realize that OnProgress is available on TVirtualTable but now on TMSQuery.

Do you have an example on how to use it?

What I would like to do is something like:

Code: Select all

MSQuery1.Execute;
and as it executes I want to update a TProgresssBar to show progress to the user.

COuld you help me?

Thanks.

AndreyZ

Re: How to use OnProgress in TMSQuery

Post by AndreyZ » Tue 18 Jun 2013 15:10

You can avoid hanging of your application by using threads. If you execute any statement in the main thread, the execution of your application is stopped until server responds about the end of execution. If you call the Execute method not in a main thread, your application will not wait for it, and therefore will not hang. In this case, you can check if query execution is in progress using the TMSQuery.Executing property (you should check it in a separate thread). There is no way to obtain the progress (in percents) of execution of a statement on the server.

brace
Posts: 213
Joined: Wed 14 Feb 2007 08:26

Re: How to use OnProgress in TMSQuery

Post by brace » Wed 19 Jun 2013 07:29

Thanks for the reply, anyway on Management studio i can see a Progress when doing restore database (it is a kind of green circular gauge that goes rom 0 to 100% in 10% increments). So i thought it could be nice.

You can consider it as a feature suggestion if possible. About the thread idea it is clear to me. Thanks.

AndreyZ

Re: How to use OnProgress in TMSQuery

Post by AndreyZ » Wed 19 Jun 2013 07:57

I have investigated this question more thoroughly. There is a way to obtain the progress of restoring of a database. For this, you should add the STATS monitoring option to the RESTORE statement (for more information, refer to http://msdn.microsoft.com/en-us/library/ms186858.aspx ). In this case, SQL Server generates the information messages that you can parse in the TMSConnection.OnInfoMessage event handler. Here is a code example:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
begin
  Memo1.Clear;
  MSQuery1.SQL.Clear;
  MSQuery1.SQL.Add('RESTORE DATABASE testdb');
  MSQuery1.SQL.Add('FROM DISK = ''C:\Backups\testdb.bak''');
  MSQuery1.SQL.Add('WITH MOVE ''testdb'' TO ''C:\Databases\testdb.mdf'',');
  MSQuery1.SQL.Add('MOVE ''testdb_log'' TO ''C:\Databases\testdb.ldf'',');
  MSQuery1.SQL.Add('STATS = 10');
  MSQuery1.Execute;
end;

procedure TForm1.MSConnection1InfoMessage(Sender: TObject; E: EMSError);
begin
  Memo1.Lines.Add(E.Message);
  Memo1.Refresh;
end;
Note that to run this code, you should add the OLEDBAccess unit to the USES clause of your unit.

Post Reply