TBufDataset is behaving in a very odd way from Release to Debug, and going boom!

[Gustavo 'Gus' Carreno]

TBufDataset is behaving in a very odd way from Release to debug, and going boom!

Hey Y'all!!

I'm playing around with in memory tables.
Had a wee Google and found this: How to write in-memory database applications in Lazarus/FPC.

So I went and started by playing with TMemDataset: Test Memory Dataset.

So, with confidence from the above Lazarus Wiki entry, I proceeded to do the exact same test app but using TBufDataset.

Immediately I found myself in trouble because:

• Build Mode Default: After the first push of the Add Data button inserts 3 rows, no data shows and on a subsequent push of the same button we get an AV right on the first Post, as you can see on image 1.
• Build Mode Debug: The app does not give us an AV, but the data shown in the DBGrid is rather suspicious, as you can see on image 2. And in image 3 you see the very top of the humongous heaptrc log produced.
• Build Mode Release: Behaves the same way as Build Mode Default.

I'm completely baffled by this whole thing and I come here, very humbled, to ask for your, very appreciated, help.

I'm attaching the code of the buggy TestBufferDataset attempt.

Many, oh so, many thanks in advance!!

EDIT: I forgot to mentions that I tested this on an Ubuntu 20.04 64b with both Lazarus 2.2.6 and main(aka trunk)

