Saving tree to Data Base

[limelect 48]

I have done this many times. This time it does not work

I have 2 components FDConnection1 and FilesFDTable (FDTable) >>> SQLite

first, I do this >>> FDConnection1 active.

procedure TForm1.FDConnection1AfterConnect(Sender: TObject);
begin
  FDConnection1.ExecSQL('CREATE TABLE IF NOT EXISTS Files (SectionsId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,Files BLOB NULL)');
  FilesFDTable.TableName:='Files';
  FilesFDTable.Active:=True;
end;
 

I put here all my tries too. Non worked I got NULL no save

 

procedure TForm1.SaveTreeToBlob(Tree: TTreeView);
var
//  MS: TMemoryStream;
  BlobField:TBlobField;
 Stream : TStream ;

begin
  //MS := TMemoryStream.Create;
  BlobField := FilesFDTable.FieldByName('Files') as TBlobField;
  try
//    Tree.SaveToStream(MS);
 //   MS.Position := 0;
    FilesFDTable.Insert;
   Stream := FilesFDTable.CreateBlobStream(BlobField, bmWrite);
   Tree.SaveToStream(Stream);
//   Stream.Write(ms,ms.Size);
   FilesFDTable.Post;
   FilesFDTable.Refresh;
   FilesFDTable.Close;// Active:=False;
   FilesFDTable.Active:=True;
  finally
   // MS.Free;
 //  Stream.Free;
  end;
end;
 

No errors just output NULL or my DATABASE READER DOES NOT SEE THE BLOB?Thanks

Edited August 13, 2021 by limelect

[Bill Meyer 337]

Some searching turned up this:
http://www.devsuperpage.com/Articles/views/Delphi/Art_1-1078.asp
I've not needed to do this, and the code there is more involved, but I would try that as a starting point, and then perhaps you can find your way to a simpler implementation.

[limelect 48]

@Bill Meyer thanks i will try

[limelect 48]

@Bill Meyer sorry save does not work

  procedure SaveTreeviewToStream(tv: TTreeview; S: TStream);
3   var
4     writer: TWriter;
5     node: TTreeNode;
6   begin
7     Assert(Assigned(tv));
8     Assert(Assigned(S));
9     writer := TWriter.Create(S, );
10    try
11      node := tv.Items[];
12      writer.WriteListBegin;
13      while node <> nil do
14      begin
15        writer.WriteInteger(node.level);
16        writer.WriteString(node.Text);
17        writer.WriteInteger(node.Imageindex);
18        writer.WriteInteger(Integer(node.data));
19        node := node.GetNext;
20      end;
21      writer.WriteListEnd;
22      writer.FlushBuffer;
23    finally
24      writer.Free;
25    end;
26  end;
27  

  BlobField := FilesFDTable.FieldByName('Files') as TBlobField;
     FilesFDTable.Insert;
   Stream := FilesFDTable.CreateBlobStream(BlobField, bmWrite);
   SaveTreeviewToStream(tree,Stream);
    FilesFDTable.Post;
   FilesFDTable.Refresh;

As for the reader it has a bug

I am stack !!! any help ?

Edited August 14, 2021 by limelect

[limelect 48]

I wander no one is using FDTable with blobs?

I used blob with other database components it works

with no problem

Is it me or Delphi vcl 10.2.3?

[Martin Wienold 35]

Using BLOBs implies that you are storing binary data in a database table.

 

This is data that can't effectifly be queried by the database using SQL alone, you always have to load it into an application.

I'm not saying this is bad, I just want to make sure that you know what the implications are.

 

Another potential problem you may face is having different versions of this data in you database.

Right now, you are saving Level, Text, ImageIndex and Data. If this list is expanded or the meaning of a value (example ImageIndex may shift) changes you have to have an application loading, converting and storing it.

[limelect 48]

