Solved

Check component adjustment TADO*

Posted on 2011-02-22
64
1,061 Views
Last Modified: 2013-11-23
Hi,

I use a MS SQL Server 2008 database.
The following code for some adjusted TADO-components should check and handle connectionerrors (whenever connection is lost).

In the OnConnectionError-event it needs to try and re-establish the connection to the server.
If this fails the application is closed, else it can continue.

Current problems with component are:
1. Property "RowsAffected" of TADOQueryExt is not accessable in Object Inspector
2. If a new TADOQueryExt is placed on a form, property CheckConnection is always False in stead of default True.

My questions:
1. Are the adjustments to the components correct? How to correct this?
2. How to re-connect to the database from within procedure "HandleConnectionError"?
3. How to access "HandleConnectionError" if TADOQueryExt are placed on other forms then Datamodule?

ad. 3: Maybe something like:
DM.HandleConnectionError((Sender AS TADOQueryExt), (Sender AS TADOQueryExt).Connection, (Sender AS TADOQueryExt).???);

procedure TDM.HandleConnectionError(Sender: TObject;
  AConnection: TADOConnection; const AErrorMessage: string;
  var AAction: TErrorAction);
begin
  // Try to reconnect

  ... CODE for reconnecting

  If succesfulreconnection then
  begin
    AAction := eaIgnore;
    Exit;
  end;

  // Show message when connection can't be re-established
  ShowMessage('Could not re-establish connection to SQL server, Application will close');
  // Stop executing the initial command
  AAction := eaAbort;
  // Close application
  PostMessage(Application.MainForm.Handle, WM_CLOSE, 0, 0);
end;


unit BPADOExt;

interface

uses
  SysUtils, Classes, DB, ADODB, Variants;

type
  TErrorAction = (eaIgnore, eaAbort, eaRaiseException);

  TOnConnectionError = procedure(Sender: TObject; AConnection: TADOConnection;
    const AErrorMessage: string; var AAction: TErrorAction) of Object;

  TADODataSetExt = class(TADODataSet)
  private
    FCheckConnection: Boolean;
    FOnConnectionError: TOnConnectionError;
  protected
    procedure SetActive(Value: Boolean); override;
  published
    property CheckConnection: Boolean read FCheckConnection write FCheckConnection default True;
    property OnConnectionError: TOnConnectionError read FOnConnectionError write FOnConnectionError;
  end;

  TADOTableExt = class(TADOTable)
  private
    FCheckConnection: Boolean;
    FOnConnectionError: TOnConnectionError;
  protected
    procedure SetActive(Value: Boolean); override;
  published
    property CheckConnection: Boolean read FCheckConnection write FCheckConnection default True;
    property OnConnectionError: TOnConnectionError read FOnConnectionError write FOnConnectionError;
  end;

  TADOQueryExt = class(TADOQuery)
  private
    FCheckConnection: Boolean;
    FLocalRowsAffected: Integer;
    FOnConnectionError: TOnConnectionError;
  protected
    procedure SetActive(Value: Boolean); override;
  public
    function ExecSQL: Integer;
  published
    property CheckConnection: Boolean read FCheckConnection write FCheckConnection default True;
    property OnConnectionError: TOnConnectionError read FOnConnectionError write FOnConnectionError;
    property RowsAffected: Integer read FLocalRowsAffected;
  end;

  TADOStoredProcExt = class(TCustomADODataSet)
  private
    FCheckConnection: Boolean;
    FOnConnectionError: TOnConnectionError;
  protected
    procedure SetActive(Value: Boolean); override;
  public
    procedure ExecProc;
  published
    property CheckConnection: Boolean read FCheckConnection write FCheckConnection default True;
    property OnConnectionError: TOnConnectionError read FOnConnectionError write FOnConnectionError;
  end;

  procedure Register;

implementation

{ TADOTableExt }
procedure TADOTableExt.SetActive(Value: Boolean);
var OpenErr: TErrorAction;
begin
  if not (csReading in ComponentState) then
    if Value and FCheckConnection and (not Connection.Connected) then
    begin
      try
        Connection.Connected := True;
      except
        on E: Exception do
        begin
          if Assigned(FOnConnectionError) then
          begin
            FOnConnectionError(Self, Connection, E.Message, OpenErr);
            case OpenErr of
              eaIgnore: ;
              eaAbort:
                Exit;
              eaRaiseException:
                raise;
            end;
          end;
        end;
      end;
    end;

  inherited SetActive(Value);
end;

{ TADOQueryExt }
function TADOQueryExt.ExecSQL: Integer;
var I: Integer;
    MasterFieldList: string;
begin
  if (Parameters.Count > 0) and Assigned(MasterDataLink.DataSource) and
    Assigned(MasterDataLink.DataSource.DataSet) then
  begin
    for I := 0 to Parameters.Count - 1 do
      if (Parameters[I].Direction in [pdInput, pdInputOutput]) and
        (MasterDataLink.DataSource.DataSet.FindField(Parameters[I].Name) <> nil) then
        MasterFieldList := MasterFieldList + Parameters[I].Name + ';';
    MasterFields := Copy(MasterFieldList, 1, Length(MasterFieldList)-1);
    SetParamsFromCursor;
  end;
  Command.Execute(FLocalRowsAffected, EmptyParam);
  Result := FLocalRowsAffected;
end;

procedure TADOQueryExt.SetActive(Value: Boolean);
var OpenErr: TErrorAction;
begin
  if not (csReading in ComponentState) then
    if Value and FCheckConnection and (not Connection.Connected) then
    begin
      try
        Connection.Connected := True;
      except
        on E: Exception do
        begin
          if Assigned(FOnConnectionError) then
          begin
            FOnConnectionError(Self, Connection, E.Message, OpenErr);
            case OpenErr of
              eaIgnore: ;
              eaAbort:
                Exit;
              eaRaiseException:
                raise;
            end;
          end;
        end;
      end;
    end;
  inherited SetActive(Value);
end;

{ TADODataSetExt }
procedure TADODataSetExt.SetActive(Value: Boolean);
var OpenErr: TErrorAction;
begin
  if not (csReading in ComponentState) then
    if Value and FCheckConnection and (not Connection.Connected) then
    begin
      try
        Connection.Connected := True;
      except
        on E: Exception do
        begin
          if Assigned(FOnConnectionError) then
          begin
            FOnConnectionError(Self, Connection, E.Message, OpenErr);
            case OpenErr of
              eaIgnore: ;
              eaAbort:
                Exit;
              eaRaiseException:
                raise;
            end;
          end;
        end;
      end;
    end;
  inherited SetActive(Value);
end;

{ TADOStoredProcExt }
procedure TADOStoredProcExt.ExecProc;
var I: Integer;
    MasterFieldList: string;
begin
  if (Parameters.Count > 0) and Assigned(MasterDataLink.DataSource) and
    Assigned(MasterDataLink.DataSource.DataSet) then
  begin
    for I := 0 to Parameters.Count - 1 do
      if (Parameters[I].Direction in [pdInput, pdInputOutput]) and
        (MasterDataLink.DataSource.DataSet.FindField(Parameters[I].Name) <> nil) then
        MasterFieldList := MasterFieldList + Parameters[I].Name + ';';
    MasterFields := Copy(MasterFieldList, 1, Length(MasterFieldList)-1);
    SetParamsFromCursor;
  end;
  Command.Execute;
end;

procedure TADOStoredProcExt.SetActive(Value: Boolean);
var OpenErr: TErrorAction;
begin
  if not (csReading in ComponentState) then
    if Value and FCheckConnection and (not Connection.Connected) then
    begin
      try
        Connection.Connected := True;
      except
        on E: Exception do
        begin
          if Assigned(FOnConnectionError) then
          begin
            FOnConnectionError(Self, Connection, E.Message, OpenErr);
            case OpenErr of
              eaIgnore: ;
              eaAbort:
                Exit;
              eaRaiseException:
                raise;
            end;
          end;
        end;
      end;
    end;
  inherited SetActive(Value);
end;

procedure Register;
begin
  RegisterComponents('dbGo', [TADODatasetExt]);
  RegisterComponents('dbGo', [TADOTableExt]);
  RegisterComponents('dbGo', [TADOQueryExt]);
  RegisterComponents('dbGo', [TADOStoredProcExt]);
end;

end.

Open in new window

0
Comment
Question by:Delphiwizard
  • 30
  • 21
  • 12
  • +1
64 Comments
 
LVL 19

Expert Comment

by:Thommy
Comment Utility
There is already a question in EE dealing with ADO reconnection.

Perhaps it might help you...

How do I reconnect ADOConnection or ADODataset after connection failure
http://www.experts-exchange.com/Programming/Languages/Pascal/Delphi/Q_22497709.html

Also check this resource

Recovering from a connection failure
http://community.devexpress.com/forums/p/79792/273154.aspx
0
 

Author Comment

by:Delphiwizard
Comment Utility
Thommy:
I visited the pages you supplied, but a real solution isn't posted there. I gues many developers have to solve this, so I hope anybody is willing to share their solution (which works).
0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility

1. RowsAffected should be moved to public section because it is a runtime property and is also read only
2. You can set the CheckConnection property to true in the create constructor for each component

      TADODataSetExt = class(TADODataSet)
      ..............
      public
        constructor Create(AOwner: TComponent); override;
        ..................
      end;
     
      //in implentation
      constructor TADODataSetExt.Create(AOwner: TComponent);
      begin
          inherited Create(AOwner);
          FCheckConnection := True;
      end;
   
3. If the components are placed on a form, add the datamodule to the uses clause of that form and the HandleConnectionError procedure will be available to that form

Let me know if you need a sample application
unit dmBase;

interface

uses
  Windows, Messages, SysUtils, Forms, Classes, ADODB, BPADOExt;

type
  TdmMain = class(TDataModule)
  private
    function TryReconnect(AConnection: TADOConnection): Boolean;
  public
    procedure HandleConnectionError(Sender: TObject; AConnection: TADOConnection;
      const AErrorMessage: string; var AAction: TErrorAction);
  end;

var
  dmMain: TdmMain;

implementation

{$R *.dfm}

{ TDataModule1 }

procedure TdmMain.HandleConnectionError(Sender: TObject;
  AConnection: TADOConnection; const AErrorMessage: string;
  var AAction: TErrorAction);