Cheers,
Gus
--
Image 1:
(https://pasteboard.co/EzyWaNkWt11K.png)

Image 2:
(https://pasteboard.co/CpAFTaL63Dlc.png)

Image 3:
(https://pasteboard.co/etyXISEgRjVt.png)

[rvk]

Creating the TBufDataset in code seems to work better.
I suspect that defining the fields in the Object Inspector is the problem.

Code: Pascal  [Select][+][-]

1. procedure TfrmMain.FormCreate(Sender: TObject);
2. //...
3.   bdsAccounts := TBufDataset.Create(nil);
4.   bdsAccounts.FieldDefs.Add('HASH', ftString, 50);
5.   bdsAccounts.FieldDefs.Add('Alias', ftString, 50);
6.   bdsAccounts.FieldDefs.Add('Label', ftString, 50);
7.   bdsAccounts.FieldDefs.Add('Pending', ftCurrency, 0, 15, false, false, 4, 0);
8.   bdsAccounts.FieldDefs.Add('Balance', ftCurrency, 0, 15, false, false, 5, 0);
9.   bdsAccounts.CreateDataset;
10.   bdsAccounts.Open;
11.  
12.   dsAccounts.DataSet := bdsAccounts;
13.   dsAccounts.Enabled := true;

(On Windows it also gives strange results when doing it design-time.)

[wp]

Is there a reason why you did not set the BufDataset's Filename? I am not sure if it is possible at all to use TBufdataset without a file, buf even if it is you will lose all records when the application terminates.

Second issue: persistent fields. Once you have persistent fields you cannot access fields by FieldByName[fieldname], but only by their variable name, e.g. bdsAccountsBalance rather than bdsAccounts.FieldByName['Balance']. An annoyance which I already hated in my Delphi times...

I deleted the persistent fields from the lfm file, added a Filename to the bdsAccounts dataset and made sure that the dataset is created properly - now the application does not crash any more:

Code: Pascal  [Select][+][-]

1. procedure TfrmMain.FormCreate(Sender: TObject);
2. begin
3.   Caption:= Format('%s v%s', [ Application.Title, cVersion ]);
4.   InitShortcuts;
5.   Application.OnHint:= @DisplayHint;
6.   bdsAccounts.FileName := 'test.db';
7.   if not FileExists(bdsAccounts.FileName) then
8.     bdsAccounts.CreateDataset;
9.   bdsAccounts.Open;
10. end;

Still... Clear does not seam to work. It clears the dataset in the current session, but when the app starts another time the data are back again.

Filtering seems to crash the application still. Well - I would not execute the filter in the OnChange event of the edit control, I would add a button to toggle bdsAccounts.Filtered and use the edit filter text once it is completed. This works, but I don't understand why your code report "Index based on unknown field" while typing the condition...

[paweld]

As @rvk wrote define dataset in code and:
1. for DBGrid, you don't need to call BeginUpdate and EndUpdate - everything happens automatically when you change DataSet. when doing a lot of data editing, it is recommended to use DisableControls and EnableCotrols for DataSet   
2. using the Clear medtode for a DataSet will clear not only the data but also the field definitions
3. currency has precision to 4 decimal places

Code: Pascal  [Select][+][-]

1. unit Forms.Main;
2.  
3. {$mode objfpc}{$H+}
4.  
5. interface
6.  
7. uses
8.   Classes, SysUtils, BufDataset, DB, Forms, Controls, Graphics, Dialogs, Menus, ActnList, ComCtrls, StdActns, ExtCtrls, StdCtrls, DBGrids;
9.  
10. type
11.  
12.   { TfrmMain }
13.  
14.   TfrmMain = class(TForm)
15.     actAccountsAddData: TAction;
16.     actAccountsClearData: TAction;
17.     alMain: TActionList;
18.     actFileExit: TFileExit;
19.     btnAccountsAddData: TButton;
20.     btnAccountsClearData: TButton;
21.     bdsAccounts: TBufDataset;
22.     dsAccounts: TDataSource;
23.     dgAccounts: TDBGrid;
24.     edtAccountsFilterByAlias: TEdit;
25.     gbFilter: TGroupBox;
26.     lblFilterByAlias: TLabel;
27.     mnuFile: TMenuItem;
28.     mnuFileExit: TMenuItem;
29.     mmMain: TMainMenu;
30.     panButtons: TPanel;
31.     sbMain: TStatusBar;
32.     procedure FormCreate(Sender: TObject);
33.     procedure alMainUpdate(AAction: TBasicAction; var Handled: Boolean);
34.     procedure actAccountsAddDataExecute(Sender: TObject);
35.     procedure actAccountsClearDataExecute(Sender: TObject);
36.     procedure edtAccountsFilterByAliasChange(Sender: TObject);
37.     procedure FormDestroy(Sender: TObject);
38.   private
39.     procedure InitShortcuts;
40.     procedure DisplayHint(Sender: TObject);
41.     procedure CreateDBDef;
42.   public
43.  
44.   end;
45.  
46. var
47.   frmMain: TfrmMain;
48.  
49. implementation
50.  
51. uses
52.   LCLType
53. ;
54.  
55. const
56.   cVersion = '0.1.0';
57.  
58. {$R *.lfm}
59.  
60. { TfrmMain }
61.  
62. procedure TfrmMain.FormCreate(Sender: TObject);
63. begin
64.   Caption:= Format('%s v%s', [ Application.Title, cVersion ]);
65.   InitShortcuts;
66.   Application.OnHint:= @DisplayHint;
67.   CreateDBDef;
68.   bdsAccounts.Open;
69. end;
70.  
71. procedure TfrmMain.InitShortcuts;
72. begin
73. {$IFDEF UNIX}
74.   actFileExit.ShortCut := KeyToShortCut(VK_Q, [ssCtrl]);
75. {$ENDIF}
76. {$IFDEF WINDOWS}
77.   actFileExit.ShortCut := KeyToShortCut(VK_X, [ssAlt]);
78. {$ENDIF}
79. end;
80.  
81. procedure TfrmMain.DisplayHint(Sender: TObject);
82. begin
83.   sbMain.SimpleText:= GetShortHint(Application.Hint);
84. end;
85.  
86. procedure TfrmMain.CreateDBDef;
87. var
88.   i: Integer;
89. begin
90.   bdsAccounts.Clear;
91.   bdsAccounts.FieldDefs.Add('HASH', ftString, 50);
92.   bdsAccounts.FieldDefs.Add('Alias', ftString, 50);
93.   bdsAccounts.FieldDefs.Add('Label', ftString, 50);
94.   bdsAccounts.FieldDefs.Add('Pending', ftCurrency);
95.   bdsAccounts.FieldDefs.Add('Balance', ftCurrency);
96.   bdsAccounts.IndexDefs.Add('idx_hash', 'HASH', [ixCaseInsensitive]);
97.   bdsAccounts.CreateDataset;
98.   bdsAccounts.IndexFieldNames := 'HASH';
99.   for i := 0 to bdsAccounts.Fields.Count - 1 do
100.   begin
101.     if bdsAccounts.Fields[i].DataType = ftCurrency then
102.       TNumericField(bdsAccounts.Fields[i]).DisplayFormat:= '#,##0.0000 N';
103.   end;
104. end;
105.  
106. procedure TfrmMain.alMainUpdate(AAction: TBasicAction; var Handled: Boolean);
107. begin
108.   if AAction = actAccountsClearData then
109.   begin
110.     actAccountsClearData.Enabled:= bdsAccounts.RecordCount > 0;
111.     Handled:= True;
112.   end;
113. end;
114.  
115. procedure TfrmMain.actAccountsAddDataExecute(Sender: TObject);
116. begin
117.   bdsAccounts.Insert;
118.   bdsAccounts.FieldByName('HASH').AsString:= 'N2983096817236';
119.   bdsAccounts.FieldByName('Alias').AsString:= 'Alias 1';
120.   bdsAccounts.FieldByName('Label').AsString:= 'Label 1';
121.   bdsAccounts.FieldByName('Pending').AsCurrency:= 0.0;
122.   bdsAccounts.FieldByName('Balance').AsCurrency:= 1000.0;
123.   bdsAccounts.Append;
124.  
125.   bdsAccounts.Insert;
126.   bdsAccounts.FieldByName('HASH').AsString:= 'N2983096817696';
127.   bdsAccounts.FieldByName('Alias').AsString:= 'Alias 2';
128.   bdsAccounts.FieldByName('Label').AsString:= 'Label 2';
129.   bdsAccounts.FieldByName('Pending').AsCurrency:= 0.0;
130.   bdsAccounts.FieldByName('Balance').AsCurrency:= 2000.0;
131.   bdsAccounts.Append;
132.  
133.   bdsAccounts.Insert;
134.   bdsAccounts.FieldByName('HASH').AsString:= 'N2983696817236';
135.   bdsAccounts.FieldByName('Alias').AsString:= 'Alias 3';
136.   bdsAccounts.FieldByName('Label').AsString:= 'Label 3';
137.   bdsAccounts.FieldByName('Pending').AsCurrency:= 0.0;
138.   bdsAccounts.FieldByName('Balance').AsCurrency:= 3000.0;
139.   bdsAccounts.Append;
140.  
141.   bdsAccounts.Last;
142.  
143.   dgAccounts.AutoAdjustColumns;
144. end;
145.  
146. procedure TfrmMain.actAccountsClearDataExecute(Sender: TObject);
147. begin
148.   bdsAccounts.DisableControls;
149.   bdsAccounts.First;
150.   while not bdsAccounts.EOF do
151.     bdsAccounts.Delete;
152.   bdsAccounts.EnableControls;
153. end;
154.  
155. procedure TfrmMain.edtAccountsFilterByAliasChange(Sender: TObject);
156. begin
157.   if Trim(edtAccountsFilterByAlias.Text) = '' then
158.     bdsAccounts.Filtered := False
159.   else
160.   begin
161.     bdsAccounts.FilterOptions:= [foCaseInsensitive];
162.     bdsAccounts.Filter:= Format('Alias=''*%s*''', [Trim(edtAccountsFilterByAlias.Text)]);
163.     bdsAccounts.Filtered:= True;
164.   end;
165. end;
166.  
167. procedure TfrmMain.FormDestroy(Sender: TObject);
168. begin
169.   bdsAccounts.Close;
170. end;
171.  
172. end.  
173.  

[wp]

Trying to keep the persistent fields... When they exist they must be used in the following way:

Code: Pascal  [Select][+][-]

1. procedure TfrmMain.actAccountsAddDataExecute(Sender: TObject);
2. begin
3.   actAccountsAddData.Enabled:= False;
4.   Application.ProcessMessages;
5.  
6.   dgAccounts.BeginUpdate;
7.  
8.   bdsAccounts.Append;
9.   bdsAccountsHASH.AsString:= 'N2983096817236';  // instead of bdsAccounts.FieldByName('HASH').AsString := ...
10.   bdsAccountsAlias.AsString:= 'Alias 1';
11.   bdsAccountsLabel.AsString:= 'Label 1';
12.   bdsAccountsPending.AsCurrency:= 0.0;
13.   bdsAccountsBalance.AsCurrency:= 1000.0;
14.   bdsAccounts.Post;
15.  
16.   bdsAccounts.Append;
17.   bdsAccountsHASH.AsString:= 'N2983096817696';
18.   bdsAccountsAlias.AsString:= 'Alias 2';
19.   bdsAccountsLabel.AsString:= 'Label 2';
20.   bdsAccountsPending.AsCurrency:= 0.0;
21.   bdsAccountsBalance.AsCurrency:= 2000.0;
22.   bdsAccounts.Post;
23.  
24.   bdsAccounts.Append;
25.   bdsAccountsHASH.AsString:= 'N2983696817236';
26.   bdsAccountsAlias.AsString:= 'Alias 3';
27.   bdsAccountsLabel.AsString:= 'Label 3';
28.   bdsAccountsPending.AsCurrency:= 0.0;
29.   bdsAccountsBalance.AsCurrency:= 3000.0;
30.   bdsAccounts.Post;
31.  
32.   dgAccounts.AutoAdjustColumns;
33.   dgAccounts.EndUpdate(True);
34.  
35.   Application.ProcessMessages;
36.   actAccountsAddData.Enabled:= True;
37. end;  

After having added a filename to bdsAccounts and creating the dataset at first run (like in my other reply), and pressing "Add data" I do get the same empty rows and the crash at the end, as before.

For the next test, I ran the application and closed it immediately afterwards (of course, after having deleted the created database file for a new fresh start). This way an empty database file is created. When I now run it again and click "Add data", the records are added correctly, and the crash at the end does not occur either.

[wp]

In order to clear the table replace the bdsAccounts.Clear by the following lines:

Code: Pascal  [Select][+][-]

1.   bdsAccounts.First;
2.   while not bdsAccounts.EoF do
3.     bdsAccounts.Delete;

[wp]

Returning to the persistent fields... I add some DebugLn instructions to the application after opening the dataset to print the FieldNo of the fields:

Code: Pascal  [Select][+][-]

1. procedure TfrmMain.FormCreate(Sender: TObject);
2. var
3.   i: Integer;
4. begin
5.   ...
6.   bdsAccounts.Open;
7.   for i := 0 to bdsAccounts.FieldCount-1 do
8.     DebugLn([bdsAccounts.Fields[i].FieldNo, ', ', bdsAccounts.Fields[i].FieldName]);  
9. end;

When application starts for the first time, i.e. without an existing database file, the output always lists FieldNo = 0 - which looks very wrong because FieldNo is an essential ingredient within the database management code. And in fact, when the application is run for the second time (when the database file already exists) the FieldNo values are as expected: 1, 2, 3, ...

I copied the bufdsdataset unit from the FPC installation over to my test project (in order to be able to edit it) and added "BindFields(true)" to "TCustomBufDataset.InternalOpen" (before searching for the AutoInc field) - now the issue is gone, and the application works now even without a filename and without CreateDataset.

I am not sure whether the position where I added BindFields is the correct one. But I think it is a good hint for a bug report which I will prepare next.

[EDIT] Bug report: https://gitlab.com/freepascal.org/fpc/source/-/issues/40450

[Gustavo 'Gus' Carreno]

Hey WP,

First and foremost, thank you so very much for taking the time to suss things out!!
Really appreciate it.

As a preamble: I was just playing around with things under the impression that TBufDataset was gonna be the same beast as TMemDataset, but with internal changes only, not the polar opposite of it.

In this context, I tried to transfer what I learned with TMemDataset, which was easy, simple and worked right out of the box, to TBufDataset and the thing was like nitroglycerine without the stabilising compound that turns it into dynamite. It went boom at the slightest touch.

Is there a reason why you did not set the BufDataset's Filename?

More than one:

• The wiki article for TMemDataset did not mention the need for it, but I do realise that the section dedicated to TBufDataset was just focusing on sorting, and nothing more.
• Again, TMemDataset worked ok without it.
• My main purpose for this whole endeavour of testing TMemDataset, TBufDataset, TRxMemoryData and ZMSQL is to not rely on, what I'm assuming should only be memory only and volatile by nature, a file or database engine, but to have the Dataset holding the data, a visual component that has way better features than a TStringGrid and some small piece of code to make filter and sorting happen.

I want the persistence of the data to be some custom, black box, thing that I can create, re-create and have my mind changed on how to read/write it every time I need to display the data.
So, in this context, having a file on the TBufDataset, made no sense and because TMemDataset did not have an issue with it's absence, I guess I made a bad assumption.

I am not sure if it is possible at all to use TBufdataset without a file, buf even if it is you will lose all records when the application terminates.

That does not have any impact, the data will come and be persisted by other means.

Second issue: persistent fields. Once you have persistent fields you cannot access fields by FieldByName[fieldname], but only by their variable name, e.g. bdsAccountsBalance rather than bdsAccounts.FieldByName['Balance']. An annoyance which I already hated in my Delphi times...

Okies, that's going on the notepad for future reference, many, many thanks!!

I deleted the persistent fields from the lfm file, added a Filename to the bdsAccounts dataset and made sure that the dataset is created properly - now the application does not crash any more:

Code: Pascal  [Select][+][-]

1. procedure TfrmMain.FormCreate(Sender: TObject);
2. begin
3.   Caption:= Format('%s v%s', [ Application.Title, cVersion ]);
4.   InitShortcuts;
5.   Application.OnHint:= @DisplayHint;
6.   bdsAccounts.FileName := 'test.db';
7.   if not FileExists(bdsAccounts.FileName) then
8.     bdsAccounts.CreateDataset;
9.   bdsAccounts.Open;
10. end;

These things should either be better documented, or raise an exception if it impacts the more simplistic operation I'm aiming, wouldn't you agree?

Still... Clear does not seam to work. It clears the dataset in the current session, but when the app starts another time the data are back again.

This Clear was a naive attempt to mimic TMemDataset.
Even the Lazarus wiki mentions that a clear is too slow and it provides a quicker solution, a bit hefty on the code, hence me being a bit lazy and tried it the easy and less optimal way.
And the loss of data between sessions is of no consequence.

Filtering seems to crash the application still. Well - I would not execute the filter in the OnChange event of the edit control, I would add a button to toggle bdsAccounts.Filtered and use the edit filter text once it is completed. This works, but I don't understand why your code report "Index based on unknown field" while typing the condition...

The filtering has a blatant error due to my ignorance of the syntax needed.
After I posted this, I progressed in doing my tests with TRxMemoryData, and after some head butting with the wall I realized that I need:

Code: Pascal  [Select][+][-]

1. DataSet.Filter:= 'Alias="*<value or partial>*"';

And with this, it now works.
I've always struggled with the many syntaxes(SQL with the Like and the % and never knowing if I should quote the value or not) for this filtering since there is not a place where this is discussed in any docs or examples that I've stumbled upon!
There is a blatant lack of documentation on the correct syntax parsed in this Filter property, that's for sure!!

Ok, summarising your findings:

• Have a filename on the respective property
• Do not use the FieldByName way
• Maybe not use the Fields, just the FieldDefs. (Is this what you mean by persistent fields?)

Am I missing something?

Nonetheless, oh so many thanks for the time you've spent on this !!!

Cheers,
Gus

[rvk]

I don't think TBufDataset.Filename is (or should be) mandatory.

I used it for in-mem database where there is no need to save to a file.
(But I created everything in code.)

The problems of using FieldByName when also having persistent fields is new to me.
(But I never use persistent fields anyway)

[Gustavo 'Gus' Carreno]

Hey Pawel,

First of all, many many thanks for the time you spent on this !!!
It is really appreciated !!

As @rvk wrote define dataset in code and:

This for me is a bit hard to swallow. Mainly due to the fact that the component has the functionality to be manipulated via the Designer.
If that makes it go boom by design, then probably not allow the manipulation via the IDE?
Makes no sense to have it RAD just to explode in your face when used, amirite?!

1. for DBGrid, you don't need to call BeginUpdate and EndUpdate - everything happens automatically when you change DataSet. when doing a lot of data editing, it is recommended to use DisableControls and EnableCotrols for DataSet   

That makes quite a lot of sense, thanks very much for the necessary schooling

2. using the Clear medtode for a DataSet will clear not only the data but also the field definitions

Yeah, that was a lame ass and lazy attempt on my part 

3. currency has precision to 4 decimal places

The default from the Designer is actually 15, witch is even a bit much for what I need: 8.
Actually, those fields should be uInt64 and then manipulated to have decimal place between the 9th and 8th from the right.
It's a whole thing in crypto not to use Floating point arithmetic due to the loss of data, which in any currency context is not acceptable.

And I think that even negative values are not, kinda, used.
A transaction is always abs and you can prepend a negative character when displaying on the Sender's side and a plus sign on the Receiver's side.
Using uInt64 makes sense, since it's the max a current register is capable of manipulating in one go.
And, like previously stated, never use Floating Point arithmetic !!

Again, many thanks for adding code to better explain your meaning!!

Cheers,
Gus

[Gustavo 'Gus' Carreno]

Hey WP,

Trying to keep the persistent fields... When they exist they must be used in the following way:

Okies, also jotted down on the notepad

After having added a filename to bdsAccounts and creating the dataset at first run (like in my other reply), and pressing "Add data" I do get the same empty rows and the crash at the end, as before.

For the next test, I ran the application and closed it immediately afterwards (of course, after having deleted the created database file for a new fresh start). This way an empty database file is created. When I now run it again and click "Add data", the records are added correctly, and the crash at the end does not occur either.

Ok, looks like the key is actually having a file.

I think my main problem stems from the fact that:

• I was completely ignorant of what the CreateDataset did in reality, even if it is one of the option when you Right-Click on it in the Designer
• The whole thing worked fine in TMemDataset

Ok, I can amend my previous list to be just:

• Have a filename on the respective property
• Do not use the FieldByName way

Thanks my good friend, this helps a lot!!!

Cheers,
Gus

[Gustavo 'Gus' Carreno]

Hey WP,

I am not sure whether the position where I added BindFields is the correct one. But I think it is a good hint for a bug report which I will prepare next.

This was my hope, that one of you more versed in the guts of these components had the opportunity to chase that eluding bug.

Like I said previously to Pawel, it made no sense that the component had all the bells and whistles to work in RAD, to just explode in your face in such a strange way between Default/Release and Debug when you use it.

Much, MUCH appreciation for your time, and patience, to get into a bug report !!

Cheers,
Gus

[Gustavo 'Gus' Carreno]

Hey rvk,

I don't think TBufDataset.Filename is (or should be) mandatory.

I could not agree more with this. After all, the "product" IS advertised as an in memory thingamaboob, so why the mandatory need of a file, right?

I used it for in-mem database where there is no need to save to a file.
(But I created everything in code.)

Yeah, I kinda was lazy and used the RAD option that is clearly something that should be supported since it allows us to use it.

The problems of using FieldByName when also having persistent fields is new to me.
(But I never use persistent fields anyway)

Yeah, that also tickles me a bit. But looks like WP has been annoyed by that lill detail ever since his Delphi time, so chalk it to "seniority", I guess?

As a disclaimer, my main aim for all this testing was to evaluate a visual thing that I could use instead of the very work intense TStringGrid to achieve data auto refresh, filtering and sorting.
Hence me doing the whole thing via the RAD path.
I was quite happy with TMemDataset, but a bit sad for the lack of sorting. So after evaluating it, I went on to TBufDatset under the little information provided by the mentioned Wiki entry.
Looks like it was a good path, since from the looks of it, there were bugs hiding in the shadows

Can not thank you enough for the time and patience you devoted to chasing this alongside me!!

Cheers,
Gus

[wp]

Now that the bugreport has been sent (https://gitlab.com/freepascal.org/fpc/source/-/issues/40450) let me correct my wrong statements made during this thread:

• TBufDataset does NOT need a filename
• Persistent fields DO work (the reason why I don't like them is that it is a pain to rename fields once persistent fields are created).
• Is is NOT necessary to call CreateDataset for a bufdataset with persistent fields.

Gus, do I understand correctly that the Filter works for you. So, I do not need to look into this issue?

[Gustavo 'Gus' Carreno]

Hey WP,

In order to clear the table replace the bdsAccounts.Clear by the following lines:

Code: Pascal  [Select][+][-]

1.   bdsAccounts.First;
2.   while not bdsAccounts.EoF do
3.     bdsAccounts.Delete;

From the wiki article, this is not the best way to do it, while it is a possibility.

They have a 20+ LoC of an example on how to just create a new dataset, fill in the fields and just return it after freeing the old one.
Makes sense, and I'll look into it. But to be honest, the need of a clear is just for the that test context.
In my particular need, the clear will not be needed... I think

Cheers,
Gus