Save reports to BLOB filed in Firebird

jajaja

Hello,

I have some code in Delphi 2007 that stores reports in a firebird BLOB binary field.
The same code does work in Delphi 10.4 (no errors, and data is stored), but when I look in a BLOB-editor the data is not correct. A lot of "?????????". And restoring the report is not possible.

The differences are unicode, and the fact the I use Firedac, and not BDE + TSQLQuery

How should I store the data in Delphi 10.4 in such a way the report is readable ?

The code is the following :

procedure TDM_Archief.archive(Filename: string; ID: integer) ;
var
BinFile: TMemoryStream;
Buff: String;
begin

//Create file stream.
BinFile := TMemoryStream.Create;
BinFile.LoadFromFile(Filename);
BinFile.Position := 0; // reset to the beginning of the stream


//Allocate memory for the buffer.
SetLength(Buff, BinFile.Size);

//Read file into buffer.
BinFile.Read(Buff[1], BinFile.Size);

QFaktuurArchiefInsert.ParamByName('ID' ).AsInteger := ID;
QFaktuurArchiefInsert.ParamByName('Bestand' ).AsBlob := buff;

QFaktuurArchiefInsert.ExecSQL;

end;

The insert-query is in essence :

SQL.Strings = INSERT INTO ARCHIEF_FAKTUREN (NUMMER, FAKTUUR) Values ( :ID, :Bestand )

ParamData = <
item
Name = 'ID'
DataType = ftInteger
ParamType = ptInput
end
item
Name = 'Bestand'
DataType = ftBlob
ParamType = ptInput
end>


The BLOB-filed is of subtype 0 (binary)

Any help/hint is very welcome,


kind regards,

Dirk Janssens

nicocizik

Hi Dirk,

One item that stands out in your code is the use of a string to hold the file contents before adding it to the database. Strings in Delphi are Unicode encoded so your data is likely being converted.

I suggest using a byte array (TBytes) rather than a string for the Buff variable. This is the standard way to operate with binary data in modern versions of Delphi.

jajaja

Hi Nico

Thank you for your reply. It makes sence, and will probably do the trick,
but I already found an other solution :

In stead of

"QFaktuurArchiefInsert.ParamByName('Bestand' ).AsBlob := buff;"

I use:

QFaktuurArchiefInsert.ParamByName('Bestand' ).DataType := ftBlob ;
QFaktuurArchiefInsert.ParamByName('Bestand' ).AsStream := TFileStream.Create(Bestandsnaam,fmOpenRead);

Now the data is stored as usual...( and it looks as if this is also faster )

kind regards,

Dirk Janssens.

jajaja

Just one more question:
I now have some useless data in my table. Is it somehow possible to convert the data that is stored as Unicode back to Ansi-string ?

nicocizik

Hi Dirk,

Yes, copying the stream data directly will also avoid any encoding issues and likely be faster than using an interim data structure.

I'm a bit unclear about your second question. What exactly are you storing to your database? Are these report archive files, templates, or something else? I am unclear what you mean by "useless data" in your table.