begin
  if TryReconnect(AConnection) then
  begin
    AAction := eaIgnore;
    Exit;
  end;

  //You can show the user a message here
  MessageBox(0,
    'Could not re-establish connection to server, Application will close',
    'Connection Error',
    MB_OK or MB_ICONERROR);
  AAction := eaAbort;      //this will not execute the command

  PostMessage(Application.MainForm.Handle, WM_CLOSE, 0, 0);  //end application
end;

function TdmMain.TryReconnect(AConnection: TADOConnection): Boolean;
begin
  AConnection.Connected := False;
  try
    //you can add more code to check whether the network is broken before trying to reconnect
    //this will reestablish the connection
    AConnection.Connected := True;
  except
    Result := False;
  end;
end;

end.

Open in new window

0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility
Modified
unit BPADOExt;

interface

uses
  SysUtils, Classes, DB, ADODB, Variants;

type
  TErrorAction = (eaIgnore, eaAbort, eaRaiseException);

  TOnConnectionError = procedure(Sender: TObject; AConnection: TADOConnection;
    const AErrorMessage: string; var AAction: TErrorAction) of Object;

  TADODataSetExt = class(TADODataSet)
  private
    FCheckConnection: Boolean;
    FOnConnectionError: TOnConnectionError;
  protected
    procedure SetActive(Value: Boolean); override;
  public
    constructor Create(AOwner: TComponent); override;
  published
    property CheckConnection: Boolean read FCheckConnection write FCheckConnection default True;
    property OnConnectionError: TOnConnectionError read FOnConnectionError write FOnConnectionError;
  end;

  TADOTableExt = class(TADOTable)
  private
    FCheckConnection: Boolean;
    FOnConnectionError: TOnConnectionError;
  protected
    procedure SetActive(Value: Boolean); override;
  public
    constructor Create(AOwner: TComponent); override;
  published
    property CheckConnection: Boolean read FCheckConnection write FCheckConnection default True;
    property OnConnectionError: TOnConnectionError read FOnConnectionError write FOnConnectionError;
  end;

  TADOQueryExt = class(TADOQuery)
  private
    FCheckConnection: Boolean;
    FLocalRowsAffected: Integer;
    FOnConnectionError: TOnConnectionError;
  protected
    procedure SetActive(Value: Boolean); override;
  public
    constructor Create(AOwner: TComponent); override;
    function ExecSQL: Integer;
    property RowsAffected: Integer read FLocalRowsAffected;
  published
    property CheckConnection: Boolean read FCheckConnection write FCheckConnection default True;
    property OnConnectionError: TOnConnectionError read FOnConnectionError write FOnConnectionError;
  end;

  TADOStoredProcExt = class(TCustomADODataSet)
  private
    FCheckConnection: Boolean;
    FOnConnectionError: TOnConnectionError;
  protected
    procedure SetActive(Value: Boolean); override;
  public
    constructor Create(AOwner: TComponent); override;
    procedure ExecProc;
  published
    property CheckConnection: Boolean read FCheckConnection write FCheckConnection default True;
    property OnConnectionError: TOnConnectionError read FOnConnectionError write FOnConnectionError;
  end;

  procedure Register;

implementation

{ TADOTableExt }

constructor TADOTableExt.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);
  FCheckConnection := True;
end;

procedure TADOTableExt.SetActive(Value: Boolean);
var
  OpenErr: TErrorAction;
begin
  if not (csReading in ComponentState) then
    if Value and FCheckConnection and (not Connection.Connected) then
    begin
      try
        Connection.Connected := True;
      except
        on E: Exception do
        begin
          if Assigned(FOnConnectionError) then
          begin
            FOnConnectionError(Self, Connection, E.Message, OpenErr);
            case OpenErr of
              eaIgnore: ;
              eaAbort:
                Exit;
              eaRaiseException:
                raise;
            end;
          end;
        end;
      end;
    end;

  inherited SetActive(Value);
end;

{ TADOQueryExt }

constructor TADOQueryExt.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);
  FCheckConnection := True;
end;

function TADOQueryExt.ExecSQL: Integer;
var I: Integer;
    MasterFieldList: string;
begin
  if (Parameters.Count > 0) and Assigned(MasterDataLink.DataSource) and
    Assigned(MasterDataLink.DataSource.DataSet) then
  begin
    for I := 0 to Parameters.Count - 1 do
      if (Parameters[I].Direction in [pdInput, pdInputOutput]) and
        (MasterDataLink.DataSource.DataSet.FindField(Parameters[I].Name) <> nil) then
        MasterFieldList := MasterFieldList + Parameters[I].Name + ';';
    MasterFields := Copy(MasterFieldList, 1, Length(MasterFieldList)-1);
    SetParamsFromCursor;
  end;
  Command.Execute(FLocalRowsAffected, EmptyParam);
  Result := FLocalRowsAffected;
end;

procedure TADOQueryExt.SetActive(Value: Boolean);
var OpenErr: TErrorAction;
begin
  if not (csReading in ComponentState) then
    if Value and FCheckConnection and (not Connection.Connected) then
    begin
      try
        Connection.Connected := True;
      except
        on E: Exception do
        begin
          if Assigned(FOnConnectionError) then
          begin
            FOnConnectionError(Self, Connection, E.Message, OpenErr);
            case OpenErr of
              eaIgnore: ;
              eaAbort:
                Exit;
              eaRaiseException:
                raise;
            end;
          end;
        end;
      end;
    end;
  inherited SetActive(Value);
end;

{ TADODataSetExt }
constructor TADODataSetExt.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);
  FCheckConnection := True;
end;

procedure TADODataSetExt.SetActive(Value: Boolean);
var OpenErr: TErrorAction;
begin
  if not (csReading in ComponentState) then
    if Value and FCheckConnection and (not Connection.Connected) then
    begin
      try
        Connection.Connected := True;
      except
        on E: Exception do
        begin
          if Assigned(FOnConnectionError) then
          begin
            FOnConnectionError(Self, Connection, E.Message, OpenErr);
            case OpenErr of
              eaIgnore: ;
              eaAbort:
                Exit;
              eaRaiseException:
                raise;
            end;
          end;
        end;
      end;
    end;
  inherited SetActive(Value);
end;

{ TADOStoredProcExt }
constructor TADOStoredProcExt.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);
  FCheckConnection := True;
end;

procedure TADOStoredProcExt.ExecProc;
var I: Integer;
    MasterFieldList: string;
begin
  if (Parameters.Count > 0) and Assigned(MasterDataLink.DataSource) and
    Assigned(MasterDataLink.DataSource.DataSet) then
  begin
    for I := 0 to Parameters.Count - 1 do
      if (Parameters[I].Direction in [pdInput, pdInputOutput]) and
        (MasterDataLink.DataSource.DataSet.FindField(Parameters[I].Name) <> nil) then
        MasterFieldList := MasterFieldList + Parameters[I].Name + ';';
    MasterFields := Copy(MasterFieldList, 1, Length(MasterFieldList)-1);
    SetParamsFromCursor;
  end;
  Command.Execute;
end;

procedure TADOStoredProcExt.SetActive(Value: Boolean);
var OpenErr: TErrorAction;
begin
  if not (csReading in ComponentState) then
    if Value and FCheckConnection and (not Connection.Connected) then
    begin
      try
        Connection.Connected := True;
      except
        on E: Exception do
        begin
          if Assigned(FOnConnectionError) then
          begin
            FOnConnectionError(Self, Connection, E.Message, OpenErr);
            case OpenErr of
              eaIgnore: ;
              eaAbort:
                Exit;
              eaRaiseException:
                raise;
            end;
          end;
        end;
      end;
    end;
  inherited SetActive(Value);
end;

procedure Register;
begin
  RegisterComponents('dbGo', [TADODatasetExt]);
  RegisterComponents('dbGo', [TADOTableExt]);
  RegisterComponents('dbGo', [TADOQueryExt]);
  RegisterComponents('dbGo', [TADOStoredProcExt]);
end;

end.

Open in new window

unit dmBase;

interface

uses
  Windows, Messages, SysUtils, Forms, Classes, ADODB, BPADOExt;

type
  TdmMain = class(TDataModule)
  private
    function TryReconnect(AConnection: TADOConnection): Boolean;
  public
    procedure HandleConnectionError(Sender: TObject; AConnection: TADOConnection;
      const AErrorMessage: string; var AAction: TErrorAction);
  end;

var
  dmMain: TdmMain;

implementation

{$R *.dfm}

{ TDataModule1 }

procedure TdmMain.HandleConnectionError(Sender: TObject;
  AConnection: TADOConnection; const AErrorMessage: string;
  var AAction: TErrorAction);
begin
  if TryReconnect(AConnection) then
  begin
    AAction := eaIgnore;
    Exit;
  end;

  //You can show the user a message here
  MessageBox(0,
    'Could not re-establish connection to server, Application will close',
    'Connection Error',
    MB_OK or MB_ICONERROR);
  AAction := eaAbort;      //this will not execute the command

  PostMessage(Application.MainForm.Handle, WM_CLOSE, 0, 0);  //end application
end;

function TdmMain.TryReconnect(AConnection: TADOConnection): Boolean;
begin
  AConnection.Connected := False;
  try
    //you can add more code to check whether the network is broken before trying to reconnect
    //this will reestablish the connection
    AConnection.Connected := True;
    Result := AConnection.Connected;
  except
    Result := False;
  end;
end;

end.

Open in new window

0
 

Author Comment

by:Delphiwizard
Comment Utility
ewangoya:
At the start of my application I open certains queries, which stay opened during program execution. These are queries that are used frequently, like settings of all kind.

What will happen with already open TADOQueryExt when a reconnect is done. Won't it close the query and therefore loose connection all together?
Should these be reopened as well?
ADOQueryExt.Open;
Or will these be restored the moment the connection is restored?

I call them like:
If (DM.ADOQueryExt.FieldByName('MySetting1').AsBoolean = True) then
  // do something

Open in new window


When the connection is broken and I try to execute the above code, then it will show an error like "table MyTable doesn't contain a field called MySetting1"
0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility

Open queries will not have a problem since the data is already in memory. If the connection is reopen and the query needs to get new data, thats fine too because its still connected to the same connection, remember the connection does not change even if its closed and reopen
0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility

#34958146
Its better to be prudent, for all queries which need to be opened at all times, it would be better to reopen them.
You can add a function to do that in the tryconnect function

BTW, if you need to keep data that is relatively static, its better to store the information in a TClientDataset. This can be used purely as a memory dataset and will not rely on the connection.

0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility
You can add some kind of object to keep track of the queries that need to stay open all the time.
Here is a simple example
unit dmBase;

