* * *

Author Topic: TDBGrid refresh problem  (Read 2127 times)

ronhud

  • New member
  • *
  • Posts: 43
TDBGrid refresh problem
« on: April 13, 2018, 02:42:03 pm »
I am struggling with getting a dbgrid to update at runtime.
I have a TDBGrid connected to a IBConnection (Firebird) and am using aTDataSource, TSQLTransaction and TSQLQuery.
I have made a simple app - the above with a TTimer.  When the app starts it displays the records correctly.  When the OnTimer event is called I have the following code which is described in several searches I have made as all that is necessary to get the grid to update.

DBGrid1.DataSource.DataSet.Refresh;

I use Robin to manually change a field in the table being displayed but when the next OnTimer event happens the data shown in the grid has not changed.

Any help please?

GetMem

  • Hero Member
  • *****
  • Posts: 3187
Re: TDBGrid refresh problem
« Reply #1 on: April 13, 2018, 02:47:40 pm »
Just Close and Open the dataset and you're good to go.

ronhud

  • New member
  • *
  • Posts: 43
Re: TDBGrid refresh problem
« Reply #2 on: April 13, 2018, 03:00:55 pm »
thanks but that did not work.   Is anything special required when the changes to the database are being made by a seperate program - Robin in this case

GetMem

  • Hero Member
  • *****
  • Posts: 3187
Re: TDBGrid refresh problem
« Reply #3 on: April 13, 2018, 03:29:46 pm »
Make sure you commit the transaction on the other side(Robin in this case).

ronhud

  • New member
  • *
  • Posts: 43
Re: TDBGrid refresh problem
« Reply #4 on: April 13, 2018, 03:49:57 pm »
Yes the changes in Robin are committed and I check the table after the change also.  Here is the code:
Code: Pascal  [Select]
  1. unit Unit1;
  2.  
  3. {$mode objfpc}{$H+}
  4.  
  5. interface
  6.  
  7. uses
  8.   Classes, SysUtils, IBConnection, sqldb, db, FileUtil, Forms, Controls,
  9.   Graphics, Dialogs, DBGrids, ExtCtrls;
  10.  
  11. type
  12.  
  13.   { TForm1 }
  14.  
  15.   TForm1 = class(TForm)
  16.     DataSource1: TDataSource;
  17.     DBGrid1: TDBGrid;
  18.     IBConnection1: TIBConnection;
  19.     Panel1: TPanel;
  20.     SQLQuery1: TSQLQuery;
  21.     SQLTransaction1: TSQLTransaction;
  22.     Timer1: TTimer;
  23.     procedure Timer1Timer(Sender: TObject);
  24.   private
  25.  
  26.   public
  27.  
  28.   end;
  29.  
  30. var
  31.   Form1: TForm1;
  32.  
  33. implementation
  34.  
  35. {$R *.lfm}
  36.  
  37. { TForm1 }
  38.  
  39. procedure TForm1.Timer1Timer(Sender: TObject);
  40. begin
  41.   SQLQuery1.Close;
  42.   SQLQuery1.Open;
  43.   Panel1.Caption := 'last timer ' + DateTimeToStr(Now);
  44. end;
  45.  
  46. end.
  47.              

ezlage

  • Sr. Member
  • ****
  • Posts: 281
  • (Silêncio)
    • Lageteck
Re: TDBGrid refresh problem
« Reply #5 on: April 13, 2018, 03:50:35 pm »
Make sure you commit the transaction on the other side(Robin in this case).

Commit maybe work, like GetMem said. If you want, I believe, you also can set uncommitted data readable.
https://www.firebirdsql.org/pdfmanual/html/isql-transactions.html

In ZEOS DBO:

Code: Pascal  [Select]
  1. procedure TForm1.ZConnection1ReadUncommitted(Sender: TObject);
  2. begin
  3.   ZConnection1.TransactIsolationLevel:=tiReadUncommitted;
  4. end;  
Sorry by my poor english.

Atenciosamente,

Ezequiel Lage
ezequiel@lageteck.com

https://github.com/ezlage, https://github.com/ezlage/sempercom

GetMem

  • Hero Member
  • *****
  • Posts: 3187
Re: TDBGrid refresh problem
« Reply #6 on: April 13, 2018, 05:04:18 pm »
@ronhud
That's really strange! What happens if your restart your application? Do you see the changes?

