How to disable AutoConnect and AutoReconnect on a connection

[horsi]

I want to manually handle connecting/disconnecting to/from database.
I don't want that "TUniQuery.Open/Execute" automatically opens the connection (the same applies to others TUni components).

I searched through included help and all UniDAC source files (our company have site license to UniDAC 3.00.0.11 with source) and I didn't find any option(s) to configure TUniConnection object in such way that works the way I want.

Could I ask You for some clue/help, what to do to achieve desired effect?

[AlexP]

Hello,

One of the ways is to check the UniConnection.Connected property each time before opening/executing a dataset.

Please specify the task in more details and I'll try to give you a more detailed answer.

[horsi]

Please specify the task in more details and I'll try to give you a more detailed answer.

Short preamble:
Our application works with Oracle database only.
The nature of our application (*not* dedicated for mobile devices!) is that it needs connection to database (single Oracle session). It cannot work without it.
At startup, immediately after connect to database, we manually set (initialize) some variables in *core* packages on db.
These variables are intensively used in all sources (PL/SQL) on database and are the basis for proper operation of the entire application.

In normal (healthy) situations everything works correctly.
However there are some emergency situations like: session was killed, physical connection was lost. After connection was lost TUniConnection tries to reconnect to the database.
Also, if TUniConnection.Connected = False, opening a query (or executing a stored proc) reconnects to database as well.
You probably say, that program will work, and all queries will works too. Yes.
But the rest of our whole database part - PL/SQL procedures - will NOT (because, new Oracle session = variables in our *core* packages are not initialized)!

So, the best solution for us is to raise exception before TUniQuery.Open, TUniQuery.Execute, etc. when TUniConnection.Connected = False.

We don't want to modify our source code adding extra check of TUniConnection.Connected before any "Open" or "Execute" methods!
Also we don't want to modify original UniDac's source code to achieve desired effect.

In all our source code we use:
TAppConnection = class(TUniConnection)
TAppQuery = class(TUniQuery)
TAppStoredProc = class(TUniStoredProc)

I did some investigation and I found that:

1) covering TUniConnection.IsFailOverAllowed method, successfully blocks "AutoReconnect" after connection was lost (@see TCustomDAConnection.DoError).

Code: Select all

function TAppConnection.IsFailOverAllowed: Boolean; override;
begin
  Result := False
end {TAppConnection.IsFailOverAllowed};

2) covering TUniQuery.BeginConnection/TUniStoredProc.BeginConnection method, successfully blocks auto-connecting to database

Code: Select all

procedure TAppQuery.BeginConnection(NoConnectCheck: Boolean); override;
begin
  if Assigned(Connection) and (not Connection.Connected) then
    Raise Exception.CreateRes(@DAConsts.SConnectionNotConnected);
  inherited
end {TAppQuery.BeginConnection};

procedure TAppStoredProc.BeginConnection(NoConnectCheck: Boolean); override;
begin
  if Assigned(Connection) and (not Connection.Connected) then
    Raise Exception.CreateRes(@DAConsts.SConnectionNotConnected);
  inherited
end {TAppStoredProc.BeginConnection};

But this is a half-solution.

Summarize, as You can see, we miss a lot some properties of TUniConnection like:
TUniConnection.AutoReconnect: Boolean;
TUniConnection.AutoConnect: Boolean;

Is my way of thinking is good, and these changes (related with disabling AutoReconnect/AutoConnect to database) correct?
Are there any chances to add these properties in the near future to UniDac components (we believe that this would be the best solution)?

[AlexP]

Hello,

We will investigate the possibility of adding the AutoConnect property in one of the next builds/versions of DAC.
Now you can use the OnConnectionLost event not to reconnect when the connection is lost like

procedure TForm1.UniConnection1ConnectionLost(Sender: TObject;
Component: TComponent; ConnLostCause: TConnLostCause;
var RetryMode: TRetryMode);
begin
RetryMode:= rmRaise;
end;

Also you can use the OnAfterConnect event to set your global Oracle variables necessarily after connect.

[horsi]

We will investigate the possibility of adding the AutoConnect property in one of the next builds/versions of DAC.

I will be grateful.

Now you can use the OnConnectionLost event not to reconnect when the connection is lost like

