How to handle "Error writing data to the connection" ?

[sugi]

Hello,

I am using TIBCQuery(CachedUpdate = true, others properties = default) and data aware components in C++ Builder(TDBEdit, TDBGrid, etc) to capture data input from users. The connection is via internet to the remote database.

When users input data in TDBEdit/TDBGrid, and then clicked Save button (in code, this clicked call ApplyUpdates method), sometimes internet connection became so slow and unstable, that caused application to raised an error : Error writing data to the connection

Internet connection was not broken, just became slow and unstable. When Internet connection became normal again, users could continue to work without need to quit appliction.

But there is a problem, when that error occurred, TDBEdit / TDBGrid became blank, all inputted data gone.

Is there any properties in TIBCQuery to make all data still exists after that error?
Or perhaps some tips, so that users don't have to re-input data again?

PS : C++ Builder 6, latest ver of IBDAC & Windows 7 64.

Thanks in advance.

[ViktorV]

To solve the issue, you can try using TIBCConnection.OnConnectionLost:
In order to enable the OnConnectionLost event handler, set the TIBCConnection.Options.LocalFailover property to True. Note, that to use the OnConnectionLost event handler, you should add the MemData unit to the USES section of your unit. Below is a sample of using OnConnectionLost:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
begin
  IBCConnection.Options.LocalFailover := True;
  IBCConnection.Open;
end;

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

In this case, on connection lost, IBDAC will attempt to reconnect and rerun the failed operation. Reed more in IBDAC documentation: https://www.devart.com/ibdac/docs/Devar ... onLost.htm

[sugi]

Setting LocalFailover to true make user's inputted data not disappeared.

As for OnConnectionLost event, seem didn't have an effect.

There are something strange here, this was happen without using OnConnectionLost event, only use LocalFailover.
1) When connection lost & users clicked Save button, on first clicked, error raised. Users must click again Save button & this time, if connection restored, application runs OK.
2) After successful operation on point 1, users edit again that data & then clicked again Save button on the second time. This time if connection lost, no Error raised, application became not responding, it seem tried to reconnect & if during this wait, connection restored, application runs OK.

My question are :
1) why there are different behavior ? how to make the second one a default behavior ?
2) what is the use of OnConnectionLost event?

[ViktorV]

The OnConnectionLost event occurs only when the following conditions are fulfilled:
- a fatal error occurs (one of the following: network_error, lost_db_connection, conn_lost, isc_net_read_err, isc_net_write_err, isc_conn_shutdown_err, isc_db_shutdown_err);
- there are no opened transactions in a connection that are not ReadOnlyReadCommitted (if connection has at least one opened transaction, which is not ReadCommitedReadOnly, FailOver does not execute. All ReadCommitedReadOnly transaction are restored with FailOver operation);
- there are no opened and non-fetched datasets;
- there are no explicitly prepared datasets or SQLs.
Please make sure that none of the conditions above is violated.
For more information, please read the IBDAC documentation: https://www.devart.com/ibdac/docs/index ... etwork.htm

[sugi]

I made a simple project. The project only using TIBCConnection, TIBCTransaction, & TIBCQuery.

TIBCConnection set to LocalFailover = true, other properties = default.
TIBCTransaction isolationlebel = iblReadCommitted, other properties = default.

Here are the codes

Code: Select all

//---------------------------------------------------------------------------

#include <vcl.h>
#pragma hdrstop

#include "Main.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma link "DBAccess"
#pragma link "IBC"
#pragma link "MemDS"
#pragma resource "*.dfm"
TForm1 *Form1;
//---------------------------------------------------------------------------
__fastcall TForm1::TForm1(TComponent* Owner)
   : TForm(Owner)
{
   ConnDba->Close();
   ConnDba->Open();
}
//---------------------------------------------------------------------------
void __fastcall TForm1::Button1Click(TObject *Sender)
{
   try
   {
      Q->Close();
      Q->Open();
      ShowMessage("OK");
   }
   catch (Exception &exception)
   {
       ShowMessage("no internet connection!");
   }

}
//---------------------------------------------------------------------------
void __fastcall TForm1::ConnDbaConnectionLost(TObject *Sender,
      TComponent *Component, TConnLostCause ConnLostCause,
      TRetryMode &RetryMode)
{
   ShowMessage(ConnLostCause);
   RetryMode = rmRaise;
}
//---------------------------------------------------------------------------

Tested like these :
1) Run app
2) After Main form show up, disconnect Internet connection, then button clicked, ConnectionLost event triggered, ConnLostCause value showed 6, and and exception "no internet connection!" raised.
3) Clicked again the button, this time ConnectionLost event not triggered, app only show "no internet connection!"

On those test, ConnectionLost event only triggered once. If there are another connection lost, this event won't be triggered. Have tested with RetryMode to rmReconnect or rmReconnectExecute, the result are the same, it was only triggered once.

Another test :
1) Run app
2) After Main form show up, clicked the button, message showed "OK"
3) Disconnect Internet, clicked the button, message showed "no internet connection!", ConnectionLost event not triggered.

On my understanding, those 2 test violated network_error/lost_db_connection, but why ConnectionLost event not always triggered? Am I missing something ?

[ViktorV]

When setting the RetryMode variable to the rmRaise value the OnConnectionLost event occurs only once. Set the IsolationLevel property to the iblReadOnlyReadCommitted value for all transactions.
If this does not help to solve the issue, please compose a small sample demonstrating the issue and send it to us via form e-support: https://www.devart.com/company/contactform.html

[sugi]

Set IsolationLevel property to the iblReadOnlyReadCommitted value, solved the issue, but another issue occurred.

