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
Solved

Ado vs MSSQL 7/2k and backup/restore (500+)

Posted on 2004-04-21
10
829 Views
Last Modified: 2012-05-04
Hi out there.

Im trying to do a application witch does a backup/restore procedure. The app is multithreaded and when I restore and take a backup by ADO I would like to have a Progressbar in a Window. If i try to send the command to MS SQL by adoquery it responds that there is no answer at the adocommand. and that i should use a sqlexec command instead. anyway if i use the queryanalyzer and run the restore/backup command i get a respond telling the progress 10% complete, 20% complete osv. and I want to have this information connected to the previus mantioned progress bad. plz help me.

and excute me for my bad english.

Best regards

JGB
0
Comment
Question by:jontebgood
  • 5
  • 3
  • 2
10 Comments
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 10879154
0
 

Author Comment

by:jontebgood
ID: 10885724
Hi again,
U missunderstood me, I know HOW TO do the backup up and so on. I want to catch the answer from the sql server WHEN i have sended the ado command and the job has started. if u do it with a asual ado.sql - open I get the answer that the query doesnt has any answer. if i do it with a ado.sqlexec I dont get any progress answers BUT the backup/restore starts and complete. I hope U understands me bette now.

BR
JBG
0
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 10885914

   Yes, it is clear now.

   You can execute the ExecSQL in Thread. set the FreeOnTerminate of the thread to true. In OnTerminate event of the thread send some user message to you form where you can handle it.

   For example :

const
  WM_OPERATION_DONE = WM_USER + 1;

  TMSBackupThread = class(TThread)
  private
    SQLStatement : String;
    ParentHandle  : THandle;
    procedure ExecuteQueryOnDB (.............)
    procedure Execute;
  public
    constructor Create(OwnerFormHandle : THandle; SQLText : String; CreateSuspended : boolean = True);
  end;

  in Create you send Form1.Handle, the SQL statement. In your execute method you can take care about executing the backup / restore.

  procedure TMSBackupThread.Execute;
  begin
    // ...
    // YOUR CODE HERE
    // ...
    SendMessage(ParentHandle, WM_OPERATION_DONE, 0, 0);
  end;

  {----------------------------------------------------------------------------------------------------------------------------}

  in your form you should handle the message sent by thread :

  TForm1 = class(TForm)
  private
    { Private declarations }
    procedure OnThreadFinished (var Msg : TMessage); message WM_OPERATION_DONE;
  public
    { Public declarations }
  end;



0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 

Author Comment

by:jontebgood
ID: 10886097
im sorry.... i have tgo refrease me becuz u dont understand me..... i will be back soon...
0
 
LVL 10

Expert Comment

by:Jacco
ID: 10890510
Hi there,

The progress is not returned as a dataset but as errormessages (with native error set to zero). The messages are issued by "print" statements. This is what OnLine books says:

"The PRINT statement takes either one character or a Unicode string expression as a parameter. It returns the string as a message to the application. The message is returned as an informational error in ADO, OLE DB, and ODBC applications. SQLSTATE is set to 01000, the native error is set to 0, and the error message string is set to the character string specified in the PRINT statement. The string is returned to the message handler call-back function in DB-Library applications."

On the ADOConnection there is an event OnInfoMessage in which the "error" is passed.

procedure TForm1.ADOConnection1InfoMessage(Connection: TADOConnection; const Error: Error; var EventStatus: TEventStatus);
begin
  Memo1.Lines.Add(Error.Description);
end;

I tested this with my version of ADODb but the event is not called... bad luck...

I will try to find if it is possible to hook the event up.

Reagrds Jacco
0
 
LVL 10

Expert Comment

by:Jacco
ID: 10890948
If you use a TADOCommand object it will give 1 message (but still not all and only when the command is finished...)
0
 
LVL 10

Expert Comment

by:Jacco
ID: 10891732
Got it :)

Use a TADOQuery set.

Set CursorLocation of both the TADOConnection and TADOQuery to clUseServer.

The SQL in the TADOQuery should look like this: "backup database MYDB to MYBACKUP with STATS = 1" (STATS = 1 makes sure the progress is fed back)

Now normally you would call ExecSQL but then no InfoMessages are returned. So use Open in stead then InfoMessage will be returned. (Although not all messages when the backup is very fast).

This will end in an exception which you can ignore. (By the way is you set EventStatus to esCancel then the operation is aborted!!)

Here is the dfm and pas file I used to test:

<<START OF DFM>>
object Form1: TForm1
  Left = 192
  Top = 194
  Width = 870
  Height = 640
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object Button1: TButton
    Left = 88
    Top = 144
    Width = 75
    Height = 25
    Caption = 'Button1'
    TabOrder = 0
    OnClick = Button1Click
  end
  object Memo1: TMemo
    Left = 232
    Top = 24
    Width = 425
    Height = 281
    Lines.Strings = (
      'Memo1')
    TabOrder = 1
  end
  object ADOConnection1: TADOConnection
    Connected = True
    ConnectionString =
      'Provider=SQLOLEDB.1;Password=password;Persist Security Info=True' +
      ';User ID=username;Initial Catalog=TestCentral;Data Source=dsbrnd1.dsbg' +
      'roep.dsb'
    CursorLocation = clUseServer
    LoginPrompt = False
    Provider = 'SQLOLEDB.1'
    OnInfoMessage = ADOConnection1InfoMessage
    Left = 80
    Top = 56
  end
  object ADOQuery1: TADOQuery
    Connection = ADOConnection1
    CursorLocation = clUseServer
    Parameters = <>
    SQL.Strings = (
      'backup database OLAP_Dimensions to TeamGeest_Bck with STATS = 1'
      '')
    Left = 168
    Top = 56
  end
end
<<END OF DFM>>

<<START OF PAS>>
unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, DB, ADODB, Grids, DBGrids;

type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    Button1: TButton;
    Memo1: TMemo;
    ADOQuery1: TADOQuery;
    procedure ADOConnection1InfoMessage(Connection: TADOConnection;
      const Error: Error; var EventStatus: TEventStatus);
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.ADOConnection1InfoMessage(Connection: TADOConnection; const Error: Error; var EventStatus: TEventStatus);
begin
  Memo1.Lines.Add(Error.Description);
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  ADOQuery1.Open;
end;

end.
<<END OF PAS>>
0
 

Author Comment

by:jontebgood
ID: 10899801
havent test yet but it seem to be right solution Thanks dude!!!

BR

JBG
0
 
LVL 10

Accepted Solution

by:
Jacco earned 500 total points
ID: 10900284
Hope it works for you :)

FYI I found information on the bug here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;294178

Regards Jacco
0
 
LVL 10

Expert Comment

by:Jacco
ID: 10913584
Thanks man :)
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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

Suggested Solutions

Title # Comments Views Activity
Delphi XE10 Round Image 2 154
select query - oracle 16 100
Wincontrol not (correctly) drawn 15 40
How to Get Images From Server to Client using App Tethering 1 25
The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

789 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