@Bill Meyer  i used your link to develop my own as strings instead

 

 procedure SaveTreeviewToString(tv: TTreeview; Sl: TStringlist);
 var
   node: TTreeNode;
   s:string;
 begin
   Assert(Assigned(tv));
   try
     node := tv.Items[0];
     while node <> nil do
     if node.CheckState=csChecked then
    begin
     s:='';
      s:=s+'#'+inttostr(node.level);
      s:=s+'#'+(node.Text);
     Sl.Add(s);
       node := node.GetNext;
     end;
   finally
   end;
 end;

procedure LoadTreeviewFromFile(tv: TTreeview; Sl: TStringlist);
 var
   node: TTreeNode;
   level,i,x,x2: Integer;
   s,s1,s2:string;
 begin
   try
     tv.Items.Clear;
     try
       node := nil;
       for i:=0 to sl.Count-1 do
       begin
       s:=Sl;
       x:=PosEx('#',s,1);
       x2:=PosEx('#',s,x+1);
       s1:=copy(s,x+1,x2-(x+1));
       s2:=copy(s,x2+1,length(s));
         level := strtoint(s1);
         if node = nil then
           {create root node, ignore its level}
           node := tv.Items.Add(nil, '')
         else
         begin
           if level = node.level then
             node := tv.Items.Add(node, '')
           else if level > node.level then
             node := tv.Items.AddChild(node, '')
           else
           begin
             while Assigned(node) and (level < node.level) do
               node := node.Parent;
             node := tv.Items.Add(node, '');
           end;
         end;
         node.Text := s2;
       end;
     finally
     end;
   finally
   end;
 end;

 

[limelect 48]

 

19 minutes ago, Martin Wienold said:

Using BLOBs implies that you are storing binary data in a database table.

 

This is data that can't effectifly be queried by the database using SQL alone, you always have to load it into an application.

I'm not saying this is bad, I just want to make sure that you know what the implications are.

 

Another potential problem you may face is having different versions of this data in you database.

Right now, you are saving Level, Text, ImageIndex and Data. If this list is expanded or the meaning of a value (example ImageIndex may shift) changes you have to have an application loading, converting and storing it.

I did not understand blob as binary cant i put any format as strigns or else? strings in stream?

Edited August 15, 2021 by limelect

[Guest]

In Firebird (and i am sure other RDBMSs) you have a BLOB subtype.

If this subtype is set to text, you can do where clauses on the BLOB column.

But the use of querying the blob content re the OPs needs sounds useless, so.

12 minutes ago, limelect said:

can't effectifly be queried

Emphasis on "effectively", ok!

[Martin Wienold 35]

3 minutes ago, Dany Marmur said:

Emphasis on "effectively", ok!

I'm no native speaker and did not have my coffee yet, have mercy!

[Martin Wienold 35]

20 minutes ago, limelect said:

 

I did not understand blob as binary cant i put any format as strigns or else? strings in stream?

BLOB is short hand for Binary large object

[Guest]

@Martin Wienold According to the current programming on the radio (sunday service) everyone, e v e r y o n e, deserves mercy so Mercy you shall have! In abundance!

[limelect 48]

Thanks every one 30+ years of Delphi did not teach me everything

[baeckerg 6]

I just played around and if you really only need to load a certain status into the database which you need to reload you could do something like this:

procedure TForm2.btn1Click(Sender: TObject);
var
  I: Integer;
  node: TTreenode;
  ms: TMemoryStream;
begin
  node := tv1.Items.Add(nil, 'ROOT Beer') ;
for I := 0 to 10 do
  begin
    tv1.items.AddChild(node, 'Beer ' + i.toString);
  end;
ms := TMemoryStream.Create;
tv1.SaveToStream(ms);
qry_1.SQL.Text := 'Insert into files (files) values (?)';
qry_1.Params[0].LoadFromStream(ms, ftBlob);
qry_1.ExecSQL();
ms.Clear;
qry_1.SQL.Text := 'Select f.files from files f limit 1';
qry_1.Open();
ms.Position := 0;
TBlobField(qry_1.FieldByName('files')).SaveToStream(ms);
ms.Position := 0;
tv2.LoadFromStream(ms);
ms.Free;
end;

just a quick and dirty hack with two treeviews (tv1, tv2) and Firedac on SQlite