bytea get data from postgre

Greetings!
Am stuck on a bit tricky problem.
I am using ado components to access postgre database.
Same old problem everyone is experiencing, problems to store files into postgre database and retrieve them.
I have managed to store file into table in bytea field.
but main problem, i dont know how to retrieve files, since its not a really a blob.

i have managed to get down data 255 byte long, but not greater, cant figure out whats the problem.

any ideas?
procedure TForm1.Button1Click(Sender: TObject);
var
  ByteCount: integer;
  EncodedStr: WideString;
  MemStream: TMemoryStream;
  //// DecodeStream: TBase64DecodingStream;
  //Signal: array of byte;

begin
q.sql.Clear;
q.SQL.Add('select by from aaa');
q.Open;
q.last;

EncodedStr := q.FieldByName('by').asstring;
MemStream  := TMemoryStream.Create;
ByteCount  := length(q.FieldByName('by').AsString);

showmessage(inttostr(ByteCount)); // returns 255  only, why???

 MemStream.Write((EncodedStr[1]), ByteCount);
 MemStream.Seek(0, soFromBeginning);
 MemStream.SaveToFile('01.jpg');
end;

Open in new window

LVL 3
KarlisBAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Geert GOracle dbaCommented:
by is a reserverd word

>> order by

select [by] from aaa
0
KarlisBAuthor Commented:
well, my mistake. but postgre is quite 'smart' and query works without problem.

my problem is how to get result into stream.

field[].asstring is wrong data type,
how can i get that bytea result into a stream???
0
Geert GOracle dbaCommented:
instead of deleting a question you could accept your own comment

this way you have it for your own reference
and for others too
0
KarlisBAuthor Commented:
how to insert and retrieve file from bytea field
solution:
...
"co" is tadocommand, where by parameter have created parameter "opa" with fieldtype ftblob

to insert:

procedure TForm1.Button2Click(Sender: TObject);
var ByteCount: integer;
  EncodedStr: WideString;
  MemStream: TMemoryStream;
begin
MemStream  := TMemoryStream.Create;
MemStream.LoadFromFile('onigiri22.jpg');
co.Parameters.ParamByName('opa').LoadFromStream(MemStream,ftblob);
co.CommandText:='insert into aaa(byteafield) values(:opa);';
co.Execute;
end;

to retrieve as a stream and store to file:

procedure TForm1.Button3Click(Sender: TObject);
var ByteCount: integer;
    EncodedStr: WideString;
   MemStream: TMemoryStream;
begin
q.sql.Clear;
q.SQL.Add('select bytefield from aaa where id=16');
q.Open;
MemStream  := TMemoryStream.Create;
TBlobField(q.FieldByName('bytefield ')).SaveToStream(MemStream);
MemStream.SaveToFile('01.jpg');
end;

and the most important thing to make all this working is:
JUST ADD "ByteaAsLongVarBinary=1" to connection string

took me a some days to stumble upon this easy solution.
Hope this will help for other desperate people aswell :)

cheers!   "ByteaAsLongVarBinary=1"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.