In Memory dataset woes

[liewald]

I've just installed a new version  of laz etc

OS opensuse 11.4 64 bit
Laz version 0.9.30.1
FPC 2.4.2

I created some Tbufdataset databases as usual and parsed my data into them only to find that the data did not post to the database it just vanished.  This is the same code as I have used previously without any errors. simply creating the datasets assigning the fields and then using them as before. there are no errors the code compiles and runs perfectly. just the data vanishes. I have replaced the tbutdatasets at the moment with sqlite memory datasets with no other changes in the code and it's working OK but a bit slow for what I'm doing and I'm not sure the indexing is wotrkin as well as it should with and in memory database. So if anyone has any ideas about getting the tbufdatasets up and running help would be greatly appreciated

[felipemdc]

Please create a bug report and attach a test project to it

[liewald]

Willdo

D

[liewald]

waiting for bugtracker account confirmation but did very simple prog to demo problem

<--------------------- Create dataset code

procedure TForm1.Create_A_Dataset;

begin
thedata := Tbufdataset.Create(self);
with thedata.FieldDefs do
 begin
   add('Field1',ftstring,10,true);
   add('Field2',ftstring,10,true);
   add('Field3',ftstring,10,true);
   add('Field4',ftstring,10,true);
   add('Field5',ftstring,10,true);
 end;
 thedata.CreateDataset;
end;       

<------------------------------Populate dataset code

procedure TForm1.PopulateDatasetClick(Sender: TObject);

var I,J,thevalue : integer ;
    thefieldname: string;
begin
thedata.close;
thedata.open;
for i := 1 to 100 do;
begin
thedata.Append;
for j := 1 to 5 do
begin
  thefieldname := 'Field' + inttostr(j);
  thevalue := i + j;
  thedata.FieldByName(thefieldname).asstring := inttostr(thevalue);
end;
thedata.post;
end;
end;



this should put in 100 records but when you inspect the DB it only has 1 record the last one. So the append is failing by overwriting the first record and not appending it as it should

D

[liewald]

Just out of interest I just tried a memdataset and got exactly the same problem

[TheBlackSheep]

I had similar issues - it appears well documented that these datasets don't work as expected.

I managed to install the RXLib in-memory dataset which appears to work well but unfortunately doesn't support the ability to load/save the dataset which is a shame as this (IMHO) would be a suitable alternative.

I temporarily replaced with a sqlite database but its overkill for a simple single table datastructure - has anyone come across any other alternatives?

Chris

[jixian.yang]

There is no problem on TBufDataset:

Code: [Select]

procedure TForm1.PopulateDatasetClick(Sender: TObject);
var
  I, J, thevalue: integer;
  thefieldname: string;
begin
  Create_A_Dataset;
  thedata.Close;
  thedata.Open;
  for i := 1 to 100 do
  begin
    thedata.Append;
    for j := 1 to 5 do
    begin
      thefieldname := 'Field' + IntToStr(j);
      thevalue := i + j;
        thedata.FieldByName(thefieldname).asstring := inttostr(thevalue);
    end;
    thedata.post;
  end;
  DataSource1.DataSet := thedata;
end;

procedure TForm1.Create_A_Dataset;
begin
  thedata := Tbufdataset.Create(self);
  with thedata.FieldDefs do
  begin
    add('Field1', ftstring, 10, True);
    add('Field2', ftstring, 10, True);
    add('Field3', ftstring, 10, True);
    add('Field4', ftstring, 10, True);
    add('Field5', ftstring, 10, True);
  end;
  thedata.CreateDataset;
end;       

[jixian.yang]

"for i := 1 to 100 do;" the ';' is not needed.

[tatamata]

See how I copy a dataset to a bufdataset:

Code: [Select]

procedure TFormMain.PassDataSetToBufDataset(var pDataSet:TDataSet;var pBufDataset:TBufDataset);

var

  vFieldDef:TFieldDef;

  vFieldCount:Integer;

  I,N:Integer;