interface

uses
  Windows, Messages, SysUtils, Forms, Classes, DB, ADODB, BPADOExt;

type
  TPersistentQueryList = class(TComponent)
  private
    FList: TList;
  protected
    procedure Notification(AComponent: TComponent; Operation: TOperation); override;
  public
    constructor Create(AOwner: TComponent); override;
    destructor Destroy; override;
    function Add(ADataset: TDataset): Integer;
    procedure ReOpenDatasets;
  end;

  TdmMain = class(TDataModule)
    procedure DataModuleCreate(Sender: TObject);
  private
    function TryReconnect(AConnection: TADOConnection): Boolean;
  public
    procedure HandleConnectionError(Sender: TObject; AConnection: TADOConnection;
      const AErrorMessage: string; var AAction: TErrorAction);
  end;

  procedure RegisterPersistentQuery(ADataset: TDataset);
  function PersistenQuery: TPersistentQueryList;

var
  dmMain: TdmMain;

implementation

{$R *.dfm}

var
  GDatasetList: TPersistentQueryList = nil;

function PersistenQuery: TPersistentQueryList;
begin
  if GDatasetList = nil then
    GDatasetList := TPersistentQueryList.Create(nil);
  Result := GDatasetList;
end;
procedure RegisterPersistentQuery(ADataset: TDataset);
begin
  PersistenQuery.Add(ADataset);
end;

{ TPersistentQueryList }

function TPersistentQueryList.Add(ADataset: TDataset): Integer;
begin
  Result := -1;
  if FList.IndexOf(ADataset) < 0 then
    Result := FList.Add(ADataset);
end;

constructor TPersistentQueryList.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);
  FList := TList.Create;
end;

destructor TPersistentQueryList.Destroy;
begin
  FreeAndNil(FList);
  inherited Destroy;;
end;

procedure TPersistentQueryList.Notification(AComponent: TComponent;
  Operation: TOperation);
var
  Index: Integer;
begin
  inherited Notification(AComponent, Operation);

  if (Operation = opRemove) and (AComponent is TDataset) then
  begin
    Index := FList.IndexOf(AComponent);
    if Index >= 0 then
      FList.Remove(AComponent);
  end;
end;

procedure TPersistentQueryList.ReOpenDatasets;
var
  I: Integer;
begin
  for I := 0 to FList.Count - 1 do
  try
    TDataset(FList[I]).Open;
  except
    //NOOP
  end;
end;


{ TDataModule1 }

procedure TdmMain.DataModuleCreate(Sender: TObject);
begin
  //register queries that should remain open at all times
  //RegisterPersistentQuery(Query1);
end;

procedure TdmMain.HandleConnectionError(Sender: TObject;
  AConnection: TADOConnection; const AErrorMessage: string;
  var AAction: TErrorAction);
begin
  if TryReconnect(AConnection) then
  begin
    AAction := eaIgnore;
    Exit;
  end;

  //You can show the user a message here
  MessageBox(0,
    'Could not re-establish connection to server, Application will close',
    'Connection Error',
    MB_OK or MB_ICONERROR);
  AAction := eaAbort;      //this will not execute the command

  PostMessage(Application.MainForm.Handle, WM_CLOSE, 0, 0);  //end application
end;

function TdmMain.TryReconnect(AConnection: TADOConnection): Boolean;
begin
  AConnection.Connected := False;
  try
    //you can add more code to check whether the network is broken before trying to reconnect
    //this will reestablish the connection
    AConnection.Connected := True;
    Result := AConnection.Connected;
    if Result then
      PersistenQuery.ReOpenDatasets;
  except
    Result := False;
  end;
end;

initialization

finalization
  FreeAndNil(GDatasetList);

end.

Open in new window

0
 

Author Comment

by:Delphiwizard
Comment Utility
Did you actually test this?
When I close my connection (through buttonclick) and try to execute the code mentioned in my previous post, then I do get an errormessage.
This means that whenever the connection is lost, this error does not trigger a reconnect.

Maybe my way of closing the connection is not a correct way to test a lost connection?
AConnection.Connected := False;
0
 

Author Comment

by:Delphiwizard
Comment Utility
 public
    procedure HandleConnectionError(Sender: TObject;
      AConnection: TADOConnection; const AErrorMessage: string;
      var AAction: TErrorAction);

This eventprocedure is still not selectable in DM nore in any other form.
Only when I placed it in the normal event-declaration space it is selectable in DM, but still not in any other form.

Any ideas?
0
 

Author Comment

by:Delphiwizard
Comment Utility
In addition:
1. HandleConnectionError can be entered manually for components placed in DataModule.

2. DataModule is in the uses clause of the other forms.
But when placing "DM.HandleConnectionError" in the OnConnectionError-event I get error: DM.HandleConnectionError is not a valid identifier
0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility

2. DataModule is in the uses clause of the other forms.
But when placing "DM.HandleConnectionError" in the OnConnectionError-event I get error: DM.HandleConnectionError is not a valid identifier


You are right, i don't know how I missed it
For components on other forms, you have to assign the procedure as
procedure TForm1.FormCreate(Sender: TObject);
begin
  ADOQueryExt1.OnConnectionError := DM.HandleConnectionError;
end;

I have modified the components as well

I'll try and show you how to create a descendant of TClientDataset that once loaded using TADOConnection, will retain its data even when the connection is lost. You can then use that for the Queries that need to be open at all times, We can call it TADOMemDataset and it will function exactly like the TADOQuery
unit BPADOExt;

interface

uses
  SysUtils, Classes, DB, ADODB, Variants;

type
  TErrorAction = (eaIgnore, eaAbort, eaRaiseException);

  TOnConnectionError = procedure(Sender: TObject; AConnection: TADOConnection;
    const AErrorMessage: string; var AAction: TErrorAction) of Object;

  TADODataSetExt = class(TADODataSet)
  private
    FCheckConnection: Boolean;
    FOnConnectionError: TOnConnectionError;
  protected
    procedure SetActive(Value: Boolean); override;
  public
    constructor Create(AOwner: TComponent); override;
  published
    property CheckConnection: Boolean read FCheckConnection write FCheckConnection default True;
    property OnConnectionError: TOnConnectionError read FOnConnectionError write FOnConnectionError;
  end;

  TADOTableExt = class(TADOTable)
  private
    FCheckConnection: Boolean;
    FOnConnectionError: TOnConnectionError;
  protected
    procedure SetActive(Value: Boolean); override;
  public
    constructor Create(AOwner: TComponent); override;
  published
    property CheckConnection: Boolean read FCheckConnection write FCheckConnection default True;
    property OnConnectionError: TOnConnectionError read FOnConnectionError write FOnConnectionError;
  end;

  TADOQueryExt = class(TADOQuery)
  private
    FCheckConnection: Boolean;
    FLocalRowsAffected: Integer;
    FOnConnectionError: TOnConnectionError;
  protected
    procedure SetActive(Value: Boolean); override;
  public
    constructor Create(AOwner: TComponent); override;
    function ExecSQL: Integer;
    property RowsAffected: Integer read FLocalRowsAffected;
  published
    property CheckConnection: Boolean read FCheckConnection write FCheckConnection default True;
    property OnConnectionError: TOnConnectionError read FOnConnectionError write FOnConnectionError;
  end;

  TADOStoredProcExt = class(TCustomADODataSet)
  private
    FCheckConnection: Boolean;
    FOnConnectionError: TOnConnectionError;
  protected
    procedure SetActive(Value: Boolean); override;
  public
    constructor Create(AOwner: TComponent); override;
    procedure ExecProc;
  published
    property CheckConnection: Boolean read FCheckConnection write FCheckConnection default True;
    property OnConnectionError: TOnConnectionError read FOnConnectionError write FOnConnectionError;
  end;

  procedure Register;

implementation

{ TADOTableExt }

constructor TADOTableExt.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);
  FCheckConnection := True;
end;

procedure TADOTableExt.SetActive(Value: Boolean);
var
  OpenErr: TErrorAction;
begin
  if not (csReading in ComponentState) then
    if Value and FCheckConnection and (not Connection.Connected) then
      if Assigned(FOnConnectionError) then
      begin
        FOnConnectionError(Self, Connection, 'Connection Lost', OpenErr);
        case OpenErr of
          eaIgnore: ;
          eaAbort:
            Exit;
          eaRaiseException:
            raise EDatabaseError.Create('Connection Lost');
        end;
      end;

  inherited SetActive(Value);
end;

{ TADOQueryExt }

constructor TADOQueryExt.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);
  FCheckConnection := True;
end;

function TADOQueryExt.ExecSQL: Integer;
var I: Integer;
    MasterFieldList: string;
begin
  if (Parameters.Count > 0) and Assigned(MasterDataLink.DataSource) and
    Assigned(MasterDataLink.DataSource.DataSet) then
  begin
    for I := 0 to Parameters.Count - 1 do
      if (Parameters[I].Direction in [pdInput, pdInputOutput]) and
        (MasterDataLink.DataSource.DataSet.FindField(Parameters[I].Name) <> nil) then
        MasterFieldList := MasterFieldList + Parameters[I].Name + ';';
    MasterFields := Copy(MasterFieldList, 1, Length(MasterFieldList)-1);
    SetParamsFromCursor;
  end;
  Command.Execute(FLocalRowsAffected, EmptyParam);
  Result := FLocalRowsAffected;
end;

procedure TADOQueryExt.SetActive(Value: Boolean);
var OpenErr: TErrorAction;
begin
  if not (csReading in ComponentState) then
    if Value and FCheckConnection and (not Connection.Connected) then
      if Assigned(FOnConnectionError) then
      begin
        FOnConnectionError(Self, Connection, 'Connection Lost', OpenErr);
        case OpenErr of
          eaIgnore: ;
          eaAbort:
            Exit;
          eaRaiseException:
            raise EDatabaseError.Create('Connection Lost');
        end;
      end;
  inherited SetActive(Value);
end;

{ TADODataSetExt }
constructor TADODataSetExt.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);
  FCheckConnection := True;
end;

procedure TADODataSetExt.SetActive(Value: Boolean);
var OpenErr: TErrorAction;
begin
  if not (csReading in ComponentState) then
    if Value and FCheckConnection and (not Connection.Connected) then
      if Assigned(FOnConnectionError) then
      begin
        FOnConnectionError(Self, Connection, 'Connection Lost', OpenErr);
        case OpenErr of
          eaIgnore: ;
          eaAbort:
            Exit;
          eaRaiseException:
            raise EDatabaseError.Create('Connection Lost');
        end;
      end;

  inherited SetActive(Value);
