dbgrid losing their position
lazarus / 16-Databases

----------------------------------------------------------------------
xinyiman · 2020-01-04 22:19

Hi guys, I have the following problem. I am creating multi-user
software that allows you to edit data in a firebird database.
Obviously when I make a change from one client I send a message to all
the other clients to update themselves. And so far so good. My problem
is that I don't know how to update clients without losing their
position on the current record. To simulate the problem, download the
backup of the firebird database (2.5) which I am attaching here. Then
open the lazarus project and in the oncreate event of the form change
the access credentials to the firebird database. Compile the program.
Run two different instances of the same program, get on line 5 of both
programs. Then change the descriptive value from one of the two. Now
on the other, press the refresh button. You will see that it does not
stay on line 5. I need it to remain on the current record. Can you
tell me how to do it ?!

----------------------------------------------------------------------
nouzi · 2020-01-05 08:03

hi all
xinyiman you can use Bookmark  

see this example :
// declaration
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i ...var 
fuser: Tfuser;  BMuser: TBookMark ;  // this line implementation  ... 
// Before updating

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i ...    
BMuser :=     DataModule1.tusers.Bookmark   ;    ...   
// after  updating
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i   
DataModule1.tusers.Bookmark :=   BMuser ;

----------------------------------------------------------------------
xinyiman · 2020-01-05 11:18

Hi nouzi, thanks for the reply. But have you looked at my example? I
already use TBookMark and it doesn't work.

----------------------------------------------------------------------
xinyiman · 2020-01-05 11:20

In the past I have used zeos components, and it worked well. I did not
deactivate and reactivate the transaction (because there is no object
for the transactions) but it worked correctly. With standard
components no. What am I doing wrong?

----------------------------------------------------------------------
xinyiman · 2020-01-05 12:37

Hi, then I corrected some things. The first big mistake is that I
wrote committed with a single t and therefore did not read remote
commits. So I was able to remove the transactor connection and
reconnection. But there are still burrs. Sometimes when I press the
refresh button it generates an access violation and I don't understand
why, and then when it refreshes it repositions me on the right line
but always on the first cell. How do I get it repositioned on the
right cell? Look at the attached example

----------------------------------------------------------------------
MichaelBM · 2020-01-05 12:48

I previously had the same problems.

Using 

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
Database.Transaction.CommitRetaining;
solved the problem.

Also, after an update I always reposition the cursor to the wanted
record by using a search on the primary key.

----------------------------------------------------------------------
xinyiman · 2020-01-05 14:12

Quote from: MichaelBM on January 05, 2020, 01:48:12 pmI previously had
the same problems.

Using 

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
Database.Transaction.CommitRetaining;
solved the problem.

Also, after an update I always reposition the cursor to the wanted
record by using a search on the primary key.

Hi, I already use commitretaing in my example. The access violation
problem occurs when I run the line:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i          
if Self.SQLQuery1.BookmarkValid(MyBookmark) then          begin      
       Self.SQLQuery1.GotoBookmark(MyBookmark);  //<-- access
violation          end;

----------------------------------------------------------------------
Soner · 2020-01-05 18:39

I solved it for me like this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i var  
aAktPos : TBookMark; // for active data row in query  r: integer;    
            // for activerow in DBGridbegin 
 aAktPos:=Query1.Bookmark;   r := DbGrid1.VisibleRowCount -
DbGrid1.Row;   Query1.DisableControls;  try   //Do something with
query  //...      Query1.Bookmark:=aAktPos;  //active active row in
Query  finally   Query1.EnableControls;    //Restore Row position in
DBGrid1    r := DbGrid1.DataSource.DataSet.MoveBy(r);               r
:= DbGrid1.DataSource.DataSet.MoveBy(-r);          end;end;

----------------------------------------------------------------------
xinyiman · 2020-01-05 18:59

Soner have you tried to see if it works with the example I posted ?! I
don't think it works. because it goes wrong when I assign the bookmark

----------------------------------------------------------------------
valdir.marcos · 2020-01-05 22:58

Quote from: xinyiman on January 04, 2020, 11:19:11 pmHi guys, I have
the following problem.
I am creating multi-user software that allows you to edit data in a
firebird database.
Obviously when I make a change from one client I send a message to all
the other clients to update themselves.
And so far so good.Ok.

QuoteMy problem is that I don't know how to update clients without
losing their position on the current record.This was easy and
important in the old times of DBase and Paradox.
From SQL engines on, this behavior is not important anymore.

Maybe, you might apply old tricks to catch up old behavior.
Since TBookMark is not working in your case, try using
TSQLQuery.Locate():
https://wiki.freepascal.org/Databases#Locate.2Flookup

