1

Whats wrong with my code? I try to save and then load again an object to a blob field in a database, but get nothing back.

Records are saved, but I can't say if the data was written correctly because I cant read the data back.

Here is the object type:

TMyObject = class
  Name: string;
end;

And here I try to save:

procedure TForm1.btnSaveObjectClick(Sender: TObject);
var
  myObject: TmyObject;
  aMemoryStream: TMemoryStream;
begin
  myObject:= TMyObject.Create;
  myObject.Name:=edtName.Text;
  aMemoryStream:= TMemoryStream.Create;
  aMemoryStream.Write(myObject, myObject.InstanceSize);
  aMemoryStream.Position:=0;

  with TSQLQuery.Create(nil) do
  begin
    DataBase:=Conn;
    SQL.Text:='INSERT INTO testtable (data) VALUES (:data)';
    try
      ParamByName('data').LoadFromStream(aMemoryStream, ftBlob);
      ExecSQL;
      TX.CommitRetaining;
    finally
      aMemoryStream.Free;
      myObject.Free;
      Free;
    end;
  end;
end;

Trying to read the data back up again:

procedure TForm1.btnLoadObjectClick(Sender: TObject);
var
  myObject: TMyObject;
  BlobStream : TStream;
begin
  with TSQLQuery.Create(nil) do
  begin
    DataBase:=Conn;
    SQL.Text:='SELECT data FROM testtable';
    myObject:= TmyObject.Create;
    try
      Open;
      Last;
      BlobStream:= CreateBlobStream(FieldByName('data'), bmread);
      BlobStream.Position:=0;
      BlobStream.Read(myObject, BlobStream.Size);
      ShowMessage('Stored Name: ' +myObject.Name);
    finally
      myObject.Free;
      Free;
    end;
  end;
end;

Also, should BlobStream be free'd?

NGLN
  • 43,011
  • 8
  • 105
  • 200
Rimfire
  • 378
  • 1
  • 3
  • 12
  • 2
    You can't save an object like that, because an object reference is simply a pointer. Besides, the example you've posted is a simple string, and you can create a `char` or `varchar` column in the DB to save that information. – Ken White Mar 27 '15 at 03:09
  • @KenWhite Would you mind sharing the correct syntax? I actually want to know how to save and load an object to a blobfield. The simple type is chosen not to complicate things. The string length is variable and makes it more difficult, and that also on purpose. :-) – Rimfire Mar 27 '15 at 03:59
  • 1
    [Here's an example](http://stackoverflow.com/a/11775398/757830) of loading/saving blob fields. For objects you need something completely different. – NGLN Mar 27 '15 at 05:53
  • 2
    Your task consists from two steps, object de/serialization and saving/loading to BLOB fields. – TLama Mar 27 '15 at 09:21
  • I've already said (and so has @TLama) that you can't directly store objects to a BLOB field, so there is no *correct syntax*. Search here for `[delphi] serialization`. – Ken White Mar 27 '15 at 13:03
  • @NGLN Good link, made me realize my class could also have SaveToStream and LoadFromStream procedures. – Rimfire Mar 28 '15 at 01:02

2 Answers2

4

The correct way for storing your objects into files, streams or blob fields is to first extend your object with additional methods for loading and saving data from your objects fields (objects internal variables) into single memory block.

You do this by saving one field after another.

If your objects are dynamically sized (containing dynamic arrays or strings) don't forget to store the size of these separately so you will know how much data belongs to them when loading your objects later on.

Also if your objects contain some other objects you also need them to have similar methods for storing and loading their data.

The implementation depends heavily on your object's class design. Here a code example for a string field:

type
  TMyObject = class
  public
    Name: string;
    procedure SaveToStream(AStream: TStream);
    procedure LoadFromStream(AStream: TStream);
  end;

procedure TMyObject.SaveToStream(AStream: TStream);
var
  Len: Integer;
begin
  Len := Length(Name);
  AStream.Write(Len, SizeOf(Len));
  AStream.Write(PChar(Name)^, Len);
end;

procedure TMyObject.LoadFromStream(AStream: TStream);
var 
  Len: Integer;
begin
  AStream.Read(Len, SizeOf(Len));
  SetString(Name, PChar(nil), Len);
  AStream.Read(PChar(Name)^, Len);
end;

With this, it is possible to use the stream that CreateBlobStream returns and just save myObject to the blobfield:

BlobField := FieldByName('data') as TBlobField;
Stream := CreateBlobStream(BlobField, bmWrite);
myObject.SaveToStream(Stream);

..or load it from the stream:

Stream:= CreateBlobStream(FieldByName('data'), bmread);
myObject.LoadFromStream(Stream);
NGLN
  • 43,011
  • 8
  • 105
  • 200
SilverWarior
  • 7,372
  • 2
  • 16
  • 22
  • I followed your instructions and now everything works perfectly. TMyObject has now SaveToStream and LoadFromStream that I use together with the stream I get from CreateBlobStream. Any comment about how I save the Name field? Maybe more modern syntax exist? The code is in my question. – Rimfire Mar 28 '15 at 01:12
0

Save file in a image type field in SQL SERVER/Delphi:

TBlobField(QCMD.FieldByName('ARQUIVO')).LoadFromFile(lFile);

Osmanito Torres
  • 1
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community May 08 '23 at 19:40