Going to a bookmark fails after Dataset.Refresh?
lazarus / 16-Databases

----------------------------------------------------------------------
tatamata · 2016-02-02 12:25

I have some master/detail datasets (SQlite) and need to refresh master
dataset in order to acquire foreign key value for detail dataset.
But, when the dataset is refreshed, cursor automatically goes to the
first record of the master dataset.
In order to go back to the current record (newly entered one), I
wanted to use bookmark. However, after refreshing, bookmark does not
work.
I know I could use Last method to go to the last record, but somehow
it seems wrong to me..Is there any other way?

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
TDataModule1.SQLQuery1AfterPost(DataSet: TDataSet);var vBookmark:
TBookmark;begin  try    vBookmark:=Dataset.GetBookmark;    (Dataset as
TSQLQuery).ApplyUpdates;    Dataset.Refresh;    
Dataset.GotoBookmark(vBookmark); //This doesn't work!   finally   
Dataset.FreeBookmark(vBookmark);  end;end;

----------------------------------------------------------------------
mangakissa · 2016-02-03 07:54

For new records its always a problem. I don't know if sqlite returns a
primary key, but with firebird I call the generator yearlier and then
post my record. so the new unique number id is known and with funcion
locate() you can find the just created record after refresh.

----------------------------------------------------------------------
tatamata · 2016-02-03 08:04

Something is strange here. When I try to inspect Dataset.State, it
seems that the dataset state is not in TDataSetState? Am I crazy or
what ???:
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
TDataModule1.SQLQuery1AfterPost(DataSet: TDataSet);var  vBookmark:
TBookmark;  vDatasetState: string;  vMessage: string;begin  try   
try      case Dataset.State of          dsInactive:
vDatasetState:='dsInactive';          dsEdit:
vDatasetState:='dsEdit';          dsInsert:
vDatasetState:='dsInsert';          dsSetKey:
vDatasetState:='dsSetKey';          dsCalcFields:
vDatasetState:='dsCalcFields';          dsFilter:
vDatasetState:='dsFilter';          dsNewValue:
vDatasetState:='dsNewValue';          dsOldValue:
vDatasetState:='dsOldValue';          dsCurValue:
vDatasetState:='dsCurValue';          dsBlockRead:
vDatasetState:='dsBlockRead';          dsInternalCalc:
vDatasetState:='dsInternalCalc';          dsOpening:
vDatasetState:='dsOpening';          dsRefreshFields:
vDatasetState:='dsRefreshFields';          else
 vDatasetState:='SomethingOther' ;      end;     
ShowMessage(vDatasetState); //This returns "SomethingOther" all the
time, no matter whether I insert a row or edit a value?!?     
vBookmark:=Dataset.GetBookmark;      (Dataset as
TSQLQuery).ApplyUpdates;      Dataset.Refresh;      if
vDatasetState<>'dsInsert' then begin       
Dataset.GotoBookmark(vBookmark);      end      else begin       
Dataset.Last;      end;    except      on E:Exception do begin       
vMessage:=E.Message;        ShowMessage(vMessage);      end;    end; 
finally    Dataset.FreeBookmark(vBookmark);  end;end;

----------------------------------------------------------------------
taazz · 2016-02-03 10:49

Quote from: tatamata on February 03, 2016, 09:04:52 amSomething is
strange here. When I try to inspect Dataset.State, it seems that the
dataset state is not in TDataSetState? Am I crazy or what ???:
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
TDataModule1.SQLQuery1AfterPost(DataSet: TDataSet);var  vBookmark:
TBookmark;  vDatasetState: string;  vMessage: string;begin  try   
try      case Dataset.State of          dsInactive:
vDatasetState:='dsInactive';          dsEdit:
vDatasetState:='dsEdit';          dsInsert:
vDatasetState:='dsInsert';          dsSetKey:
vDatasetState:='dsSetKey';          dsCalcFields:
vDatasetState:='dsCalcFields';          dsFilter:
vDatasetState:='dsFilter';          dsNewValue:
vDatasetState:='dsNewValue';          dsOldValue:
vDatasetState:='dsOldValue';          dsCurValue:
vDatasetState:='dsCurValue';          dsBlockRead:
vDatasetState:='dsBlockRead';          dsInternalCalc:
vDatasetState:='dsInternalCalc';          dsOpening:
vDatasetState:='dsOpening';          dsRefreshFields:
vDatasetState:='dsRefreshFields';          else
 vDatasetState:='SomethingOther' ;      end;     
