[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

bytea get data from postgre

Posted on 2010-04-05
4
Medium Priority
?
2,257 Views
Last Modified: 2013-11-23
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

0
Comment
Question by:KarlisB
  • 2
  • 2
4 Comments
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 29886641
by is a reserverd word

>> order by

select [by] from aaa
0
 
LVL 3

Author Comment

by:KarlisB
ID: 29887392
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
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 29896236
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
 
LVL 3

Accepted Solution

by:
KarlisB earned 0 total points
ID: 29896255
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
The video provides a quick and easy steps to migrate MBOX file to well known Outlook PST and Office 365. Besides this, it also supports and migrates more than 20 email clients of MBOX which include AppleMail, Opera, Thunderbird and SeaMonkey effortl…
Suggested Courses

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question