// Code from Borland CodeCenter
unit code_fragments;
(*
This unit contains only fragments of code,
to clarify idea - how to make the Oracle session self reconnecting.

Lost connection - situation when server was shut down
or cable was pulled out from network card etc.
*)

interface

uses
{ RTL }
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs, Db,
{ Direct Oracle Access }
Oracle, OracleData;

type
{ TOracleSessionState }
TOracleSessionState = (
osNone, // I don't use it in the real world, but keep for purity
osNormal, // Connection is OK
osReconnect, // Connection was lost, try to restore it repeatedly
osIsolation // No connection, don't even try to restore it
);

{ TMyOracleSession }
TMyOracleSession = class (TOracleSession)
private
FState : TOracleSessionState; // Current state of session
FLastReconnect : TDateTime; // When the last reconnection attempt took place
public
constructor Create(AOwner: TComponent); override;
property State : TOracleSessionState
read FState write FState;
property LastReconnect: TDateTime
read FLastReconnect write FLastReconnect;
end;

{ TMyOracleQuery }
TMyOracleQuery = class (TOracleQuery)
private
FStatePermitted : TOracleSessionState; //At what state of session this
//query could be executed
public
constructor Create(AOwner: TComponent); override;
function Execute: Boolean;
property StatePermitted : TOracleSessionState
read FStatePermitted write FStatePermitted;
end;

{ TMyDM - My Data Module. Only fragments here }
TMyDM = class(TDataModule)
{ skipped }
private
FState: TOracleSessionState ; //Same values as for Oracle Session
FSessionOne : TCPOOracleSession;
FSessionTwo : TCPOOracleSession;
FQuery : TMyOracleQuery;
{ skipped }
procedure SetState(const Value: TOracleSessionState);
{ skipped }
public
{ skipped }
property State: TOracleSessionState
read FState write SetState;
end;

var
MyDM: TMyDM;

implementation
uses
math;
const
ReconnectDelay = 5/1440; // minimum reconnection delay is 5 minutes

{ TMyOracleSession }
{------------------------------------------------------------------------------}
constructor TMyOracleSession.Create(AOwner : TComponent);
begin
inherited Create(Aowner);
FState := osNormal;
FLastReconnect := now;
end;

{ TMyOracleQuery }
{------------------------------------------------------------------------------}
constructor TMyOracleQuery.Create(AOwner: TComponent);
begin
inherited Create(AOwner);
FStatePermitted := osNormal;
end;
{------------------------------------------------------------------------------}
function TMyOracleQuery.Execute: Boolean;
var
CheckResult : TCheckConnectionResult;
TempSession : TMyOracleSession;
ReconnectFlag : boolean;
begin
Result:= false; //failed
with Self.Session as TMyOracleSession do
try
//Set reconnection flag
ReconnectFlag := (now - LastReconnect) > ReconnectDelay;
if ReconnectFlag then
LastReconnect := now;

//Check the state of connection for normal and reconnection mode
//skip it for isolation and none
//Try to restore it only if Reconnect Property is set to true
if (not Connected) and ReconnectFlag then
LogOn;

//Then check connection
if Connected then
begin
//correlation with MyDM.State - only if no reconnection
if MyDM.State <> osReconnect then
State := MyDM.State;

// check and maybe reestablish connection
if (State in [osNormal, osReconnect]) and ReconnectFlag then
begin
CheckResult := CheckConnection (true);
if CheckResult = ccError then
State := osReconnect
else
State := osNormal;
end;

//Execute something only if it is permitted
if (State <= StatePermitted) then
begin
inherited Execute;
Result := true ;
end;
end;
except
on E:Exception do
begin
State := osReconnect;
//Call to some error documentation procedure
LogError('Query :' + Copy(Self.SQL.Text,1,30)+ E.Message);
end;
end; {try}
end;

{ TMyDM }
{------------------------------------------------------------------------------}
procedure TMyDM.SetState(const Value: TOracleSessionState);
var
lMode : Byte;
begin
if FState <> Value then
begin
FState := Value;
{ Call to procedures which update state indication controls}
end;

{------------------------------------------------------------------------------}
procedure TMyDM.SyncTimerTimer(Sender: TObject);
var
i : integer;
DoUpdate : boolean;
begin
{ skipped }
//check the state
i := max(Ord(FSessionOne.State),Ord(FSessionTwo.State));
case i of
0,1: State := osNormal;
2: if State <> osIsolation then State := osReconnect;
3: State := osIsolation;
end;
end;
{------------------------------------------------------------------------------}
end.