SQL Server 2008 - Connection error (EOleException) II

I do not use SQL Server (I use other DBs) anyway I can give suggestions:

Did you try to create a separate application that can run in the server which communicates with the SQL Server to keep it not to go in the idle state?

Or, you can do that from the clients by executing a do-nothing transaction on Timer counts just for keeping the session with the database alive? (i.e. You will be having a Timer with a proper interval, which has to be less than the idle time-out of the SQL Server, and whenever a transaction is performed the Timer's counter will be reset to start counting until another transaction is called or it executes keep-Alive transaction).

I am not sure if this can be handled from the Configuration Manager of the SQL Server but it should be there as an option.

You can try the following code.
You can install the components on your tab, just add a register procedure

Also you can add similar code to TADOStoredProc and replace the ExecProc procedure

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 False;
    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 False;
    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 False;
    property OnConnectionError: TOnConnectionError read FOnConnectionError write FOnConnectionError;
    property RowsAffected: Integer read FLocalRowsAffected;
  end;

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;

Select allOpen in new window

ewangoya:
Can you please explain how I can use this?
By the way. I also use EurekaLog which is handling the errors. Does this solution mandle the error before EurekaLog is handling it?
And I do need to handle it for TADOStoredProc  too.

And isn't it possible to add this directly to the TADOQuery etc.
Because now I probably need to change all TADO*** components in my application. There are quite some.

And last but not least.
Wouldn't it be easier the handle this at TADOConnection instead of the underlying components? Or is this not possible?

I looked at the ADOConnection object and its almost impossible to handle it in there though it would be the best place.
There is a procedure CheckActive but its neither virtual nor protected and is not called in all cases furthermore TADOConnection is a Member of TADOCommand and not TADOQuery or TADOTable

//Adding directly to TADOQuery.
Yes its possible to do so, the only problem is if you upgrade delphi then all that code is overwriten

You can also set the default property for CheckConnection to true
ie property CheckConnection: Boolean read FCheckConnection write FCheckConnection default True

To use it, create a procedure to handle the error in your base datamodule
eg

procedure TBaseDataModule.HandleConnectionError(Sender: TObject;
  AConnection: TADOConnection; const AErrorMessage: string;
  var AAction: TErrorAction);
begin
  //You can show the user a message here
  ShowMessage('Could not re-establish connection to serve, Application will close');
  AAction := eaAbort;      //this will not execute the command
  PostMessage(Application.MainForm.Handle, WM_CLOSE, 0, 0);  //end application
  //Application.Terminate;

  //since you pass the connection here and receive the error message as well,
  //you can decide to try and reconnect again, then set AAction to eaIgnore if it is successful
end;

Then set the OnConnectionError event to this procedure
it should be available in the eventlist once you include the datamodule to your form or you can just set it by code
ADOQuery1.OnConnectionError := DataModule.HandleConnectionError;

{ 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;

Select allOpen in new window

TADOStoredProcExt = class(TCustomADODataSet)
  private
    FCheckConnection: Boolean;
    FOnConnectionError: TOnConnectionError;
  protected
    procedure SetActive(Value: Boolean); override;
  public
    procedure ExecProc;
  end;

Select allOpen in new window

ewangoya:
Thank you for your code, but I still don't know how to include it in my application.
In my application I have a DataModule with about 100 TADOQuery and StoredProc components. Also in some forms in the application I have added some TADOQuery and StoredProc directly.
Maybe for me it's best to add these to the components palet. Can you show me how to register?

Where do I put your code? In a separate unit or in the DataModule or somewhere else?

Adding code procedure TBaseDataModule.HandleConnectionError to my own DataModule gives an error
"Undeclared identifier TErrorAction". Should I declare the types in my DM as well?
Like:

type
  TErrorAction = (eaIgnore, eaAbort, eaRaiseException);

Select allOpen in new window

Also I do want to have CheckConnection set to True by default.

Maybe anybody else can explain how to actually use the proposed solution. See also my previous post.

ewangoya:
Shouldn't the TADOStoredProcExt also have published properties like below?

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;

Select allOpen in new window

Below is the unit for the adjusted TADO components.
Can anybody check if this is correct so it will do as requested?

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.

Select allOpen in new window

When I add these components to the componentpalet and add TADOQueryExt to a form, then property CheckConnection is always False.
Also property RowsAffected is grayed and can't be changed.

Also:
When I try to assign procedure DM..HandleConnectionError to a TADOQueryExt, then it gives an error.
"DM.HandleConnectionError is not a valid identifier"

The above error does only occure when the TADOQueryExt is placed on an other form then Datamodule "DM"

"Include your datamodule to the forms that use the Extended components, the HandleConnectionError will then be visible to those units"
This is not the case.

How should it be declared in the Datamodule?
Currently it's declared at the top of the datamodule, where all other events are declared as well.

type
  TDM = class(TDataModule)
    ADOConnection: TADOConnection;
    QADOQueryExt1: TADOQueryExt;
    procedure DataModuleCreate(Sender: TObject);
    procedure HandleConnectionError(Sender: TObject; AConnection: TADOConnection; const AErrorMessage: string; var AAction: TErrorAction);

Select allOpen in new window

Let's move the rest of the discussion to the newly created question:
https://www.experts-exchange.com/questions/26839092/Check-component-adjustment-TADO.html?anchorAnswerId=34958146#a34958146