end;

{ TADOStoredProcExt }
constructor TADOStoredProcExt.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);
  FCheckConnection := True;
end;

procedure TADOStoredProcExt.ExecProc;
var I: Integer;
    MasterFieldList: string;
begin
  if (Parameters.Count > 0) and Assigned(MasterDataLink.DataSource) and
    Assigned(MasterDataLink.DataSource.DataSet) then
  begin
    for I := 0 to Parameters.Count - 1 do
      if (Parameters[I].Direction in [pdInput, pdInputOutput]) and
        (MasterDataLink.DataSource.DataSet.FindField(Parameters[I].Name) <> nil) then
        MasterFieldList := MasterFieldList + Parameters[I].Name + ';';
    MasterFields := Copy(MasterFieldList, 1, Length(MasterFieldList)-1);
    SetParamsFromCursor;
  end;
  Command.Execute;
end;

procedure TADOStoredProcExt.SetActive(Value: Boolean);
var OpenErr: TErrorAction;
begin
  if not (csReading in ComponentState) then
    if Value and FCheckConnection and (not Connection.Connected) then
      if Assigned(FOnConnectionError) then
      begin
        FOnConnectionError(Self, Connection, 'Connection Lost', OpenErr);
        case OpenErr of
          eaIgnore: ;
          eaAbort:
            Exit;
          eaRaiseException:
            raise EDatabaseError.Create('Connection Lost');
        end;
      end;

  inherited SetActive(Value);
end;

procedure Register;
begin
  RegisterComponents('dbGO', [TADODatasetExt]);
  RegisterComponents('dbGO', [TADOTableExt]);
  RegisterComponents('dbGO', [TADOQueryExt]);
  RegisterComponents('dbGO', [TADOStoredProcExt]);
end;

end.

Open in new window

0
 

Author Comment

by:Delphiwizard
Comment Utility
"ADOQueryExt1.OnConnectionError := DM.HandleConnectionError;"
Ok. I'll do so.

"I'll try and show you how to create a descendant of TClientDataset that once loaded using TADOConnection, will retain its data even when the connection is lost."
That would be perfect.
I think after that I have all the pieces in place.
0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
hmmm, you seemed to have missed some basics about the concept i use ...
http://www.experts-exchange.com/Programming/Languages/Pascal/Delphi/Q_26502495.html

it looks like you took a few tidbits but you're missing some vital clues from my code
i admit, i didn't put a lot of comments in there, i'm not perfect either

the concept:
open a query using a named connection
the connection collection finds the specific connection, open's it if necessary, and then runs the query

your concept (and 98% of the programmers in delphi):
open a connection to the database
attach a query to the opened connection
start the query

you see the difference ?
once your connection is open, that's it, no controlling of errors or not

my solution:
every action done on the query is passed through a collection object first, which checks the connection,
does everything necessary to open that connection if closed (even start a secondary connection in thread)
and then lets the action for the query run

if you want a robust solution, you'll have to change your concept

or wrap every action done by every query in some error checking

the code i provided took (only ...) 2 years to develop in a production environment and is very thoroughly put to the test every day

the challenge you need to think of:
a computer in a truck on a highway passing through wireless zones where it has access to a network
you will have network for a second or 2 here and there

if you can make that work ... you can make anything work ... but you need the concept i pointed out
0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
TClientDataset with memory ?

you have SDAC set, which includes VirtualTable
That's just what you ask
0
 

Author Comment

by:Delphiwizard
Comment Utility
Geert Gruwez:
Yes I believe I missed out on your idea behind the solution.

All my queries have a connection attatched to it (at designtime). And yes this connection is opened at the start of the application. I don't see the difference. The moment one query is opened, the connection is opened too. Then the connection will stay open untill you closed it yourself.

"open a query using a named connection"
How would this look like? Can you give me some example on how to set this up correctly?

"every action done on the query is passed through a collection object first, which checks the connection, does everything necessary to open that connection if closed (even start a secondary connection in thread) and then lets the action for the query run"
Maybe you can explain a bit more about this concept.
0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
delphi and 98% of all samples on the internet do this:
step 1: open a connection to the database

if you want your app to work with possible connection loss,
then the first you must try to achieve:
open your application without possible connection to the database
next: allow connection to the database
(easiest to test is pulling out your network cable, start app, then plug in your network cable)

look for TDacConnection.DoConnect procedure in my uRoot.pas
http://www.experts-exchange.com/Programming/Languages/Pascal/Delphi/Q_26502495.html#33776495
0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
i have given up on trying to explain this concept to other (very good) delphi developers

their concept is to wrap every action to the database in a try except loop

a possible sample:

procedure OpenQuery(Query: TQuery);
var HasError: boolean;
begin
  HasError := False;
  repeat
    try
      Query.Open;
    except
      on E: Exception do
        HasError := CheckQueryError(E.Message);
    end;
  until not HasError;
end;
0
 

Author Comment

by:Delphiwizard
Comment Utility
Geert Gruwez:
I believe that I understand the concept you are talking about, but how do you set that up correctly?
Currently I only have part of the solution you propose and therefore I still haven't solved my issue.
Please have a look at:
http://www.experts-exchange.com/Programming/Languages/Pascal/Delphi/Q_26843899.html
0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
well this is how i use a query: (no you don't see any difference, except the connection string)
 
procedure TForm1.Button1Click(Sender: TObject);
var Q: TDacQuery;
begin
  Q := TDacQuery.Create(self, 'employees', 'Data Source=TORCL;User Id=myUsername;Password=myPassword;';
  try
    Q.SQL.Text := 'Select count(*) n from schema.employees');
    Q.Open;
    fEmployeeCount := Q.FieldByName('n').AsInteger;
    Q.Close;
  finally
   Q.Free;
  end;
end;

Open in new window



when i create a TDacQuery object, it attaches itself to a connection by calling ChangeConnectionTo
 
procedure TDacRoot.ChangeConnectionTo(aConnectionName, aConnectionString: string;
  aConnectPrompt: boolean);
begin
  ResetDisconnectTimer;
  FConnector := DacConnections.Add(aConnectionName, aConnectionString, aConnectPrompt);
  FDacType := FConnector.DacType;
  if FIsQuery or not FIsStoredProc then
  begin
    DestroyQuery;
    CreateQuery;
  end else if FIsStoredProc then
  begin
    DestroyStoredProc;
    CreateStoredProc;
  end;
end;

Open in new window

this calls the connections TDacConnections.Add >
 
function TDacConnections.Add(aConnectionName, aConnectionString: string; aConnectPrompt: boolean): TDacConnection;
var aItem: TDacConnection;
begin
  Result := nil;
  fcsConnection.Enter;
  try
    FTimeOutTimer.Enabled := True;
    if aConnectionName = '' then
      aConnectionName := DefaultConnection;
    aItem := FindConnection(aConnectionName);
    if aItem = nil then
    begin
      if Count < MaxConnections then
        aItem := TDacConnection.Create(Self, aConnectionName, aConnectionString, aConnectPrompt)
      else
        Raise ETooManyConnections.Create('Too many open connections');
    end;
    Result := aItem;
    if (Result <> nil) and fFirstConnection then
      fFirstConnection := False;
  finally
    fcsConnection.Leave;
  end;
end;

Open in new window


the thing is TDacQuery, descendant from TDacRoot, on open
 
procedure TDacRoot.Open;
begin
  ResetDisconnectTimer;
  try
    Dataset.Open;
  except
    // Catch connection errors
    on E: Exception do
    begin
      if FAutoReconnect and ConnectionError(E.Message) then
      begin
        while not Application.Terminated do
        begin
          FConnector.Reconnect;
          try
            FQry.Connection := Connector.Connection;
            Dataset.Open;
            Break;
          except
            on E: Exception do
              if not ConnectionError(E.Message) then
                Break;
          end;
        end;
      end else
        Raise Exception.Create(FConnector.FConnectionName + ' ' + E.Message);
    end;
  end;
end;

Open in new window

you see how every call is wrapped in a try except handler ?

also everything has been written for use in threads
with protection by critical sections

i hope you followed that a little ?

when a error occurs, this is caught by such a exception handler of the connection
procedure TDacConnection.ConnectionErrorOra(Sender: TObject; E: EDAError; var Fail: boolean);
begin
  if FAutoReconnect and ConnectionError(E.Message + #13#10'ORA-' + IntToStr(E.ErrorCode)) then
  begin
    Reconnect;
    //Fail := True;
  end;
end;

Open in new window


the reconnect then uses a thread and a secondary connection to wait and try until the database is back
0
 

Author Comment

by:Delphiwizard
Comment Utility
Can this be done with standard ADO-components too, and how?

"when i create a TDacQuery object, it attaches itself to a connection by calling ChangeConnectionTo"
How do you trigger this?
The sane goes for ConnectionErrorOra, I gues this is an event on your DACQuery-component?

What is the ADO-equivelant of TDACRoot (if there is any)?
0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility

I have included the new component, called TADOClientDataset

Basically just set the Connection and CommandText, then set Active to true or call open

ADOClientDataset.Connection := Connection;
ADOClientDataset.CommandText := 'select * from table1';
ADOClientDataset.Active := True;

Even if your connection is lost, the data is still in memory and you can call FieldByName with no problem.
Since this is based on clientdataset, you can even make changes when there is no connection, and call ApplyUpdates when the connection becomes available

Enjoy
unit BPADOExt;

interface

uses
  SysUtils, Classes, Variants, DB, ADODB, Provider, DBClient;

type
  TErrorAction = (eaIgnore, eaAbort, eaRaiseException);

  TOnConnectionError = procedure(Sender: TObject; AConnection: TADOConnection;
    const AErrorMessage: string; var AAction: TErrorAction) of Object;

  TADODataSetExt = class(TADODataSet)
  private
    FCheckConnection: Boolean;
    FOnConnectionError: TOnConnectionError;
  protected
    procedure SetActive(Value: Boolean); override;
  public
    constructor Create(AOwner: TComponent); override;
  published
    property CheckConnection: Boolean read FCheckConnection write FCheckConnection default True;
    property OnConnectionError: TOnConnectionError read FOnConnectionError write FOnConnectionError;
  end;

  TADOTableExt = class(TADOTable)
  private
    FCheckConnection: Boolean;
    FOnConnectionError: TOnConnectionError;
  protected
    procedure SetActive(Value: Boolean); override;
  public
    constructor Create(AOwner: TComponent); override;
  published
    property CheckConnection: Boolean read FCheckConnection write FCheckConnection default True;
    property OnConnectionError: TOnConnectionError read FOnConnectionError write FOnConnectionError;
  end;

  TADOQueryExt = class(TADOQuery)
  private
    FCheckConnection: Boolean;
    FLocalRowsAffected: Integer;
    FOnConnectionError: TOnConnectionError;
  protected
    procedure SetActive(Value: Boolean); override;
  public
    constructor Create(AOwner: TComponent); override;
    function ExecSQL: Integer;
    property RowsAffected: Integer read FLocalRowsAffected;
  published
    property CheckConnection: Boolean read FCheckConnection write FCheckConnection default True;
    property OnConnectionError: TOnConnectionError read FOnConnectionError write FOnConnectionError;
  end;

  TADOStoredProcExt = class(TCustomADODataSet)
  private
    FCheckConnection: Boolean;
    FOnConnectionError: TOnConnectionError;
  protected
    procedure SetActive(Value: Boolean); override;
  public
    constructor Create(AOwner: TComponent); override;
    procedure ExecProc;
  published
    property CheckConnection: Boolean read FCheckConnection write FCheckConnection default True;
    property OnConnectionError: TOnConnectionError read FOnConnectionError write FOnConnectionError;
  end;

  TADOClientDataset = class(TCustomClientDataSet)
  private
    FConnection: TADOConnection;
    FProvider: TDatasetProvider;
    FDataSet: TADODataSet;
    function GetCommandText: WideString;
    function GetCommandType: TCommandType;
    function GetOptions: TProviderOptions;
    procedure SetCommandType(Value: TCommandType);
    procedure SetConnection(Value: TADOConnection);
    procedure SetOptions(const Value: TProviderOptions);
  protected
    property DataSet: TADODataSet read FDataSet;
    procedure Notification(AComponent: TComponent; Operation: TOperation); override;
    procedure SetCommandText(Value: WideString); override;
  public
    constructor Create(AOwner: TComponent); override;
    property Active stored False;
    property Aggregates;
    property AggregatesActive;
    property AutoCalcFields;
    property CommandText: WideString read GetCommandText write SetCommandText;
    property CommandType: TCommandType read GetCommandType write SetCommandType;
    property Connection: TADOConnection read FConnection write SetConnection;
    property Constraints;
    property DisableStringTrim;
    property FileName;
    property Filter;
    property Filtered;
    property FilterOptions;
    property FieldDefs;
    property IndexDefs;
    property IndexFieldNames;
    property IndexName;
    property FetchOnDemand;
    property MasterFields;
    property MasterSource;
    property ObjectView;
    property PacketRecords;
    property Params;
    property Options: TProviderOptions read GetOptions write SetOptions;
    property ReadOnly;
    property StoreDefs;
    property BeforeOpen;
    property AfterOpen;
    property BeforeClose;
    property AfterClose;
    property BeforeInsert;
    property AfterInsert;
    property BeforeEdit;
    property AfterEdit;
    property BeforePost;
    property AfterPost;
    property BeforeCancel;
    property AfterCancel;
    property BeforeDelete;
    property AfterDelete;
    property BeforeScroll;
    property AfterScroll;
    property BeforeRefresh;
    property AfterRefresh;
    property OnCalcFields;
    property OnDeleteError;
    property OnEditError;
    property OnFilterRecord;
    property OnNewRecord;
    property OnPostError;
    property OnReconcileError;
    property BeforeApplyUpdates;
    property AfterApplyUpdates;
    property BeforeGetRecords;
    property AfterGetRecords;
    property BeforeRowRequest;
    property AfterRowRequest;
    property BeforeExecute;
    property AfterExecute;
    property BeforeGetParams;
    property AfterGetParams;
  end;

  procedure Register;

implementation

procedure Register;
begin
  RegisterComponents('dbGO', [TADODatasetExt]);
  RegisterComponents('dbGO', [TADOTableExt]);
  RegisterComponents('dbGO', [TADOQueryExt]);
  RegisterComponents('dbGO', [TADOStoredProcExt]);
  RegisterComponents('dbGO', [TADOClientDataset]);
end;

{ TADOTableExt }

constructor TADOTableExt.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);
  FCheckConnection := True;
end;

procedure TADOTableExt.SetActive(Value: Boolean);
var
  OpenErr: TErrorAction;
begin
  if not (csReading in ComponentState) then
    if Value and FCheckConnection and (not Connection.Connected) then
      if Assigned(FOnConnectionError) then
      begin
        FOnConnectionError(Self, Connection, 'Connection Lost', OpenErr);
        case OpenErr of
          eaIgnore: ;
          eaAbort:
            Exit;
          eaRaiseException:
            raise EDatabaseError.Create('Connection Lost');
        end;
      end;

  inherited SetActive(Value);
end;

{ TADOQueryExt }

constructor TADOQueryExt.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);
  FCheckConnection := True;