QuoteTo simulate the problem, download the backup of the firebird
database (2.5) which I am attaching here.
Then open the lazarus project and in the oncreate event of the form
change the access credentials to the firebird database.
Compile the program.
Run two different instances of the same program, get on line 5 of both
programs.
Then change the descriptive value from one of the two.
Now on the other, press the refresh button.
You will see that it does not stay on line 5.
I need it to remain on the current record.
Can you tell me how to do it ?!

----------------------------------------------------------------------
xinyiman · 2020-01-05 23:11

But TSQLQuery.Locate only moves me to the correct record, it doesn't
keep me the correct position of the dbgrid scrollbar. Otherwise the
user sees the dbgrid scroll up and down. It's terrible behavior if I
have to work on it all day with that program.

----------------------------------------------------------------------
valdir.marcos · 2020-01-05 23:12

Quote from: xinyiman on January 05, 2020, 12:20:23 pmIn the past I
have used zeos components, and it worked well.
I did not deactivate and reactivate the transaction (because there is
no object for the transactions) but it worked correctly.
With standard components no.
What am I doing wrong?You may try CommitRetaining or
sqoKeepOpenOnCommit to replicate ZeosLib behavior.
https://www.freepascal.org/docs-
html/fcl/sqldb/tsqltransaction.commitretaining.html
https://www.freepascal.org/docs-html/fcl/sqldb/tsqlquery.options.html
https://www.freepascal.org/docs-html/fcl/sqldb/tsqlqueryoptions.html

Be very careful with known performance problems when not committing
transactions immediately.

----------------------------------------------------------------------
valdir.marcos · 2020-01-05 23:24

Quote from: xinyiman on January 06, 2020, 12:11:27 amBut
TSQLQuery.Locate only moves me to the correct record, it doesn't keep
me the correct position of the dbgrid scrollbar.Maybe:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLQuery1.DisableControls;// ...SQLQuery1.EnableControls;
QuoteOtherwise the user sees the dbgrid scroll up and down.
It's terrible behavior if I have to work on it all day with that
program.This is by design.
Perhaps, you should inherit and hack TDGrid and change that behavior.

----------------------------------------------------------------------
xinyiman · 2020-01-05 23:53

How!? Is there an example?!

----------------------------------------------------------------------
valdir.marcos · 2020-01-06 01:03

Quote from: xinyiman on January 06, 2020, 12:53:09 amHow!? Is there an
example?!I beg your pardon, this is very common in Portuguese, but I
could not find so many links in English.

Here they are:

How to refresh dbgrid without close and open dataset in delphi?
https://stackoverflow.com/questions/2311007/how-to-refresh-dbgrid-
without-close-and-open-dataset-in-delphi

Customizing a Database Grid
http://etutorials.org/Programming/mastering+delphi+7/Part+III+Delphi+D
atabase-Oriented+Architectures/Chapter+13+Delphi+s+Database+Architectu
re/Customizing+a+Database+Grid/

Selecting and Highlighting a Row in a DBGrid
https://www.thoughtco.com/select-and-highlight-row-in-dbgrid-4077236

How to set active cell in TDBGrid?
https://stackoverflow.com/questions/7978780/how-to-set-active-cell-in-
tdbgrid

Delphi DBGrid alternate row colors for all DBGrids in the project
https://stackoverflow.com/questions/22083444/delphi-dbgrid-alternate-
row-colors-for-all-dbgrids-in-the-project

How do I get screen coordinates of the DBGrid cell
https://stackoverflow.com/questions/9355555/how-do-i-get-screen-
coordinates-of-the-dbgrid-cell

Add DBLookupCombobox to Delphi DBGrid
https://stackoverflow.com/questions/32916374/add-dblookupcombobox-to-
delphi-dbgrid

And so on...

----------------------------------------------------------------------
balazsszekely · 2020-01-06 08:51

Locate calls method GotoBookMark internally, so Locate/Bookmark
basically it's the same thing.

@xinyiman
Put this code in your refresh button onclick event:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i var  ID:
Integer;begin  ID := SQLQuery1.FieldByName('ID').AsInteger; 
SQLTransaction1.CommitRetaining;  SQLQuery1.Refresh; 
SQLQuery1.Locate('ID', ID, []);end;

This will work fine row 5 or any higher rows for that matter, hower
there is a catch for higher row numbers. Unfortunately
GoToBookMark(unit db.pas in fpc) is implemented like this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TDataSet.GotoBookmark(const ABookmark: TBookmark);begin  if
Assigned(ABookMark) then  begin    CheckBrowseMode;   
DoBeforeScroll;    InternalGotoBookMark(pointer(ABookMark));   
Resync([rmExact,rmCenter]);    DoAfterScroll;  end;end;
As you can see Resync is called with rmCenter, which will position the
current record in the center, thus scrolling the grid.