procedure TForm1.UniConnection1ConnectionLost(Sender: TObject;
Component: TComponent; ConnLostCause: TConnLostCause;
var RetryMode: TRetryMode);
begin
RetryMode:= rmRaise;
end;.

This is not possible, because this event is triggered *ONLY* if Options.LocalFailover is True (@see TCustomDAConnection.DoError).

Also you can use the OnAfterConnect event to set your global Oracle variables necessarily after connect.

In our case, handling this event to initializing database variables would require a redesign of the application.
In a previous post I gave just a simplified model. In real, everything is much more complicated.

Thank You for the answer.

[AlexP]

Hello,

We will try to add this feature as soon as possible.

[horsi]

Hello,

We will try to add this feature as soon as possible.

The latest build (3.60.0.15) doesn't contain this modification. We look forward to the next builds

Can you determine the approximate date of implementation?
Thanks!

[AlexP]

Hello,

This feature will not be added in the next version, maybe it will be added in the next nearest version.

[DanielH]

Hello,

I have a problem with the OnConnectionLost handler.

PROBLEM:
I get a "Lost Connection" Exception, but the OnConnectionLost Handler where not called!

What I use:
- Embarcadero RAD Studio XE with C++
- UniDAC 3.70.0.17

What I made:
1) needed Parameter are set:
qu->Options->LocalMasterDetail = true;
qu->CachedUpdates = true;
qu->Connection->Options->LocalFailover = true;
qu->Connection->Options->DisconnectedMode = true;
qu->Connection->OnConnectionLost = PK21NeuConnetionConnectionLost;

2) MemData Class included (#inlude )

3) OnConnectionLost Handler are defined:
void __fastcall TDM_UniSQL::PK21NeuConnetionConnectionLost
(TObject *Sender, TComponent *Component,
TConnLostCause ConnLostCause, TRetryMode &RetryMode)
{
switch(ConnLostCause)
{
//Connection loss detected during DataSet.ApplyUpdates
case clApply:
// Connection loss detected during Connection.ApplyUpdates
case clConnectionApply:
// Connection loss detected during query opening
case clRefresh:
// Connection loss detected during service information request
case clServiceQuery:
// Connection loss detected during transaction start
case clTransStart :
RetryMode = rmReconnectExecute;
break;
......
}

}

What is missing or wrong?

Thanks

[AlexP]

Hello,

I cannot reproduce the problem.
The OnConnectionLost event is called correctly on connection break.
I checked this behaviour on the following sample:

Code: Select all

void __fastcall TForm3::FormCreate(TObject *Sender)
{
UniConnection1->Connect();
UniQuery = new TUniQuery(NULL);
UniQuery->Connection = UniConnection1;
UniQuery->Options->LocalMasterDetail = true;
UniQuery->CachedUpdates = true;
UniQuery->Connection->Options->LocalFailover = true;
UniQuery->Connection->Options->DisconnectedMode = true;
UniQuery->Connection->OnConnectionLost = ConnectionLost;
UniQuery->SQL->Text = "DECLARE i NUMBER; BEGIN FOR i IN 1..1000000 LOOP EXECUTE IMMEDIATE 'SELECT * FROM DUAL'; END LOOP; END; ";
}

void __fastcall TForm3::ConnectionLost(TObject *Sender, TComponent *Component,
		  TConnLostCause ConnLostCause, TRetryMode &RetryMode)
{
	switch(ConnLostCause)
	{
		//Connection loss detected during DataSet.ApplyUpdates
		case clApply:
		// Connection loss detected during Connection.ApplyUpdates
		case clConnectionApply:
		// Connection loss detected during query opening
		case clRefresh:
		// Connection loss detected during service information request
		case clServiceQuery:
		// Connection loss detected during transaction start
		case clTransStart :
			RetryMode = rmReconnectExecute;
		case clExecute:
			RetryMode = rmReconnectExecute;
		break;
	}
}

void __fastcall TForm3::Button1Click(TObject *Sender)
{
	UniQuery->ExecSQL();
}

After clicking Button1 during large cycle execution I killed session, and the ConnectionLost event worked successfully.
Please execute this code, and, if it works, modify it so that the problem is reproduced.