end;

function TADOQueryExt.ExecSQL: Integer;
var I: Integer;
    MasterFieldList: string;
begin
  if (Parameters.Count > 0) and Assigned(MasterDataLink.DataSource) and
    Assigned(MasterDataLink.DataSource.DataSet) then
  begin
    for I := 0 to Parameters.Count - 1 do
      if (Parameters[I].Direction in [pdInput, pdInputOutput]) and
        (MasterDataLink.DataSource.DataSet.FindField(Parameters[I].Name) <> nil) then
        MasterFieldList := MasterFieldList + Parameters[I].Name + ';';
    MasterFields := Copy(MasterFieldList, 1, Length(MasterFieldList)-1);
    SetParamsFromCursor;
  end;
  Command.Execute(FLocalRowsAffected, EmptyParam);
  Result := FLocalRowsAffected;
end;

procedure TADOQueryExt.SetActive(Value: Boolean);
var OpenErr: TErrorAction;
begin
  if not (csReading in ComponentState) then
    if Value and FCheckConnection and (not Connection.Connected) then
      if Assigned(FOnConnectionError) then
      begin
        FOnConnectionError(Self, Connection, 'Connection Lost', OpenErr);
        case OpenErr of
          eaIgnore: ;
          eaAbort:
            Exit;
          eaRaiseException:
            raise EDatabaseError.Create('Connection Lost');
        end;
      end;
  inherited SetActive(Value);
end;

{ TADODataSetExt }
constructor TADODataSetExt.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);
  FCheckConnection := True;
end;

procedure TADODataSetExt.SetActive(Value: Boolean);
var OpenErr: TErrorAction;
begin
  if not (csReading in ComponentState) then
    if Value and FCheckConnection and (not Connection.Connected) then
      if Assigned(FOnConnectionError) then
      begin
        FOnConnectionError(Self, Connection, 'Connection Lost', OpenErr);
        case OpenErr of
          eaIgnore: ;
          eaAbort:
            Exit;
          eaRaiseException:
            raise EDatabaseError.Create('Connection Lost');
        end;
      end;

  inherited SetActive(Value);
end;

{ TADOStoredProcExt }
constructor TADOStoredProcExt.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);
  FCheckConnection := True;
end;

procedure TADOStoredProcExt.ExecProc;
var I: Integer;
    MasterFieldList: string;
begin
  if (Parameters.Count > 0) and Assigned(MasterDataLink.DataSource) and
    Assigned(MasterDataLink.DataSource.DataSet) then
  begin
    for I := 0 to Parameters.Count - 1 do
      if (Parameters[I].Direction in [pdInput, pdInputOutput]) and
        (MasterDataLink.DataSource.DataSet.FindField(Parameters[I].Name) <> nil) then
        MasterFieldList := MasterFieldList + Parameters[I].Name + ';';
    MasterFields := Copy(MasterFieldList, 1, Length(MasterFieldList)-1);
    SetParamsFromCursor;
  end;
  Command.Execute;
end;

procedure TADOStoredProcExt.SetActive(Value: Boolean);
var OpenErr: TErrorAction;
begin
  if not (csReading in ComponentState) then
    if Value and FCheckConnection and (not Connection.Connected) then
      if Assigned(FOnConnectionError) then
      begin
        FOnConnectionError(Self, Connection, 'Connection Lost', OpenErr);
        case OpenErr of
          eaIgnore: ;
          eaAbort:
            Exit;
          eaRaiseException:
            raise EDatabaseError.Create('Connection Lost');
        end;
      end;

  inherited SetActive(Value);
end;

{ TADOClientDataset }

constructor TADOClientDataset.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);
  FDataSet := TADODataSet.Create(Self);
  FDataSet.SetSubComponent(True);
  FDataSet.Name := 'LocalDataSet';
  FDataSet.Connection := FConnection;

  FProvider := TDataSetProvider.Create(Self);
  FProvider.SetSubComponent(True);
  FProvider.Name := 'LocalProvider';
  FProvider.DataSet := FDataSet;
  SetProvider(FProvider);
end;

function TADOClientDataset.GetCommandText: WideString;
begin
  Result := DataSet.CommandText;
end;

function TADOClientDataset.GetCommandType: TCommandType;
begin
  Result := DataSet.CommandType;
end;

function TADOClientDataset.GetOptions: TProviderOptions;
begin
  Result := FProvider.Options;
end;

procedure TADOClientDataset.Notification(AComponent: TComponent;
  Operation: TOperation);
begin
  inherited Notification(AComponent, Operation);

  if (Operation = opRemove) and (not (csDestroying in ComponentState)) and
      (AComponent = FConnection) then
    FConnection := nil;
end;

procedure TADOClientDataset.SetCommandText(Value: WideString);
begin
  DataSet.CommandText := Value;
end;

procedure TADOClientDataset.SetCommandType(Value: TCommandType);
begin
  DataSet.CommandType := Value;
end;

procedure TADOClientDataset.SetConnection(Value: TADOConnection);
begin
  if Value = FConnection then
    Exit;

  FConnection := Value;
  FConnection.FreeNotification(Self);
  FDataSet.Connection := FConnection;
end;

procedure TADOClientDataset.SetOptions(const Value: TProviderOptions);
begin
  FProvider.Options := Value;
end;

end.

Open in new window

0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
The TAdoQuery and TAdoStoredProc have to be created in CreateQuery and CreateStoredProc of TDacRoot

  TDacRoot = class(TComponent)
  private
    FIsQuery: boolean;
    FIsStoredProc: boolean;
    FConnector: TDacConnection;
    FQry: TCustomDADataset;
    FStp: TCustomDaDataset;
    procedure CreateQuery;
    procedure CreateStoredProc;

i attach them to the FQry and fStp for further internal use in the class

i used DevArt components ... you may notice SDAC removed in places
SDAC is the devart component for mssql server database :)
if you have that, just uncomment and use SDAC as default type to create queries

there is also the capability of writing 1 sql text with translations enabling the queries on both oracle and mssql server

procedure TDacRoot.CreateQuery;
var doProc: boolean;
begin
  ResetDisconnectTimer;
  doProc := FIsStoredProc or ((FDacType <> dacUnknown) and not Assigned(FQry));
  if doProc then
  begin
    if FIsStoredProc then
      DestroyStoredProc;
    if (FDacType <> dacUnknown) and not Assigned(FQry) then
    begin
      case FDacType of
        dacOracle:
        begin
          FQry := TOraQuery.Create(Owner);
          TOraQuery(FQry).FetchAll := True;
        end;
        // Removed SDAC
        //dacMSSQL: FQry := TMSQuery.Create(Owner);
      end;
      if Assigned(FQry) then
      begin
        FIsQuery := True;
        FQry.Connection := FConnector.Connection;
        FQry.CachedUpdates := DacCachedUpdates;
        FQry.LocalUpdate := DacLocalUpdate;
        FQry.BeforeOpen := TriggerBeforeOpen;
        FQry.BeforeUpdateExecute := TriggerBeforeExecuteUpdate;
        FQry.OnUpdateRecord := InternalUpdateRecord;
        FQry.SaveSQL
      end;
    end;
  end;
end;

Open in new window

0
 

Author Comment

by:Delphiwizard
Comment Utility
Hi Geert,

Maybe I ask a lot, but could you please supply me with a sample application where all of this is working. That way I can see how everything is connected to eachother.
Please leave out everything that is not needed, so I can easily check how it is working.

By the way I'm currently in the process of migration my application to SDAC.
But I don't know if this will work out. There are quite some changes to be made. All my reports need to be updated and TStringfields in FieldEditors are now changed to TWideStringfields. Also a lot of commands are slightly different.
Also every update needs to be done within a Transaction. With ADO that isn't the case. It makes the application a bit more secure though :-)

What is the reason you don't use SDAC anymore?
0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
i used to have a all in one application which ran on mssql server and also on oracle
1 app for both databases ... i gues you have heard of the myth that some sql languages work for all databases ...
well actually i achieved that ... at the cost of a lot of work

i don't have time at the moment to edit all that code to give you a test sample
you may need to really dig into SDAC documents ... they may have autoreconnect options already built in :)
they were busy with some new ideas 3 years back, i don't know where they ended up
had version 6.4 back then ...
0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
ewangoya ... that doesn't do any reconnecting ...

you threw a comment at thommy lately (in a other question) about hoping something would stick
can we throw the same comment at you now ?
0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility

Well, he was making wild guesses. not even related to the question that was put forth

I did not add the code to reconnect but it will just be similar to the other components, I was probably just tired.
That code can be added in no time
0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
oh yeah ?
i'd really like to see you're code ...
0
 

Author Comment

by:Delphiwizard
Comment Utility
Me too, but for real. :-)
0
 

Author Comment

by:Delphiwizard
Comment Utility
ewangoya:
Are you actually building an automatic reconnect into the ADO-components?
0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility

Thats exactly what I'm doing with the components here, if the connection is lost, the component will call Handle ConnectionError which tries to reconnect to the database again before executing the required function.

The TADOClientDataset that I showed you will work even without a connection. Its only upon saving data that it needs an active connection.

I'm going to rework the components and let TADOConnection call the reconnect procedure so you only have to set the procedure on the Connection object


0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
ewangoya
are you considering the delta approach  ?

query opens, takes first 25 lines, takes next 25 lines ... looses connection ... reconnects ... takes next 25 lines ...

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Delphiwizard
Comment Utility
ewangoya: Are things working out for you? Are you getting near a solution?
0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility

Just a little hectic schedule. I'll finish by the end of the day
0
 

Author Comment

by:Delphiwizard
Comment Utility
Great.
0
 
LVL 32

Accepted Solution

by:
ewangoya earned 500 total points
Comment Utility

I moved the connection check to the Connection itself

You can improve this further
unit BPADOExt;

interface

uses
  SysUtils, Classes, Variants, DB, ADODB, Provider, DBClient;

type
  TErrorAction = (eaFixed, eaAbort, eaRaiseException);

  TOnConnectionError = procedure(Sender: TADOConnection;
    const AErrorMessage: string; var AAction: TErrorAction) of Object;

  TADOConnectionExt = class(TADOConnection)
  private
    FOnConnectionError: TOnConnectionError;
  protected
    function ConnectionLost: Boolean;
    procedure DoCheckConnection(const AErrorMessage: string; var AErrorAction: TErrorAction);
  published
    property OnConnectionError: TOnConnectionError read FOnConnectionError write FOnConnectionError;
  end;

  TADODataSetExt = class(TADODataSet)
  private
    FCheckConnection: Boolean;
  protected
    procedure SetActive(Value: Boolean); override;
  public
    constructor Create(AOwner: TComponent); override;
  published
    property CheckConnection: Boolean read FCheckConnection write FCheckConnection default True;
  end;

  TADOTableExt = class(TADOTable)
  private
    FCheckConnection: Boolean;
  protected
    procedure SetActive(Value: Boolean); override;
  public
    constructor Create(AOwner: TComponent); override;
  published
    property CheckConnection: Boolean read FCheckConnection write FCheckConnection default True;
  end;

  TADOQueryExt = class(TADOQuery)
  private
    FCheckConnection: Boolean;
    FLocalRowsAffected: Integer;
  protected
    procedure SetActive(Value: Boolean); override;
  public
    constructor Create(AOwner: TComponent); override;
    function ExecSQL: Integer;
    property RowsAffected: Integer read FLocalRowsAffected;
  published
    property CheckConnection: Boolean read FCheckConnection write FCheckConnection default True;
  end;

  TADOStoredProcExt = class(TCustomADODataSet)
  private
    FCheckConnection: Boolean;
  protected
    procedure SetActive(Value: Boolean); override;
  public
    constructor Create(AOwner: TComponent); override;
    procedure ExecProc;
  published
    property CheckConnection: Boolean read FCheckConnection write FCheckConnection default True;
  end;

  TADOClientDataset = class(TCustomClientDataSet)
  private
    FCheckConnection: Boolean;
    FConnection: TADOConnection;
    FProvider: TDatasetProvider;
    FDataSet: TADODataSet;
    function GetCommandText: WideString;
    function GetCommandType: TCommandType;
    function GetOptions: TProviderOptions;
    procedure SetCommandType(Value: TCommandType);
    procedure SetConnection(Value: TADOConnection);
    procedure SetOptions(const Value: TProviderOptions);
  protected
    property DataSet: TADODataSet read FDataSet;
    procedure Notification(AComponent: TComponent; Operation: TOperation); override;
    procedure SetActive(Value: Boolean); override;
    procedure SetCommandText(Value: WideString); override;
  public
    constructor Create(AOwner: TComponent); override;
    property Active stored False;
    property Aggregates;
    property AggregatesActive;
    property AutoCalcFields;
    property CheckConnection: Boolean read FCheckConnection write FCheckConnection default True;
    property CommandText: WideString read GetCommandText write SetCommandText;
    property CommandType: TCommandType read GetCommandType write SetCommandType;
    property Connection: TADOConnection read FConnection write SetConnection;
    property Constraints;
    property DisableStringTrim;
    property FileName;
    property Filter;
    property Filtered;
    property FilterOptions;
    property FieldDefs;
    property IndexDefs;
    property IndexFieldNames;
    property IndexName;
    property FetchOnDemand;
    property MasterFields;
    property MasterSource;
    property ObjectView;
    property PacketRecords;
    property Params;
    property Options: TProviderOptions read GetOptions write SetOptions;
    property ReadOnly;
    property StoreDefs;
    property BeforeOpen;
    property AfterOpen;
    property BeforeClose;
    property AfterClose;
    property BeforeInsert;
    property AfterInsert;
    property BeforeEdit;
    property AfterEdit;
    property BeforePost;
    property AfterPost;
    property BeforeCancel;
    property AfterCancel;
    property BeforeDelete;
    property AfterDelete;
    property BeforeScroll;
    property AfterScroll;
    property BeforeRefresh;
    property AfterRefresh;
    property OnCalcFields;
    property OnDeleteError;
    property OnEditError;
    property OnFilterRecord;
    property OnNewRecord;
    property OnPostError;
    property OnReconcileError;
    property BeforeApplyUpdates;
    property AfterApplyUpdates;
    property BeforeGetRecords;
    property AfterGetRecords;
    property BeforeRowRequest;
    property AfterRowRequest;
    property BeforeExecute;
    property AfterExecute;
    property BeforeGetParams;
    property AfterGetParams;
  end;

  procedure Register;

implementation

procedure Register;
begin
  RegisterComponents('dbGO', [TADOConnectionExt]);
  RegisterComponents('dbGO', [TADODatasetExt]);
  RegisterComponents('dbGO', [TADOTableExt]);
  RegisterComponents('dbGO', [TADOQueryExt]);
  RegisterComponents('dbGO', [TADOStoredProcExt]);
  RegisterComponents('dbGO', [TADOClientDataset]);
end;

{ TADOConnectionExt }

function TADOConnectionExt.ConnectionLost: Boolean;
begin
  Result := False;
  try
    Execute('SELECT NULL'); //this will restore the connection if possible by calling CheckActive
  except
    Result := True;
  end;
end;

procedure TADOConnectionExt.DoCheckConnection(const AErrorMessage: string;
  var AErrorAction: TErrorAction);
begin
  AErrorAction := eaRaiseException;

  if Assigned(FOnConnectionError) and ConnectionLost then
    FOnConnectionError(Self, AErrorMessage, AErrorAction);
end;

{ TADOTableExt }

constructor TADOTableExt.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);
  FCheckConnection := True;
end;

procedure TADOTableExt.SetActive(Value: Boolean);
var
  OpenErr: TErrorAction;
begin
  try
    inherited SetActive(Value);
  except
    on E: Exception do
    begin
      if (not (csReading in ComponentState)) and Value and FCheckConnection and
         Assigned(Connection) and (Connection is TADOConnectionExt) then
      begin
        TADOConnectionExt(Connection).DoCheckConnection(E.Message, OpenErr);
        case OpenErr of
          eaFixed: inherited SetActive(Value); //retry
          eaAbort: ; //dont run
          eaRaiseException:
            raise;
        end;
      end
      else
        raise;
    end;
  end;
