bytea field save\load

Forum related to PostgreSQL

Moderators: gto, cipto_kh, EgonHugeist, olehs

bser
Fresh Boarder
Fresh Boarder
Posts: 5
Joined: 24.12.2010, 08:34

bytea field save\load

Postby 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?
You do not have the required permissions to view the files attached to this post.

mrLion
Senior Boarder
Senior Boarder
Posts: 71
Joined: 20.03.2010, 10:17

Postby mrLion » 24.12.2010, 21:01

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

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

bser
Fresh Boarder
Fresh Boarder
Posts: 5
Joined: 24.12.2010, 08:34

Postby 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
Fresh Boarder
Fresh Boarder
Posts: 5
Joined: 24.12.2010, 08:34

Postby bser » 27.12.2010, 11:53

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

trupka
Expert Boarder
Expert Boarder
Posts: 140
Joined: 26.08.2007, 22:10

Postby 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
Fresh Boarder
Fresh Boarder
Posts: 5
Joined: 24.12.2010, 08:34

Postby 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.
You do not have the required permissions to view the files attached to this post.

bser
Fresh Boarder
Fresh Boarder
Posts: 5
Joined: 24.12.2010, 08:34

Postby 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
Expert Boarder
Expert Boarder
Posts: 140
Joined: 26.08.2007, 22:10

Postby 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 :wink: )
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-general/2010-10/msg00434.php

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby 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
Image

trupka
Expert Boarder
Expert Boarder
Posts: 140
Joined: 26.08.2007, 22:10

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

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby 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
Image


Return to “PostgreSQL”

Who is online

Users browsing this forum: No registered users and 1 guest