How to use OnProgress in TMSQuery

[brace]

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]

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]

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]

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.