Recent

Author Topic: IBX : Can it handle database operation when network connection is not stable?  (Read 25300 times)

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Not all data needs to be replicated, only the data that are being input / edit by users.
That's the core-definition of Replication.
Only Data which has been
1) Added
2) Changed
3) Deleted
gets (its statement) replicated. No change=No Replication

And Mark described it perfectly
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

tonyw

  • Sr. Member
  • ****
  • Posts: 319
    • MWA Software
Hi guys,

I have this situations :
  • Use IBX for database operations
  • Use TIBDataset with CacheUpdate Enabled

I have an TDBEdit, with value = 'abc', then edit its value to 'abcde'

To Update database, I clicked Save button, which codes something like this
Code: Pascal  [Select][+][-]
  1. procedure TForm1.btnSaveClick(Sender: TObject);
  2. var
  3.   Tbl : array [1..1] of TDataSet;
  4.  
  5. begin
  6.   Tbl[1] := Dt;
  7.   Dba.ApplyUpdates( Tbl);
  8.   ShowMessage('saved');
  9. end;
  10.  

That code works OK when network connection is OK, database value updated to 'abcde', but when network connection is lost, Dialog box showed with error message and option to click OK or Abort.
Error reading data from connection

That error was expected, if users click Abort, app closed, if user click OK, app not closed, value of TDBEdit in the screen still 'abcde'.

Problem is, when network connection restored again while app still open/ not restarted again, clicked again button Save still got same error like above.

Is IBX able to save data to the data  when network connection is restore again without need to restart the app?

IBX depends on the Firebird Client library as does any Firebird database client. This does not have any explicit code to recover from lost connections. When a connection is lost, the Firebird server will put the current transaction into limbo which, in theory, could allow a database adminstrator to recover any "lost" data. However, that is not always an easy procedure.

In the case of an unstable connection, your strategies are:

1. In your client systems firebird.conf file set DummyPacketInterval=0. This will avoid timeouts on inactive connections when the underlying network is unstable. However, this will not help drop outs during an update. Could be problematic.

2. Don't use ApplyUpdates or cached updates in general. All this does it make it more likely that you will lose all your updates when a connection fails. Instead, set each dataset's AutoCommit property to acCommitRetaining. This will ensure that every update is written through to the database i.e. minimising the risk of data loss.

3. Add an event handler to your TIBDatabase component to automatically re-open the database after an unexpected disconnection. You will probably want to use Application.QueueAsyncCall in the event handler to queue the re-open to ensure a tidy-up before re-opening. Your re-open procedure should re-open all previously open datasets. They should then open in the state they were in after the last autocommit.

incendio

  • Sr. Member
  • ****
  • Posts: 269

1. In your client systems firebird.conf file set DummyPacketInterval=0. This will avoid timeouts on inactive connections when the underlying network is unstable. However, this will not help drop outs during an update. Could be problematic.

2. Don't use ApplyUpdates or cached updates in general. All this does it make it more likely that you will lose all your updates when a connection fails. Instead, set each dataset's AutoCommit property to acCommitRetaining. This will ensure that every update is written through to the database i.e. minimising the risk of data loss.

3. Add an event handler to your TIBDatabase component to automatically re-open the database after an unexpected disconnection. You will probably want to use Application.QueueAsyncCall in the event handler to queue the re-open to ensure a tidy-up before re-opening. Your re-open procedure should re-open all previously open datasets. They should then open in the state they were in after the last autocommit.

1. Usually, I don't install firebird in client, only supplied my app + fbclient.dll to client.

2. Back in C++ Builder, always use ApplyUpdates. If I use dataset's AutoCommit, in Master Detail table, wouldn't it be problems when only data in Master table saved, while data in Detail not saved due to some errors?

3. I am able re-open the database with this code
Code: Pascal  [Select][+][-]
  1. procedure TForm1.OpenDt();
  2. begin
  3.   Dba.Close(true);
  4.   Dba.Open;
  5.   Trs.Active:=true;
  6.   Dt.Open;
  7.  
  8.   if (Dba.Connected) then ShowMessage('data opened');
  9. end;
  10.  
  11.  

Dba must be closed first (which means lost all data entry/edit from users), otherwise, when tried to saved again, same error about reading connection occur again.

MarkMLl

  • Hero Member
  • *****
  • Posts: 6676
1. Usually, I don't install firebird in client, only supplied my app + fbclient.dll to client.

