TBufDataset affected IBX?

[incendio]

Hi guys,

I have database application using IBX 2.5.0.9999 with Lazarus 2.2.2 on Windows 10.

App runs OK until I add TBufDataset into datamodule and do some processing with  it.

The error is :
invalid typecast

Here if the code that raised that error

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

1.   QIdFrmN.Close();
2.   QIdFrmN.ParamByName('IdDivLc').Value  := IdDivLc; // this code raised an error
3.   QIdFrmN.ParamByName('Yr').Value       := PUB_ACC_YR.ToInteger();
4.   QIdFrmN.ParamByName('Mn').Value       := PUB_ACC_MNTH.ToInteger();
5.   QIdFrmN.ParamByName('IdTrsTyp').Value := IdTrsTyp;
6.   QIdFrmN.Open();
7.  

I removed every codes related with TBufDataset, and app ran OK again.

Here are the codes with TBufDataset (named Vt)

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

1. procedure TDM.AddVt(DtTbl2 :TIBDataset );
2. begin
3.   Vt.Append();
4.   VtID_IN_OT1.Value   := DtTbl2.FieldByName('ID_IN_OT1').AsInteger;
5.   VtLN_N.Value        := DtTbl2.FieldByName('LN_N').AsInteger;
6.   VtID_GD.Value       := DtTbl2.FieldByName('ID_GD').AsInteger;
7.   VtQTY.Value         := DtTbl2.FieldByName('QTY').AsFloat;
8.   VtPRC.Value         := DtTbl2.FieldByName('PRC').AsFloat;
9.  
10.   if DtTbl2.FieldByName('QTY_PER_UNT').IsNull then VtQTY_PER_UNT.Value := 1
11.   else VtQTY_PER_UNT.Value := DtTbl2.FieldByName('QTY_PER_UNT').AsFloat;
12. end;
13.  
14. function TDM.FillVt(DtTbl2 :TIBDataset ) : smallint;
15. var
16.   LnN : smallint;
17.   Kg  : double;
18. begin
19.   LnN :=1;
20.   Vt.Close();
21.   Vt.Open();
22.  
23.   Kg :=0;
24.  
25.   DtTbl2.DisableControls();
26.  
27.   while not (DtTbl2.Eof) do
28.   begin
29.     AddVt(DtTbl2);
30.     Kg := Kg + (VtQTY.AsFloat * VtQTY_PER_UNT.AsFloat);
31.     if(DtTbl2.FieldByName('LN_N').AsInteger > LnN) then LnN := DtTbl2.FieldByName('LN_N').AsInteger;
32.     DtTbl2.Next();
33.   end;
34.  
35.   Vt.Edit;
36.   VtKG.Value:=Kg;
37.   if (Vt.State = dsEdit) or (Vt.State = dsInsert) then Vt.Post();
38.  
39.   DtTbl2.First();
40.   DtTbl2.EnableControls();
41.   Result := LnN +1;
42. end;
43.  
44. procedure TDM.DelVtRec(DtTbl2:TIBDataset);
45. begin
46.   Vt.First();
47.   if Vt.Locate('ID_IN_OT1;LN_N',VarArrayOf([DtTbl2.FieldByName('ID_IN_OT1').AsInteger, DtTbl2.FieldByName('LN_N').AsInteger]),[]) then Vt.Delete();
48. end;
49.  

How come TBufDataset affected IBX?

[rvk]

What type is IdDivLc in your program and why are you using param.value and not param.asinteger and param.asstring?

And are you sure your variables contain integers when you do PUB_ACC_YR.ToInteger()?
If not then this can also cause this error.

Don't use .value of you are unsure of is contents because if you assign it to an integer for example it gives an invalid typecast.

Ps. This has nothing to do with IBX. It's all about the use of variants (which .value is).

[incendio]

What type is IdDivLc in your program and why are you using param.value and not param.asinteger and param.asstring?

And are you sure your variables contain integers when you do PUB_ACC_YR.ToInteger()?
If not then this can also cause this error.

Don't use .value of you are unsure of is contents because if you assign it to an integer for example it gives an invalid typecast.

Ps. This has nothing to do with IBX. It's all about the use of variants (which .value is).

IdDivLc is integer, I am using param.value cause of habit, now change it to param.asinteger/asstring.

The error is not in PUB_ACC_YR, but this one : QIdFrmN.ParamByName('IdDivLc').Value  := IdDivLc;

Already deleted TBufDataset, add again to try your suggestion about param.asinteger, but got another error when set TBufDataset Active property to true :
missing underlaying dataset