----------------------------------------------------------------------
xinyiman · 2020-01-06 14:12

Hi guys, I'm trying to manipulate the properties of the dbgrid.
LeftCol works, I thought TopRow also worked (and this would have
solved the problem) but always returns 1 as a value. I don't
understand which property to manipulate to keep the grill in the right
place.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i type
THackDataSet=class(TDataSet); THackDBGrid=class(TDBGrid); 
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.Button1Click(Sender: TObject);var   ID  : Integer;   LC  :
integer;   //TR  : integer;   //SC  : TScrollStyle;   DX  : integer; 
 DY  : integer;   SP  : TPoint;begin     {     TR :=
THackDBGrid(Self.DBGrid1).TopRow;     SC :=
THackDBGrid(Self.DBGrid1).ScrollBars;    }     LC :=
THackDBGrid(Self.DBGrid1).LeftCol;     SP :=
THackDBGrid(Self.DBGrid1).GetPxTopLeft;       ID :=
SQLQuery1.FieldByName('ID').AsInteger;   
 SQLTransaction1.CommitRetaining;     SQLQuery1.Refresh;   
 SQLQuery1.Locate('ID', ID, []);     
 THackDBGrid(Self.DBGrid1).ScrollBy(SP.X, SP.Y);   
 THackDBGrid(Self.DBGrid1).LeftCol    := LC;   
 THackDBGrid(Self.DBGrid1).Invalidate;    
 {THackDBGrid(Self.DBGrid1).ScrollBy(6,8);    
 THackDBGrid(Self.DBGrid1).ScrollBars := SC;   
 THackDBGrid(Self.DBGrid1).TopRow     := TR;         }    
 {GetActualRowPosition();     Self.SQLQuery1.Refresh;   
 //Self.CloseQuery;     //Self.OpenQuery;     SetActualRowPosition();
}end;

----------------------------------------------------------------------
Soner · 2020-01-06 16:01

Quote from: xinyiman on January 05, 2020, 07:59:21 pmSoner have you
tried to see if it works with the example I posted ?! I don't think it
works. because it goes wrong when I assign the bookmark
I have not tried your example, but my code works. I am using it in my
db-application.
You have to know what you want to do. When you update the query then
the bookmarks will be deleted. In this case you have to use
TQuery.Locate function like this:

Quotevar
  aAktPos : TBookMark; // for active data row in query
  r: integer;                  // for activerow in DBGrid
begin
   aAktPos:=Query1.Bookmark;
   r := DbGrid1.VisibleRowCount - DbGrid1.Row;
  Query1.DisableControls;
  try
   //Do something with query
  //...


   try
    Query1.Bookmark:=aAktPos;  //active active row in Query
   except
    on E: Exception do begin // Bookmark is deleted, try with locate
      if not Query1.Locate(...) then begin //cannot locate then do
this.
         //refresh the query with: Select * from table where
tableid=xyz
      end;

    end;
   end;


  finally
   Query1.EnableControls;

   //Restore Row position in DBGrid1
    r := DbGrid1.DataSource.DataSet.MoveBy(r);         
    r := DbGrid1.DataSource.DataSet.MoveBy(-r);       
 end;
end;


Quote from: xinyiman on January 06, 2020, 03:12:21 pmHi guys, I'm
trying to manipulate the properties of the dbgrid. LeftCol works, I
thought TopRow also worked (and this would have solved the problem)
but always returns 1 as a value. I don't understand which property to
manipulate to keep the grill in the right place.
My code above do this too.

----------------------------------------------------------------------
Soner · 2020-01-06 16:17

I wanted try your example, but it has problems itself.
First the projectfile(lpi) was empty, i  added the units, then lazarus
showed "project has no mainfile". I fixed this too, now it compiles
but exe-file is nowhere.
Guys, why don't you try your examples before you upload it?

----------------------------------------------------------------------
Soner · 2020-01-06 16:28

I analyzed your code it is trash. You can't use there bookmarks. Store
the row-id (Indexfield) use locate.
This is your code with my comments.
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.Button1Click(Sender: TObject);begin     GetActualRowPosition();
//store here rowid and dbgrid.row ...     Self.SQLTransaction1.Active
:= false;     Self.SQLTransaction1.Active := true;   
 Self.CloseQuery;     Self.OpenQuery;     SetActualRowPosition(); //
.. and restore it here.end; 
I am fixing your example and upload here.

----------------------------------------------------------------------
Soner · 2020-01-06 16:44