@ezlage
Quote
I believe, you also can set uncommitted data readable.
True, but it's a two edge sword. Seeing/working with uncommitted changes it's a recipe for disaster(see dirty reads).

valdir.marcos

  • Hero Member
  • *****
  • Posts: 515
Re: TDBGrid refresh problem
« Reply #7 on: April 13, 2018, 05:30:23 pm »
Try:

Code: Pascal  [Select]
  1. procedure TForm1.Timer1Timer(Sender: TObject);
  2. begin
  3.   SQLTransaction1.Commit;
  4.  
  5.   // SQLQuery1.Close;
  6.   SQLQuery1.Open;
  7.   Panel1.Caption := 'last timer ' + DateTimeToStr(Now);
  8. end;

Cesare

  • New member
  • *
  • Posts: 7
Re: TDBGrid refresh problem
« Reply #8 on: April 13, 2018, 10:44:14 pm »
try :

SQLQuery1.Refresh;

in a similar scenario with mysql it works

ronhud

  • New member
  • *
  • Posts: 43
Re: TDBGrid refresh problem
« Reply #9 on: April 14, 2018, 09:22:15 am »
The response from Valdir did the job - calling the transaction commit before opening the query.

ronhud

  • New member
  • *
  • Posts: 43
Re: TDBGrid refresh problem
« Reply #10 on: April 14, 2018, 09:24:46 am »
......so thanks Valdir

GetMem

  • Hero Member
  • *****
  • Posts: 3187
Re: TDBGrid refresh problem
« Reply #11 on: April 14, 2018, 09:49:59 am »
For me a simple Query.Close/Query.Open works fine, so something fishy is happening at your side. SQLTransaction1.Commit flags a transaction as concluded and will close everything. Just to read something from the database your don't need a commit:
Quote
Commit commits an active transaction. The changes will be irreversably written to the database.
What did you write to the database in this particular case?  :)


rvk

  • Hero Member
  • *****
  • Posts: 3461
Re: TDBGrid refresh problem
« Reply #12 on: April 14, 2018, 10:14:20 am »
SQLTransaction1.Commit flags a transaction as concluded and will close everything. Just to read something from the database your don't need a commit:
That depends on the isolation level. If it is in snapshot mode (which is default), the reading will also be done in the current transaction. You are not able to see written data from another transaction. You see a snapshot of the database at the moment of the start of your own transaction.

Note: If you DON'T specify a isolation leven, SNAPSHOT is the default in Firebird. So set the level to READ_COMMITED and you won't have to close the transaction (rollback or commit).
Quote
SNAPSHOT isolation level—the default level—allows the transaction to see only those changes that were committed before this one was started.
https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-transacs-statements.html

PS. A SQLTransaction1.Rollback would have the same effect as a SQLTransaction.Commit. The transaction is closed (rolled back) and a new one is opened on Query.Open (and you can read the current state of the DB again).

GetMem

  • Hero Member
  • *****
  • Posts: 3187
Re: TDBGrid refresh problem
« Reply #13 on: April 15, 2018, 04:12:48 pm »
@rvk
Sorry for the late response(I was out in the wild) and thanks for the detailed explanation. In my applications I always use IBObjects, which is in my opinion one of the best database related component. It's not free, but is worth every penny(http://www.ibobjects.com/). The IBOTransaction's isolation level is tiCommited by default(see attached image), I wrongly assumed that is also true for a TSQLTransaction. I will quote from the IBObjects help:
Quote
tiCommitted - Read-Committed
This is the ideal isolation for a browsing user interface because it keeps up with all committed changes in the database as they occur. Thus, the interface can be refreshed to reflect changes in the database without having to start a new transaction.
If using an IB_Query component it is even possible to refresh individual rows of a dataset or, based on a refresh of the keys of the dataset, refresh the order or inclusion (in case of deletes and inserts by other users or a change in the ORDER BY criteria) of records without having to refetch the individual records of the dataset again. When this type of refresh is performed all of the row buffers are stored aside so that when their keys are fetched from the server they are reassociated with the record buffer in memory.
For years I only did a IBQuery.Refresh and everything was up to date. More over whit IBObjects you can easily subscribe to firebird events, you get notified when something changes without the need to do refresh.


 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus