Recent

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

incendio

  • Full Member
  • ***
  • Posts: 157
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?

MarkMLl

  • Hero Member
  • *****
  • Posts: 4187
I am not the best person to answer this, and hopefully somebody with specialist knowledge will come along shortly.

In general, I think you have to assume that the basic database components and underlying libraries require that a connection stays live. In addition, I think you should assume that the server is likely to abort a transaction if the connection goes down.

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

korba812

  • Sr. Member
  • ****
  • Posts: 269
Is IBX able to save data to the data  when network connection is restore again without need to restart the app?
I guess that's not possible. Queries linked to a dataset are prepared when the dataset is opened and are associated with the given connection.
If you cannot guarantee a permanent connection to the base, consider another solution, e.g. a web service or some RCP.

incendio

  • Full Member
  • ***
  • Posts: 157
That is too bad.

I have DevArt VCL for C++ builder that can handle database operation under unstable network.

I will ask them if their LCL for Lazarus also have the same features.

Zvoni

  • Hero Member
  • *****
  • Posts: 1185
a "manual" Workaround could be
1) Check Connected-Property before saving. If True everything OK, if False you have no Network.
2) If 1) is answered False, instead of "saving" implement a "queue" (Could be a StringList with the SQL-Statement).
You would also need a Procedure to try to reconnect back (own Thread?). TIBConnection's Ancestor TCustomConnection offers a published "AfterDisconnect"-Event. You could start the reconnect try from there. You would need a logic to exempt this code, if the User really wants to disconnect.
3) TIBConnection's Ancestor TCustomConnection offers a published "AfterConnect"-Event. If in 2) you reconnect successfully, "AfterConnect" should fire, and you could check if there are SQL-Statements waiting in the Queue
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

MarkMLl

  • Hero Member
  • *****
  • Posts: 4187
a "manual" Workaround could be
1) Check Connected-Property before saving. If True everything OK, if False you have no Network.
2) If 1) is answered False, instead of "saving" implement a "queue" (Could be a StringList with the SQL-Statement).
You would also need a Procedure to try to reconnect back (own Thread?). TIBConnection's Ancestor TCustomConnection offers a published "AfterDisconnect"-Event. You could start the reconnect try from there. You would need a logic to exempt this code, if the User really wants to disconnect.
3) TIBConnection's Ancestor TCustomConnection offers a published "AfterConnect"-Event. If in 2) you reconnect successfully, "AfterConnect" should fire, and you could check if there are SQL-Statements waiting in the Queue

@Zvoni: can you confirm that the DB-aware grids etc. aren't really suitable for this sort of thing, and that if a user wants to continue to show the current row etc. he should parse/copy the data into standard (i.e. non-DB) components?

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

Zvoni

  • Hero Member
  • *****
  • Posts: 1185
Mark,

honestly? No Idea. Never used DB-bound controls (never in 20 years of Visual Basic, and never in 4 years of Lazarus),
since i want to do the CRUD's on a Database myself (more fine-control).

But i think you're on the right path.
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

incendio

  • Full Member
  • ***
  • Posts: 157
Checking Connected property isn't enough.

When there are a lots of data, such as in the Dbgrid, network connection, could be disconnected and reconnected again during ApplyUpdate process.

Zvoni

  • Hero Member
  • *****
  • Posts: 1185
Then the only "fail"-safe method i know of would be to use a local database, which replicates itself to the server if network is available
https://www.firebirdfaq.org/faq249/
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

MarkMLl

  • Hero Member
  • *****
  • Posts: 4187
honestly? No Idea. Never used DB-bound controls (never in 20 years of Visual Basic, and never in 4 years of Lazarus), since i want to do the CRUD's on a Database myself (more fine-control).

But i think you're on the right path.

Where I've got to in the past is that for real robustness I needed to do app the result parsing myself, in at least one case with scripted control of queries. However there's a real possibility that Delphi/Lazarus developments have moved beyond that, hence the question.

However I'd imagine that a hybrid approach would be viable: use the DB-aware controls for all of the parsing etc. with the result shadowed onto a standard control. If the program has reason to think that the connection is bad (which might take a ping etc.) it switches the shadows to the foreground.

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

  • Full Member
  • ***
  • Posts: 157
Then the only "fail"-safe method i know of would be to use a local database, which replicates itself to the server if network is available
https://www.firebirdfaq.org/faq249/
I think local database is to complicated.

Not all data needs to be replicated, only the data that are being input / edit by users.

I think of table in memory that holds all data before it is updated to the database server.

I don't know much of Lazarus, perhaps there is a LCL components that could be served for storing temporary data in memory.

Zvoni

  • Hero Member
  • *****
  • Posts: 1185
I think of table in memory that holds all data before it is updated to the database server.

I don't know much of Lazarus, perhaps there is a LCL components that could be served for storing temporary data in memory.
And that's different to my proposal of a Queue, how?
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

incendio

  • Full Member
  • ***
  • Posts: 157
I think of table in memory that holds all data before it is updated to the database server.

I don't know much of Lazarus, perhaps there is a LCL components that could be served for storing temporary data in memory.
And that's different to my proposal of a Queue, how?

No, it's the same with your ideas.

But my question is not that, I want to know whether IBX has capabilities to handle database operations under unstable network connection.

It seems that it's not.

I will try to implement yours ideas for database operations over internet. Thanks.

Zvoni

  • Hero Member
  • *****
  • Posts: 1185
But my question is not that, I want to know whether IBX has capabilities to handle database operations under unstable network connection.
I wouldn't know of a component in any language capable of that. It's the programmers job to provide for that eventuality.
As i said: the only "fail"-safe method i know off is a replication-setup, with a proxy in between application and database-server(s) switching servers if the one you're connected to goes down
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

MarkMLl

  • Hero Member
  • *****
  • Posts: 4187
Please treat this as a rhetorical question.

I think local database is to complicated.

Do you have any idea whatsoever, how often an old-timer such as myself has heard an admittedly-inexperienced user say "Your solution is too complicated, I know better"?

However, I think the important thing to appreciate here is that the local database doesn't have to be the same as the backend one. For simple data entry you might do better using e.g. SQLite locally, with the upload carefully wrapped in transactions to make sure it's atomic.

And that approach /is/ different from a queue, unless your queue implementation is enhanced by a whole lot of non-volatile storage and robustness protection... in which case you've just reinvented the wheel and written a database server.

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