Set RetryMode = rmReconnectExecute, will make App runs indefinitely until Internet connection available. So to prevent this, I made these codes

Code: Select all

void __fastcall TForm1::ConnDbaConnectionLost(TObject *Sender,
      TComponent *Component, TConnLostCause ConnLostCause,
      TRetryMode &RetryMode)
{
   static short err;
   err++;
   lblerr->Caption = err;
   Application->ProcessMessages();

   if(err > 10)
   {
      err = 0;
      RetryMode = rmRaise;
   }
   else RetryMode = rmReconnectExecute;
}
//---------------------------------------------------------------------------

With those codes, when Internet connection lost, app will only try 10x to resolve the issue, if it fail after 10x, app gives back control to users to decide what will do.

Those codes work well while rmRaise not triggered.
Once rmRaise triggered, every time Internet connection lost again, ConnectionLost event never be triggered again.

[ViktorV]

This is correct behavior of our components when setting the RetryMode variable to the rmRaise value the error message will appear and the OnConnectionLost event will not occur anymore until you do not connect to the server again.

[sugi]

For now, I will only use rmRaise.

I have to tell you that ConnectionLost & LocalFailover works as expected on the sample above or on a simple project, but I have a large project that these (ConnectionLost & LocalFailover) didn't worked as expected.

The behavior is erratic, and unpredictable and the most wort part is, Error couldn't be catch. I have wrote codes between try and catch, but when error occurred, codes on catch block never executed.

When connection lost, message "Error writing data to the connection" always appeared though I have replace all error message in catch blocked. And when this error showed, some functions of the app could still worked after connection restored, others couldn't.

I build these application not from the fresh, still mix with IBDatasets component from Borland, so perhaps these erratic and unpredictable behavior have nothing to do with ConnectionLost & LocalFailover things at all, still trying to find out what are wrong.

[ViktorV]

Unfortunately, we cannot reproduce the issue. Maybe, its cause lies in using third-party tools. To understand the issue cause , we need a test sample, without using third-party components, in which the issue is stably reproduced. As soon as we get such a sample and if the cause of the issue is in our product code, we will try to fix it in the shortest time.

[sugi]

I found a few cause of errors, it was from TIBDataset from Borland, not from your vcl.

I hope though you could upgrade the behavior of ConnectionLost event regarding rmRaise. Making ConnectionLost event only call once when rmRaise issued, makes others RetryMode kind of useless.

Like I wrote before, rmReconnectExecute will cause app runs indefinitely until connection problems solved. This will look to users as app is hanging. Combining rmReconnectExecute & rmRaise will make app able to returns control to users when abortive operation fail to reexecuted after a few times attempt.

Now, I only use rmRaise & combine with Timer to mimic rmReconnectExecute operation.

[ViktorV]

As we have already written, this is correct behavior of our components when setting the RetryMode variable to the rmRaise value there will an error message and the OnConnectionLost event won't occur any more until you connect to the server again. When setting the RetryMode variable to the rmReconnectExecute value there will be attempts of reconnecting until the connection is established or user stops the attempts by setting RetryMode to the rmRaise value. You found the right issue solution in your particular case.

[sugi]

Another question please. Here is the situation :
1) Run app & after successfully connect to the remote database, do nothing
2) Simulate unstable internet connection by disconnect from the network & then connect again
3) click button to close & open the query to the remote server

Operation no 3 performs under connected internet, but app raised an error as if there is no internet connection. The second click, app runs OK. Why ?

Here is the source codes

Code: Select all

__fastcall TForm1::TForm1(TComponent* Owner)
   : TForm(Owner)
{
   TrsDba->IsolationLevel = iblReadOnlyReadCommitted;
   ConnDba->Pooling = true;
   ConnDba->Options->LocalFailover = true;
   ConnDba->Options->DisconnectedMode = true;
   ConnDba->DefaultTransaction = TrsDba;
   ConnDba->Open();
   Q->Transaction = TrsDba;
   Q->Close();
   Q->Open();
}
//---------------------------------------------------------------------------
void __fastcall TDataM::ConnDbaConnectionLost(TObject *Sender,
      TComponent *Component, TConnLostCause ConnLostCause,
      TRetryMode &RetryMode)
{
   RetryMode = rmRaise;
}
//---------------------------------------------------------------------------
void __fastcall TForm1::Button1Click(TObject *Sender)
{
   try
   {
      Q->Close();
      Q->Open();
   }
   catch (Exception &exception)
   {
      ShowMessage("no internet connection!");
   }
}
//---------------------------------------------------------------------------

[ViktorV]

This behavior is related to the Firebird feature: InterBase / Firebird requires an active transaction for any data operation, even for opening the dataset. Therefore, when calling TIBCQuery.Open, it checks whether the associated transaction is running and, if it is not, it automatically starts. When closing the dataset, the associated transactions are not automatically closed. And when restarting the server, all transactions are reset. If you try to call the opening of the dataset again, an error will be generated. You can see this if you add

Code: Select all

ConnDba->Close();

in your example before the

Code: Select all

Q->Close();

line in the OnClick event handler. In this case, all transactions will be restarted and no error will occur.

[sugi]

You mean like these :

Code: Select all

void __fastcall TForm1::Button1Click(TObject *Sender)
{
   try
   {
      ConnDba->Close();
      Q->Close();
      Q->Open();
   }
   catch (Exception &exception)
   {
      ShowMessage("no internet connection!");
   }
}
//---------------------------------------------------------------------------

No effect, first clicked always showed Error, second clicked and so on, OK.