end;

{ TADOQueryExt }

constructor TADOQueryExt.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);
  FCheckConnection := True;
end;

function TADOQueryExt.ExecSQL: Integer;
var I: Integer;
    MasterFieldList: string;
begin
  if (Parameters.Count > 0) and Assigned(MasterDataLink.DataSource) and
    Assigned(MasterDataLink.DataSource.DataSet) then
  begin
    for I := 0 to Parameters.Count - 1 do
      if (Parameters[I].Direction in [pdInput, pdInputOutput]) and
        (MasterDataLink.DataSource.DataSet.FindField(Parameters[I].Name) <> nil) then
        MasterFieldList := MasterFieldList + Parameters[I].Name + ';';
    MasterFields := Copy(MasterFieldList, 1, Length(MasterFieldList)-1);
    SetParamsFromCursor;
  end;
  Command.Execute(FLocalRowsAffected, EmptyParam);
  Result := FLocalRowsAffected;
end;

procedure TADOQueryExt.SetActive(Value: Boolean);
var
  OpenErr: TErrorAction;
begin
  try
    inherited SetActive(Value);
  except
    on E: Exception do
    begin
      if not (csReading in ComponentState) and Value and FCheckConnection and
         Assigned(Connection) and (Connection is TADOConnectionExt) then
      begin
        TADOConnectionExt(Connection).DoCheckConnection(E.Message, OpenErr);
        case OpenErr of
          eaFixed: inherited SetActive(Value); //retry
          eaAbort: ; //dont run
          eaRaiseException:
            raise;
        end;
      end
      else
        raise;
    end;
  end;
end;

{ TADODataSetExt }
constructor TADODataSetExt.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);
  FCheckConnection := True;
end;

procedure TADODataSetExt.SetActive(Value: Boolean);
var
  OpenErr: TErrorAction;
begin
  try
    inherited SetActive(Value);
  except
    on E: Exception do
    begin
      if not (csReading in ComponentState) and Value and FCheckConnection and
         Assigned(Connection) and (Connection is TADOConnectionExt) then
      begin
        TADOConnectionExt(Connection).DoCheckConnection(E.Message, OpenErr);
        case OpenErr of
          eaFixed: inherited SetActive(Value); //retry
          eaAbort: ; //dont run
          eaRaiseException:
            raise;
        end;
      end
      else
        raise;
    end;
  end;
end;

{ TADOStoredProcExt }
constructor TADOStoredProcExt.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);
  FCheckConnection := True;
end;

procedure TADOStoredProcExt.ExecProc;
var I: Integer;
    MasterFieldList: string;
begin
  if (Parameters.Count > 0) and Assigned(MasterDataLink.DataSource) and
    Assigned(MasterDataLink.DataSource.DataSet) then
  begin
    for I := 0 to Parameters.Count - 1 do
      if (Parameters[I].Direction in [pdInput, pdInputOutput]) and
        (MasterDataLink.DataSource.DataSet.FindField(Parameters[I].Name) <> nil) then
        MasterFieldList := MasterFieldList + Parameters[I].Name + ';';
    MasterFields := Copy(MasterFieldList, 1, Length(MasterFieldList)-1);
    SetParamsFromCursor;
  end;
  Command.Execute;
end;

procedure TADOStoredProcExt.SetActive(Value: Boolean);
var
  OpenErr: TErrorAction;
begin
  try
    inherited SetActive(Value);
  except
    on E: Exception do
    begin
      if not (csReading in ComponentState) and Value and FCheckConnection and
         Assigned(Connection) and (Connection is TADOConnectionExt) then
      begin
        TADOConnectionExt(Connection).DoCheckConnection(E.Message, OpenErr);
        case OpenErr of
          eaFixed: inherited SetActive(Value); //retry
          eaAbort: ; //dont run
          eaRaiseException:
            raise;
        end;
      end
      else
        raise;
    end;
  end;
end;

{ TADOClientDataset }

constructor TADOClientDataset.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);
  FDataSet := TADODataSet.Create(Self);
  FDataSet.SetSubComponent(True);
  FDataSet.Name := 'LocalDataSet';
  FDataSet.Connection := FConnection;

  FProvider := TDataSetProvider.Create(Self);
  FProvider.SetSubComponent(True);
  FProvider.Name := 'LocalProvider';
  FProvider.DataSet := FDataSet;
  SetProvider(FProvider);

  FCheckConnection := True;
end;

function TADOClientDataset.GetCommandText: WideString;
begin
  Result := DataSet.CommandText;
end;

function TADOClientDataset.GetCommandType: TCommandType;
begin
  Result := DataSet.CommandType;
end;

function TADOClientDataset.GetOptions: TProviderOptions;
begin
  Result := FProvider.Options;
end;

procedure TADOClientDataset.Notification(AComponent: TComponent;
  Operation: TOperation);
begin
  inherited Notification(AComponent, Operation);

  if (Operation = opRemove) and (not (csDestroying in ComponentState)) and
      (AComponent = FConnection) then
    FConnection := nil;
end;

procedure TADOClientDataset.SetActive(Value: Boolean);
var
  OpenErr: TErrorAction;
begin
  try
    inherited SetActive(Value);
  except
    on E: Exception do
    begin
      if not (csReading in ComponentState) and Value and FCheckConnection and
         Assigned(Connection) and (Connection is TADOConnectionExt) then
      begin
        TADOConnectionExt(Connection).DoCheckConnection(E.Message, OpenErr);
        case OpenErr of
          eaFixed: inherited SetActive(Value); //retry
          eaAbort: ; //dont run
          eaRaiseException:
            raise;
        end;
      end
      else
        raise;
    end;
  end;
end;

procedure TADOClientDataset.SetCommandText(Value: WideString);
begin
  DataSet.CommandText := Value;
end;

procedure TADOClientDataset.SetCommandType(Value: TCommandType);
begin
  DataSet.CommandType := Value;
end;

procedure TADOClientDataset.SetConnection(Value: TADOConnection);
begin
  if Value = FConnection then
    Exit;

  FConnection := Value;
  FConnection.FreeNotification(Self);
  FDataSet.Connection := FConnection;
end;

procedure TADOClientDataset.SetOptions(const Value: TProviderOptions);
begin
  FProvider.Options := Value;
end;

end.

Open in new window

0
 

Author Comment

by:Delphiwizard
Comment Utility
Can you point out how to use this?
Did you already test it in a small application?
0
 
LVL 32

Assisted Solution

by:ewangoya
ewangoya earned 500 total points
Comment Utility

program Project1;

uses
  madExcept,
  madLinkDisAsm,
  madListHardware,
  madListProcesses,
  madListModules,
  Forms,
  Unit1 in 'Unit1.pas' {Form1},
  dmBase in 'dmBase.pas' {DM: TDataModule};

{$R *.res}

begin
  Application.Initialize;
  Application.CreateForm(TDM, DM);
  Application.CreateForm(TForm1, Form1);  
  Application.Run;
end.
dmBase.dfm
dmBase.pas
Unit1.pas
Unit1.dfm
0
 
LVL 32

Assisted Solution

by:ewangoya
ewangoya earned 500 total points
Comment Utility
For client dataset I add the published keyword for the properties
TADOClientDataset = class(TCustomClientDataSet)
  private
    FCheckConnection: Boolean;
    FConnection: TADOConnection;
    FProvider: TDatasetProvider;
    FDataSet: TADODataSet;
    function GetCommandText: WideString;
    function GetCommandType: TCommandType;
    function GetOptions: TProviderOptions;
    procedure SetCommandType(Value: TCommandType);
    procedure SetConnection(Value: TADOConnection);
    procedure SetOptions(const Value: TProviderOptions);
  protected
    property DataSet: TADODataSet read FDataSet;
    procedure Notification(AComponent: TComponent; Operation: TOperation); override;
    procedure SetActive(Value: Boolean); override;
    procedure SetCommandText(Value: WideString); override;
  public
    constructor Create(AOwner: TComponent); override;
  published
    property Active stored False;
    property Aggregates;
    property AggregatesActive;
    property AutoCalcFields;
    property CheckConnection: Boolean read FCheckConnection write FCheckConnection default True;
    property CommandText: WideString read GetCommandText write SetCommandText;
    property CommandType: TCommandType read GetCommandType write SetCommandType;
    property Connection: TADOConnection read FConnection write SetConnection;
    property Constraints;
    property DisableStringTrim;
    property FileName;
    property Filter;
    property Filtered;
    property FilterOptions;
    property FieldDefs;
    property IndexDefs;
    property IndexFieldNames;
    property IndexName;
    property FetchOnDemand;
    property MasterFields;
    property MasterSource;
    property ObjectView;
    property PacketRecords;
    property Params;
    property Options: TProviderOptions read GetOptions write SetOptions;
    property ReadOnly;
    property StoreDefs;
    property BeforeOpen;
    property AfterOpen;
    property BeforeClose;
    property AfterClose;
    property BeforeInsert;
    property AfterInsert;
    property BeforeEdit;
    property AfterEdit;
    property BeforePost;
    property AfterPost;
    property BeforeCancel;
    property AfterCancel;
    property BeforeDelete;
    property AfterDelete;
    property BeforeScroll;
    property AfterScroll;
    property BeforeRefresh;
    property AfterRefresh;
    property OnCalcFields;
    property OnDeleteError;
    property OnEditError;
    property OnFilterRecord;
    property OnNewRecord;
    property OnPostError;
    property OnReconcileError;
    property BeforeApplyUpdates;
    property AfterApplyUpdates;
    property BeforeGetRecords;
    property AfterGetRecords;
    property BeforeRowRequest;
    property AfterRowRequest;
    property BeforeExecute;
    property AfterExecute;
    property BeforeGetParams;
    property AfterGetParams;
  end;

Open in new window

0
 

Author Comment

by:Delphiwizard
Comment Utility
One zipfile of the project would be much easier, but I'm happy anyway :-)
0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
you nearly handled everything
except ... handling a connection loss from the front-end inside a thread
but i'm pushing ... aren't i ?
0
 

Author Comment

by:Delphiwizard
Comment Utility
When I run the application I get an error:
Invalid object name 'cabinet'.

Also I uses EurekaLog and not MadExcept. Does this give a problem?
0
 

Author Comment

