bytea field save\load

bser · Post by **bser** » 24.12.2010, 08:48

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?

mrLion · Post by **mrLion** » 24.12.2010, 21:01

bser, дружище, тебе надо сперва грузить файл в поток, а потом загружать параметр из потока.

Сори, не могу сейчас привести код, да он у меня на С++.
Смотри в сторону ReadFromStream / SaveToStream

bser · Post by **bser** » 27.12.2010, 05:52

mrLion wrote:bser, дружище, тебе надо сперва грузить файл в поток, а потом загружать параметр из потока.

Сори, не могу сейчас привести код, да он у меня на С++.
Смотри в сторону ReadFromStream / SaveToStream

I tried:

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()?

bser · Post by **bser** » 27.12.2010, 11:53

Is there any example to save file to bytea field and to load it from into disk?

trupka · Post by **trupka** » 27.12.2010, 23:59

bser,
What Zeos/postgres versions are you using?
I'm using pg9 with Zeos 7 and it works fine to me.

bser · Post by **bser** » 28.12.2010, 05:47

trupka wrote:bser,
What Zeos/postgres versions are you using?
I'm using pg9 with Zeos 7 and it works fine to me.

Zeos 6.6.6 stable
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.

bser · Post by **bser** » 28.12.2010, 07:26

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!

trupka · Post by **trupka** » 03.01.2011, 15:22

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

Post by **mdaems** » 03.01.2011, 22:45

trupka,
Is there a way zeoslb can force the connection to use 'escape' mode during the connection phase? Or would that affect the functionality with the 9.x client dll's?

Mark

trupka · Post by **trupka** » 04.01.2011, 17:06

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??

Post by **mdaems** » 13.01.2011, 21:21

trupka,

Seems like we better leave it like it is for now. However, if somebody feels like writing and testing the Connection properties param, he's welcome and the patch can be accepted if it's OFF by default.

Mark