I think he means the "client software", i.e. the libraries etc. which are installed on any computer which wants to talk to the server.

Going back to the original question, any unique or sequential columns will obviously need to be handled specially since the value to be stored won't be known until on the actual server.

MarkMLl
« Last Edit: November 25, 2021, 09:29:52 am by MarkMLl »
MT+86 & Turbo Pascal v1 on CCP/M-86, multitasking with LAN & graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

tonyw

  • Sr. Member
  • ****
  • Posts: 319
    • MWA Software

1. Usually, I don't install firebird in client, only supplied my app + fbclient.dll to client.

2. Back in C++ Builder, always use ApplyUpdates. If I use dataset's AutoCommit, in Master Detail table, wouldn't it be problems when only data in Master table saved, while data in Detail not saved due to some errors?

3. I am able re-open the database with this code
Code: Pascal  [Select][+][-]
  1. procedure TForm1.OpenDt();
  2. begin
  3.   Dba.Close(true);
  4.   Dba.Open;
  5.   Trs.Active:=true;
  6.   Dt.Open;
  7.  
  8.   if (Dba.Connected) then ShowMessage('data opened');
  9. end;
  10.  
  11.  

Dba must be closed first (which means lost all data entry/edit from users), otherwise, when tried to saved again, same error about reading connection occur again.

The main issue with Master/Detail is that you usually need to make sure that changes to master and detail tables are posted in the correct order to avoid foreign key constraints being violated. If you are using master/detail then yes, you do usually want to post the changes in the correct order and then commit them all at once. ApplyUpdates does not help you here any more than a standard Post. ApplyUpdates is used with cached updates and can be more problematic with Master/Detail than using non-cached updates. This is because it is very easy to get the update order wrong if you are mixing insert and delete actions on the master record and using cached updates on the detail.

I would advise using proper transaction control instead of cached updates. When you have an unstable connection, you want to commit as often as makes sense for your application as that minimises the risk of a transaction being lost or ending up in limbo. With a simple one table at a time approach, AutoCommit works. With Master/Detail then you have to be cleverer.

If you are reacting to an exception that reports the loss of a connection then yes, you should explicitly close the database "IBConnection.Active := false;" before re-opening and telling your user that the last transaction may have been lost.

incendio

  • Sr. Member
  • ****
  • Posts: 269
Thanks to all who gave me all info/hints regarding this topic.

I think we can agree that, there are no build in features in IBX to resume database operations (insert/update/delete) on an unstable network.

Build in -> no settings in properties/ events to create to automatically do this.

Developer must implement his/her own method to handle this situation.

I will try to test methods that is simple and easy to implement. Perhaps, I will open a new topic to discuss this.


MarkMLl

  • Hero Member
  • *****
  • Posts: 6676
I think we can agree that, there are no build in features in IBX to resume database operations (insert/update/delete) on an unstable network.

Ditto in Lazarus/LCL/FPC itself :-/

The one thing I would suggest is that before you put too much effort into reinventing the wheel you investigate whether any other database backend has something in its client libraries which does this.

Apart from that a generalised discussion as to how to do it would probably be of interest to quite a lot of people.

MarkMLl
MT+86 & Turbo Pascal v1 on CCP/M-86, multitasking with LAN & graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

incendio

  • Sr. Member
  • ****
  • Posts: 269

The one thing I would suggest is that before you put too much effort into reinventing the wheel you investigate whether any other database backend has something in its client libraries which does this.

I don't know about other databases, only playing with Firebird.

I think, it is not because of Firebird.

I tried JDBC with Firebird, unstable network is not a problem. With JDBC, when connection is broken, it will throw an error, but as soon as connection restored again, it can resume inser/edit /update data normally without a need to close and reopen connection again.

Also tried LCL from DevArt, only need to set three properties on the connection component to solve this problem.

MarkMLl

  • Hero Member
  • *****
  • Posts: 6676
Also tried LCL from DevArt, only need to set three properties on the connection component to solve this problem.

More detail please.

However note that anything at the client end is (probably) only a partial fix: a broken connection will still almost certainly abort a transaction irrespective of server type.

MarkMLl
MT+86 & Turbo Pascal v1 on CCP/M-86, multitasking with LAN & graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

incendio

  • Sr. Member
  • ****
  • Posts: 269
Also tried LCL from DevArt, only need to set three properties on the connection component to solve this problem.

