bytea field save\load
Moderators: gto, cipto_kh, EgonHugeist, olehs
bytea field save\load
I have a table:
CREATE TABLE blob_tmp
(
id serial NOT NULL,
b bytea
CONSTRAINT blob_tmp_pk PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
Then I save txt-file in it (this works fine; see source-file attachment):
ZQuery1.Close;
ZQuery1.SQL.Clear;
ZQuery1.SQL.Add('insert into blob_tmp (id, b) values (nextval(''blob_tmp_id_seq''), :bl)');
ZQuery1.Params.ParamByName('bl').LoadFromFile(fnm,ftBlob);
ZQuery1.ExecSQL;
ZQuery1.Close;
ZQuery1.Params.Clear;
And when I trying to load it back to file:
ZQuery1.Close;
ZQuery1.SQL.Clear;
ZQuery1.SQL.Add('select b from blob_tmp where id=2');
ZQuery1.Open;
ZQuery1.First;
if ZQuery1.RecordCount>0
then TBlobField(ZQuery1.Fields[0]).SaveToFile(fnm);
ZQuery1.Close;
I get that file (not my source txt-file):
x312e350d0a573a5c446f63756d656e747320616e642053657474696e67735c417074656b615f506f73745f355cd0e0e1eef7e8e920f1f2eeeb
Where I am wrong?
CREATE TABLE blob_tmp
(
id serial NOT NULL,
b bytea
CONSTRAINT blob_tmp_pk PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
Then I save txt-file in it (this works fine; see source-file attachment):
ZQuery1.Close;
ZQuery1.SQL.Clear;
ZQuery1.SQL.Add('insert into blob_tmp (id, b) values (nextval(''blob_tmp_id_seq''), :bl)');
ZQuery1.Params.ParamByName('bl').LoadFromFile(fnm,ftBlob);
ZQuery1.ExecSQL;
ZQuery1.Close;
ZQuery1.Params.Clear;
And when I trying to load it back to file:
ZQuery1.Close;
ZQuery1.SQL.Clear;
ZQuery1.SQL.Add('select b from blob_tmp where id=2');
ZQuery1.Open;
ZQuery1.First;
if ZQuery1.RecordCount>0
then TBlobField(ZQuery1.Fields[0]).SaveToFile(fnm);
ZQuery1.Close;
I get that file (not my source txt-file):
x312e350d0a573a5c446f63756d656e747320616e642053657474696e67735c417074656b615f506f73745f355cd0e0e1eef7e8e920f1f2eeeb
Where I am wrong?
You do not have the required permissions to view the files attached to this post.
I tried:mrLion wrote:bser, дружище, тебе надо сперва грузить файл в поток, а потом загружать параметр из потока.
Сори, не могу сейчас привести код, да он у меня на С++.
Смотри в сторону ReadFromStream / SaveToStream
fl_stream:=TFileStream.Create(fnm, fmCreate);
try
TBlobField(ZQuery1.Fields[0]).SaveToStream(fl_stream);
finally
fl_stream.Free;
end;
And even:
stream:=ZQuery1.CreateBlobStream(ZQuery1.Fields[0],bmRead);
fl_stream:=TFileStream.Create(fnm,fmCreate);
try
fl_stream.CopyFrom(stream,stream.Size);
finally
fl_stream.Free;
end;
stream.Free;
With no luck (same hex-encoded file).
Why it does not invoke DecodeBytea()?
Zeos 6.6.6 stabletrupka wrote:bser,
What Zeos/postgres versions are you using?
I'm using pg9 with Zeos 7 and it works fine to me.
pg 9.0.2
Here is an examle application (see attachment) I'm using.
pg_file_in.txt - source file
pg_file_myout.txt - file from pg bytea (hex encoded and without leading \)
pg_db_test.sql - create sql for my table in db test (db and table in UTF8 encoding)
First, button 'insert bytea' must insert pg_file_in.txt into bytea-field.
Then I want to save pg_file_in.txt into disk (from bytea field) after clicking 'load bytea' button.
But I recieve hex encoded text file (see my pg_file_myout.txt).
Can you test it in your 7/0/0 version and correct my code if it's buggy.
You do not have the required permissions to view the files attached to this post.
After a few days of codding I found what I need to do to save bytea-field data to disk correctly:
- delphi 7
- using zeos version 7.0.0 alfa
- pg version 9/0/2
- protocol:='postgresql-8';
- ADD files libpq.dll, libintl-8.dll, libiconv-2.dll to project exe-file (from PG bin directory)
If I delete 3 PG dll files, then I get hex encoding file.
So the solution is to using this dll-files!
- delphi 7
- using zeos version 7.0.0 alfa
- pg version 9/0/2
- protocol:='postgresql-8';
- ADD files libpq.dll, libintl-8.dll, libiconv-2.dll to project exe-file (from PG bin directory)
If I delete 3 PG dll files, then I get hex encoding file.
So the solution is to using this dll-files!
I did some investigations and it seems that problem lies in pg 9 client changes so it's not zeos specific (actually, it's more feature then bug )
Here is excerpt from pg 9 change log:
---
bytea output now appears in hex format by default (Peter Eisentraut)
--
I just want to warn all of you who intend to upgrade existing applications to pg 9 - change client library (libpq) to 9.x or set bytea_output param to 'escape'. Otherwise you may end up with corrupted bytea fields. More about that here http://archives.postgresql.org/pgsql-ge ... g00434.php
Here is excerpt from pg 9 change log:
---
bytea output now appears in hex format by default (Peter Eisentraut)
--
I just want to warn all of you who intend to upgrade existing applications to pg 9 - change client library (libpq) to 9.x or set bytea_output param to 'escape'. Otherwise you may end up with corrupted bytea fields. More about that here http://archives.postgresql.org/pgsql-ge ... g00434.php
Hi Mark,
"forcing" zeoslib to use bytea_output format in escape mode is possible but
won't cover all possibilities - format can be changed in many places - server configuration, database and role configuration, session level and even per transaction or inside stored procedure (function).
We also must consider that:
- hex format is faster and it's new default for postgresql server.
- it seems that particular problem affects only applications which use old versions of libpq (<9.0) with server >= 9.0. Using old client libraries with new servers is always bad idea.
- problem can occur during migration /upgrading to 9.x but that is DBA /developer responsibility. All we can do is to warn.
- what if developer, for some reason, wants hex output?
What we can do about it? IMO:
- put additional note somewhere and raise community awareness of this problem.
- put libpq v9 inside zeoslib distribution. It' seems that it's safe to use v9 libpq with 8.x server.
- maybe introduce new ZConnection.properties param (e.g. bytea_output=escape). This one first came to my mind but if problem can be solved by new dll is that really necessary??
"forcing" zeoslib to use bytea_output format in escape mode is possible but
won't cover all possibilities - format can be changed in many places - server configuration, database and role configuration, session level and even per transaction or inside stored procedure (function).
We also must consider that:
- hex format is faster and it's new default for postgresql server.
- it seems that particular problem affects only applications which use old versions of libpq (<9.0) with server >= 9.0. Using old client libraries with new servers is always bad idea.
- problem can occur during migration /upgrading to 9.x but that is DBA /developer responsibility. All we can do is to warn.
- what if developer, for some reason, wants hex output?
What we can do about it? IMO:
- put additional note somewhere and raise community awareness of this problem.
- put libpq v9 inside zeoslib distribution. It' seems that it's safe to use v9 libpq with 8.x server.
- maybe introduce new ZConnection.properties param (e.g. bytea_output=escape). This one first came to my mind but if problem can be solved by new dll is that really necessary??