by:Delphiwizard
Comment Utility
Forget my last remark.
It was a tablename in the TClientDataset.CmdText
0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility

Thats a table from my database. try using your test database
0
 

Author Comment

by:Delphiwizard
Comment Utility
I started the application and disabled my networkconnection (LAN).
The application started to re-connect, and apparently it did even without an enabled networkconnection.
Maybe it connected to the sever in some other way?

I can't see how often (or how long) re-connection is done. What would be the trigger to give up reconnecting and close the application?
In the example of a truckdriver driving into a tunnel. Re-connecting might need some time untill the connection can be re-established.

The persistent tables/queries that are opened work fine. But can I still do a requery on those tables/queries?
Are there any consideration that I need to know, what might give problems?
0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility

The application will stay open unless in your HandleConnectionError method, you instruct it to Terminate

There is no thread looping trying to constantly reconnect, when an action is specified and it is disconnected, then the object will try and reconnect.

The ADOClientDataset will reload if you want to re-query the database, you just reset Active property (or Close, Open), ie
ADOClientDataset1.Close;
ADOClientDataset1.Open;

Infact when using this adoclientdataset for data insertion, your application can keep running without a viable connection until such a connection is re-established and then save the data that was cached by calling ADOClientDataset1.ApplyUpdates(0)

What all this does is that your application does not have to crash (or shutdown) every time you loose connection.
You can still have a thread check for active connections if you want to in conjunction with this.
0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
actually the last idea i had for reconnection was to use a frequency table for reconnecting
every 5 seconds
every 20 seconds after 2 minutes
every minute after 10 minutes

it only made it to the drawing board
so i don't have the code for it

a other thing which was implemented was because we have to reboot every server containing financial info every month (sarbanse-oxley- stuff)
so we send a message to all db apps, the server is going offline, and then a second message when the server is back online



0
 

Author Comment

by:Delphiwizard
Comment Utility
I'm in the process of testing it all. It might take me some time, but I'll be back.
Specially with regard to my current application.
0
 

Author Comment

by:Delphiwizard
Comment Utility
ewangoya:

When I replace TADOStoredProc with the new TADOStoredProcExt, I get some errors when loading the project.
Error because of missing properties:
Parameters
ProcedureName

Also some other properties are missing too (without generating an error:
ConnectionTimeOut
DataSource
EnableBCD
Prepared

Can you correct please this in BPADOExt?
0
 
LVL 32

Assisted Solution

by:ewangoya
ewangoya earned 500 total points
Comment Utility

Change the object its inheriting from

  TADOStoredProcExt = class(TADOStoredProc)
  private
    FCheckConnection: Boolean;
  protected
    procedure SetActive(Value: Boolean); override;
  public
    constructor Create(AOwner: TComponent); override;
    procedure ExecProc;
  published
    property CheckConnection: Boolean read FCheckConnection write FCheckConnection default True;
  end;
0
 

Author Comment

by:Delphiwizard
Comment Utility
ewangoya:
Will it be required to add BPADOExt besides ADODB in the unit clause, or will only adding BPADOExt be sufficient?
0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility

If you drop the components on a form or datamodule, delphi will automatically add ADODB to the uses clause.

If you are just creating a simple non visual object , the its sufficient to add only BPADOExt
0
 

Author Comment

by:Delphiwizard
Comment Utility
ewangoya:
Currently I have about 25 persistent queries.
In the current setup the AfterOpen-event is used for these queries.
There it (re-)opens all persistent queries. This means that whenever one of the persistent queries is (re-)opened all persistent queries will be (re-)opened.
This is great when the connection is lost, but how will this work at application startup?

Can I still uses ADOQuery1.Requery for persistent queries?
0
 

Author Comment

by:Delphiwizard
Comment Utility
Hi,

I created an image for the TADOClientDatasetExt, as that one didn't have a image.
Image for TADOClientDatasetExt
Is there a way to add it to the component palet and show the image when the TADOClientDatasetExt is added to a form or DataModule?
0
 

Author Comment

by:Delphiwizard
Comment Utility
Currently all persistent TADOClientDataset are opened multiple times at program startup.
Of course this is not a desired behaviour.


// All persistent TADOClientDataset have AfterOpen-event set like:
procedure TDM.PersistentQueryAfterOpen(DataSet: TDataSet);
begin
  PersistentQuery.ReOpenDatasets;
end;

Open in new window

// For each persistent TADOClientDataset
// The CommandText is set in BeforeOpen-event, like:
procedure TDM.QGebruikersProfielenBeforeOpen(DataSet: TDataSet);
begin
  with QGebruikersProfielen do
    CommandText := 'SELECT * FROM vwGebruikersProfielen ';
end;

Open in new window

procedure TDM.DataModuleCreate(Sender: TObject);
begin
  RegisterPersistentQuery(QGebruikersProfielen);
  RegisterPersistentQuery(QGebruikers);
  RegisterPersistentQuery(QInstellingen);
  QGebruikersProfielen.Open;
  QGebruikers.Open;
  QInstellingen.Open;
end;

Open in new window

0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility

procedure TDM.PersistentQueryAfterOpen(DataSet: TDataSet);
begin
  PersistentQuery.ReOpenDatasets;
end

If a Dataset is already open then it should not trigger this event. The open method sets the Active property using the SetActive(Value: Boolean)

The method SetActive first checks whether the value being set is different from the current one
if Active <> Value then
....

If the Dataset is already Open, it will not reopen it a second time. Even though the method may be called multiple times, its only effective once

0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility

To add a component image, you need to add it to a resource file with the same name as the component.
Rename the resource file to .dcr and put it in the same folder as the component source

Delphi should automatically pick it up when installing the component
0
 

Author Comment

by:Delphiwizard
Comment Utility
Adding the image to the component is solved now.

"If a Dataset is already open then it should not trigger this event."
It will however trigger the BeforeOpen-event of the persistent tables.

Then I have trouble with TADOClientDataset.
All persistent queries are move to TADOClientDataset. In the BeforeOpen-event it will set the CommandText.
Also I changed the OnCreate-event of the Datamodule.

  QGebruikersProfielen.Open;
  QGebruikers.Open;
  QInstellingen.Open;
The above code was removed from it because the TADOClientDataset's are opened anyway.
procedure TDM.DataModuleCreate(Sender: TObject);
begin
  RegisterPersistentQuery(QGebruikersProfielen);
  RegisterPersistentQuery(QGebruikers);
  RegisterPersistentQuery(QInstellingen);
end;

Open in new window

0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility

Correct, you don't need the RegisterPersistentQuery any more
0
 

Author Comment

by:Delphiwizard
Comment Utility
Hi, I did it the other way around, but you are right. This is taken care of in the AfterOpen-event of the Dataset.

Now I think I only have two questions left.
1.In which situations will it actualy be able to establish a re-connect?
And more important in which situation will it not?
2. What would be a good approach to keep re-connecting after connection is broken (will my code work)?


f.e.:
- computer goes into hypernation (connection to SQL Server is stopped)
- bad internet- / intranetconnection (f.e. wireless)
- networkcard is disabled (or crashed) and alternate card is activated
- SQL Server service is stopped and re-started
- Windows server is restarted (after an update)
0
 

Author Comment

by:Delphiwizard
Comment Utility
procedure TDM.HandleConnectionError(Sender: TADOConnection; const AErrorMessage: string;
  var AAction: TErrorAction);
var Wachttijd, AantalRuns : Integer;
begin
  VerbindingMetDatabaseHersteld   := False;
  VerbindingMetDatabaseHerstellen := True;
  AantalRuns := 0;
  while NOT VerbindingMetDatabaseHersteld AND VerbindingMetDatabaseHerstellen do
  begin
    if TryReconnect(Sender) then
    begin
      VerbindingMetDatabaseHersteld := True;
      FHoofdscherm.ToonPanelVerbindingHerstel(False);
      AAction := eaFixed;
      Exit;
    end;
    // Verbinding is nog niet hersteld.
    // Panel voor herstel verbinding tonen.
    FHoofdscherm.ToonPanelVerbindingHerstel(True);
    AantalRuns := AantalRuns + 1;
    if (AantalRuns <= 5) then
      Wachttijd  := 1000 * AantalRuns;
    // Even wachten voor het starten van een nieuwe poging.
    Sleep(Wachttijd);
  end;
  AAction := eaAbort;      // this will not execute the command
  MyMessageDlg(Format(blcMessageFout
                    , [ConstantP])
              , blcVerbindingDatabaseNietHersteld
              , dkFout, NMV);
  ApplicatieWordtAfgesloten;
  Application.Terminate;
  ExitProcess(0);
end;

function TDM.TryReconnect(AConnection: TADOConnection): Boolean;
begin
  AConnection.Connected := False;
  try
    //you can add more code to check whether the network is broken before trying to reconnect
    //this will reestablish the connection
    AConnection.Connected := True;
    Result := AConnection.Connected;
    if Result then
      PersistentQuery.ReOpenDatasets;
  except
    Result := False;
  end;
end;

Open in new window


If connection can't be re-established at the first try, than a panel is shown on the main form.
Re-connecting will go on till it is re-establshed or whenever a user hits the button on the panel to stop reconnecting, in which case the application is terminated.

procedure TFHoofdscherm.ToonPanelVerbindingHerstel(Toon : Boolean);
begin
  lbVerbindingDatabaseVerbroken.Caption := blcVerbindingDatabaseHerstellen;
  btStopDatabaseReconnect.Caption       := blcStopHerstellenVanDeVerbinding;
  VerbindingMetDatabaseHerstellen       := Toon;
  pnVerbindingDatabaseVerbroken.Visible := Toon;
end;

procedure TFHoofdscherm.btStopDatabaseReconnectClick(Sender: TObject);
begin
  VerbindingMetDatabaseHerstellen := False;
end;

Open in new window

0
 

Author Comment

by:Delphiwizard
Comment Utility
Any feedback on reconnecting to DB untill user stops?
0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility

That sounds fine, the user at least has some feedback of exactly whats happening and can exit whenever they wish.

One of the most frustrating things is not knowing what is going on with an application when its not responding so this is a really good method you have thought of
0
 

Author Closing Comment

by:Delphiwizard
Comment Utility
Once in a while someone goes the distance to help. ewangoya did this by solving my issue.
Thank you very much for you effort and great solution.
All others that have helped and inspired: I thank you too.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
This video discusses moving either the default database or any database to a new volume.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

771 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

12 Experts available now in Live!

Get 1:1 Help Now