Stef Merlijn
asked on
SQL Server 2008 - Connection error (EOleException) II
Hi,
This question is a follow up on my previous question:
https://www.experts-exchange.com/questions/26502495/SQL-Server-2008-Connection-error-EOleException.html
Some of my customers still get once in a while an errormessage that the connection to the database server is lost. The given solution on my previous post to check the connection in a thread doesn't seem to work in all cases, so I'm looking for a new solution, or updated one.
Is there a way to add some properties, methods or events to TADOQuery and/or TADOConnection?
The moment a request or update is done this will then check if the connection is still valid and, if not, it tries to restart the connection. If it fails to reconnect, an error is shown and application needs to shut down.
Any other ideas are very welcome too.
This question is a follow up on my previous question:
https://www.experts-exchange.com/questions/26502495/SQL-Server-2008-Connection-error-EOleException.html
Some of my customers still get once in a while an errormessage that the connection to the database server is lost. The given solution on my previous post to check the connection in a thread doesn't seem to work in all cases, so I'm looking for a new solution, or updated one.
Is there a way to add some properties, methods or events to TADOQuery and/or TADOConnection?
The moment a request or update is done this will then check if the connection is still valid and, if not, it tries to restart the connection. If it fails to reconnect, an error is shown and application needs to shut down.
Any other ideas are very welcome too.
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
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;
ASKER
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.
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.
ASKER
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.
Because now I probably need to change all TADO*** components in my application. There are quite some.
ASKER
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?
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.HandleConn
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.Ma
//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.OnConnectionErro
{ 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;
TADOStoredProcExt = class(TCustomADODataSet)
private
FCheckConnection: Boolean;
FOnConnectionError: TOnConnectionError;
protected
procedure SetActive(Value: Boolean); override;
public
procedure ExecProc;
end;
ASKER
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.HandleConn ectionErro r to my own DataModule gives an error
"Undeclared identifier TErrorAction". Should I declare the types in my DM as well?
Like:
Also I do want to have CheckConnection set to True by default.
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.HandleConn
"Undeclared identifier TErrorAction". Should I declare the types in my DM as well?
Like:
type
TErrorAction = (eaIgnore, eaAbort, eaRaiseException);
Also I do want to have CheckConnection set to True by default.
ASKER
Maybe anybody else can explain how to actually use the proposed solution. See also my previous post.
ASKER
ewangoya:
Shouldn't the TADOStoredProcExt also have published properties like below?
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;
ASKER
Below is the unit for the adjusted TADO components.
Can anybody check if this is correct so it will do as requested?
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.
ASKER
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 property RowsAffected is grayed and can't be changed.
ASKER
Also:
When I try to assign procedure DM..HandleConnectionError to a TADOQueryExt, then it gives an error.
"DM.HandleConnectionError is not a valid identifier"
When I try to assign procedure DM..HandleConnectionError to a TADOQueryExt, then it gives an error.
"DM.HandleConnectionError is not a valid identifier"
ASKER
The above error does only occure when the TADOQueryExt is placed on an other form then Datamodule "DM"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
"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.
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);
ASKER
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
https://www.experts-exchange.com/questions/26839092/Check-component-adjustment-TADO.html?anchorAnswerId=34958146#a34958146
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.