More detail please.

However note that anything at the client end is (probably) only a partial fix: a broken connection will still almost certainly abort a transaction irrespective of server type.

MarkMLl

With DevArt's LCL
1. Set 3 properties on connection component
2. Execute ApplyUpdate, if connection not available, it will wait for sometime before throw an error
3. If connection became available again before it throws error, ApplyUpdate automatically becomes successful.
4. If connection still unavailable, it will throw an error, but you can Execute ApplyUpdate again to try again.

There's a trial version of DevArt LCL, you can try it yourself.

MarkMLl

  • Hero Member
  • *****
  • Posts: 6676
There's a trial version of DevArt LCL, you can try it yourself.

Link please. Not something I'm familiar with.

If this is specifically an LCL thing but still uses the underlying FCL/RTL it might be reportable as a fixable bug or at least wishlist item.

MarkMLl
MT+86 & Turbo Pascal v1 on CCP/M-86, multitasking with LAN & graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

incendio

  • Sr. Member
  • ****
  • Posts: 269
There's a trial version of DevArt LCL, you can try it yourself.

Link please. Not something I'm familiar with.

If this is specifically an LCL thing but still uses the underlying FCL/RTL it might be reportable as a fixable bug or at least wishlist item.

MarkMLl

https://www.devart.com/ibdac/download.html

MarkMLl

  • Hero Member
  • *****
  • Posts: 6676
OK, but that is "for Lazarus" rather than "fixes the LCL".

Nobody denies that the disconnect/reconnect problem should be fixable, although transactions might be aborted by an unexpected disconnection.

Also the limited time I can spare to look at the documentation suggests that it doesn't buffer when disconnected... or that only buffers up to the limits of the visual components and then requires a connection before work can continue.

https://www.devart.com/ibdac/IBDAC.pdf (section 4.7, "Working in an unstable network").

At the moment the best way I can see of fixing this in the general case is to use the database-specific LCL components to collect data, copy the data into non-DB components for editing/display, and buffering locally-enetered data using e.g. SQLite subject subject to requirements of sequential fields etc.

MarkMLl
MT+86 & Turbo Pascal v1 on CCP/M-86, multitasking with LAN & graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

incendio

  • Sr. Member
  • ****
  • Posts: 269
OK, but that is "for Lazarus" rather than "fixes the LCL"

MarkMLl
What do you mean? Sorry, I am new to Lazarus, isn't LCL is short for Lazarus Components Library? Is there a different between Lazarus and LCL?


Also the limited time I can spare to look at the documentation suggests that it doesn't buffer when disconnected... or that only buffers up to the limits of the visual components and then requires a connection before work can continue.

https://www.devart.com/ibdac/IBDAC.pdf (section 4.7, "Working in an unstable network").

At the moment the best way I can see of fixing this in the general case is to use the database-specific LCL components to collect data, copy the data into non-DB components for editing/display, and buffering locally-enetered data using e.g. SQLite subject subject to requirements of sequential fields etc.

MarkMLl

I don't know if there is a buffer or not in IBDac, so far, never experienced data lost when using this components in C++ Builder.

For now, I still thinking whether to use Sqlite or  sql scripts as a buffer. Perhaps sql scripts is simpler than Sqlite.

MarkMLl

  • Hero Member
  • *****
  • Posts: 6676
What do you mean? Sorry, I am new to Lazarus, isn't LCL is short for Lazarus Components Library? Is there a different between Lazarus and LCL?

(Sigh) This is not something that fixes a problem in the LCL. It is something that exists in various variants that install onto a Delphi or Lazarus development system. Go read the description.

Quote
I don't know if there is a buffer or not in IBDac, so far, never experienced data lost when using this components in C++ Builder.

There isn't. Read the docs.

Come on man, you're the one who threw that particular hat into the ring: it's your job to know what it actually does.

Quote
For now, I still thinking whether to use Sqlite or  sql scripts as a buffer. Perhaps sql scripts is simpler than Sqlite.

Both are viable, but I'm not sure how you'd get an SQL script out of a DB-aware component. Use components as a front-end for a local SQLite database and "all" you'll need will be something non-graphical that detects when the backend is contactable (using ping etc.) and transfers the local data in the context of a pair of nested transactions.

MarkMLl
MT+86 & Turbo Pascal v1 on CCP/M-86, multitasking with LAN & graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

 

TinyPortal © 2005-2018