This is the first time using TBufDataset, didn't see how to set underlaying dateset via Object Inspector.

Read this post
https://forum.lazarus.freepascal.org/index.php?topic=58616.0

Could it be TBufDataset have the same bug as that thread mentioned?

[wp]

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

1. procedure TDM.AddVt(DtTbl2 :TIBDataset );
2. begin
3.   Vt.Append();
4.   VtID_IN_OT1.Value   := DtTbl2.FieldByName('ID_IN_OT1').AsInteger;
5.   VtLN_N.Value        := DtTbl2.FieldByName('LN_N').AsInteger;
6.   VtID_GD.Value       := DtTbl2.FieldByName('ID_GD').AsInteger;
7.   VtQTY.Value         := DtTbl2.FieldByName('QTY').AsFloat;
8.   VtPRC.Value         := DtTbl2.FieldByName('PRC').AsFloat;
9.  
10.   if DtTbl2.FieldByName('QTY_PER_UNT').IsNull then VtQTY_PER_UNT.Value := 1
11.   else VtQTY_PER_UNT.Value := DtTbl2.FieldByName('QTY_PER_UNT').AsFloat;
12. end;

This code is supposed to add a new record to the Bufdataset (Vt.Append), but the record is not posted - add a Vt.Post at the end of the procedure. No idea if this could indirectly cause the reported error message... But it's a bug, definitely.

[incendio]

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

1. procedure TDM.AddVt(DtTbl2 :TIBDataset );
2. begin
3.   Vt.Append();
4.   VtID_IN_OT1.Value   := DtTbl2.FieldByName('ID_IN_OT1').AsInteger;
5.   VtLN_N.Value        := DtTbl2.FieldByName('LN_N').AsInteger;
6.   VtID_GD.Value       := DtTbl2.FieldByName('ID_GD').AsInteger;
7.   VtQTY.Value         := DtTbl2.FieldByName('QTY').AsFloat;
8.   VtPRC.Value         := DtTbl2.FieldByName('PRC').AsFloat;
9.  
10.   if DtTbl2.FieldByName('QTY_PER_UNT').IsNull then VtQTY_PER_UNT.Value := 1
11.   else VtQTY_PER_UNT.Value := DtTbl2.FieldByName('QTY_PER_UNT').AsFloat;
12. end;

This code is supposed to add a new record to the Bufdataset (Vt.Append), but the record is not posted - add a Vt.Post at the end of the procedure. No idea if this could indirectly cause the reported error message... But it's a bug, definitely.

If it's a bug, I will discard TBufdataset then.

[rvk]

If it's a bug, I will discard TBufdataset then.

I think that was meant as "It's a bug in your code" 

(not adding .Post when you want the record to be stored is considered a bug  )

But yeah, I've never worked with TBufDataset at designtime. Always only in runtime (were you define and create the fields and database yourself).

[wp]

If it's a bug, I will discard TBufdataset then.

No no - TBufDataset is fine. It's *your* bug.

[incendio]

If it's a bug, I will discard TBufdataset then.

No no - TBufDataset is fine. It's *your* bug.

Previously Vt variables in that code was TIBDataset type.

Without a post method, it worked OK, beside, on procedure FillVt, post method will be called if Vt state is in edit/insert mode.

Then I came a cross with Dataset in memory, with is TBufDataset, I thought, using this dataset, may be data loading could be faster than using TIBDataset, but since this problem, I am using TIBDataset again.

Perhaps, in future project will try TBufDataset again.

[CharlyTango]

Database sensitive components always use some kind of buffer mechanism. So you dom't need wo create a parallel structure with an extra TBufdataset.

e.g. the component set SQLDB (delivered with Lazarus) uses TBufDataset to buffer data.

I don't know exactly how TIBDataset does it but it should be quite similar

[wp]

Without a post method, it worked OK

Revisited my statement that Dataset.Append must always be accompanied by a Dataset.Post and made the attached test application: no, I was wrong - a dataset in Insert/Append mode is automatically posted when the user selects another records, even without explicitely calling Dataset.Post. But I definitely consider this as a bad programming practice - in particular in case of the big server database systems where usually a additional Dataset.ApplyUpdates and a Commit of the transaction are required to finally get a new record into the database.

[tonyw]

Database sensitive components always use some kind of buffer mechanism. So you dom't need wo create a parallel structure with an extra TBufdataset.

e.g. the component set SQLDB (delivered with Lazarus) uses TBufDataset to buffer data.

I don't know exactly how TIBDataset does it but it should be quite similar

