Save record to BLOB Field

[Sc0li0sis]

Hello,
I have a question:
I have a record

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

1. TRec = record
2. Version : String[5];
3. Date : TDate;
4. jed : string;
5. rr : Float;
6. ....
7. end;
8.  

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]

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

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

1. Rec = record
2.    Version : String[5];
3.    Date : TDate;
4.    jed : string;
5.    rr : Float;
6.    ....
7. end;
8.  
9. ARecord = TStringList;
10.  
11. ///////////////////////////////////////////
12. ARecord := TStringList.Create;
13. with ARecord do begin
14.    Values['Version'] := Rec.Version ;
15.    Values['Date'] := DateToStr(Rec.Date);
16.    Values['jed'] := Rec.jed;
17.    Values['rr'] := FloatToStr(Rec.rr);
18. end;
19.  
20.  
21. // and with database
22.  
23. qr1.sql.text := Format('insert into RecTable (blob_field) values (''%s'');', [ARecord.Text]);
24. qr1.execsql;
25.  
26. ARecord.Free;
27.  

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]

You can use streams, e.g.

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

1. //save
2. procedure TForm1.Button1Click(Sender: TObject);
3. var
4.   r: TRec;
5.   ms: TMemoryStream;
6. begin
7.   r.Version := Edit1.Text;
8.   r.Date := DateTimePicker1.Date;
9.   r.rr := FloatSpinEdit1.Value;
10.   r.jed := Memo1.Lines.Text;
11.   //record to stream
12.   ms := RecToStream(r);
13.   //save to db
14.   SQLQuery1.SQL.Text := ' update mytable set recblob=:rb where id=:id ';
15.   SQLQuery1.ParamByName('id').AsInteger := 1;
16.   SQLQuery1.ParamByName('rb').LoadFromStream(ms, ftBlob);
17.   SQLQuery1.ExecSQL;
18.   ms.Free;
19. end;
20.  
21. //read
22. procedure TForm1.Button2Click(Sender: TObject);
23. var
24.   r: TRec;
25. begin
26.   //read from db
27.   SQLQuery1.SQL.Text := ' select recblob from mytable where id=:id ';
28.   SQLQuery1.ParamByName('id').AsInteger := 1;
29.   SQLQuery1.Open;
30.   //load from stream
31.   r := StreamToRec(SQLQuery1.CreateBlobStream(SQLQuery1.FieldByName('recblob'), bmRead));
32.   SQLQuery1.Close;
33.   //
34.   Edit1.Text := r.Version;
35.   DateTimePicker1.Date := r.Date;
36.   FloatSpinEdit1.Value := r.rr;
37.   Memo1.Lines.Text := r.jed;
38. end;
39.  
40. function TForm1.RecToStream(r: TRec): TMemoryStream;
41. begin
42.   Result := TMemoryStream.Create;
43.   Result.WriteBuffer(r, SizeOf(r));
44.   Result.Position := 0;
45. end;
46.  
47. function TForm1.StreamToRec(ms: TStream): TRec;
48. begin
49.   ms.Position := 0;
50.   if ms.Size >= SizeOf(Result) then
51.     ms.ReadBuffer(Result, SizeOf(Result));
52. end;

[Thaddy]

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]

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]

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][+][-]

1. CREATE TABLE `TheTable` (
2.         `TABLEID` INT NOT NULL AUTO_INCREMENT,
3.         `Version ` VARCHAR(50) NOT NULL DEFAULT '0',
4.         `TheDate ` DATE NOT NULL DEFAULT '0',
5.         `jed ` VARCHAR(50) NOT NULL DEFAULT '0',
6.         `rr ` FLOAT NOT NULL DEFAULT 0,
7.         PRIMARY KEY (`TABLEID`)
8. )
9. COLLATE='utf8mb4_general_ci'
10. ;

[egsuh]

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

[wp]

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]

Hello

I use Blob file to store image or file like PDF 

[Sc0li0sis]

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