So here is your working example. I noticed that my gridposition
location goes sometimes wrong when the grid is very small like in your
example. Make it bigger then it works.
Note that you can use Bookmarks only, when you don't close and open
again it, like first, next,last iterations. When you close a Query
then the bookmark list will be freed.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i unit
Unit1; {$mode objfpc}{$H+} interface uses  Classes, SysUtils,
IBConnection, SQLDB, DB, Forms, Controls, Graphics,  Dialogs, DBGrids,
DBCtrls, StdCtrls, grids, Interfaces; type   { TForm1 }   TForm1 =
class(TForm)    Button1: TButton;    DataSource1: TDataSource;   
DBGrid1: TDBGrid;    Conn: TIBConnection;    DBNavigator1:
TDBNavigator;    SQLQuery1: TSQLQuery;    SQLTransaction1:
TSQLTransaction;    procedure Button1Click(Sender: TObject);   
procedure FormCreate(Sender: TObject);    procedure
FormDestroy(Sender: TObject);    procedure SQLQuery1AfterPost(DataSet:
TDataSet);  private    //soner: MyBookmark : TBookMark;    rowid, r:
integer;    procedure OpenQuery;    procedure CloseQuery;    procedure
GetActualRowPosition();    procedure SetActualRowPosition();  public  
end; var  Form1: TForm1; implementation {$R *.lfm} type  TDbGridHack =
class(TDBGrid)  public    property VisibleRowCount;    property Row; 
end; { TForm1 } procedure TForm1.FormCreate(Sender: TObject);begin 
Self.Conn.DatabaseName :=
ExtractFileDir(Application.ExeName)+PathDelim+'test.fdb'; //
'/db/test.fdb';  Self.Conn.UserName     := 'sysdba'; 
Self.Conn.Password     := 'masterkey';   with
Self.SQLTransaction1.Params do  begin   
Add('character_set_client=utf8');   
Add('character_set_connection=utf8');   
Add('character_set_database=utf8');   
Add('character_set_results=utf8');   
Add('character_set_server=utf8');   
Add('character_set_system=utf8');   
Add('collation_connection=utf8_general_ci');   
Add('collation_database=utf8_general_ci');   
Add('collation_server=utf8_general_ci');    Add('Codepage=utf8');    
Add('isc_tpb_read_commited');    Add('isc_tpb_concurrency');   
Add('isc_tpb_nowait');  end;   Self.Conn.Open; 
Self.SQLTransaction1.Active:=true;  OpenQuery; end; procedure
TForm1.Button1Click(Sender: TObject);begin   
 GetActualRowPosition();     Self.SQLTransaction1.Active := false;   
 Self.SQLTransaction1.Active := true;     Self.CloseQuery;   
 Self.OpenQuery;     SetActualRowPosition();end; procedure
TForm1.FormDestroy(Sender: TObject);begin     Self.CloseQuery;   
 Self.Conn.Close(true);end; procedure
TForm1.SQLQuery1AfterPost(DataSet: TDataSet);begin   
 Self.SQLQuery1.ApplyUpdates;   
 Self.SQLTransaction1.CommitRetaining;end; procedure
TForm1.OpenQuery;begin  Self.SQLQuery1.SQL.Text := 'select * from
t_test;';  Self.SQLQuery1.Open; 
Self.DBGrid1.AutoAdjustColumns;end; procedure TForm1.CloseQuery;begin 
   Self.SQLQuery1.Close;end; procedure
TForm1.GetActualRowPosition();begin     if Self.SQLQuery1.Active then 
   begin       //MyBookmark := Self.SQLQuery1.Bookmark;     
 rowid:=SQLQuery1.FieldByName('id').AsInteger;     
 r:=TDbGridHack(DBGrid1).row;     end;end; procedure
TForm1.SetActualRowPosition();begin   
 Self.SQLQuery1.DisableControls;     try     
 SQLQuery1.Locate('id',rowid, []);        //
Self.SQLQuery1.GotoBookmark(MyBookmark);     finally       //    
Self.SQLQuery1.FreeBookmark(MyBookmark);        //Restore Row position
in DBGrid1        r := DbGrid1.DataSource.DataSet.MoveBy(r);        r
:= DbGrid1.DataSource.DataSet.MoveBy(-r);        
SQLQuery1.EnableControls;     end;   
 //Self.SQLQuery1.EnableControls;end;end. 
Why are you using self-keyword overall in class like
"Self.SQLQuery1.EnableControls;" you don't need it.

----------------------------------------------------------------------
xinyiman · 2020-01-07 13:44

Taken from despair I am writing an extension to the TStringGrid to
display the contents of a dataset there. So I give up on the problem
and get around it.

----------------------------------------------------------------------
valdir.marcos · 2020-01-11 04:59

Quote from: xinyiman on January 07, 2020, 02:44:34 pmTaken from
despair I am writing an extension to the TStringGrid to display the
contents of a dataset there. So I give up on the problem and get
around it.I don't need this feature myself, but I must admit that it's
occasionally remembered or requested.
It's a pity this thread hasn't became a bug report or a patch.