TIBDataset has its own internal buffer management, and does not use TBufDataset. IBX's buffer management is intended to be as efficient as possible and adding any extra buffering is just going to slow you down. If there is a problem with IBX's internal buffer management then please let me know.

[incendio]

Database sensitive components always use some kind of buffer mechanism. So you dom't need wo create a parallel structure with an extra TBufdataset.

e.g. the component set SQLDB (delivered with Lazarus) uses TBufDataset to buffer data.

I don't know exactly how TIBDataset does it but it should be quite similar

TIBDataset has its own internal buffer management. This is intended to be as efficient as possible and adding any extra buffering is just going to slow you down. If there is a problem with IBX's internal buffer management then please let me know.

I am back using TIBDataset with cache update enable to store temporary data.

Hope that future IBX will have Virtual Dataset in memory without the need for underlying table, but still have all properties & methods of TIBDataset, except properties & methods related to store data to actual table, such as apply updates, cancel updates, etc.

[rvk]

Hope that future IBX will have Virtual Dataset in memory without the need for underlying table, but still have all properties & methods of TIBDataset, except properties & methods related to store data to actual table, such as apply updates, cancel updates, etc.

That's not up to IBX but up to Firebird or the driver for Firebird.

But if that would be implemented (if it's not already), it would be a memory table on server side, not on client side.

On client side there are other solutions for in-memory tables (which have nothing to do with Firebird).

I'm not sure what the problem is that you are having and why there is a need for such "Virtual Dataset in memory".
If it's the slowness over internet... then you need to look at a middleware solution (which is always a better solution for access over internet).

If that's what you want you need to ask specific questions about that (and state your use-case), not hint at half measures from what you think would be a possible solution

[incendio]

Hope that future IBX will have Virtual Dataset in memory without the need for underlying table, but still have all properties & methods of TIBDataset, except properties & methods related to store data to actual table, such as apply updates, cancel updates, etc.

That's not up to IBX but up to Firebird or the driver for Firebird.

But if that would be implemented (if it's not already), it would be a memory table on server side, not on client side.

On client side there are other solutions for in-memory tables (which have nothing to do with Firebird).

I'm not sure what the problem is that you are having and why there is a need for such "Virtual Dataset in memory".
If it's the slowness over internet... then you need to look at a middleware solution (which is always a better solution for access over internet).

If that's what you want you need to ask specific questions about that (and state your use-case), not hint at half measures from what you think would be a possible solution

Virtual dataset in memory has nothing to do with Database Server.

It is simply just to store temporary data, process this data, then display the results to users.

For example, in actual dataset, there's a column with integer type.

I need to sum this column, to do this, must loop the entire records, but looping the entire records on the dataset will triggers lots of methods, such as before post, on calculated, etc.

To avoid trigger those methods & events, I copied values of that column to temporary table and do same processing, then display the result in a formated value, 1000 will be displayed as 1,000.

[rvk]

It is simply just to store temporary data, process this data, then display the results to users.

So... TBufDataset.

I need to sum this column, to do this, must loop the entire records, but looping the entire records on the dataset will triggers lots of methods, such as before post, on calculated, etc.

YIKES. Are you saying you do a count on the client side just to get a sum for a column?

I (now) understand that you are a beginner in database development, and there is nothing wrong with that.
But if you would have stated that from the beginning we could have steered you in a better direction.

Especially if you are doing a SELECT * FROM TABLE and want to display a count/sum, and be responsive with Firebird over a high latency connection like the internet, that's absolutely NOT the way to go. This is really really really inefficient.

With a slow connection what you want to do is not show all 100 or 1000 (or 10.000) records. You only want to retrieve the records that are visible (in a TDBGrid) on screen. And during scrolling, more records are retrieved. That's done automatically. If you need a count or sum then you do a separate SELECT SUM(AMOUNT), COUNT(*) FROM TABLE so you can show the sum and count separately.

You can still do a SELECT * FROM TABLE (although specifying the fields is more efficient) and the TIBQuery will do the caching automatically and only retrieve the needed records. But if you are doing a while not dataset.EOF or a dataset.Last then ALL records are retrieved immediately, resulting in a MAJOR slowdown, and you don't want that.

To avoid trigger those methods & events, I copied values of that column to temporary table and do same processing, then display the result in a formated value, 1000 will be displayed as 1,000.

Even with that method... ALL the records need to be retrieved. You don't do that in a database program for the reason stated above.

Above method does take some getting use to but it's much better than using a virtual database or doing a loop through ALL the records.
(if you have 1000 or more records, your method would become unbearable slow over the internet, even with a memory table.)

I hope that the explanation above is clear otherwise feel free to ask questions.