Save record to BLOB Field
lazarus / 16-Databases

----------------------------------------------------------------------
Sc0li0sis · 2025-11-24 19:53

Hello,
I have a question:
I have a record

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i TRec =
recordVersion : String[5];Date : TDate;jed : string;rr :
Float;....end; 
I have an SQL database (mySQL) with a Computers table and a data field
(BLOB).

How do I save a record to a BLOB field? (And then read it?)

Best regards,
Sc0li0sis

----------------------------------------------------------------------
egsuh · 2025-11-25 05:17

I recommend to use TStringList to read / write to DB Blob Field. An
example of writing to DB :

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Rec =
record   Version : String[5];    Date : TDate;   jed : string;   rr :
Float;   ....end; ARecord =
TStringList; ///////////////////////////////////////////ARecord :=
TStringList.Create; with ARecord do begin    Values['Version'] :=
Rec.Version ;   Values['Date'] := DateToStr(Rec.Date);  
 Values['jed'] := Rec.jed;   Values['rr'] := FloatToStr(Rec.rr);end;
  // and with database qr1.sql.text := Format('insert into RecTable
(blob_field) values (''%s'');', [ARecord.Text]); qr1.execsql;
 ARecord.Free; 
And you can read back from db field to TStringList (and back to
record).

There may be other ways that count the size of record, but this is the
simpleist, I think.

----------------------------------------------------------------------
paweld · 2025-11-25 05:36

You can use streams, e.g.Code: Pascal  [Select][+][-]window.onload =
function(){var x1 = document.getElementById("main_content_section");
if (x1) { var x = document.getElementsByClassName("geshi");for (var i
= 0; i //saveprocedure TForm1.Button1Click(Sender: TObject);var  r:
TRec;  ms: TMemoryStream;begin  r.Version := Edit1.Text;  r.Date :=
DateTimePicker1.Date;  r.rr := FloatSpinEdit1.Value;  r.jed :=
Memo1.Lines.Text;  //record to stream  ms := RecToStream(r);  //save
to db  SQLQuery1.SQL.Text := ' update mytable set recblob=:rb where
id=:id ';  SQLQuery1.ParamByName('id').AsInteger := 1; 
SQLQuery1.ParamByName('rb').LoadFromStream(ms, ftBlob); 
SQLQuery1.ExecSQL;  ms.Free;end; //readprocedure
TForm1.Button2Click(Sender: TObject);var  r: TRec;begin  //read from
db  SQLQuery1.SQL.Text := ' select recblob from mytable where id=:id
';  SQLQuery1.ParamByName('id').AsInteger := 1;  SQLQuery1.Open; 
//load from stream  r := StreamToRec(SQLQuery1.CreateBlobStream(SQLQue
ry1.FieldByName('recblob'), bmRead));  SQLQuery1.Close;  // 
Edit1.Text := r.Version;  DateTimePicker1.Date := r.Date; 
FloatSpinEdit1.Value := r.rr;  Memo1.Lines.Text := r.jed;end; function
TForm1.RecToStream(r: TRec): TMemoryStream;begin  Result :=
TMemoryStream.Create;  Result.WriteBuffer(r, SizeOf(r)); 
Result.Position := 0;end; function TForm1.StreamToRec(ms: TStream):
TRec;begin  ms.Position := 0;  if ms.Size >= SizeOf(Result) then   
ms.ReadBuffer(Result, SizeOf(Result));end;

----------------------------------------------------------------------
Thaddy · 2025-11-25 06:28

The record contains one string that is not shortstring. That will give
you a problem when using streams. You have to handle those separately.
Also, use a packed record for storage.

----------------------------------------------------------------------
Zvoni · 2025-11-25 07:56

Ughhh....
if you already have a Record-Type with no Byte-Data as a Member, why
in blazes do you want to save it as a blob?
What do you think you are gaining from that?

Usually you would have separate columns for each Record-Field.

If you still want to go with a "One Field for that Type", i'd rather
use JSON and be done with it

----------------------------------------------------------------------
CharlyTango · 2025-11-25 08:18

After more than three decades of front-end development for relational
databases, I still consider storing combined data in a table field to
be a serious design flaw.

Even though databases can now handle XML and JSON internally, I still
find it annoying and poor design.
It may make sense in very special cases, but I don't know of any so
far, which doesn't mean that there aren't any.

To simply store a record, just use a table like this.

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE `TheTable` (        `TABLEID` INT NOT NULL AUTO_INCREMENT,     
 `Version ` VARCHAR(50) NOT NULL DEFAULT '0',        `TheDate ` DATE
NOT NULL DEFAULT '0',        `jed ` VARCHAR(50) NOT NULL DEFAULT '0', 
      `rr ` FLOAT NOT NULL DEFAULT 0,        PRIMARY KEY
(`TABLEID`))COLLATE='utf8mb4_general_ci';

----------------------------------------------------------------------
egsuh · 2025-11-25 12:09

The easier way would be to use No-SQL Database, like MongoDB, instead
of MySQL

----------------------------------------------------------------------
wp · 2025-11-25 16:56

I absolutely agree with CharlyTango: Don't abuse a TBlobField, use
separate fields instead. This way you can take full advantage of
database features (searching, filtering, sorting by TRec elements), if
the record elements are combined in a blob you can't.

----------------------------------------------------------------------
trom · 2025-11-25 17:06

Hello

I use Blob file to store image or file like PDF

----------------------------------------------------------------------
Sc0li0sis · 2025-12-07 18:31

Thanks a lot,

pawel, it worked.!!!

The record is very large (about 20kB), so I had to use BLOB. This is
additional data that I will rarely use.
Thank you again for your answers.

Sc0li0sis