ShowMessage(vDatasetState); //This returns "SomethingOther" all the
time, no matter whether I insert a row or edit a value?!?     
vBookmark:=Dataset.GetBookmark;      (Dataset as
TSQLQuery).ApplyUpdates;      Dataset.Refresh;      if
vDatasetState<>'dsInsert' then begin       
Dataset.GotoBookmark(vBookmark);      end      else begin       
Dataset.Last;      end;    except      on E:Exception do begin       
vMessage:=E.Message;        ShowMessage(vMessage);      end;    end; 
finally    Dataset.FreeBookmark(vBookmark);  end;end;well
1) There are methods to get the text of an enumerated value that do
not require a case statement (rtti and runtyp unit I believe) although
an overkill in this occasion, it is something to look up for the
future.
2) There is a handy little trick to instruct the IDE to populate a
case statement with all possible values, write "case Dataset.State
of|" and instead of space or enter press ctrl+J.

In your case you overlooked the most obvious state aka dsBrowse which
is the state set by SQLDB before calling the after post event.

PS. please stop comparing strings when there is a perfectly good
integer value. Why anyone would want to compare vDatasetState as
string when it can do the same job and faster as TDatasetstate (or
even longint) is beyond my comprehension.

----------------------------------------------------------------------
tatamata · 2016-02-03 12:46

Quote from: taazz on February 03, 2016, 11:49:17 amPS. please stop
comparing strings when there is a perfectly good integer value. Why
anyone would want to compare vDatasetState as string when it can do
the same job and faster as TDatasetstate (or even longint) is beyond
my comprehension.
The only reason I introduced vDatasetState was to show it in
ShowMessage as string- ShowMessage(vDatasetState);  it was not
intentionaly introduced in comparisson, it was a mistake.

----------------------------------------------------------------------
tatamata · 2016-02-03 14:59

Ok, obvioulsy Bookmark does not work correctly after Refresh method is
executed. On other hand, refresh is needed only if a new record is
inserted and then we know that the new record is the last one. So, the
solution is to chech DataSet.UpdateStatus property of a dataset on
AfterPost event. If UpdateStatus is usInserted then Refresh and Last.
Otherwise, do nothing.
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
RefreshADatasetAfterInsert(pDataSet: TDataSet);//This procedure
refreshes a dataset and positions cursor to last recordvar 
{vBookmark: TBookmark; //This does not work correctly after Refresh} 
vUpdateStatus: string;  vMessage: string;  vID: Integer;begin  try   
try      //Take info about dataset update status BEFORE Refresh!     
case pDataset.UpdateStatus of          usUnmodified:
vUpdateStatus:='usUnmodified';          usModified:
vUpdateStatus:='usModified';          usInserted:
vUpdateStatus:='usInserted';          usDeleted:
vUpdateStatus:='usDeleted';          else
 vUpdateStatus:='SomethingOther' ;      end;     
{vBookmark:=pDataset.GetBookmark; //This does not work correctly after
Refresh!}       (pDataset as TSQLQuery).ApplyUpdates;       //Now come
back to respective row      if vUpdateStatus='usInserted' then begin 
     pDataset.Refresh;       {pDataset.GotoBookmark(vBookmark); //This
does not work correctly after Refresh!}        pDataSet.Last;     
end;     except      on E:Exception do begin       
vMessage:=E.Message;        ShowMessage(vMessage);      end;    end; 
finally    {pDataset.FreeBookmark(vBookmark); //This does not work
correctly after Refresh! }  end;end;

----------------------------------------------------------------------
taazz · 2016-02-03 15:35

Quote from: tatamata on February 03, 2016, 03:59:23 pmOk, obvioulsy
Bookmark does not work correctly after Refresh method is executed. On
other hand, refresh is needed only if a new record is inserted and
then we know that the new record is the last one. So, the solution is
to chech DataSet.UpdateStatus property of a dataset on AfterPost
event. If UpdateStatus is usInserted then Refresh and Last. Otherwise,
do nothing.
Wrong assumption. Bookmark works as designed refresh is the same as
calling close and open in sequence. When a dataset is closed the
bookmarks become invalid, bookmarks are nothing more than a pointer in
memory where the record is. When the dataset reopens the component can
not guaranty that the records will be placed on the same memory blog.
There is one exception that I have seen so far and that is on
IBcomponents where refresh will reload the selected record's data from
the database and will not reload everything. You could request a
feature in the bug tracker if you think that will help.