[SOLVED] IBX 2.4.3, How Get Old Value of a field lazarus / 16-Databases ---------------------------------------------------------------------- incendio · 2022-02-22 04:17 Hi guys, I am using IBX 2.4.3 on Lazarus 2.0.12 on Windows. I have a TIBDataset object Dt2 with CachedUpdates Enabled. I tried to get old value data with this codes 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 TFrm.GrdDtKeyDown(Sender: TObject; var Key: Word;  Shift: TShiftState);begin  if Key = 13 then  begin    if GrdDt.SelectedField.Name ='Dt2CD' then    begin      GrdDt.SelectedField  := Dt2QTY;      ShowMessage(Dt2CD.OldValue);    ShowMessage(Dt2CD.NewValue);    end;  end;end;  The value of Dt2CD.OldValue always the same with the value of Dt2CD.NewValue. Is this normal? If this is normal, how to get the old value of a field? ---------------------------------------------------------------------- tonyw · 2022-02-22 14:45 OldValue = NewValue unless the record is in the edit state and you have modified the field value, in which case, Old Value is the field value prior to the edit and NewValue is what you just set it to. Cached updates should have no impact on this. Once you have posted a change - cached or otherwise, the oldvalue is no longer accessible. The only exception is if you call CancelUpdates (before ApplyUpdates)  in which case all pending cached updates are reversed. ---------------------------------------------------------------------- incendio · 2022-02-23 02:53 So OldValue only valid in edit mode. How about in insert mode, is it possible to catch the old value? If user input data on the field and edit the data again, before user posted, can we catch the old value? The sequence could be like these : No   OldValue   NewValue ==  ======  ====== 1      empty      abc 2      abc          cde 3      cde          efg ---------------------------------------------------------------------- Zvoni · 2022-02-23 07:00 Use a Trigger to write the OldValues in its own table, and process them from there? ---------------------------------------------------------------------- incendio · 2022-02-23 07:20 Using trigger and save old value in its own table require lots of process, I rather use other method if this Old Value not applicable in insert mode. ---------------------------------------------------------------------- Zvoni · 2022-02-23 07:29 Maybe this?: https://stackoverflow.com/questions/4338077/is-it- possible-to-use-arguments-with-firebird-events-post-event-event-name Look at second answer with context variable ---------------------------------------------------------------------- incendio · 2022-02-23 08:24 I am afraid it's not the solution. I just want to search id from a database based on the code input by users. For example, after users input a code : abc then pressed ENTER, the app will search the code abc in the database. Problem is, when users just pressed ENTER without changing the code, app will always search to database. If app can catch the old value and compare to new value, it doesn't need to search database if no change in the value. ---------------------------------------------------------------------- rvk · 2022-02-23 08:44 Quote from: incendio on February 23, 2022, 09:24:40 amI just want to search id from a database based on the code input by users. Wait... where does the user enter that code? Is that a database TDBEdit? In that case you are CHANGING the database. You are not searching for a record. If you just want the user to search for something you should use a TEdit. And that has nothing to do with a database and there is also no OldValue. Also... then you search that record and find it and locate it (so the current record is that found record) then when the user presses enter again you can just check if the current record holds that same value as the TEdit value. Or am I missing something? If so, please explain a bit more about which components you are using and how they interact with the user. ---------------------------------------------------------------------- incendio · 2022-02-23 09:01 Users input the code in a tdbgrid with has a dataset, but this inputted code is not really change the value of the table, because this code is not saved in the table. The code is not searched in the same table, but search in different table. Here are the sample of tables structure : T_Detail (id int, id_gd int) -> this is the table for grid's dataset. M_Gd (id int, cd varchar(20)) -> this is the table to search the code inputted by users. ---------------------------------------------------------------------- rvk · 2022-02-23 09:24 Quote from: incendio on February 23, 2022, 10:01:52 amUsers input the code in a tdbgrid with has a dataset, but this inputted code is not really change the value of the table, because this code is not saved in the table. Mmm. Then why is that a dataset if the user-input isn't going to be saved? Or is the T_Detail a list of possible values in M_Gd and the user SELECTS one of the records in T_Detail??? In that case it's a whole other story. You have a few possible coices. 1) You can save the value in OnEnter and check it each time. When you do a search you can reset the old value to the just searched text. That would be easiest to implement in your current code. 2) If the T_Detail is a set of possible key values of M_Gd (in that case it's a really strange name because normaly it's the other way around) then you can make a Master-Detail relation with 2 DBGrids. In that case, when you scroll though DB1, the records matching in DB2 will be displayed automatically. Although in that case DB2 will only show that record(s) and not all other records. 3) You can create the master-detail element yourself by using DB1.OnScroll and automatically search/locate the correct record in DB2. In that case the searching is instant (during scrolling of DB1). That could be slow depending on you datasets (so you need to figure out if that's what you want). But thinking of it... what exactly is the problem at this moment? If you have typed a value in the first grid, and you searched it in the second... you can just check if you are already on that record, can't you? 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 TFrm.GrdDtKeyDown(Sender: TObject; var Key: Word;  Shift: TShiftState);begin  if Key = 13 then  begin    if GrdDt.SelectedField.Name ='Dt2CD' then    begin      GrdDt.SelectedField  := Dt2QTY;      if Dt2CD.NewValue <> VALUE_FROM_DB2_RECORD then      begin         YOU NEED TO SEARCH      end;    end;  end;end; ---------------------------------------------------------------------- dseligo · 2022-02-23 09:51 Quote from: incendio on February 23, 2022, 10:01:52 amUsers input the code in a tdbgrid with has a dataset, but this inputted code is not really change the value of the table, because this code is not saved in the table. But if inputted code doesn't change value, then it isn't inserted either thus you can't have old and new values. Even if it would been possible at insert time. ---------------------------------------------------------------------- incendio · 2022-02-23 09:58 Quote from: rvk on February 23, 2022, 10:24:52 amQuote from: incendio on February 23, 2022, 10:01:52 amUsers input the code in a tdbgrid with has a dataset, but this inputted code is not really change the value of the table, because this code is not saved in the table. Mmm. Then why is that a dataset if the user-input isn't going to be saved? Don't need to save the value of the code. Only its id need to be saved. When cursor is in the first column of the grid, users input the code and pressed ENTER to move cursor to the second column. After ENTER pressed, app search the value of the code from table M_Gd, if found, app input the id of table M_Gd into field id_gd in table T_Detail. The problem is, without doing comparison of old value and new value, when cursor on the first column of the grid and users pressed ENTER without changing the value of the code, app will always search the code into table M_Gd. Of course, it's possible to save the old value of code into different table, but since TIBDataset already have property OldValue, probably I could use it without a need to use different table. ---------------------------------------------------------------------- rvk · 2022-02-23 10:58 Quote from: incendio on February 23, 2022, 10:58:29 amThe problem is, without doing comparison of old value and new value, when cursor on the first column of the grid and users pressed ENTER without changing the value of the code, app will always search the code into table M_Gd.There are multiple ways to detect if it's changed. But in what kind of field is the user typing? In a cell of a TDBGrid? In that case your dataset will change to State Editing. If the user does not change the code, the State should still be Browsing. If you would save the code then it would all become much easier. Is the user typing in the field for id or is there a separate code column? It's all a bit confusing. Could you post some screenshots and explain how the user interface works. (I'm starting to think it's all a bit complicated from a users point of view and that there are easier ways to accomplish what you want.) ---------------------------------------------------------------------- incendio · 2022-02-23 11:02 I will post a screen shot later, right now I not working on the computer. ---------------------------------------------------------------------- tonyw · 2022-02-23 11:19 Quote from: incendio on February 23, 2022, 03:53:24 amSo OldValue only valid in edit mode. How about in insert mode, is it possible to catch the old value? If user input data on the field and edit the data again, before user posted, can we catch the old value? In "insert mode" no. There is no old value to return. Only after you post and then change to the editing state is a potential old value available. The TField OldValue/Newvalue is really no more than the same functionality that you get with a database update trigger - and probably better done by an update trigger. I guess that it dates back to Delphi 1 and support of simple paradox databases. ---------------------------------------------------------------------- rvk · 2022-02-23 11:48 Quote from: tonyw on February 23, 2022, 12:19:05 pmThe TField OldValue/Newvalue is really no more than the same functionality that you get with a database update trigger - and probably better done by an update trigger. Does IBX implement OldValue? Because FPC does not (or it changed without changing the docs). https://www.freepascal.org/docs-html/fcl/db/tfield.oldvalue.html QuoteOldValue returns the value of the field prior to an edit operation. This feature is currently not supported in FPC. ---------------------------------------------------------------------- tonyw · 2022-02-23 12:00 Quote from: rvk on February 23, 2022, 12:48:27 pmQuote from: tonyw on February 23, 2022, 12:19:05 pmThe TField OldValue/Newvalue is really no more than the same functionality that you get with a database update trigger - and probably better done by an update trigger. Does IBX implement OldValue? Because FPC does not (or it changed without changing the docs). https://www.freepascal.org/docs-html/fcl/db/tfield.oldvalue.html QuoteOldValue returns the value of the field prior to an edit operation. This feature is currently not supported in FPC.The FCL-DB package does support OldValue and NewValue as TField properties ( I assume for Delphi compatibility). I guess that the remark in the documentation applies to FCL DB drivers. IBX does support OldValue and NewValue. The code is 100% Borland legacy and I had  to look it up when the question was asked. But it should work. When a dataset row is being edited, IBX keeps a copy of the original buffer so that it can implement the Cancel method. When you request an OldValue TIBCustomDataSet.GetActiveBuf is used to return the oldbuffer rather than the current buffer and hence the reader gets the original value rather than any updated version. Quite neat really. ---------------------------------------------------------------------- incendio · 2022-02-24 03:23 Quote from: rvk on February 23, 2022, 11:58:35 am It's all a bit confusing. Could you post some screenshots and explain how the user interface works. (I'm starting to think it's all a bit complicated from a users point of view and that there are easier ways to accomplish what you want.) Attached is the screen shot. The cursor is in the first cell of the grid. This is the cell when users input a code.When input finished, users press ENTER, app search the code inputted by users in table M_GDIf Code found, Column Name & unit will be automatically filled and cursor moved to column Qty When cursor in column code and users pressed ENTER without changing the value, I don't want app to search the code in table M_GD. I only want app to search the code if there is a value changed in code. ---------------------------------------------------------------------- incendio · 2022-02-24 03:31 Quote from: tonyw on February 23, 2022, 01:00:26 pmQuote from: rvk on February 23, 2022, 12:48:27 pmQuote from: tonyw on February 23, 2022, 12:19:05 pmThe TField OldValue/Newvalue is really no more than the same functionality that you get with a database update trigger - and probably better done by an update trigger. Does IBX implement OldValue? Because FPC does not (or it changed without changing the docs). https://www.freepascal.org/docs-html/fcl/db/tfield.oldvalue.html QuoteOldValue returns the value of the field prior to an edit operation. This feature is currently not supported in FPC.The FCL-DB package does support OldValue and NewValue as TField properties ( I assume for Delphi compatibility). I guess that the remark in the documentation applies to FCL DB drivers. IBX does support OldValue and NewValue. The code is 100% Borland legacy and I had  to look it up when the question was asked. But it should work. When a dataset row is being edited, IBX keeps a copy of the original buffer so that it can implement the Cancel method. When you request an OldValue TIBCustomDataSet.GetActiveBuf is used to return the oldbuffer rather than the current buffer and hence the reader gets the original value rather than any updated version. Quite neat really. Thanks for the clarification. I will use another method to catch Old Value. Actually, the solution is very simple. I found that, on TDBGrid/TDBEdit, the value of a field does not changed until the cursor moved to the next TDBEdit or next cell ( in case of TDBGrid). So I codes these to catched old value in 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 TInpInOtFrm.GrdDtKeyDown(Sender: TObject; var Key: Word;  Shift: TShiftState);Var  PrevVal : string;begin  if Key = 13 then  begin    if GrdDt.SelectedField.Name ='Dt2CD' then     begin      PrevVal := Dt2CD.AsString; // Get Old Value      GrdDt.SelectedField := Dt2QTY; // Moved cursor to next cell, Dt2CD.AsString will contains new value    if Dt2CD.AsString <> PrevVal then SearchGdCd();    end;end; ---------------------------------------------------------------------- rvk · 2022-02-24 09:18 Quote from: incendio on February 24, 2022, 04:31:13 amI found that, on TDBGrid/TDBEdit, the value of a field does not changed until the cursor moved to the next TDBEdit or next cell ( in case of TDBGrid). Indeed. (and if you explained your exact program in the beginning this would have been pointed out before Something like I explained in #9  ) Glad you've got it working. ---------------------------------------------------------------------- tonyw · 2022-02-24 10:32 Quote from: incendio on February 24, 2022, 04:31:13 amThanks for the clarification. I will use another method to catch Old Value. Actually, the solution is very simple. I found that, on TDBGrid/TDBEdit, the value of a field does not changed until the cursor moved to the next TDBEdit or next cell ( in case of TDBGrid). Yes. That's how it works. The Cell Editor puts the row into the editing state as soon as it appears. However, it does not push a change through to the database until its "EditingDone" method is called - typically when you move to another cell or press the Enter key. The TDBGrid itself is really only a static display of row values. When you edit a cell, the cell editor becomes visible, is located over the cell you want to edit and is given the current cell value (you are not really editing the cell, that is just an illusion). You then edit the displayed value, as required, and once the edited value is saved to the database, the cell editor disappears and the underlying cell is refreshed with the newly written value in the database. You can write your own cell editors or customise an existing one and you may find that to get all the functionality you want, your best bet is to customise the cell editor. In your case, I assume that this will be the TStringCellEditor (Grids unit). You can use the TDBGrid's OnSelectEditor method to tell it to use your overridden version rather than the default, and on a cell by cell basis. ---------------------------------------------------------------------- incendio · 2022-02-25 01:48 Thanks guys for all your help. @tonyw, This is the first time I use TDBGrid for editing purpose. For now, I will just settle for my simple solution. Should a needs a rise in the future, I definitely follow your hints.