begin

  vFieldCount:=pDataSet.FieldDefs.Count;

  with pBufDataSet do begin

    try

      {if Active then Refresh;}

      Active:=False;

      //Clear all FieldDefs.

      Fields.Clear;

      FieldDefs.Clear;

      //Disable controls.

      DisableControls;

      //Create FieldDefs.

      for I:=0 to vFieldCount-1 do begin

        try

          vFieldDef:=FieldDefs.AddFieldDef;

          vFieldDef.Name:=pDataSet.FieldDefs[I].Name;

          vFieldDef.DataType:=pDataSet.FieldDefs[I].DataType;

          vFieldDef.Size:=pDataSet.FieldDefs[I].Size;

          vFieldDef.Required:=pDataSet.FieldDefs[I].Required;

        except

          on E:Exception do begin

            ShowMessage('Could not add definition of the field: '+pDataSet.FieldDefs[I].Name

              +'. '+E.Message);

          end;

        end;

      end;

      MaxIndexesCount:=1000;

      CreateDataSet;

      try

        Active:=True;

      except

        on E:Exception do begin

          ShowMessage ('Could not activate BufDataSet. '+E.Message);

        end;

      end;

      //Inser Fields Data.

      pDataSet.First;

      while not pDataSet.EOF do begin

        Append;

        for N:=0 to vFieldCount-1 do begin

          try

            Fields[N].Value:=pDataSet.Fields[N].Value;

          except

          end;

        end;

        Post;

        pDataSet.Next;

      end;

    finally

      //Enable controls.

      EnableControls;

    end;

  end;

end;
I create bufdataset on FormCreate and release on FormDestroy:
  BufDatasetQueryResult:=TBufDataset.Create(nil);
-----
  BufDatasetQueryResult.Free;

[garlar27]

I know how to speed-up SQLite:
Since SQLite send to disk every modification and waits until its done for data safety sake it gets slow just by accessing the disc repeatedly for instance to update or add 10 records you have 10 separated writes.

AFAIK You have 2 ways to improve this:
   1- compile or configure SQLite for caching updates.
   2- And my favorite: build a script like this:

Code: [Select]

BEGIN;
INSERT INTO ....;
INSERT INTO...;
UPDATE ....;
COMMIT;

Doing this you can add 1000 records in no time. The key is using "BEGIN;" and "COMMIT;" otherwise will do it one by one...

Hope this help until the problem is solved.

[TheBlackSheep]

Yep using SQLite is definitely an option (especially with an in-memory database using ":memory:" as the database name).

I found that more then around 5000 updates in a begin/commit block before the performance degrades again - in my case I was reading from a set of proprietary btrieve objects with 100K+ rows which weren't always needed so I dynamically read the tables on the fly as the user chose a particular table and then wrapped the import process into begin/commit blocks limited to 5000 records - it worked really well.

I've tried using the TBufDataset but I just get seg faults on using a memo fieldtype and even when replaced with a ftString I got issues loading and saving.

A sqlite database is still a bit too much for a lot of problems - I just need a simple structure to be able to save and load as required (I've considered using a TIniFile but I need a TStringList as one of the fieldtypes, hence needs to support memos field, and it would make a bit more sense to have something slightly better designed for this purpose but not quite as heavy as SQLite).

Chris

[garlar27]

AFAIR TMemDataSet and maybe TBufDataSet do not support some TFieldType like ftMemo and ftBlob. That's the main reason why I don't use TMemDataSet.

[tatamata]

Some techniques for in-memory datasets:
http://wiki.lazarus.freepascal.org/How_to_write_in-memory_database_applications_in_Lazarus/FPC

[TheBlackSheep]

After a bit of digging I came across this

http://w7site.ru/wiki/index.php/RxFPC

it looks like there's a current maintainer of a RXLib port to Lazarus although unfortunately I don't speak Russian so I don't know how much of this is Linux/OSX compatible (credit to Alex for this and it looks as if this might be the full port of the old RXlib);

I created a quick lazarus package for just the memds unit and it appears to compile and run ok (appears to support most datatypes including memo).

To supplement this there's this code which adds persistence (credit to Alexey here);

http://vowca.chat.ru/Delphi/RxMemDSUtils_eng.html

it needs some minor amendments to compile under Lazarus (just comment out the WriteSignature, ReadSignature and FlushBuffer lines which aren't in the Lazarus TWriter and TReader classes - look like they're old Delphi4 requirements which possibly aren't required anymore - can anyone confirm this?)

I knocked together a quick test creating a dataset with 3 fields (string, integer and memo fields) at design-time, attached a grid via a datasource, pushed 10,000 records into it, saved it, emptied it and then reloaded it and it all appears to work well including the memo field contents.   

It needs a bit more testing (including the other components in the RXlib port) but I'm sure this in-memory dataset component always worked well in the past so there's no reason to assume it's gone awry in the interim. 

If Alex could add Alexey's persistence code into the memds (i.e. add SaveToStream, LoadFromSteam, SaveToFile, LoadFromFile) then it's winner all round. 

Chris

[tatamata]

AFAIR TMemDataSet and maybe TBufDataSet do not support some TFieldType like ftMemo and ftBlob. That's the main reason why I don't use TMemDataSet.

As far as I remember, TMemDataset couldn't filter dataset...this was the reason I started using TBufDataset instead. I don't know whether this changed?