Page 1 of 1
How to use OnProgress in TMSQuery
Posted: Mon 17 Jun 2013 07:14
by 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:
and as it executes I want to update a TProgresssBar to show progress to the user.
COuld you help me?
Thanks.
Re: How to use OnProgress in TMSQuery
Posted: Tue 18 Jun 2013 15:10
by 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.
Re: How to use OnProgress in TMSQuery
Posted: Wed 19 Jun 2013 07:29
by 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.
Re: How to use OnProgress in TMSQuery
Posted: Wed 19 Jun 2013 07:57
by 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.